Tuesday 13 July 2010

SCCM Dashboard Queries 2: Office Apps

So in part 1 I covered 3 basic computer summary views I use.

As in many businesses, my work like to keep an eye on the install count for various Microsoft apps like Office/Visio/Project for license reconciliation.

SCCM has some good reporting but sometimes its better to just give managers an up to date pretty chart or a nice table to look at, thus freeing up your own time to focus on important stuff.

Some of the App views I use in my SCCM Dashbaord are below, these are for Office and Visio/Project versions but can be adapted to any product displayname like Adobe Reader, AutoCAD etc

I normally use a datagrid with the product name and count from these:

View 1: Office Suite Versions
select
DisplayName0,
Count (Distinct arp.ResourceID) as 'Client Count'
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 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 DisplayName0
Order by Count(*) desc


View 2: Visio/Project Version Count

select
DisplayName0,
Count (Distinct arp.ResourceID) as 'Client Count'
From
dbo.v_Add_Remove_Programs ARP
Where
DisplayName0 in ('Microsoft Visio Standard 2010','Microsoft Visio 2010 (Technical Preview)','Microsoft Office Visio Standard 2007','Microsoft Office Visio Standard 2003','Microsoft Office Visio Professional 2007 Trial','Microsoft Office Visio Professional 2007','Microsoft Office Visio Professional 2003','Microsoft Office Visio 2010','Microsoft Office Project Standard 2007 Trial','Microsoft Office Project Standard 2007','Microsoft Office Visio Professional 2007','Microsoft Office Project Professional 2010','Microsoft Office Project Professional 2007 Trial','Microsoft Office Project Professional 2007','Microsoft Office Project Professional 2003','Microsoft Office Visio Professional 2010','Microsoft Office Visio Professional 2010 Trial','Microsoft Office Project Standard 2010')

Group by DisplayName0
Order by Count(*) desc

4 comments:

  1. Many thanks - saved me a bit of typing (and thinking also)

    ReplyDelete
  2. Hi,very good tips is there a way to limit the report to a collection?

    ReplyDelete
  3. Very interesting reading and very useful. Thank you.

    ReplyDelete
  4. If I try using the same query , SCCM tells me it has syntax error, for visio / project

    ReplyDelete