SCCM – Audit users account in active directory part 2

How to Build a audit Active Directory Dashboard from the SCCM dashboard

In this post, I Will show you how I’m using SCCM Dashboard to build a audit Dashboard for my Active Directory , this way you’ll get the information from a central location and without the need of creating multiple reports.  To be able to get working, first you’ll need to install the SCCM dashboard;

http://technet.microsoft.com/en-us/library/ff369719.aspx

Then, you must configure your SCCM to import the Users data as I mention in the first part of the Audit Users Account in Active Directory;

https://anotherblabla.wordpress.com/2012/01/31/sccm-audit-users-account-in-active-directory/

if not, you have to configure your SCCM users discovery to get the lastlogontimestamp, pwdlastSet properties and convert it.

Note, in this example, I’m using WSS3.0 SP2 and SCCM 2007 SP2

1- From your SCCM Dashboard, create a new site based on ConfigMgr Dashboard template(from the Site and Workspace menu). Also, for my part, I’ve configure a alias in the DNS (SCCMDashboard), restrict access to HTTPS, and configure the Sharepoint site to use unique permission.

2- From the Sccm Console, create a new report with the following SQL Statement (sccm users discovery Must enable to get the data, also you can add more group name if needed):

SELECT

dbo.v_RA_User_UserGroupName.User_Group_Name0, dbo.v_R_User.User_Name0

FROM dbo.v_RA_User_UserGroupName INNER JOIN

dbo.v_R_User ON dbo.v_RA_User_UserGroupName.ResourceID = dbo.v_R_User.ResourceID

WHERE

(dbo.v_RA_User_UserGroupName.User_Group_Name0 IN (‘SIDBEC\Domain Admins’, ‘SIDBEC\Schema Admins’, ‘SIDBEC\Enterprise Admins’))

ORDER BY dbo.v_RA_User_UserGroupName.User_Group_Name0

3- From the Edit Mode of your Newly created Sharepoint Site, Go to the hidden Microsoft Dashboard Configuration Part,  give them a appropriate Dataset Configuration Name (ex; PrivilegedAccount.xml). Then, configure the Dataset with your Datasource information (SCCM sql server name\instance, Database name, Time Zone.

 Also, in the dataset part you should configure the link to the  new sccm report (create in step 2).

Http://SCCM_reportinig_Server/SMSReporting_001/report.asp?ReportID=(reportID#)

so you will be able to get the detail from the dashdoard;

After, add the SQL Query;

SELECT User_Group_Name0 AS ‘Group Name’,

Count (ResourceID) as ‘Count’

FROM dbo.v_RA_User_UserGroupName

WHERE User_Group_Name0 in (‘SIDBEC\Domain Admins’,’SIDBEC\Schema Admins’,’SIDBEC\Enterprise Admins’)

group by User_Group_Name0

Order By Count(*) desc

Finally, create a data grid by adding the two following column; Group Name – Count, and save the file.

4- Always from the edit mode of your AD Dashboard site, add a new web part, then go to the modify web part menu, select Microsoft Dashboard Viewer and ADD.

In the Modify Web part Menu, select your XML file from the drop down list.  Then,  Apply change … and Voila your first Webpart is create .

Next you must repeat the step 2 to 4 for each Web Part, so  here’s another code which allow you to monitor password age, In my case, all users who haven’t change is password in the last 90 days. (Query is based on a custom create from Part 1, So If you use SCCM to get lastpwdset, you must modify the query)

– SCCM report query;

 SELECT

AccountName AS ‘User Name’,PwdLastSet as ‘Nbs Of Days’, parentcontainer as ‘OU’

FROM  dbo.AM_AD_Users

WHERE PwdLastSet  >= 91 and AccountName not like ‘%$’ and parentcontainer not like ‘%Exchange%’

Order By ParentContainer

Dashboard Query;

SELECT

Count (PwdLastSet) as ‘Count’

FROM  dbo.AM_AD_Users

WHERE PwdLastSet  >= 91 and AccountName not like ‘%$’ and parentcontainer not like ‘%Exchange%’ and IfDisable = ‘False’

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