MIM Hybrid Reporting with PowerBI

With the release with MIM 2016, it includes a new feature called hybrid reporting which collects identity management activities across different MIM Service systems and provides a unified report in Azure portal. Currently there are only three reports available in Azure Active Directory (Self-Service Password registration/reset and self-service groups activity). In additional, you can also export the data to your security information and event management(SIEM) system for your own custom views. In this blog, I will leverage Microsoft PowerBI platform to analyse those requests and generate some essential report.

Firstly, we need download and install Microsoft Identity Manager Hybrid Reporting Agent  in all MIM Service Servers. Then, the activity data of Identity Manager in JSON structure is sent to the Windows Event Log, in a well-defined path: Application and Services Logs, Identity Manager Request Log. Afterwards, we can start exporting those data to your SIEM.

To simply the data collection, I run following PowerShell command to export those data to a CSV file and import the data into PowerBI desktop by selecting "Get Data"-->"text/CSV"

Get-WinEvent -LogName "Identity Manager Request Log" | foreach {"$($_.id)|$($_.message)"|Out-File MIMRequestNew.csv -Append

During the import of CSV file, PowerBI allows us to customize the delimiter and load them into separate columns, click "Load" to confirm .

Next, we are going to leverage the "The Query Editor" to transform these MIM requests to a table for further reporting. As it is known that the MIM requests are written in JSON format, we shall choose the message column and right click "Transform"-->"JSON".

Once completion, these MIM requests are parsed into the "records" which allows us to navigate the request details.

Then we click the button at the right corner to expand these properties/attributes of the records. please note the attributes of the records vary from different types (CRUD) of MIM request which you shall compose the query editor accordingly.

Finally, all weneed is to publish the dataset into the PowerBI and build the report from there. Below is some basic reports via "Quick insights", enjoy :)