GroupID 9.0 & Above
We would like to extend the feature of the audit log to our PowerBI infrastructure as a quick visual representation of changes made to the AD objects. PowerBI is capable of pulling in the CSV file however we would like a method to directly query the GroupID history to retrieve the same content shown in the history/audit log or expand the existing retention export options to include history data to PowerBI.
In GroupID v9.0 and above, history is being stored in two repositories (SQL database & Elasticsearch). In SQL database, this information is stored in a long string in which the information is stored in a single cell that is 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 the top of the Rest API so PowerBI is able to interact with Elasticsearch. Below are the steps to fetch and view Elasticseach data into PowerBI.
- Open PowerBI console, click on Get Data button and hit Web option.
- Provide URL for history index of Elasticsearch as per your environment and click OK.
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 GroupID v10 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 Elasticsearch UserName and password in Basic settings and select the level on which this authentication would be applied. Click connect.
- Now expand the number of hits Record as shown in the image below.
- Click on List to expand the history records.
- Right-click on List and hit To Table. In the next dialogue box, simply click OK button.
- Now expand history data into columns.
- You'll be able to see history events abstract info, in order to view history items, expand further columns as shown in the image below.
- If you want to see complete history details. For example, each member's information that is added to a group. Follow the instruction in the images below.
- Final result should be as shown in following image.