SCCM Report : Count of all instances of software registered with Add or Remove Programs that you want to list

April 13, 2010 at 6:32 pm in Uncategorized by Kenny Buntinx [MVP]

Did you ever want to list your software counts for your licenses. Did management ever requested a report like that. Well below you will find the query code to do that . I am pretty sure there are still better ways to do it , but hey I am a novice in SQL reporting …When I have figured out a better way , because it is really static right now , I’ll post it anyhow .

*************The query *************

select
    DisplayName0,
    Version0,
    Count (Distinct arp.ResourceID)
From
    dbo.v_Add_Remove_Programs ARP
Where
    DisplayName0 in ( ‘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 Project Professional 2003′,’Microsoft Office Project Standard 2003′,’Microsoft Office Visio Professional 2003′,’Microsoft Office Visio Professional 2007′,’Microsoft Office Visio Standard 2003′,’Microsoft Office Visio Standard 2007′,’Microsoft Visual Studio 2005′,’Microsoft Visual Studio 2008′,’Microsoft Visual Studio 2005 Professional Edition – ENU’,’Microsoft Visual Studio 2005 Team Suite – ENU’,’Microsoft Visual Studio 2008 Professional Edition – ENU’,’ Microsoft Visual Studio 6.0 Enterprise Edition’,’Microsoft SQL Server 2005′,’Microsoft SQL Server 2000′,’Microsoft Project 2000 SR-1′,’Microsoft Project 2000′,’Microsoft Office 2000 SR-1 Professional’,’Microsoft Office 2000 SR-1 Standard’,’Microsoft Exchange’)

Group by
    DisplayName0,
    Version0
Order by
    DisplayName0,
    Version0

*************The query *************

The outcome of the report ;

image

Hope it Helps ,

Kenny Buntinx

Tweet about this on TwitterShare on FacebookShare on Google+Share on LinkedInPin on Pinterest