Back to Blog

Azure AD Sync Tool HTML Report

Image of Michael Van Horenbeeck MVP, MCSM
Michael Van Horenbeeck MVP, MCSM
Azure logo

Last year, Exchange Server MVP Mike Crowley wrote a script which would interactively report on the Office 365 Directory Synchronization tool. In the meantime –last September to be more exact – Microsoft released the new “Azure AD Sync Service” tool which seems deemed to replace DirSync at some point in the future. As I do see the tool being used in production from time to time, effectively already replacing DirSync, I thought it would be useful to “upgrade” Mike’s script to work with the new kid on the block.

As Mike and I were going back and forth about this, he had a great idea to make the script create an HTML report instead of having it display the information interactively. As such, you can schedule the task and have the report emailed to you.While the code below is definitely a “version 1” and it can use some improvements like error handling, I did not want to keep it from you. Over time – especially as AADSync gets new features – I will be adding new functionalities to this script as well.You can use the script, as depicted in the following example. The script will create a filed called “AADSyncInfo.html” in the specified file path:

GetAADSyncInfo.ps1 -filePath c:\temp\
Before you can actually run the script, you will have to install SQL PowerShell on the AADSync machine first. DirSync had this installed by default, but it seems that AADSync does not. To install the SQL PS module, you must install the following components separately:
  1. Microsoft® System CLR Types for Microsoft® SQL Server® 2012
  2. Microsoft® SQL Server® 2012 Shared Management Objects
  3. *Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012
The binaries can be installed from the installation instructions on the following page: you have installed the components, run the following command from the AADSync server and verify that the SQLPS module is listed:
Get-Module -ListAvailable
Once you have verified the SQLPS module is installed and available, you can run the script. In a next version of the script, I will be looking into optimizing the code not to rely on the SQLPS module anymore.As for the script itself, here’s the source code:
This script gathers AADSync information from various locations and reports to the screen.
Script originally written for DirSync on November 5, 2013 by Mike Crowley
Modified by Michael Van Horenbeeck on October 21, 2014
 GetAADSyncInfo.ps1 -filePath c:\temp\
This will generate an HTML file called "AADSyncInfo.html" in the folder c:\temp
1) Modified to work with Azure AD Sync tool
2) Updated source for Service Account Guess. Information comes now from internal SQL DB instead of local registry
3) Removed features that are not included in the Sept. 2014 release of Azure AD Sync
4) Script will now generate an HTML report rather than relying on an interactive output on the screen
Known Issues:
1) All commands, including SQL queries run as the local user.  This may cause issues on locked-down SQL deployments.
2) For remote SQL installations, the SQL PowerShell module must be installed on the dirsync server.
        #Specify the report file path
