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
}


Keywords:
AADSync DirSync FIM MIM PowerShell SQL MSOL o365 Office 365 Active Directory ActiveDirectory ILM MIIS 
Doc ID:
45954
Owned by:
MST Support in Identity and Access Management
Created:
2015-01-06
Updated:
2023-08-11
Sites:
Identity and Access Management