SCCM – Client health report for a specific computer

*** Update August 28: I have updated the RDL file and added the information for CM client 1606 . I’ve also added the required permission for the datasource***

In this post I will share with you a report that I’ve build to get all the information required for a specific computer. This report is divided in 4 different tables and here are the details;

  1. Operating System, in this table you will get all the information about the OS, such as display name, last reboot, etc. Specific_Comp_1
  2. Configuration Manager Client & AD, well this is a very interesting table that contains a lot of information, so in this table you get all the information about CM and AD sites, as well as all the information about the inventories,discoveries,policy request, MP, SUP, evaluation  and lastly the information about the collections, which can be expand to get the collection name. Specific_Comp_3
  3. System Information, in this table you will get all the information about the system, like computer Name & AD DN, Network, System drive, CM User Affinity and the next effective maintenance window.Specific_Comp_2
  4. Software Updates Information, in this last table you’ll get all the information about software updates (can be filter on vendors and classifications). Again, you’ll get information about the level of compliance, WUA version, missing updates and all the detail about the missing updates, like if they are targeted or not, etc.Specific_Comp_4

Alright, so now we need to discuss about requirements. First you need to add the Managed By attribute to your AD system discovery in configuration manager. Then you will also need to add the following properties to your hardware inventory, Logical_Disk — Free Space (MB) and Services — State, and finally but not the least you will need to create the following SQL function, Report for upcoming maintenance window.

Once the SQL function is created, you’ll now have to modify the SQL permissions (unless the reporting point service account already have db_datareader permission on the CM DB) and grant at least the Select permission on the 2 following SQL objects to your reporting service point account:

DS_Permission

So here the link to get the RDL file: Client Health – Specific Computer

Specific_Comp_5

Enjoy.

SCCM – Build Active Directory Dashboard from CM Discovery

In this post I will show you how you can use informations from sccm discoveries to build a dashboard for Active Directory users states. This can be helpfull, for active directory auditing purpose or for identity management. Before you can proceed, you must have the sccm dashboard installed, a linked server in SQL, which points to one of your DC and a sccm users discoveries enable.

Ok so Here’s an overview of the dashboard;

Main dashboard view

sample of Detailled report

Now let’s move on with the steps

1- Go to your sccm dashboard site and from the edit mode,  Enter a Dataset Configuration name(UserAccountStates.xml) then configurre the data refresh rate, database Server Name\instance, database name (your sccm database name), time zone.

2- Copy/paste the following query; SQLQuery

3- Validate the query, then create a column charts named User Accounts status

4- From the data grid section, add a column for each of those who are available.

5- Repeat these steps for all the web parts you want to add in your dashboard, based on these queries

SQL Queries

SCCM – Building a Software Inventory Dashboard part 2

Here’s some of the reports I’ve create to build my software inventory Dashboard, https://anotherblabla.wordpress.com/2012/02/20/sccm-building-a-software-inventory-dashboard/, also if needed you can use these reports in sccm only.

1- Create a report called Detail By Version, later we will linked this reports to all of our software count reports and it will allow us to get the detail about the device on which the application is installed (Ex, pc name, last user logged on, free space, pc model, etc)

Sql Query;

*********************************************************

SELECT Distinct

rs.Netbios_Name0 AS ‘PC Name’, DisplayName0 AS ‘Product Version’,

Case When cpt.UserName0 is Null Then ‘Unkown’ Else cpt.UserName0 End AS ‘Last Logon Name’,

ld.size0 as ‘Disk Size’, ld.FreeSpace0*100/ld.Size0  as ‘% Free Disk Space’, csys.Model0 as ‘Computer Model’

FROM

v_Add_Remove_Programs arp

Join v_R_System rs ON rs.ResourceID = arp.ResourceID

Join v_GS_COMPUTER_SYSTEM cpt ON cpt.ResourceID = arp.ResourceID

Join v_GS_LOGICAL_DISK ld on ld.ResourceID = arp.ResourceID

