GroupID 9.0 and above
Area of Impact:
We would like to extend the GroupID audit log feature to our Power BI infrastructure as a quick visual representation of changes made to the 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 include history data to Power BI?
In GroupID 9.0 and above, 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.
- Open the Power BI console, click the Get Data button, and hit the Web option.
- 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:
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
- 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.
- Now expand Hits Record as shown below.
- Click List to expand the history records.
- Right-click on List and hit To Table. On the next dialog box, simply click OK.
- Now expand history data into columns.
- You'll be able to see abstract info for history events. In order to view history items, further expand columns, as shown below.
- 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.
- The final result should be as shown below.