SCCM – Audit users account in active directory

In some situations, you could be asked to provide reports about inactive accounts, validate the use of certain accounts, provide password information for specific account or all user in a specific OU, etc.. so here ‘s the method that I used. I know that you can add custom attribut from SCCM discovery and get almost the same result, but For my part I prefer this method because the conversion of the attributes is done by the powershell script…and I find it easier than using SQL conversion

First, it will require a powershell script (to export the data from AD) and a SQL Server agent Job (to import data in SCCM). Also This will give you the ability to use SCCM reporting point to access report from a web page and easily delegate access, more over you’ll be able to use SQL queries to filter the out put of the data.

So, Before you begin, you must have the tools of Quest ActiveRoles Management Command for active directory to install (Probably the powershell module for AD work, but I was not available for me).

Then, once the quest tools are installed, create the following script and create a daily schedule task with it (in my case I used a management server with all my tools installed on and a service account (no interactive logon) for this procedure) ;


Add-PSSnapin quest.activeroles.admanagement

$FilePath = \\UNC\adusers.csv

Get-QADUser -SearchRoot “OU PATH” -IncludedProperties  employeeID -sizeLimit 0 | Select LogonName,FirstName,LastName,@{n=”PasswordLastSet”;e={((get-date)- $_.PasswordLastSet).days}}, `


@{n=”whenCreated”;e={((get-date)- $_.whenCreated).days}},`

@{n=”LastLogonTimeStamp”;e={((get-date)- $_.lastLogonTimestamp).days}},`

AccountISDisabled,PasswordNeverExpires `

,accountexpires | export-Csv $FilePath -noType -Encoding unicode

(gc $FilePath) | % {$_ -replace ‘”‘, “”} | out-file $FilePath -Fo -En unicode

(gc $FilePath) | where {$_.readcount -gt 1} | Set-Content $FilePath

– you could also change the share permission to configure the appropriate permission, in my case only the service account,domain admin
Next, create a job with the SQL Server Agent to import the data to a new table in the BD of SCCM, here is the SQL script that I use;


IF EXISTS(SELECT name FROM sys.tables WHERE name = ‘AD_Users’)


CREATE TABLE AD_Users (AccountName varchar(128),FirsrName varchar(128),LastName varchar(128),PwdLastSet int,ParentContainer varchar(512), EmployeeID int, whenCreated varchar(64),LastLogontimestamp int,IfDisable varchar(64),IfPwdExpired varchar(64),AccountExpired varchar (124))


INSERT dbo.AD_Users

FROM ‘  FILE  Created by the script  ‘








ON dbo.AD_Users

TO [webreport_approle]

– To validate that the table has import the data correctly , test it with this query Select * from dbo.AD_Users
– Also important to note that the data is overwrite, so only a snapshot of the current state of the AD

Finally, once you get the data in SCCM, you’ll be able to create reports according to criteria like ;

– Users that have not login for x number of days
– Account with password never has expired
– Active account with a password age of x number of days



AccountName as “Account Name”, FirsrName as “First Name”, LastName as “Last Name”, PwdLastSet as “Last Password Set”, ParentContainer as “OU”, LastLogin as “Last Login” , IfDisable as “Account Disable”, IfPwdExpired as “Password Never Expired”, AccountExpired as “Account Expired Date”,WhenCreate AS “Created On”

FROM  dbo.AD_Users

where  (ParentContainer like @variable

or AccountName like @users)

Order by ParentContainer, AccountName


Name : Variable

Text : OU Name


if (@__filterwildcard = ”)

SELECT DISTINCT ParentContainer from dbo.AD_Users Order by ParentContainer


SELECT DISTINCT ParentContainer from dbo.AD_Users

WHERE ParentContainer like @__filterwildcard

Order by ParentContainer


Name : users

Text : User Name


if (@__filterwildcard = ”)

SELECT DISTINCT AccountName from dbo.AD_Users Order by AccountName


SELECT DISTINCT AccountName from dbo.AD_Users

WHERE AccountName like @__filterwildcard

Order by AccountName



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s