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′)

Advertisements

One thought on “SCCM – Building a Software Inventory Dashboard part 2

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