ADFS: Finding when users were synced

ADFS – Active Directory Federation Services – is a great technology that allows you to synchronise your on-premises Active Directory environment with an Office365 tenancy.

Unfortunately it can sometimes be a little impenetrable in terms of querying when changes to objects are replicated to O365. While you can search for objects using the Metaverse Search tab in the GUI, this may not always give you what you’re looking for. Indeed, searching is hampered by the fact that ADFS references objects based on either their ImmutableID or Cloud DN values. And ImmutableID value might look something like this:


While a Cloud DN would look something like this:


Neither of these things are recorded against any on-premises object, and ADFS PowerShell doesn’t make it easy to to find an object by one value to obtain the other. This can be frustrating when you realise that all entries in the ADSync SQL database are referenced by Cloud DN value, making querying the database tricky. Thankfully the following script (found here, credits to the author) allows you to enter either value and obtain its counterpart:

[Parameter(Mandatory = $true,
HelpMessage="ImmutableID string or Azure CS DN value")]
$done = $NULL
If ($value.EndsWith("=="))
$enc = [system.text.encoding]::utf8
$result = $enc.getbytes($Value)
write-host "CN={" -nonewline
$result | foreach {write-host -object ([convert]::tostring($_,16)) -NoNewline};write-host "}"
ElseIf ($value.ToLower().StartsWith("cn="))
$hexstring = $value.replace("CN={","")
$hexstring = $hexstring.replace("}","")
$array = @{}
$array = $hexstring -split "(..)" | ? {$_}
$array | FOREACH {WRITE-HOST –object ( [CHAR][BYTE]([CONVERT]::toint16($_,16))) –nonewline };write-host
Write-host -fore red "You provided a value that was neither an ImmutableID (ended with ==) or a DN (started with CN=), please try again."

If you were interested in which synchronisation runs a user was updated in, you would find the ImmutableID value by running the following command in ADFS PowerShell after connecting to your tenant:

Get-MsolUser -UserPrincipalName | Select ImmutableID

This can then be passed through a run of the script above to get the Cloud DN value, which can then in turn be entered into the following SQL command which you can run against your ADSync SQL database:

select * from mms_step_object_details so where so.step_history_id in (
       select step_history_id from mms_step_history sh where sh.run_history_id in (
       select run_history_id from mms_run_history where start_date > '2018-01-13 17:00:00' and
       start_date < '2018-01-13 23:59:59')
       and sh.stage_update > 50) and cs_dn = 'CN={635257457869502f4145365a65474e6c4166637377513d3d}'

Naturally you can tailor this to extend the date range you’re searching for; the sh.stage_update criteria can also be removed if you’re interested in every run the user appeared in; running the statement as it is above will only return those batches where the update stage of the run contained more than 50 objects.

I hope this helps if you’re having trouble querying the internals of your ADFS / ADSync databases, but as ever if you have any questions please feel free to let me know and I’ll do my best to try and help.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email
Bookmark the permalink. Follow any comments here with the RSS feed for this post.
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Your email address will not be published. Required fields are marked *

This blog is kept spam free by WP-SpamFree.