Join v_GS_COMPUTER_SYSTEM csys on csys.ResourceID = arp.ResourceID

WHERE

DisplayName0 like @version and ld.DeviceID0 = ‘C:’

ORDER BY Netbios_Name0

*********************************************************

Prompt;

Name = version

Sql query

*********************************************************

Begin

if (@__filterwildcard = ”)

SELECT DISTINCT DisplayName0 from v_Add_Remove_Programs

Order by DisplayName0

else

SELECT DISTINCT DisplayName0 from v_Add_Remove_Programs

WHERE DisplayName0 like @__filterwildcard

Order by DisplayName0

end

*********************************************************

2- Then, create reports to count the number of instances based on software you want to monitor and then use the report link to link them to the report Detail By Version .

– Sccm report to count numbers of Java by version

*********************************************************

Sql query ;

SELECT

‘This report contain the Numbers of Java Installed –

If You want to get detail by version, click on the arrow   ‘ AS Report

SELECT

DisplayName0,

Count (Distinct v_R_System.ResourceID) as ‘Client Count’

FROM

dbo.v_Add_Remove_Programs INNER JOIN

dbo.v_R_System ON dbo.v_Add_Remove_Programs.ResourceID = dbo.v_R_System.ResourceID

WHERE

(dbo.v_Add_Remove_Programs.DisplayName0 LIKE ‘%java%’) AND (dbo.v_Add_Remove_Programs.DisplayName0 NOT LIKE ‘%Microsoft%’) AND (dbo.v_Add_Remove_Programs.DisplayName0 NOT LIKE ‘%Updater%’)

Group By DisplayName0

Order By Count(*) desc

Select

‘These Sections return the details on – Total Count of Java Installed’ AS Report

Select

Count (v_R_System.ResourceID) AS ‘Total Count’

FROM

dbo.v_Add_Remove_Programs INNER JOIN

dbo.v_R_System ON dbo.v_Add_Remove_Programs.ResourceID = dbo.v_R_System.ResourceID

WHERE

(dbo.v_Add_Remove_Programs.DisplayName0 LIKE ‘%java%’) AND (dbo.v_Add_Remove_Programs.DisplayName0 NOT LIKE ‘%Microsoft%’)

AND (dbo.v_Add_Remove_Programs.DisplayName0 NOT LIKE ‘%Updater%’)

*********************************************************

– Sccm report to count number of flash player by version;

*********************************************************

Sql Query

SELECT

‘This report contain the Numbers of Adobe Acrobat Installed –

If You want to get detail by version, click on the arrow   ‘ AS Report

SELECT

DisplayName0,

Count (Distinct v_R_System.ResourceID) as ‘Client Count’

FROM

dbo.v_Add_Remove_Programs INNER JOIN

dbo.v_R_System ON dbo.v_Add_Remove_Programs.ResourceID = dbo.v_R_System.ResourceID

WHERE     (dbo.v_Add_Remove_Programs.DisplayName0 LIKE ‘%dobe%Flash%’)

Group By DisplayName0

Order By Count(*) desc

Select

‘These Sections return the details on – Total Count of Adobe Flash Installed ‘ AS Report

Select

Count (v_R_System.ResourceID) AS ‘Total Count’

FROM

dbo.v_Add_Remove_Programs INNER JOIN

dbo.v_R_System ON dbo.v_Add_Remove_Programs.ResourceID = dbo.v_R_System.ResourceID

WHERE     (dbo.v_Add_Remove_Programs.DisplayName0 LIKE ‘%dobe%Flash%’)

*********************************************************

– Sccm report to count number of office suite by version (note, this report use the table v_GS_INSTALLED_SOFTWARE, if needed you can change it for add_remove_Programs)

*********************************************************

Sql Query

SELECT

‘This report contain the Numbers of Office Suite Installed – If You want to get detail by version, click on the arrow   ‘ AS Report

select

ProductName0,

Count (Distinct ResourceID) as ‘Client Count’

From

dbo.v_GS_INSTALLED_SOFTWARE

Where

