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;
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;
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):
FROM dbo.v_RA_User_UserGroupName INNER JOIN
dbo.v_R_User ON dbo.v_RA_User_UserGroupName.ResourceID = dbo.v_R_User.ResourceID
(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).
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’
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;
AccountName AS ‘User Name’,PwdLastSet as ‘Nbs Of Days’, parentcontainer as ‘OU’
WHERE PwdLastSet >= 91 and AccountName not like ‘%$’ and parentcontainer not like ‘%Exchange%’
Order By ParentContainer
Count (PwdLastSet) as ‘Count’
WHERE PwdLastSet >= 91 and AccountName not like ‘%$’ and parentcontainer not like ‘%Exchange%’ and IfDisable = ‘False’