Using your Voice to Search Microsoft Identity Manager – Part 2


Last month I wrote this post that detailed using your voice to search/query Microsoft Identity Manager. That post demonstrated a working solution (GitHub repository coming next month) but was still incomplete if it was to be used in production within an Enterprise. I hinted then that there were additional enhancements I was looking to make. One is an Auditing/Reporting aspect and that is what I cover in this post.


The one element of the solution that has visibility of each search scenario is the IoT Device. As a potential future enhancement this could also be a Bot. For each request I wanted to log/audit;

  • Device the query was initiated from (it is possible to have many IoT devices; physical or bot leveraging this function)
  • The query
  • The response
  • Date and Time of the event
  • User the query targeted

To achieve this my solution is to;

  • On my IoT Device the query, target user and date/time is held during the query event
  • At the completion of the query the response along with the earlier information is sent to the IoT Hub using the IoT Hub REST API
  • The event is consumed from the IoT Hub by an Azure Event Hub
  • The message containing the information is processed by Stream Analytics and put into Azure Table Storage and Power BI.

Azure Table Storage provides the logging/auditing trail of what requests have been made and the responses.  Power BI provides the reporting aspect. These two services provide visibility into what requests have been made, against who, when etc. The graphic below shows this in the bottom portion of the image.
Auditing Reporting Searching MIM with Speech.png

Sending IoT Device Events to IoT Hub

I covered this piece in a previous post here in PowerShell. I converted it from PowerShell to Python to run on my device. In PowerShell though for initial end-to-end testing when developing the solution the body of the message being sent and sending it looks like this;

