Simple reporting from the FIM/MIM Metaverse to PowerBI using the Lithnet FIM/MIM Sync Service PowerShell Module

I have a customer that is looking to report on FIM/MIM identity information. The reports they are looking for aren’t overly complex and don’t necessarily justify the need the full FIM/MIM reporting infrastructure. So I spent a few hours over a couple of days looking at alternatives. In this blog post I give an overview of using the awesome Lithnet FIM/MIM Sync Service PowerShell Module recently released from Ryan Newington to do basic reporting on the Microsoft (Forefront) Identity Manager Metaverse into PowerBI.

I’ll briefly show how to leverage the Lithnet FIM/MIM Sync Service PowerShell Module to extract Person objects and their metadata (based on a search filter criteria) from the MIM/FIM Metaverse and output to a file for PowerBI.

I cover;

• Building a query
• Executing the query
• Filtering the results for output to a file (CSV)
• Importing to PowerBI as a dataset, creating a report showing results in a Dashboard

First up you’ll need to download and install the module from https://github.com/lithnet/miis-powershell

Using the FIM/MIM Sync Service PowerShell Module to query the Metaverse

What operators you can choose on your attribute types (boolean, string, integer, reference etc) in the Metaverse Search function in the Synchronisation Service Manager you can also perform using the Lithnet FIM/MIM Sync Service PowerShell Module.

By creating a search with multiple criteria in the Metaverse Search you can filter the results from the Metaverse.

As shown below you can see that we get 302 results.

So let’s import the Lithnet FIM/MIM Sync Service PowerShell Module, create a filter execute it and look at the results. As you’d expect we get the same result. Excellent.

Remember that using this PowerShell automation module, the backend is still the WMI interface behind the Synchronisation Service Manager. This means you can’t for example create a query filter using “greater than/less than” if you can’t do it in the UI.

Take my Twitter FriendsCount attribute of type Number/Integer as an example.

I can’t create a query filter that would return results where FriendsCount > 20,000. I can only use the IsPresent, IsNotPresent and Equals.

On a sidenote the PowerShell error message will give you a hint at what operators you can use as shown below.

However, if you try and use StartsWith for an Integer attribute the search will execute but just return no results. My tip then is define your query in the Metaverse Search GUI and when you get what results you want/expect, create the equivalent query in PowerShell and validate you get the same number of results.

Final note on query filters. Multiple criteria are an AND operation filter, NOT OR.

Let’s do something with the results

Now that we have a query sorted let’s do something with the results. The result set is the full attribute list and values for each associated object that matched our query from the Metaverse. That’s way more info than what I and probably you need as well. So iterate through the results, pull out the attribute values that we want to do something with and export them as a CSV file.

What to do with the output ?

For this overview I’ve just chosen the local file (CSV) that I exported as part of the script as the input dataset in PowerBI. https://app.powerbi.com

On the right hand side I’ve chosen the columns that were exported to the CSV and they appear in the main window.

Click Pin to Live Page. You’ll be prompted to save the report first so do that then I choose New Dashboard for the report. Click Pin live.

I can then refine and get some visual reports quickly using text based queries using keywords from the dataset columns. Like Top 10 by number of friends from the dataset.

Create a couple of queries and pin them to the Dashboard and the data comes to life.

Summary

The Lithnet FIM/MIM Sync Service PowerShell Module provides a really easy way to expose information from the Metaverse that may satisfy many reporting and other requirements. Taking the concept further it wouldn’t be too complex to export the data to an Azure SQL DB on a schedule and have the results dynamically update on a PowerBI Dashboard.
The concept of exporting data for reporting is just one practical example using the tools. Huge thanks to Ryan for creating the Lithnet tools and publishing to the community. Keep in mind the tools disclaimer too.

Here is the sample PowerShell.

See the gist on github.

Follow Darren on Twitter @darrenjrobinson