Views:

Applies To: 

GroupID 9 and 10 - History and Elastisearch

Business Scenario:

We would like to extend the GroupID audit log feature to our Power BI infrastructure as a quick visual representation of changes made to AD objects.

Is there a method to directly query the GroupID history to retrieve the content shown in the history/audit log or expand the existing retention export options to display history data in Power BI?

Explanation:

In GroupID 9.0 and 10, history is stored in two repositories: SQL database and Elasticsearch repository. In the SQL database, this information may be hard to read and understand.

However, in Elasticsearch, this information is stored in a structured form that can be viewed in different formats (JSON, XML, HTML). Also, Elasticsearch is built on top of the Rest API, so Power BI is able to interact with Elasticsearch.

Below are the steps to fetch and view Elasticseach data in Power BI.

Steps:

  1. Open the Power BI console, click the Get Data button, and hit the Web option.


     
  2. Provide the URL for the history index of Elasticsearch as per your environment and click OK.
    The URL format is as follows:
    http://[GroupID Machine Name]:9200/[History Index]/_search/?size=10000&pretty=true

    In my case, I provided the following URL:
    http://GID10:9200/corp.esharp.online_history_1/_search/?size=10000&pretty=true

    URL Details:
    GID10 is the hostname of the GroupID 10 machine.

    corp.esharp.online_history_1 is the index name that you can get from the Elasticsearch indices URL: http://[GroupID Machine Name]:9200/_cat/indices


     
  3. Now provide the Elasticsearch user name and password in Basic settings and select the level on which this authentication would be applied. Then click Connect.


     
  4. Now expand Hits Record as shown below.


     
  5. Click List to expand the history records.


     
  6. Right-click on List and hit To Table. On the next dialog box, simply click OK.




     
  7. Now expand history data into columns.


     
  8. You'll be able to see abstract info for history events. In order to view history items, further expand columns, as shown below.


     
  9. If you want to see complete history details (for example, the information about each member that is added to a group), follow the steps in the images below.




     
  10. The final result should be as shown below.

Keywords: GroupID, Reporting, PowerBI, History, History Data, Audit
Comments (0)