ProductName0 in ( ‘Microsoft Office 97, Professional Edition’,’Microsoft Office 97 Professional’, ‘Microsoft Office Professional Edition 2003′,’Microsoft Office Standard Edition 2003′,’Microsoft Office Enterprise 2007′,’Microsoft Office Standard 2007′,’Microsoft Office Professional Plus 2007′,’Microsoft Office 2000 SR-1 Professional’,’Microsoft Office 2000 SR-1 Standard’,’Microsoft Office Professional Plus 2010′,’Microsoft Office Standard 2010′,’Microsoft Office Enterprise 2007′)

Group by ProductName0

Order by Count(*) desc

Select

Count (ResourceID) AS ‘Total Count’

FROM  dbo.v_GS_INSTALLED_SOFTWARE

Where

ProductName0 in ( ‘Microsoft Office 97, Professional Edition’,’Microsoft Office 97 Professional’, ‘Microsoft Office Professional Edition 2003′,’Microsoft Office Standard Edition 2003′,’Microsoft Office Enterprise 2007′,’Microsoft Office Standard 2007′,’Microsoft Office Professional Plus 2007′,’Microsoft Office 2000 SR-1 Professional’,’Microsoft Office 2000 SR-1 Standard’,’Microsoft Office Professional Plus 2010′,’Microsoft Office Standard 2010′,’Microsoft Office Enterprise 2007′)

SCCM – Building a Software Inventory Dashboard

Recently I was ask by a client to provide him a inventory software from Sccm database from where he can get the information about all the softwares installed on their workstation (including Microsoft office, project, visio, adobe, java and more). So, to help me to determine which applications to include in this dashboard, I have use the Asset Intelligence reports and get the top 20 applications as well as all softwares who require’s licence. Also, Note that in this example I’ve use the SCCM Dashboard (Free Download) with WSS 3.0, which must be fonctional before proceed

Main View, from where you can use the “Report” Link to drill down to the following Report

Here’s Another report, which is linked to the previous one , from where you can get the detail information by version.

So, In this post I will show you How I have build a Detailled Software Dashboard.

1- Create SCCM reports that we will use in the Dashboard to allow us link the appropriate information. Here’s some Query for Acrobat (You may have to change it, base on your infrastructure).

Report Name; ApplicationName – Count Version

##########################################

SELECT
‘This report contain the Numbers of Adobe Acrobat Installed –
If You want to get detail by version, click on the arrow ‘ AS Report

select

ProductName0,
Count (Distinct ResourceID) as ‘Client Count’
From
dbo.v_GS_INSTALLED_SOFTWARE
Where
ProductName0 in
(‘Adobe Acrobat 4.0’,
‘Adobe Acrobat 5.0’,
‘Adobe Acrobat 6.0 Professional – English, Français, Deutsch’,
‘Adobe Acrobat 6.0.1 Professional’,
‘Adobe Acrobat 7.0 Professional – English, Français, Deutsch’,
‘Adobe Acrobat 7.0 Standard’,
‘Adobe Acrobat 7.0 Standard – English, Français, Deutsch’,
‘Adobe Acrobat 8 Standard – English, Français, Deutsch’,
‘Adobe Acrobat 9 Standard – English, Français, Deutsch’,
‘Adobe Acrobat 9 Pro – English, Français, Deutsch’,
‘Adobe Acrobat X Standard – English, Français, Deutsch’)
Group by ProductName0
Order by Count(*) desc
Select
‘These Sections return the details on – Total Count of Adobe Acrobat Installed in Sidbec Site and the number of Support/Unsupport Version Based on- http://www.adobe.com/support/programs/policies/supported.html Note that you cannot get the detail for this section’ AS Report

Select

