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 – Dashboard for Adobe Reader

Here’s some queries to build a detailed dashboard for adobe reader versions,  which will give you a count by version (Grouping by major version) and detailed information  on computers by version;

SQLQueries

Reference; http://smsug.ca/blogs/garth_jones/archive/2012/05/13/adobe.aspx

Also note that each of these reports can be use without the sccm dashboard accelerator

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 .