Function check-even ($num) {[bool]!($num%2)}
#Console Prep
Write-Host "Please wait..." -F Yellow
ipmo SQLps
#Check for SQL Module
if ((gmo sqlps) -eq $null) {
    write-host "The SQL PowerShell Module Is Not loaded.  Please install and try again" -F Red
    Write-Host "Quitting..." -F Red; sleep 5; Break
#Get Dirsync Registry Info
$DirsyncVersion = (gp 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\MicrosoftAzureADConnectionTool').DisplayVersion
$DirsyncPath = (gp 'HKLM:\SOFTWARE\Microsoft\MSOLCoExistence\CurrentVersion').InstallationPath
#AAD Sync does not support external SQL yet, hence static SQL config from localDB
$SQLServer = $env:computername
$SQLInstance = (gp 'HKLM:\SYSTEM\CurrentControlSet\services\ADSync\Parameters').SQLInstance
$ADSyncInstance = (gp 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Shared Instances\ADSync').InstanceName
$MSOLInstance = ("np:\\.\pipe\"+$ADSyncInstance+"\tsql\query")
$SQLVersion = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')"
#Get AD Management Agents
$ADMAxml = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT [ma_id] ,[ma_name] ,[private_configuration_xml],[ma_type] FROM [ADSync].[dbo].[mms_management_agent]" | ? {$_.ma_type -eq 'AD'}
foreach($ADMAgent in $ADMAxml){
    [xml]($ADMAgent | select -Expand private_configuration_xml)
    $individualADMAgent += [xml]($ADMAgent | select -Expand private_configuration_xml)
    $maName += ($ADMAgent | select ma_name)
#Get DirSync Database Info
$SQLDirSyncInfo = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files WHERE DB_NAME(database_id) = 'AdSync'"
$DirSyncDB = $SQLDirSyncInfo | ? {$_.Logical_Name -eq 'ADSync'}
$DirSyncLog = $SQLDirSyncInfo | ? {$_.Logical_Name -eq 'ADSync_log'}
#Get connector space info (optional)
$ADMA = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT [ma_id] ,[ma_name],[ma_type] FROM [ADSync].[dbo].[mms_management_agent] WHERE ma_type = 'AD'"
$AzureMA = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT [ma_id] ,[ma_name],[subtype],[private_configuration_xml] FROM [ADSync].[dbo].[mms_management_agent] WHERE subtype = 'Windows Azure Active Directory (Microsoft)'"
$UsersFromBothMAs = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT [ma_id] ,[rdn] FROM [ADSync].[dbo].[mms_connectorspace] WHERE object_type = 'user'"
$AzureUsers = $UsersFromBothMAs | ? {$_.ma_id -eq $AzureMA.ma_id}
#$ADUsers = $UsersFromBothMAs | ? {$_.ma_id -eq $ADMA.ma_id}
#Get DirSync Run History
$SyncHistory = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT [step_result] ,[end_date] ,[stage_no_change] ,[stage_add] ,[stage_update] ,[stage_rename] ,[stage_delete] ,[stage_deleteadd] ,[stage_failure] FROM [ADSync].[dbo].[mms_step_history]" | sort end_date -Descending
#GetDirSync interval (3 hours is default)
#AADSync uses Task Scheduler to get interval
$SyncTimeInterval = ((Get-ScheduledTaskInfo "Azure AD Sync Scheduler").NextRunTime - (Get-ScheduledTaskInfo "Azure AD Sync Scheduler").LastRunTime).TotalMinutes
#Generate Output
$html += "<html>"
    $html += "<head>"
        $html += "<style type='text/css'>"
            $html += "body {font-family:verdana;font-size:10pt}"
            $html += "table {border:0px solid #000000;font-family:verdana; font-size:10pt;cellspacing:1;cellspacing:0}"
            $html += "tr.color {background-color:#00A2E8;color:#FFFFFF;font-weight:bold}"
            $html += "tr.title {background-color:#E5E5E5;text-decoration:underline}"
            $html += "font.value {color:#808080}"
        $html += "</style>"
    $html += "</head>"
    $html += "<body>"
    $html += "<b>Azure AADSync Report Info</b><br/>"
    $html += "Date: <font class='value'>"+(Get-Date)+"</font></br>"
    $html += "Server: <font class='value'>"+$env:computername+"</font></br>"
    $html += "<p>&nbsp;</p>"
    $html += "<b>Account info</b><br/>"
    #Get Account Info for each domain:
    #$ServiceAccountGuess = (((gci 'hkcu:Software\Microsoft\MSOIdentityCRL\UserExtendedProperties' | select PSChildName)[-1]).PSChildName -split ':')[-1]
    #Update to use info from SQL
    $ServiceAccountGuess = (([xml]$AzureMA.private_configuration_xml | select -ExpandProperty MaConfig | select -ExpandProperty parameter-values).parameter | ? Name -eq "username").'#text'
    foreach ($agent in $individualADMAgent){
        $ADServiceAccountUser = $Agent.'adma-configuration'.'forest-login-user'
        $ADServiceAccountDomain = $Agent.'adma-configuration'.'forest-login-domain'
        $ADServiceAccount = $ADServiceAccountDomain + "\" + $ADServiceAccountUser
        $html += "Active Directory Service Account <font class='value'>"+$ADServiceAccountDomain+": "+$ADServiceAccount+"</font>"
        $html += "<br/>"
        #Write-Host "Active Directory Service Account $ADServiceAccountDomain : " -F Cyan -NoNewline ; Write-Host $ADServiceAccount -F DarkCyan
    $html += "Azure Service Account Guess: <font class='value'>"+$ServiceAccountGuess+"</font>"
    #Write-Host "Azure Service Account Guess: " -F Cyan -NoNewline ; Write-Host $ServiceAccountGuess -F DarkCyan
    $html += "<p>&nbsp;</p>"
    $html += "<b>Azure AD Sync Info</b><br/>"
    $html += "Version: <font class='value'>"+$DirsyncVersion+"</font><br/>"
    $html += "Path: <font class='value'>"+$DirsyncPath+"</font><br/>"
    $html += "Sync Interval (Minutes): <font class='value'>"+$SyncTimeInterval+"</font>"
    $html += "<p>&nbsp;</p>"
    $html += "<b>User Info:</b><br/>"
    foreach($ad in $ADMA){
        $html += "User in AD "+$ad.ma_name+": <font class='value'>"+($UsersFromBothMAs | ? {$_.ma_id -eq $ad.ma_id}).count+"</font><br/>"
        #Write-Host "Users in AD"$ad.ma_name": " -F Cyan -NoNewLine ; Write-Host ($UsersFromBothMAs | ? {$_.ma_id -eq $ad.ma_id}).count -ForegroundColor DarkCyan
    $html += "Users in Azure Connector Space: <font class='value'>"+$AzureUsers.Count+"</font><br/>"
    $html += "Total users: <font class='value'>"+$UsersFromBothMAs.Count+"</font><br/>"
    $html += "<p>&nbsp;</p>"
    $html += "<b>SQL Info</b><br/>"
    $html += "Version: <font class='value'>"+$SQLVersion.Column1+" "+$SQLVersion.Column2+" "+$SQLVersion.Column3+"</font><br/>"
    $html += "Instance: <font class='value'>"+$MSOLInstance+"</font><br/>"
    $html += "Database Location: <font class='value'>"+$DirSyncDB.Physical_Name+"</font><br/>"
    $html += "Database Size: <font class='value'>"+$DirSyncDB.SizeMB+"MB</font><br/>"
    $html += "Database Log Size: <font class='value'>"+$DirSyncLog.SizeMB+"MB</font>"
    $html += "<p>&nbsp;</p>"
    $html += "<b>Most Recent Sync Activity</b><br/>"
    $html += "<i>(For more detail, launch:"+$DirsyncPath+"\UIShell\miisclient.exe)<br/><br/>"
    $html += "<table>"
    $html += "<tr class='title'>"
        $html += "<td width='250'>"
            $html += "Date"
        $html += "</td>"
        $html += "<td>"
            $html += "Result"
        $html += "</td>"
    $html += "</tr>"
    for($j=0;$j -ne 9;$j++){
    if(check-even $j -eq $true){
        $color = "#C3C3C3"
        $color = "#E5E5E5"
        $html += "<tr style='background-color:$color'>"
            $html += "<td>"
                $html += ($SyncHistory[$j].end_date).ToLocalTime()
            $html += "</td>"
            $html += "<td>"
                $html += $SyncHistory[$j].step_result+"<br/>"
            $html += "</td>"
        $html += "</tr>"
    $html += "</table>"
    $html += "<p>&nbsp;</p>"
    $html += "</body>"
$html += "</html>"
$html | Out-File $filePath"\AADSyncInfo.html"
Remove-Variable html

Azure AD Connect issue listing image

Azure AD Connect Installation that Stopped Working After Reboot

Image of Sander Berkouwer
Sander Berkouwer

Hybrid Identity, the relationship between Active Directory and Azure AD, has benefitted from many...

Read more
Azure AD Connect

A Closer Look at Azure AD Connect – Part 3

Image of Michael Van Horenbeeck MVP, MCSM
Michael Van Horenbeeck MVP, MCSM

Welcome to the third part of this article series about Azure AD Connect. In the previous article, I...

Read more