AD Group Auditing with Powershell
Here’s another handy Powershell script Ive created. It borrows components from another(credited at the bottom of the post) and expands on the repadmin functionality in the Active Directory Powershell module. The main aim of this is to keep track of members being added or removed to Active Directory groups. There is functionality to do this within AD but if, like myself, you have no control over AD administration this may help you out.
I wanted to keep track of specific groups used for reporting & SharePoint site access that may also require database access. Being notified of additions to these groups by other teams/managers would allow me to identify & fill in missing database perms before they become a problem. Ill append a few example SQL queries as I go, to show how Im using it.
Here’s a quick flow of what Im doing here:
This can be ran regularly on any schedule just through task scheduler. It will always pick up the exact date/time that a user was removed or added to a group so how often you schedule it to run depends on how quickly you want to be notified of a change.
AD Tombstone Lifetime
This script is particularly helpful if you want to regularly report on changes to AD group members outside of AD administration. There is an attribute in AD called the Tombstone Lifetime that will sheer off metadata for groups after a certain time has elapsed. By default this is anywhere between 60 – 180 days. More info can be found here: AD DS: Tombstone Lifetime.
Now, if/when this “TSL” date is reached for a member in a group, the script below will overwrite their record in the table, marking the GroupState as LEGACY (more on this in the reference below) but showing no LastModified date. So if you want to retain these dates and you are unable or reluctant to increase the TSL in AD Id recommend regular outputs or backups of this output table.
The Script
I have commented the script as much as I can but if you have any questions or suggestions, drop me a comment below.
#-----------------------------------------------------------------------
# DATE: 20/09/2016
# AUTHOR: Craig Porteous
# USE: Auditing Active Directory group member changes.
#-------------------------------------------------------------------------------
# Define Variables
#-------------------------------------------------------------------------------
cls
Import-Module ActiveDirectory
$dbServer = 'DATABASE_SERVER'
$database = 'DATABASE_NAME'
$schema = 'SCHEMA'
$sTable = 'AD_Auditsource' # and AD_Auditdata
$oTable = 'AD_Auditdata'
$auditSource = @()
$auditData = @()
<#
--------------------------------------------
# Create table statement for source table.
--------------------------------------------
CREATE TABLE [dbo].[AD_AuditSource](
[ADGroupName] [varchar](100) NOT NULL PRIMARY KEY,
[ADGroupDomain] [varchar](4) NULL
)
GO
--------------------------------------------
#>
#-------------------------------------------------------------------------------
# Function to pull down group audit log
#-------------------------------------------------------------------------------
Function ADGroupAuditHistory {
[OutputType('ActiveDirectory.Group.Info')]
[cmdletbinding()]
Param (
[parameter(ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True,Mandatory=$True)]
[Alias('DistinguishedName')]
[string]$Group,
[parameter()]
[string]$DomainController = ($env:LOGONSERVER -replace "\\\\")
)
Begin {
#RegEx pattern for output
[regex]$pattern = '^(?<State>\w+)\s+member(?:\s(?<DateTime>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})\s+(?:.*\\)?(?<DC>\w+|(?:(?:\w{8}-(?:\w{4}-){3}\w{12})))\s+(?:\d+)\s+(?:\d+)\s+(?<Modified>\d+))?'
}
Process {
If ($Group -notmatch "^CN=.*") {
Write-Verbose "Attempting to get distinguishedname of $Group"
Try {
$distinguishedName = ([adsisearcher]"name=$group").Findone().Properties['distinguishedname'][0]
If (-Not $distinguishedName) {Throw "Fail!"}
} Catch {
Write-Warning "Unable to locate $group"
Break
}
} Else {$distinguishedName = $Group}
Write-Verbose "Distinguished Name is $distinguishedName"
#Use repadmin to show metadata from group.
$data = (repadmin /showobjmeta $DomainController $distinguishedName | Select-String "^\w+\s+member" -Context 2)
ForEach ($rep in $data) {
If ($rep.line -match $pattern) {
#Get User/Group's distinguished Name - used to pull more info on user/group
$subdistinguishedName = [regex]::Matches($rep.context.postcontext,"(?<dName>CN=.*)") | ForEach {$_.Groups['dName'].Value}
#SINGLE DOMAIN:------------------------------
#$domain = $DomainController
#MULTIPLE DOMAINS ONLY:----------------------
#Get User's domain so we search the correct domain in the forest
$dom = [regex]::Matches($rep.context.postcontext,"(?<domain>DC=.*)") | ForEach {$_.Groups['domain'].Value}
#Clean up matched string: "DC=TEST,DC=CONTOSO,DC=COM" -> "TEST.CONTOSO.COM"
$domArray = ($dom).split("="",",6)
$domain = $domArray[1] + "." + $domArray[3] + "." + $domArray[5]
#--------------------------------------------
#TESTING: Show member name. Errors may show for deleted users #Write-Host $subdistinguishedName
$obj = @{
#Using regex to pull out the full name from the distinguishedName string found in the metadata
Fullname = [regex]::Matches($rep.context.postcontext,"CN=(?<Username>.*?)[,]?OU") | ForEach {$_.Groups['Username'].Value}
distinguishedName = $subdistinguishedName
LastModified = If ($matches.DateTime) {[datetime]$matches.DateTime} Else {$Null}
DomainController = $matches.dc
ADGroup = $distinguishedName
State = $matches.state
ModifiedCount = $matches.modified
#Using Get-ADObject to get additional info on user/group. Cant use "adsisearcher" as it doesnt return all properties
AccountName = (Get-ADObject $subdistinguishedName -Server $domain -properties *).sAMAccountName
Class = (Get-ADObject $subdistinguishedName -Server $domain).ObjectClass
}
$obj.pstypenames.insert(0,'ActiveDirectory.Group.Info')
$auditData += New-Object PSObject -Property $obj
}
}
Return $auditData
}
}
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
# Open DB connection to Source SQL table.
#-------------------------------------------------------------------------------
# Set up new connection to DB
$ConnSELECT = New-Object System.Data.SQLClient.SQLConnection
$ConnSELECT.ConnectionString = "server='$DBserver';database='$Database';trusted_connection=true;"
$ConnSELECT.Open()
#Define Query to pull groups from source table
$sqlSELECT = "SELECT [ADGroupDomain],
[ADGroupName]
FROM $database.$schema.$sTable"
$Command = New-Object System.Data.SQLClient.SQLCommand($sqlSELECT, $ConnSELECT)
#Push results to object
$adGroups = $Command.ExecuteReader()
#Adding resultset to object to be added to array
ForEach($row in $adGroups) {
$myobj = @{
ADGroupDomain = $row.Item("ADGroupDomain")
ADGroupName = $row.Item("ADGroupName")
}
#Add each group found in source table to array
$auditSource += New-Object PSObject -Property $myobj
}
$ConnSELECT.Close()
#-------------------------------------------------------------------------------
# Loop through all records in the Source SQL Table
#-------------------------------------------------------------------------------
ForEach ($group in $auditSource){
Write-Verbose "Getting audit data for $group.ADGroupName"
$auditData = @(ADGroupAuditHistory $group.ADGroupName)
}
#-------------------------------------------------------------------------------
#Create DB connection to Insert users into SQL Table. Uses $auditData.
#-------------------------------------------------------------------------------
# Set up new connection to DB for Table INSERT/UPDATE
$ConnINSERT = New-Object System.Data.SQLClient.SQLConnection
$ConnINSERT.ConnectionString = "server='$DBserver';database='$Database';trusted_connection=true;"
$ConnINSERT.Open()
#Check if destination table exists & create if it does not
$sqlINSERT ="IF NOT EXISTS
(SELECT * FROM sys.tables t join sys.schemas s ON (t.schema_id = s.schema_id)
WHERE s.name = '$schema' and t.name = '$oTable')
Begin
CREATE TABLE $database.$schema.$oTable(
[DistinguishedName] [varchar](250) NOT NULL,
[AccountName] [varchar](250) NULL,
[Fullname] [varchar](250) NULL,
[Class] [varchar](50) NULL,
[ADGroup] [varchar](250) NOT NULL,
[GroupState] [varchar](50) NULL,
[LastModified] [datetime] NULL,
[AuditDate] [datetime] NULL,
[ModifiedCount] [int] NULL
CONSTRAINT pk_NameGroup PRIMARY KEY (DistinguishedName,ADGroup)
)
end"
$Command = New-Object System.Data.SQLClient.SQLCommand($sqlINSERT, $ConnINSERT)
#TESTING: Show command that will be executed against DB #$Command.CommandText
$Command.ExecuteNonQuery()
#Clear Variable for re-use
$sqlINSERT = ""
#Step through each record in the result set, check if its already in the table & INSERT if not found, UPDATE if it is.
ForEach($record in $auditData) {
#Need to escape ' characters used in name fields. IE. John O'Brien
$record.DistinguishedName = $record.DistinguishedName.replace("'","''")
$record.Fullname = $record.Fullname.replace("'","''")
#Remove \ that appears in full name (as it is built from distinguishedName)
$record.Fullname = $record.Fullname.replace("\","")
#Build SQL Query
$sqlINSERT ="
if not exists (select 1 from $database.$schema.$oTable where [DistinguishedName] = '$($record.DistinguishedName)' AND [ADGroup] = '$($record.ADGroup)')
begin
INSERT INTO $database.$schema.$oTable([DistinguishedName], [AccountName], [Fullname], [Class], [ADGroup], [GroupState], [LastModified], [AuditDate], [ModifiedCount])
SELECT
'$($record.DistinguishedName)',
'$($record.AccountName)',
'$($record.Fullname)',
'$($record.Class)',
'$($record.ADGroup)',
'$($record.State)',
'$($record.LastModified)',
Getdate(),
'$($record.ModifiedCount)'
end
else if exists (select 1 from $database.$schema.$oTable where [DistinguishedName] = '$($record.distinguishedName)' AND [ADGroup] = '$($record.ADGroup)' AND [LastModified] <> '$($record.LastModified)')
begin
UPDATE $database.$schema.$oTable
SET
GroupState = '$($record.State)',
LastModified = '$($record.LastModified)',
AuditDate = Getdate(),
ModifiedCount = '$($record.ModifiedCount)'
WHERE [DistinguishedName] = '$($record.distinguishedName)'
AND [ADGroup] = '$($record.ADGroup)'
end"
$Command = New-Object System.Data.SQLClient.SQLCommand($sqlINSERT, $ConnINSERT)
#TESTING: Show command that will be executed against DB #$Command.CommandText
$numROWS = $Command.ExecuteNonQuery()
}
$ConnINSERT.Close()
#-------------------------------------------------------------------------------
# End of script.
#-------------------------------------------------------------------------------
The Output
From this table you can see the member’s details. The 4 columns Ive not obscured show the current state of that member within that group. The columns are summarised below.
GroupState
- The options being PRESENT, ABSENT & LEGACY (More on this in the reference link below).
LastModified
- This is the date of the last change for that member (in that group). As you can see we are either NOT enforcing a TSL date or it has been extended well beyond the default. 6 years+
AuditDate
- This is the date I append in the script to keep track of when the record was last updated in this output table. In its present state, this will only update on a record when it has changed state. This logic can be changed by simply removing the following from the WHERE Clause on line 199.
AND [LastModified] <> '$($record.LastModified)'
ModifiedCount
- This is the number of times the user has been added/removed from the group. IE. a count of 4 would mean they have been Added > Removed > Added > Removed & the status should show as Absent.
Example Queries
- Members Added to groups in the past week
SELECT [DistinguishedName]
,[AccountName]
,[Fullname]
,[Class]
,[ADGroup]
,[GroupState]
,[LastModified]
,[AuditDate]
,[ModifiedCount]
FROM [DATABASE].[SCHEMA].[AD_Auditdata]
WHERE LastModified >= GetDate()-7
AND GroupState = 'PRESENT'
Simlarly, you could look at recently removed users by changing the GroupState in the WHERE clause to ‘ABSENT’
- Last Audit Date with changes
SELECT MAX([AuditDate]) FROM [DATABASE].[SCHEMA].[AD_Auditdata]
More to follow…
References
To pull the metadata for the AD groups I used the script at the following site. It was then changed & edited to include more information on the members. There’s a great explanation of the script & the metadata over there too.
To check & build the regular expressions used to pull parts of the metadata out I used regex101. It was really handy for this as Im not too familiar with regex.