[string]$datetime = get-date
$datetime = $datetime.Replace("/","-")
$body = @{
 deviceId = $deviceID
 messageId = $datetime
 messageString = "$($deviceID)-to-Cloud-$($datetime)"
 MIMQuery = "Does the user Jerry Seinfeld have an Active Directory Account"
 MIMResponse = "Yes. Their LoginID is jerry.seinfeld"
 User = "Jerry Seinfeld"
$body = $body | ConvertTo-Json
Invoke-RestMethod -Uri $iotHubRestURI -Headers $Headers -Method Post -Body $body

Event Hub and IoT Hub Configuration

First I created an Event Hub. Then on my IoT Hub I added an Event Subscription and pointed it to my Event Hub.
IoTHub Event Hub.PNG

Streaming Analytics

I then created a Stream Analytics Job. I configured two Inputs. One each from my IoT Hub and from my Event Hub.
Stream Analytics Inputs.PNG
I then created two Outputs. One for Table Storage for which I used an existing Storage Group for my solution, and the other for Power BI using an existing Workspace but creating a new Dataset. For the Table storage I specified deviceId for Partition key and messageId for Row key.
Stream Analytics Outputs.PNG
Finally as I’m keeping all the data simple in what I’m sending, my query is basically copying from the Inputs to the Outputs. One is to get the events to Table Storage and the other to get it to Power BI. Therefore the query looks like this.
Stream Analytics Query.PNG

Events in Table Storage

After sending through some events I could see rows being added to Table Storage. When I added an additional column to the data the schema-less Table Storage obliged and dynamically added another column to the table.
Table Storage.PNG
A full record looks like this.
Full Record.PNG

Events in Power BI

Just like in Table Storage, in Power BI I could see the dataset and the table with the event data. I could create a report with some nice visuals just as you would with any other dataset. When I added an additional field to the event being sent from the IoT Device it magically showed up in the Power BI Dataset Table.


Using the Azure IoT Hub REST API I can easily send information from my IoT Device and then have it processed through Stream Analytics into Table Storage and Power BI. Instant auditing and reporting functionality.
Let me know what you think on twitter @darrenjrobinson

Geographically Visualizing your workforce using Microsoft Identity Manager, xMatters and Power BI


In the last couple of weeks I’ve posted about visualizing relationships of data from Microsoft Identity Manager using Power BI. Earlier this week I posted about building a Management Agent for Microsoft Identity Manger to integrate with xMatters.
In this post I combine data from the last two in order to allow us to visualise the geographic office locations for an organisation and then summary data about it (how many employees are located there, and what departments).


You’ll need an Azure AD and Office 365 subscription to allow you to create a Power BI Application. Too create a Power BI Application see Registering a Power BI Application in this post here.
You’ll also need the Power BI PowerShell Module. I’m using available from the PowerShell Gallery here and of course the Lithnet MIIS PowerShell Module available from here.


Using our registered Power BI Application we’ll create a Dataset consisting of two tables. One for the xMatters Sites (that we also get the geographic co-ordinates of from the xMatters Management Agent), and the other with our xMatters Users that contains the officeLocation that maps to an xMatters Site.
I create a relationship between the two tables on xMattersSite displayName (which is the location name) and the xMattersUsers officeLocation. We can then create a nice visual using data from both tables.

Create the Dataset (two tables with relationship)

Initially I tried to create the dataset with a relationship as I’ve previously shown here. However that didn’t work. After some debugging I got the result I wanted after some trial and error using the Power BI API Explorer. So I’ll provide you with the raw JSON format for creating a New Dataset, Two Tables (xMattersSites and xMattersUsers) and a relationship between them (where xMattersSites\displayName joins with xMattersUsers\officeLocation) as per my xMatters Management Agent detailed here.
Start by authenticating to the Power BI API Explorer with an account in the environment where you created your Power BI Application and navigate to the Create Dataset section here.
Create Dataset
Update this JSON formatted object that details the Dataset, Tables and Relationships for your environment.

Paste your validated JSON object into the Body section of the API Explorer and select Call Resource.
Dataset Body
If your JSON object is formatted corrected you’ll get a 201 response and your DataSet and Tables with Relationship will be created.
Create Success
Switching over to Power BI you’ll see the xMatters Dataset in the bottom left, then the two tables in on the right hand side with their columns.
xMatters DataSet PBI.PNG

Load xMatters User Data into Power BI

Now that we have somewhere to put the data, lets populate the dataset. I’m using the Lithnet MIIS Automation PowerShell Module (detailed in the prerequsites to query the Metaverse and return all users. Then I refine the list down to those that are Active (based on my employeeActive Boolean attribute) then finally, only those users that are connected on the xMatters Management Agent (see lines 14 & 18).

The script will drop any existing values from the xMatters Users table then upload what we have retrieved from the Metaverse (and refined).
Upload Users.PNG

Load xMatters Site Data into Power BI

Again I’m also using the Lithnet MIIS Automation PowerShell Module to query the Metaverse and return all xMatters Sites.

The script will drop any existing values from the xMatters Sites table then upload what we have retrieved from the Metaverse.
Upload Sites.PNG

Creating the Power BI Visual

Now we have data we can build the visual. I’m using the ArcGIS Maps for Power BI visual which is available in the default set of visuals. Then by selecting displayName and geo the map will automagically show all xMatters Sites in their respective co-ordinates.
xMatters Sites to Map
We can then add a Card Visual and choose officeLocation and then configure the visual for Count of officeLocation and we’ll get a count of the employees at that location. As we can see below with the Sydney location selected from the map the card updates to tell me there are 665 Employees at that officeLocation.
Count of Employees at Selected Location
Pretty quickly we can also expand out other data points, like departments at a location, employees etc as shown below (I’ve obfuscated the departments and a number of the other office locations).


We haven’t generated any new data. We’ve taken information we already have in Microsoft Identity Manager from connected systems and quickly visualized it via Power BI. However providing this to the business and with the ability for consumers of the information to export it from the visual can be pretty powerful.

A modern way to track FIM/MIM Attribute Value History utilizing Power BI


Microsoft Identity Manager is fantastic for keeping data consistent between connected systems. Often however you want to know what a previous value of an attribute was. FIM/MIM however can only tell you the current value and the Management Agent it was received on and when.
In the past where I’ve had to provide a solution to either make sure an attribute has a unique value forever (e.g email address or loginID (don’t reuse email addresses or loginID)) or just attribute value history I’ve used two different approaches;

  • Store previous values in an SQL Table and have an SQL MA that flows out the values
  • Store historical values in a Multi-Valued attribute on the user object in the Metaverse

Both are valid approaches but often fall down when you want to quickly get a report on that metadata.
Recently we had a similar request to be able to know when Employees EndDates were updated in HR. Specifically useful for contractors who have their contracts extended. Instead of stuffing the info into a Multi-Valued attribute or an SQL DB this time I used Power BI. This provides the benefit of being able to quickly develop a graphical report and embed it in the FIM/MIM Portal.
Such a report looks like the screenshot below. Power BI Report
Using the filters on the right hand side of the report you can find a user (by EmployeeID or DisplayName), select them and see attribute value history details for that user in the main part of the report. As per the screenshot below Andrew’s EndDate was originally the 8th of December (as received on the 5th of November), but was changed to the 24th of November on the 13th of November.
End Date History
In this Post I describe how I quickly built the solution.


The process to do this involves;

  • creating a Power BI Application
  • creating a Power BI Dataset
  • creating a script to retrieve the data from the MV and inject it into the Power BI Dataset
  • creating a Power BI Report for the data
  • embedding the Report in the MIM Portal

Registering a Power BI Application

Head over to Power BI for Developers and Register an Application for Power BI. Login to Power BI with an account for the tenant you’ll be reporting data for. Give your Application a name and choose Native Application. Set the Redirect URL to https://localhost
Choose the permissions for you Application. As we’ll be writing data into Power BI you’ll need a minimum of Read and Write all Datasets. Select Register App.
Create PBI App Permissions
Record your Client ID for your Application. We’ll need this to connect to Power BI.
Register the App
We need to authenticate to Power BI the first time using a UI to provide Authorization for our Application. In order to do that we need to add another Reply URL to our application. Head to the Apps Dev Portal, select your application and Edit the Application Manifest. Add an additional Reply URL for as shown below.
Add Reply URL for AuthZ
The following PowerShell commands will then allow us to Authenticate utilizing the Power BI PowerShell module. If you don’t have the Power BI PowerShell Module installed un-comment Install-Module PowerBIPS -RequiredVersion -Force  to install the PowerShell Power BI PowerShell Module.
Update for your Client ID for the App you registered in the previous steps.

# Install-Module PowerBIPS -RequiredVersion -Force
Import-Module PowerBIPS -RequiredVersion
# PowerBI App
$clientID = "4036df76-4de6-43cb-afe6-1234567890"
$authtoken = Get-PBIAuthToken -ClientId $clientID

Sign in with an account for the Tenant where you created the Power BI App.
Interactive Login for Dataset Creation
Accept the permissions you chose when registering the Power BI App.
Authorize PowerBI App

Creating the Power BI Dataset

Now we will create the Power BI Table (Dataset) that we will use when we insert the records.
My table is named Employee and the DataSet EmployeeEndDateReport.  I’m keeping the table slim to enough info for our purpose. Date added to the dataset, employees Accountname, Displayname, Active state, EndDate and EndDateReceived. The following script will create the Dataset.

Populating the Dataset

With our table created, lets populate the table with employees that have an EndDate. As this is the first time we run it, we set a watermark date to add people from. I’ve gone with the previous year.  I then query the MV for Employees with an EndDate within the last 365 days, build a PowerShell Object with the columns from our table and insert them into Power BI. I also set a watermark of the last time we had an EndDate Received from the MA and output that to the watermark file. This is so next time we can quickly get only users that have an EndDate that was received since the last time we ran the process.
NOTE: for full automation you’ll need to change line 6 for your secure method of choice of providing credentials to scripts. 

Create a Power BI Report

Now in Power BI select your Data Set and design your report. Here is a sample one that I’ve put together. I simply selected the columns from the dataset and updated the look and feel. I then added in a column (individually for AccountName, DisplayName and Active) and chose it as Filter so that I have various ways of filtering whoever I’m looking for.
Power BI Report.png
Once you have run the process for a while and you have changed values for the attribute you are keeping history for, you will see when you select a user with changed values, you will see the history.
End Date History


To complete the solution you’ll want to automate the script that queries the MV for changes (probably after each run from the MA that provides the attribute you are recording history for), and you’ll want to embed the report in the MIM Portal. In this post here I detail how to do that step by step.

How to embed Power BI Reports into the Microsoft Identity Manager Portal

About seven years ago at a conference in Los Angeles I attended I remember a session where a consultant from Oxford Computer Group gave a presentation on integrating Quest Identity Manager (now Dell One Identity Manager) with the Forefront Identity Manager Portal. I’ve recently had a requirement to do something similar and Carol pointed me in the direction of her experiments with doing something similar based off inspiration from that same presentation/session.
Well it is now 2017 and FIM and SharePoint have all moved through a few versions and doing something similar has changed. So now that I’ve got it working I thought I’d share how I’ve done it, and also to solicit any improvements. I’ve done this with SharePoint 2013.


In this post I’ll detail;

  • Publishing a Power BI Report
  • Creating new Microsoft Identity Manager Navigation Bar Resources
  • Embedding as an IFrame the published Power BI Report in the Microsoft Identity Manager Portal so that it appears like below


Obviously to follow this verbatim you are going to need to have a Power BI workspace and a Power BI Report. But you could embed any page you want to test it out.
You’ll also need;

Publish a Power BI Report

In Power BI select a Report you are looking to embed in the MIM Portal. I selected License Plans under Reports from my Power BI Worksapce.
From the File menu select Publish to Web.
Select Create embed code.
Copy the link to your report somewhere where you can retrieve it easily later. Don’t worry about the HTML line or the size.

SharePoint Designer

Download and install with the defaults SharePoint Designer 2013 from the link above. I’m using the 64-bit version. I installed it on my Development MIM Portal Server. I’m using 2013 as my MIM Portal is using SharePoint 2013 Foundation (with SP1).
Once installed start SharePoint Designer and select Open Site.
Enter the URL for your MIM Portal and select Open.
Note: In order for SharePoint Designer to successfully load your MIM Portal Site, the URL you provide above must be in your SharePoint Alternate Access Mappings. If it isn’t you will probably get the error “The server could not complete your request. For more specific information, click the Details button.”
And in your Windows Application Event Log Event ID 3 – WebHost

WebHost failed to process a request.
 Sender Information: System.ServiceModel.ServiceHostingEnvironment+HostingManager/42194754
 Exception: System.ServiceModel.ServiceActivationException: The service '/_vti_bin/client.svc' cannot be activated due to an exception during compilation.

Select Microsoft Identity Management, then All Files. You should then see a list of all the files in the MIM Portal website.
Locate the aspx folder, right click on it and select New => Folder. Create a new folder under the aspx directory named ‘reports’.
Right click on your new Reports Folder and select New => ASPX. Create an aspx file named reports.aspx.
Repeat to create another aspx file named report.aspx.
Click on the Reports.aspx file form the main pane and put the following contents in it overwritting everything else. Select Save.

<%@ Page Language="C#" %>
<html dir="ltr">
<head runat="server">
<meta name="WebPartPageExpansion" content="full" />

Click on the report.aspx file and replace the contents with the following and select Save.
Replace <yourreportlink> in<yourreportlink> with your Power BI link.

<%@ Page masterpagefile="~masterurl/custom.master" Title="Reports" language="C#" inherits="Microsoft.SharePoint.WebPartPages.WebPartPage, Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c" meta:progid="SharePoint.WebPartPage.Document" UICulture="auto" Culture="auto" meta:webpartpageexpansion="full" %>
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> <%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> <%@ Import Namespace="Microsoft.SharePoint" %> <%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<asp:Content ContentPlaceHolderID="PlaceHolderTitleBar" Visible="true" runat="server">
<asp:Content id="content1" ContentPlaceHolderID="PlaceHolderMain" runat="server">

MIM Portal Navigation Resources

Now we need to create the MIM Portal Navigation Resources to link to our new files.
In the MIM Portal Select Navigation Bar Resources. Select New.
Provide a Display Name, Description and select Next. Ignore Usage Keyword for now. More on that later.
Make the Parent Order 8 to have it at the bottom of the Left Nav bar. Order is 0 as this is going to be our Group header. Select Next.
Provide the path to the Reports.aspx file  ~/IdentityManagement/aspx/reports/reports.aspx Select Next.
Provide the Localised Display name, select Finish and then Submit.
Repeat, this time for linking to ~/IdentityManagement/aspx/reports/report.aspx and name it Licensing Report or whatever makes sense for your report. Also make the Order 1 so it nests under Reports.
Perform an IISReset.
Refresh you MIM Portal Page and you should see your new menu items on the left Navigatin Bar at the bottom.
Click on Reports and your Licensing Report will auto-magically load. Same as if you click on Licensing Report. You can now add as many reports as you need. And change which report you want to be the default by updating the Reports.aspx file in SharePoint Designer.
You will probably also want to limit who see’s what reports. You can do that through Usage Keywords and Sets etc. By default as described here the reports will only be visible to Administrators.  Details to get you started on changing who can see what can be found here.
Let me know if you have any improvements.
Follow Darren Robinson on Twitter

How to use a Powershell Azure Function App to get RestAPI IoT data into Power BI for Visualization


This blog post details using a Powershell Azure Function App to get IoT data from a RestAPI and update a table in Power BI with that data for visualization.
The data can come from anywhere, however in the case of this post I’m getting the data from WioLink IoT Sensors. This builds upon my previous post here that details using Powershell to get environmental information and put it in Power BI.  Essentially the major change is to use a TimerTrigger Azure Function to perform the work and leverage the “serverless” Azure Functions model. No need for a reporting server or messing around with Windows scheduled tasks.


The following are the prerequisites for this solution;

  • The Power BI Powershell Module
  • Register an application for RestAPI Access to Power BI
  • A Power BI Dataset ready for the data to go into
  • AzureADPreview Powershell Module

Create a folder on your local machine for the Powershell Modules then save the modules to your local machine using the powershell command ‘Save-Module” as per below.
Save-Module -Name PowerBIPS -Path C:\temp\PowerBI
Save-Module -Name AzureADPreview -Path c:\temp\AzureAD 

Create a Function App Plan

If you don’t already have a Function App Plan create one by searching for Function App in the Azure Management Portal. Give it a Name, Select Consumption Plan for the Hosting Plan so you only pay for what you use, and select an appropriate location and Storage Account.

Register a Power BI Application

Register a Power BI App if you haven’t already using the link and instructions in the prerequisites. Take a note of the ClientID. You’ll need this in the next step.

Configure Azure Function App Application Settings

In this example I’m using Azure Functions Application Settings for the Azure AD AccountName, Password and the Power BI ClientID. In your Azure Function App select “Configure app settings”. Create new App Settings for your UserID and Password for Azure (to access Power BI) and our PowerBI Application Client ID. Select Save.
Not shown here I’ve also placed the URL’s for the RestAPI’s that I’m calling to get the IoT environment data as Application Settings variables.

Create a Timer Trigger Azure Function App

Create a new TimerTrigger Azure Powershell Function App. The default of a 5 min schedule should be perfect. Give it a name and select Create.

Upload the Powershell Modules to the Azure Function App

Now that we have created the base of our Function App we’re going to need to upload the Powershell Modules we’ll be using that are detailed in the prerequisites. In order to upload them to your Azure Function App, go to App Service Settings => Deployment Credentials and set a Username and Password as shown below. Select Save.
Take note of your Deployment Username and FTP Hostname.
Create a sub-directory under your Function App named bin and upload the Power BI Powershell Module using a FTP Client. I’m using WinSCP.
To make sure you get the correct path to the powershell module from Application Settings start Kudu.
Traverse the folder structure to get the path to the Power BI Powershell Module and note the path and the name of the psm1 file.
Now upload the Azure AD Preview Powershell Module in the same way as you did the Power BI Powershell Module.
Again using Kudu validate the path to the Azure AD Preview Powershell Module. The file you are looking for is the Microsoft.IdentityModel.Clients.ActiveDirectory.dll” file. My file after uploading is located in “D:\home\site\wwwroot\MyAzureFunction\bin\AzureADPreview\\Microsoft.IdentityModel.Clients.ActiveDirectory.dll”
This library is used by the Power BI Powershell Module.

Validating our Function App Environment

Update the code to replace the sample from the creation of the Trigger Azure Function as shown below to import the Power BI Powershell Module. Include the get-help line for the module so we can see in the logs that the modules were imported and we can see the cmdlets they contain. Select Save and Run.
Below is my output. I can see the output from the Power BI Module get-help command. I can see that the module was successfully loaded.

Function Application Script

Below is my sample script. It has no error handling etc so isn’t production ready, but gives a working example of getting data in from an API (in this case IoT sensors) and puts the data directly into Power BI.

Viewing the data in Power BI

In Power BI it is then quick and easy to select our Inside and Outside temperature readings referenced against time. This timescale is overnight so both sensors are reading quite close to each other.


This shows how easy it is to utilise Powershell and Azure Function Apps to get data and transform it for use in other ways. In this example a visualization of IoT data into Power BI. The input could easily be business data from an API and the output a real time reporting dashboard.
Follow Darren on Twitter @darrenjrobinson

Leveraging the PowerBI Beta API for creating PowerBI Tables with Relationships via PowerShell

If anyone actually reads my posts you will have noticed that I’ve been on a bit of a deep dive into PowerBI and how I can use it to provide visualisation of data from Microsoft Identity Manager (here via CSV, and here via API). One point I noticed going direct to PowerBI via the API (v1.0) though was how it is not possible to provide relationships (joins) between tables within datasets (you can via PowerBI Desktop). After a lot of digging and searching I’ve worked out how to actually define the relationships between tables in a dataset via the API and PowerShell. This post is about that.


In order to define relationships between tables in a dataset (via the API), there are a couple of key points to note:

  • You’ll need to modify the PowerBIPS PowerShell Module to leverage the Beta API
    • see the screenshot in the “How to” section


To use PowerBI via PowerShell and the PowerBI API you’ll need to get:

How to leverage the PowerBI Beta API

In addition to the prerequisites above, in order to leverage the PowerShell PowerBI module for the PowerBI Beta API I just changed the beta flag $True in the PowerBI PowerShell (PowerBIPS.psm1) module to make all calls on the Beta API. See screenshot below. It will probably be located somewhere like ‘C:\Program Files\WindowsPowerShell\Modules\PowerBIPS

Example Dataset Creation

The sample script below will create a New Dataset in  PowerBI with two tables. Each table holds “Internet of Things” environmental data. One for data from an Seeed WioLink IoT device located outside and one inside. Both tables contain a column with DateTime that is extracted before the sensors are read and then their data added to their respective tables referencing that DateTime.

That is where the ‘relationships‘ part of the schema comes in. The options for the direction of the relationship filter are OneDirection (default), BothDirections and Automatic. For this simple example I’m using OneDirection on DateTime.

To put some data into the tables here is my simple script. I only have a single IoT unit hooked up so I’m fudging the more constant Indoor Readings. The Outside readings are coming from a Seeed WioLink unit.


So there we have it. Something I thought would have been part of the minimum viable product (MVP) release of the API is available in the Beta and using PowerShell we can define relationships between tables in a dataset and use that in our visualisation.


Follow Darren on Twitter @darrenjrobinson

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

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.

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.


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.

Follow Darren on Twitter @darrenjrobinson

Big Dater – Finding the biggest cheaters with Power BI

Hacking of commercial enterprises is never a good thing and in no way would I condone the dubious logic that was the basis behind the hack and release of the Ashley Madison database. There was no doubt a lot of personal and commercial damage caused.

But with every one of these hacks, there is opportunity. The hack has arguably done more for marriage fidelity than Fatal Attraction and has also given us a chance to relook at data security practices, personal data storage and password management practices. For me it’s a good chance to bust out one of this new tools in the BI suite Power BI on a good sized, real world dataset.

If you don’t want to run through the technical just scroll down to the results section below.

Power BI

Power BI comes in both a desktop and a hosted service model. I’m going to use the desktop model which is available here. It is a powerful BI tool which operates over PowerQuery, the data processing and transformation engine introduced in a previous blog. The tools are built for scale and so are not limited by those annoying little 1 million row limits of Excel. That’s a good thing because there is a lot of data in the Ashley Madison dataset.

Preparing the Data

The data can be downloaded from a number of places. I chose a torrent file sharing link with a set of zipped up files with an encryption key that matches the one that the hackers published to avoid tampering. The data comes as number of .dump files which are the tables from a mySql database, a set of .csv transaction files and a collection of other internal files. All up about 10GB of zipped up data to be explored.

The .dump files are mySQL dumps. These are a CREATE TABLE statement followed by lots and lots of INSERT statements. That’s great if you want to reconstruct the database (and if your prepared to wait a really long time to insert them all, but we don’t really need all the complexity of a relational database, flat files are just what we need. So I wrote a script to parse the INSERT statements and turn it back into a flat CSV file (with a little bit of finesse to get the right quote style for PowerQuery)

The daily transaction CSV folder is much easier, just trim off the titles and concatenate the contents into one great big CSV file. I’ve written a small c# script (attached) to concatenate the data and do a bit of cleaning along the way (it looks like the hackers, in their haste didn’t flush some files properly so there are a few truncated lines). Once the data is together it makes one great big 2.5GB AllTransactions.csv file with 10 million transactions!

The code is available here

Now, we get our first glimpse at the magnitude of the data available.

File Type Size (GB) Rows Columns Contents
amino_member.dump mySQL dump 12.7 36,399,119 61 All member details
am_am_member.dump mySQL dump 9.5 31,971,589 48 Some member details
member_details.dump mySQL dump 2.3 36,416,003 12 Member details summary table
aminno_member_email.dump mySQL dump 1.7 36,397,897 8 Member emails and notification they have signed up for
member_login.dump mySQL dump 4.2 36,400,530 5 Every account, username and password hash
Transactions folder Daily CSV 2.4 9,692,318 19 Every credit card transaction, who made it and where from
(partial credit card details)


There has been a lot of discussion online about the actual validity of the database since it appears there is some irregularity about the makeup of population of users. There is a heavy bias of males to females (which might be expected) and the females appear to not be distributed properly, suggesting that he female population was actually manufactured by paid employees. To avoid that discussion, I’m going to look at the one part of the dataset that doesn’t lie. Money! The Ashley Madison dataset includes every credit card transaction since inception in a daily csv file.

Start the Power BI application and launch the GetData wizard. This has a large number of different data source options but we are going with plain old .csv to load the AllTransactions.csv file.

This starts the PowerQuery editor and gives the first look at the data. There’s plenty of juicy information in here but gladly the full credit card number is truncated!

So now is the opportunity to pre-process the data for analysis. Choose Edit and then in the PowerQuery editor use the Choose Columns button. First we’ll pick the interesting columns for analysis.

Since we are in development mode, and the data set is so large, it’s worth trimming the data set to keep it manageable. Use the “Keep Rows button” to keep just 100,000 rows then make a few changes.

PowerQuery is great for importing data from many sources and dealing with large data sets. The code development experience is pretty ordinary so I recommend using PowerQuery to trim up and filter out unnecessary data to make it as small as possible and leave the complex stuff for a later (see DAX below). Trim up the data like this:

  1. Date column is actually a DateTime so rename it DateTime. Then use the Date button to add a “DateOnly” column, and a “Day”, “Month” and “Year” column.
  2. There are a number of failed attempts to charge against a credit card, these are identified by a not null value in the ERROR_CODE column. So trim out those rows and drop the column
  3. The credit card transactions come in 4 types:
    Authorizations – Submit credit card details to take up the free membership offer (Ignore these ones)
    Settlements – Commit previous Authorization (Add these ones)
    Purchases – Buy/Renew a membership outright (Add these ones)
    Credits and Chargebacks – The wife found out, so quickly back out! (Subtract these ones)
  4. Use the “Add Custom Column” to add a new column called “Value” which represents the actual value of the transaction using an if statement to add just the Settlements and Purchases and subtracting the refunds.
  5. Add a UserId column that will help identifying unique users. There’s plenty of personally identifiable information which can be combined to form an effective “UserId”. I have used these FIRST NAME, CARD ENDING and ZIP columns which will give a reasonable approximation for a unique User Id so choose “No Separator” and name the column UserId. Select the columns and hit the Merge Column button.
  6. Finally remove all unused columns including all that nasty personal and credit card information from the data set so we can safely publish.

The Advanced Query button shows the Power Query code that was generated by the import wizard and transformation steps

Now “Close and Load” the data into Power BI.


One of the nice challenges in the data set is the Amount column is recorded in the native currency of the credit card. So if we want to do any reporting across different countries then that number needs to be normalised to a single currency such as the $US. This is easy if we use the Excel spreadsheet created in my previous blog here Use Excel, PowerQuery and Yahoo Finance to manage your Portfolio. Download the Excel workbook from that blog and open it.

First we need a data source that links CountryCode to CurrencyCode, theres plenty available. I chose this one . One of the really powerful features of PowerQuery is the ability to directly parse a web page into tables which can be imported into a PowerQuery table using the “From Web” data source.

Open the page.

Now using PowerQuery tool bar rename the Country column to CountryCode and Split the Currency column by the first 3 characters

Drop all the other columns we don’t need and finally, to retrieve the Rate from yahoo you need the currency in the form of AFNUSD=X so create a new Custom Column called Symbol.

Which generates this table

Paste the Symbols into the Symbol column of the Portfolio tab then hit Refresh Data and you’ll end up with a table like this with the latest currency rates populated from Yahoo Finance in the “Last Trade Price Only” column.

Add one extra row in the table to handle those records with no country set and assume they are US dollars

Save that as currencies.xlsm and then back to Power Query to use the Get Data -> Excel to import it

And import the Portfolio table from the Excel workbook.

Remove the columns other than CountryCode and Rate.

Now we will be using this table to do a significant large join to bring currency rate data onto the transaction table. To do this efficiently we can help the join performance by providing a primary key on the table. There is no GUI wizard feature to do this so open the Advanced Editor and add this line to set a Primary key on the CountryCode column to be used in the join engine.

  #"Indexed Table" = Table.AddKey( #"Renamed Columns", {"CountryCode"}, true) 

Here’s the currency query:

Global Data

Given that the site was a global phenomenon, we want to get some global perspective on the data so let’s upload some global reference data from here which has some useful country stats.

Import the page using New Source->Web feature in Power Query.

Use the PowerQuery GUI to

  1. Rename the GDP column to and Split it on the first space ‘ ‘
  2. The create a new Multiplier column by parsing the Million and Billion words into numbers using an if statement

    #”Added Custom” = Table.AddColumn(#”Changed Type1″, “Multipler”, each
    if [GDP.2]=”Million” then 1000000 else
    if [GDP.2]=”Billion” then 1000000000 else
    if [GDP.2]=”Trillion” then 1000000000000 else 1),

  3. Split the ISO CODES column by the first ‘/’ and rename the first column CountryCode
  4. Remove all the unnecessary columns
  5. And add an Index on CountryCode for the join performance

The table should look like this

Here’s the generated global query:

Use Close and Apply to run all the queries


At this point the data sets are all completely independent. On the left hand side of the Power BI Desktop is a button to switch to Relationship mode.

Power BI will do a reasonable job of guessing the relationships but may need a little help. If not already there, create a one-to-many relationship with the transaction table by CountryCode by dragging and dropping the CountryCode column from the “many” fact table to the “one” dimension tables. (The use of the multidimensional terms used in SQL Server Analysis Services is no mistake as we will see later)

Data Typing

Now switch into Report mode

There’s a bit of additional data type coercion that needs to happen to help Power BI make the right choices when presenting the data. For each of the geographical entities use the Data Category menu to assign the correct type of geographical data.

This will put a small globe icon next to each and will be useful in the mapping process.

Do the same for the currency fields setting to Data Type currency.


Now it’s time to generate some new columns of data that join across related tables. This is the first opportunity to use DAX (or Data Analysis eXpressions) which is a real pleasure. This is the query language under PowerPivot and DAX has great debugging, helpful error handling and intellisense. On the transactions table use New Column

to add the following columns

Quarter = "Q" & transactions[Qtr]  -- the year quarter string
Value US = transactions[Value]*RELATED('currency'[Rate]) -- the value in $US

(Note the use of the ‘RELATED’ keyword to traverse the join relationship to the currency table)

Value US per Capita(M) = IFERROR(transactions[Value US]/RELATED('global'[POPULATION])*1000000,0) -- the value in $US per million people

(Note how the variables don’t have to follow normal syntax rule, spaces and punctuation are fine)

Value US per GDP(M) = IFERROR(transactions[Value US]/RELATED('global'[GDP])*1000000,0) --the value in $US per million GDP


Now the fun part, build a report! I did this

And this

And this

And this

Now we can start to really see the data. Don’t forget the transactions query is limited to 100,000 rows which is not even all of the first year of operation. Ashley Madison, by any measure was already a success having brought in $US3 million in revenue, with 39,000 paying customers!

Scaling Up

Once happy with the result, open up the transactions query

and remove the Kept First Rows step so the full data set is processed.

Now Close and Load the Power Query editor and go make a cup of tea. The full data set is over 2GB and 10 million rows so it’s going to take a while. My i7 8GB SSD laptop takes 20 minutes to process the data.

While that is working take a look at the Task Manager. The Microsoft Mashup Evaluation container is Power Query engine doing its work. But interestingly under the Power BI hood is SQL Server Analysis Services! What we are doing while using the Relationship editor and the Report builder is creating a “cube” which is then embedded in the Power BI .pbix file.


Now with all the data we see the real success story that is Ashley Madison

  • $US240M in total revenue and on target to hit $US100M in 2015 alone (the hack only captured up to Q2 2015)
  • 1M paying customers and climbing rapidly

So the burning question which country spent the most on cheating per capita? It’s as simple as clicking on the largest red circle or clicking on (sorting by) Value US per Capita in the table.

The Vatican! It has 10 paying customers with 59 separate transactions totalling US$1303. We can even see that the initial transactions occurred in 2009, then a period of inactivity and then a return to paid up member status in 2013, 14, and 15. It’s a little bit unfair to label the Vatican since the population is so small it could be a statistical error, but given 1% of residents were paid up members, there’s certainly something interesting there. The other interesting cases are Palau, Falkland Islands and Bermuda.


Publishing a report is as easy as pressing this button

The report is publised to here. It isn’t a static set of graphs and figures, it is the full cube which means all of the richness of the Power BI Desktop is available including filtering and drill downs on each of the report elements. Clicking on a circle in the map filters all other elements on the page with that category.

However, the published report is only available to licensed users.


This is some very powerful tooling at the early stages of development. Power Query needs some work but DAX is great so use Power Query to reduce the data down and use DAX to do the more complex stuff.

The Power BI Desktop Report Builder is primitive but getting better and there is a release every every month. It’s lacking some of the things you’d expect like colour control and font size control on text, z-order control on report elements and snap to grid layout control, theres some room for improvement before the tool is ready to take on the big players.

But thats just the thing Power BI isn’t a multi million dollar BI platform, its a desktop editing and SaaS publishing platform for BI, much like the way I use Word to create this blog which is then published to the Internet. Its another example of cloud computing at its best. A very capable tool once only available to enterprises put in the hands of everyone. This is a disruption for over priced software vendors and consultancies in the business intelligence space.

If you want to have a go at doing some more data analysis I’ve saved the anonymised Power BI files (with no personal information in it) here. The ‘transactions’ data set is the one used in this blog, the ‘members’ dataset has a much richer set of demographics information including sex, preferences, height, weight, dob, date joined etc. I’ll leave it to the reader (or the next blog) to find some interesting insights in there.

Follow ...+

Kloud Blog - Follow