How to determine list of users or groups edited on a given date/time (4379790)

It may be required to determine a list of Active Directory Users or Groups edited during a specific time period.

This can be done by analyzing the JobHistory table. 

If no further jobs have been run (i.e. analyzing the most recent synchronization) - run the analysis based on the XDateUpdated field in the relevant table.

Normally, when investigating a single user or group account, an administrator will drill down and use the timeline view to see the changes over a period of time.

The approach in this article describes how an administrator might determine all the accounts/groups changed on a given date.

The following SQL Queries can help to determine a list of user and groups that were edited on a specific date. Follow the solutions below:

To run the SQL queries, open the ObjectBrowser tool, and launch a SQL/Query > New SQL Window

Part one - Investigating users affected by the most recent synchronization

AD Users updated

The following query will find all users edited on a specific date. By casting from DATETIME to DATE, we get all accounts edited on that day.

-- Set the date value accordingly.
@DateToCheck = 'yyyy-mm-dd' AS DATE
SELECT UID_ADSAccount, cn, DisplayName, UserPrincipalName
FROM ADSAccount
WHERE CONVERT(DATE,XDateUpdated) = @DateToCheck

AD Users updated

Similar query for the AD Group table.

SELECT UID_ADSGroup, cn,SAMAccountName
FROM ASGroup
WHERE CONVERT(DATE,XDateUpdated) = @DateToCheck

Part two - not the most recent sync

When investigating the updates performed by an older sync then it is necessary to examine the JobHistory table.

To start with 

SELECT * FROM JobHistory

Filter by date on the columns StartAt and/or EndedAt
A second filter maybe needed on the Job Type (Column JobChainName )

Once the entries are gathered using the filters above, then copy the column BasisObjectKey to Excel. Use the Excel "TextToColums" feature to split out the GUIDs

The format of the data in this column is

<key>table name</key><p>Guid</p><p>Guid</p> ...

Where "table name" is the name of the table (For example ADSAccount or ADSGroup) and "Guid" is the UID_ADSAccount for ADSAccount, or UID_ADSGroup for the ADSGroup table.