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 .

Advertisements

2 thoughts on “SCCM – Building a Software Inventory Dashboard

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