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}}, `

ParentContainer,employeeID,`

@{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’)

BEGIN DROP TABLE AD_Users END

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))

BULK

INSERT dbo.AD_Users

FROM ‘  FILE  Created by the script  ‘

WITH

(

FIELDTERMINATOR = ‘,’,

ROWTERMINATOR = ‘\n’

)

GO

GRANT SELECT

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

Example:

SELECT

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

PROMPT

Name : Variable

Text : OU Name

Begin

if (@__filterwildcard = ”)

SELECT DISTINCT ParentContainer from dbo.AD_Users Order by ParentContainer

else

SELECT DISTINCT ParentContainer from dbo.AD_Users

WHERE ParentContainer like @__filterwildcard

Order by ParentContainer

End

Name : users

Text : User Name

Begin

if (@__filterwildcard = ”)

SELECT DISTINCT AccountName from dbo.AD_Users Order by AccountName

else

SELECT DISTINCT AccountName from dbo.AD_Users

WHERE AccountName like @__filterwildcard

Order by AccountName

end

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s