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

Introduction

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.

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.

In this Post I describe how I quickly built the solution.

Overview

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.

Record your Client ID for your Application. We’ll need this to connect to Power BI.

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 https://login.live.com/oauth20_desktop.srf as shown below.

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 1.2.0.9 -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 1.2.0.9 -Force
Import-Module PowerBIPS -RequiredVersion 1.2.0.9

# PowerBI App
$clientID = "4036df76-4de6-43cb-afe6-1234567890"

$authtoken = Get-PBIAuthToken -ClientId $clientID

Accept the permissions you chose when registering the Power BI 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.

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.

Summary

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.

July 18, 2017July 17, 2017

Resolving Microsoft Identity Manager “sync-rule-validation-parsing-error” error

A couple of weeks back I inherited a Microsoft Identity Manager development environment that wasn’t quite complete. When I performed a sync on a user object I got the following error; sync-rule-validation-parsing-error

Looking into the error for further details, Details and Stack Trace were both greyed out as shown below.

I looked at the object being exported on the MA and the awaiting export details and found slightly different information. The error was CS to MV to CS synchronization failed 0x8023055a

Still not a lot to go on. So I looked in the Application Event Log and nothing. Anything in the System Event Log? No, nothing.

So my attention turned to the Export Synchronization Rule. Here is a partial screenshot of the Export Sync Rule. The object (user) in question had been flagged as inactive and the intent appeared to be a clearing of a number of attributes. Sending “” (crude empty/null) to an attribute isn’t very elegant.

I changed each to use the null function. So for export, null() will flow to each of the attributes. I tried the export again and the same error and problem resulted.

Running short on ideas I created a brand new Export Synchronization Rule and replicated the configuration except for the attributes being exported. Then I added one attribute into the rule at a time, tested the export and repeated until I could replicate the error.

I was able to replicate the error once I hit the terminalServer attribute.
*Note: the screenshot below is prior to changing over to flow null() instead of “”.

Sending null() to the terminalServer Active Directory attribute was causing the error. It was at this point I actually just removed that flow rule and continued with other tasks.

Coming back to this later, and thinking it through I understand the error. When dealing with Terminal Services you actually normally manage four attributes that are part of the userParameters attribute. The four attributes that define a users Terminal Services Profile are;

allowLogon
terminalServicesHomeDirectory
terminalServicesProfilePath
terminalServicesHomeDrive

For a user that has a fully configured set of Terminal Services attributes, sending null() to the terminalServer attribute isn’t going to work.

So, posting this as I couldn’t find any reference to sync-rule-validation-parsing-error or CS to MV to CS synchronization failed 0x8023055a elsewhere and chances are I’ll come across it again, and it’ll probably help someone else too.

August 3, 2016June 13, 2018

Adding/Removing User Office365 Licences using PowerShell and the Azure AD Graph RestAPI

In a recent blog post here I posted about the Azure AD v2.0 Preview Powershell cmdlets that are currently in preview. These update the functionality the current MSOL cmdlets provide whilst also supporting features they don’t (such as managing users with MFA).

The Azure AD v2.0 cmdlets interface with the Azure AD Graph API and this week I tried using the Set-AzureADUserLicense cmdlet to add/remove licenses from users in a test tenant. With no sample documentation for syntax I didn’t kick any goals so I figured I’d just go straight to using the Azure AD Graph API to get the job done direct from Powershell instead.

In this post I’m going to show you how to add/remove Office365 licenses from users using PowerShell and the Azure AD Graph API.

As per my other post linked above if you’ve installed the Azure AD Preview Powershell module you’ll have the Microsoft.IdentityModel.Clients.ActiveDirectory.dll which we can leverage via Powershell to then connect to the Azure AD Graph API. Chances are you’ll have Microsoft.IdentityModel.Clients.ActiveDirectory.dll though if you also have the AzureRM Modules installed or TFS. Just search your Program Files sub-directories.