Count (ResourceID) AS ‘Total Count’
FROM dbo.v_GS_INSTALLED_SOFTWARE
Where
ProductName0 in
(‘Adobe Acrobat 4.0’,
‘Adobe Acrobat 5.0’,
‘Adobe Acrobat 6.0 Professional – English, Français, Deutsch’,
‘Adobe Acrobat 6.0.1 Professional’,
‘Adobe Acrobat 7.0 Professional – English, Français, Deutsch’,
‘Adobe Acrobat 7.0 Standard’,
‘Adobe Acrobat 7.0 Standard – English, Français, Deutsch’,
‘Adobe Acrobat 8 Standard – English, Français, Deutsch’,
‘Adobe Acrobat 9 Standard – English, Français, Deutsch’,
‘Adobe Acrobat 9 Pro – English, Français, Deutsch’,
‘Adobe Acrobat X Standard – English, Français, Deutsch’)

Select
Count (ResourceID) AS ‘Supported Version’
FROM dbo.v_GS_INSTALLED_SOFTWARE
Where
ProductName0 in
(‘Adobe Acrobat 9 Standard – English, Français, Deutsch’,
‘Adobe Acrobat 9 Pro – English, Français, Deutsch’,
‘Adobe Acrobat X Standard – English, Français, Deutsch’
)

Select
Count (ResourceID) AS ‘Unsupported Version’
FROM dbo.v_GS_INSTALLED_SOFTWARE
WHERE ProductName0 IN
(‘Adobe Acrobat 4.0’,
‘Adobe Acrobat 5.0’,
‘Adobe Acrobat 6.0 Professional – English, Français, Deutsch’,
‘Adobe Acrobat 6.0.1 Professional’,
‘Adobe Acrobat 7.0 Professional – English, Français, Deutsch’,
‘Adobe Acrobat 7.0 Standard’,
‘Adobe Acrobat 7.0 Standard – English, Français, Deutsch’,
‘Adobe Acrobat 8 Standard – English, Français, Deutsch’
)

##########################################

Also, If you cannot use the v_GS_INSTALLED_SOFTWARE , there is possible to use the  Add_remove_program view.

After the first report his created,  create another report such as ApplicationName – Detail By Version;

SQL Report Query By Version

##########################################

SELECT dbo.v_GS_INSTALLED_SOFTWARE.ProductName0 AS “Adobe Acrobat Version”, dbo.v_R_System.Netbios_Name0 AS “PC Name”, dbo.v_R_System.User_Name0 AS “Last Logon Name”
FROM
dbo.v_GS_INSTALLED_SOFTWARE INNER JOIN
dbo.v_R_System ON dbo.v_GS_INSTALLED_SOFTWARE.ResourceID = dbo.v_R_System.ResourceID
WHERE
ProductName0 = @version
ORDER BY Netbios_Name0

##########################################

Then configure a link on the first report, toward to this one

 

 

 

 

 

 

 

 

 

 

 

 

2- Open the Sccm dashboard web page and go to the edit mode. Create a new dataset and configure the datasource information, add  the following SQL Query, configure the Hyperlink URL to point to the report – ApplicationName – Count Version.

##########################################

select
ProductName0,
Count (Distinct ResourceID) as ‘Client Count’
From
dbo.v_GS_INSTALLED_SOFTWARE
Where
ProductName0 in
(‘Adobe Acrobat 4.0’,
‘Adobe Acrobat 5.0’,
‘Adobe Acrobat 6.0 Professional – English, Français, Deutsch’,
‘Adobe Acrobat 6.0.1 Professional’,
‘Adobe Acrobat 7.0 Professional – English, Français, Deutsch’,
‘Adobe Acrobat 7.0 Standard’,
‘Adobe Acrobat 7.0 Standard – English, Français, Deutsch’,
‘Adobe Acrobat 8 Standard – English, Français, Deutsch’,
‘Adobe Acrobat 9 Standard – English, Français, Deutsch’,
‘Adobe Acrobat 9 Pro – English, Français, Deutsch’,
‘Adobe Acrobat X Standard – English, Français, Deutsch’)
Group by ProductName0
Order by Count(*) desc

##########################################

HyperlinkUrl, must point to the Sccm reporting point URL with the appropriate Report ID

Then, add a Pie Chart and a DataGrid

3- Finally, always from the edit mode, 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 .

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’