AADSync PowerShell sql query to monitor job status exceptions

As part of our Office365 Directory Synchronization project we have written several scripts to monitor the integrity of our AADSync systems. The script below queries the ADSync database based on a time defined in a state file csv to look for any job statuses that do not equal 'success'. Reference KB 46071 for shared configuration
Reference https://kb.wisc.edu/kbAdmin/document.php?group_id=12&id=46071 for shared configuration. . .\uw-shared-config.ps1 $statefile = $scriptpath + "schedtasks\dbquery-jobstatus-state.csv" if (!(test-path $statefile)){ $csv=New-Object PSObject -Property @{lastrun=(get-date);errorcount='0';lastemailtime=((get-date).adddays(-1))} $csv | export-csv $statefile -notype } $state = import-csv $statefile $lastrun = $state.lastrun $StartDate = (get-date) $minutestocheck = ((New-timespan -start $StartDate -end $lastrun).totalminutes) $pestermehours = 1 $nextemailtime = (($state.lastemailtime | get-date).addhours($pestermehours)) [System.Int32]$errorcount = $state.errorcount "Last run time: $lastrun" $ErrorActionPreference = "Stop" trap{ "Sending error email" $Body = ($_ | out-string) $Subject = "Error in dbquery-jobstatus.ps1 on $hostname at " + (get-date -format yyyyMMdd-HHmmss) Send-MailMessage -Body $Body -Subject $Subject -From $fromEmail -to $toEmail -SmtpServer $EmailServer break } $connection.Open() if ($connection.State -eq 'Open') { "Opened successfully." $query = "SELECT a.[step_result],DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), a.start_date) AS start_date,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), a.end_date) AS end_date,a.[step_xml] FROM [ADSync].[dbo].[mms_step_history] AS a WHERE a.step_result != 'success' and a.start_date >= dateadd(mi,$minutestocheck,getutcdate()) order by a.start_date" $table = DBQuery $query if ((($table | measure-object).count -eq 0) -and ($errorcount -eq 0)){ "Query result empty and all clear" $errorcount = 0 $state.lastrun = (get-date) $state.errorcount = $errorcount } if (($table | measure-object).count -gt 0) { "step_result != success" if ($nextemailtime -le $StartDate){ "Sending email" $Body = ($table | out-string) $Body += "As of " + (get-date -format yyyyMMdd-HHmmss) $Subject = "$hostname step_result != 'success'" Send-MailMessage -Body $Body -Subject $Subject -From $fromEmail -to $toEmail -SmtpServer $EmailServer $state.lastemailtime = (get-date) } else {"Less than pester time, will not email until $nextemailtime"} $errorcount = $errorcount + 1 $state.lastrun = (get-date) $state.errorcount = $errorcount } if ((($table | measure-object).count -eq 0) -and ($errorcount -ne 0)){ "Query result empty, sending all clear" $Body = "All clear as of " + (get-date -format yyyyMMdd-HHmmss) $Subject = "$hostname step_result all clear" Send-MailMessage -Body $Body -Subject $Subject -From $fromEmail -to $toEmail -SmtpServer $EmailServer $errorcount = 0 $state.lastemailtime = ((get-date).adddays(-1)) $state.lastrun = (get-date) $state.errorcount = $errorcount } $connection.Close() $state | export-csv $statefile -notype } "Errors logged: $errorcount"


KeywordsAADSync DirSync FIM MIM PowerShell SQL   Doc ID45953
OwnerMST SupportGroupIdentity and Access Management
Created2015-01-06 11:03:27Updated2022-05-25 15:13:52
SitesIdentity and Access Management
Feedback  0   0