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"

	"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

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
	$state | export-csv $statefile -notype
"Errors logged: $errorcount"

Keywords:AADSync DirSync FIM MIM PowerShell SQL   Doc ID:45953
Owner:Tony B.Group:Middleware
Created:2015-01-06 11:03 CDTUpdated:2015-01-09 14:28 CDT
Feedback:  2   0