# the default path to where the Azure AD Preview PS Module puts the Libs
'C:\Program Files\WindowsPowerShell\Modules\AzureADPreview\1.1.143.0\Microsoft.IdentityModel.Clients.ActiveDirectory.dll'
# TFS Path
'C:\Program Files\Common Files\microsoft shared\Team Foundation Server\14.0\Microsoft.IdentityModel.Clients.ActiveDirectory.dll'
# Azure RM Cmdlets
'C:\Program Files\WindowsPowerShell\Modules\AzureRM.ApiManagement\1.1.2\Microsoft.IdentityModel.Clients.ActiveDirectory.dll'

Below you’ll find what you need to script your connection to Azure AD via the GraphAPI using the dll discussed above. Change $tenantID $username and $password to reflect you tenant and credentials.

Now that we’ve authenticated let’s enumerate our licenses. Bascially we make a RestAPI call to “https://graph.windows.net/{0}/subscribedSkus?api-version=1.6” as below. The particular license I want to add in my tenant is ExchangeStandard_Student. A key difference from the MSOL cmdlets though is adding or removing a license via the Graph API we reference the License skuId rather than skuPartNumber. The last line gets the skuId for my license.

Now to find the users that need to have the license assigned. Below in line 4 I search for users that are account enabled using this URI “https://graph.windows.net/{0}/users?$filter=accountEnabled eq true &api-version=1.6″ You can modify the filter for your criteria.

I then iterate through the users and find the users that aren’t assigned the license identified earlier above. I also exclude the AADConnect account for this tenant.

The GraphAPI expects the body with the info of licenses to be added or removed in a hashtable. So create the hashtable and convert it to JSON which is the format for Azure AD Graph API requires. Then for each of the unlicensed users we call the Azure AD GraphAPI URI “https://graph.windows.net/myorganization/users/$usertolicense`/assignLicense?api-version=1.6” and assign the license . In my environment it processed users at just over 1 user/sec.

A similar approach to remove licenses from users. A subtle difference is you only need to specify the skuId for removal as shown below. Note: you can add and remove licenses in the same call (if say you’re switching users over from one license plan to another).

All together for quick copy and paste. Update it for your tenant and creds. And remark out the Add or Remove depending on what you’re looking to do. If you’re looking to add/remove multiple licenses just add them to the hashtable/array.

Follow Darren on Twitter @darrenjrobinson

July 12, 2016July 20, 2017

PowerShell error “Run Login-AzureRmAccount to login.” in AzureRM when already logged in

Usually when I’m writing PowerShell scripts I do it from a development virtual machine with a known environment state. However, yesterday I was trying to do something simple and quick and was writing it on my everyday laptop.

My script was using Windows Management Framework 5.0 and I was creating a new burn environment in AzureRM. I was authenticated and could query and enumerate most of my AzureRM environment, however I was getting erroneous responses on some cmdlets and was unable to create a new resource group. Essentially whenever I tried to perform anything of value to what I was trying to achieve PowerShell would return ”Run Login-AzureRmAccount to login.”

I was authenticated and all looks as it should.

Query ARM RG and get the error, ”Run Login-AzureRmAccount to login.”

I started digging to find out what my environment was looking like. Did I have WMF5 installed? $PSVersionTable showed I did and all looked as it should.

What modules did I have installed? Get-InstalledModule | Out-GridView

Lots of differing versions is what I saw *Note: screenshot above is after resolving my issue.

So what fixed my issue? I ran Update-Module and watched PowerShell update my modules and get my environment back into spec. A restart of my laptop and back in action.

What caused my issue? I’m thinking I updated an individual module at some point and dependencies were missed.

Hope this helps someone else and saves some of those “this should just be working, why isn’t it” moments.

Introduction

Overview

Registering a Power BI Application

Creating the Power BI Dataset

Populating the Dataset

Create a Power BI Report

Summary

Share this:

Share this:

Share this:

Share this: