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"