Azure AD Connect Installation that Stopped Working After Reboot
Hybrid Identity, the relationship between Active Directory and Azure AD, has benefitted from many...
Azure AD Sync Tool HTML Report
Michael Van Horenbeeck MVP, MCSM
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:
- Microsoft® System CLR Types for Microsoft® SQL Server® 2012
- Microsoft® SQL Server® 2012 Shared Management Objects
- *Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012
Get-Module -ListAvailableOnce 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:
|
<#
Description:
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
mikecrowley.us
Modified by Michael Van Horenbeeck on October 21, 2014
Example:
GetAADSyncInfo.ps1 -filePath c:\temp\
This will generate an HTML file called "AADSyncInfo.html" in the folder c:\temp
Changes:
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.
#>
[CmdletBinding()]
[OutputType([int])]
Param
(
#Specify the report file path
[Parameter(Mandatory=$true,
ValueFromPipelineByPropertyName=$true,
Position=0)]
[Alias("ReportPath")]
[ValidateNotNullOrEmpty()]
$filePath
)
#Functions
Function check-even ($num) {[bool]!($num%2)}
#Console Prep
cls
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 "http://technet.microsoft.com/en-us/library/hh231683.aspx" -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'}
$individualADMAgent=@()
$MaName=@()
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
cls
#HTML HEADERS
$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> </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'
$i=0
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
$i++
}
$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> </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> </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> </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> </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"
}
else{
$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> </p>"
$html += "</body>"
$html += "</html>"
$filePath.TrimEnd("\")
$html | Out-File $filePath"\AADSyncInfo.html"
Remove-Variable html
|
Michael Van Horenbeeck MVP, MCSM
Michael Van Horenbeeck is a Microsoft Certified Solutions Master (MCSM) and Exchange Server MVP from Belgium, with a strong focus on Microsoft Exchange, Office 365, Active Directory, and a bit of Lync. Michael has been active in the industry for about 12 years and developed a love for Exchange back in 2000. He is a frequent blogger and a member of the Belgian Unified Communications User Group Pro-Exchange. Besides writing about technology, Michael is a regular contributor to The UC Architects podcast and speaker at various conferences around the world.
