AADSync PowerShell SQL Query to Monitor Object Changes
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 have had an update to a user object. It outputs upn and attribute name, as well as the times involved. Reference KB 46071 for shared configuration.
Reference for shared configuration.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
. .\uw-shared-config.ps1$statefile = $scriptpath + "schedtasks\dbquery-updatedaccounts-state.csv"if (!(test-path $statefile)){ $csv=New-Object PSObject -Property @{lastrun=(get-date);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)-1$pestermehours = 1[System.Int32]$errorcount = $state.errorcount"Last run time: $lastrun"$ErrorActionPreference = "Stop"trap{ "Sending error email" $Body = ($_ | out-string) $Subject = "Error in dbquery-updatedaccounts.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 d.userPrincipalName, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), a.start_date) AS start_date,e.attribute_name, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), e.lineage_date) AS attrib_change_date,e.string_value_indexable FROM [ADSync].[dbo].[mms_step_history] AS a JOIN [ADSync].[dbo].[mms_step_object_details] AS b ON (a.step_history_id = b.step_history_id) JOIN [ADSync].[dbo].[mms_csmv_link] AS c ON (b.cs_object_id = c.cs_object_id) JOIN [ADSync].[dbo].[mms_metaverse] AS d ON (c.mv_object_id = d.object_id) JOIN [ADSync].[dbo].[mms_metaverse_multivalue] AS e ON (d.object_id = e.object_id) WHERE a.export_update != 0 and a.start_date >= dateadd(mi,$minutestocheck,getutcdate()) order by a.start_date" $table = DBQuery $query if (($table | measure-object).count -gt 0) { $Body = ($table | out-string) $Body += "As of " + (get-date -format yyyyMMdd-HHmmss) $Subject = "$hostname user modified" Send-MailMessage -Body $Body -Subject $Subject -From $fromEmail -to $toEmail -SmtpServer $EmailServer $state.lastemailtime = ((get-date)) } else {"Query result empty."} $connection.Close() $state.lastrun = (get-date) $state | export-csv $statefile -notype} |