Video Screencast Help
Protect Your POS Environment Against Retail Data Breaches. Learn More.

A "Simple" SQL Report for Reporting on Microsoft Office Installed Versions

Created: 01 Oct 2010 • Updated: 01 Oct 2010 | 4 comments
Language Translations
carubin's picture
+2 2 Votes
Login to vote

Altiris has been great for us.  It’s taken a chaotic world of hardware and software spread over many sites and business units and allowed us to report and categorize our data in all kinds of meaningful ways for our management. 

A little information can be a dangerous thing and for every report we produce our management has raised more questions and wanted to slice the data in another way. 

The biggest questions seem to revolve around software, just what do we have and what version.  This would seem to be a trivial request but the software vendors seem to like to name their products in ways that are deceptive and not uninstall older entries in Add/Remove Programs when installing updates or service packs.  My understanding is that Altiris 7 does a better job of this with their software catalog but we are not ready to go down that “rabbit hole” quite yet.

So in the nature of contributing to the common good I’m going to help you all that are grappling with a similar issue to produce that simple report for Microsoft Office. 

Create a new report and paste the SQL code below for a simple (yet elegant) report on all “known” Microsoft Office versions that culls out the junk and leaves the good stuff:

SELECT AeXInv_AeX_OS_Add_Remove_Programs.Name, Count(*) AS Total

FROM AeXInv_AeX_OS_Add_Remove_Programs

WHERE (((AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Service%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Like 'Microsoft Office%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Security%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Project%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Visio%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Disc 2%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Communicator%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Viewer%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Visual%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Resource%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Share%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Publisher%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%proof%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Powerpoint%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%outlook%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%onenote%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%live%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%infopath%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%frontpage%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%converter%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%access%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%web%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%excel%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%groove%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%word%' And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%interop%') AND ((AeXInv_AeX_OS_Add_Remove_Programs.Publisher) Like 'Microsoft%'))

GROUP BY AeXInv_AeX_OS_Add_Remove_Programs.Name

HAVING (((Count(*))>10))

ORDER BY AeXInv_AeX_OS_Add_Remove_Programs.Name;

 Good hunting!

Comments 4 CommentsJump to latest comment

yabru's picture

I use something very similar, although with upgrades to Office 2010 looming we add RAM and Service Pack columns to ours. (Office 2010 requires WinXp SPK3)

 

 

Select Distinct
  vComputer.Name As [Computer Name],
  vUser.[Display Name],
  vUser.[Office Location] As Location,
  vUser.Department As Department,
  vComputer.[OS Name] As OS,
  Inv_AeX_AC_Identification.[OS Revision],
  vHWComputerSystem.[Total Physical Memory (Bytes)] / (1024 * 1024) As  [RAM USED],
  vComputer.[IP Address] As [IP Address],
  Inv_AddRemoveProgram.DisplayName,
  vComputer.IsManaged As [Altiris Agent]
From
  vComputer Inner Join
  vUser On vComputer.[User] = vUser.Name Left Join
  Inv_AddRemoveProgram On vComputer.Guid = Inv_AddRemoveProgram._ResourceGuid
  Inner Join
  Inv_AeX_AC_Identification On vComputer.Guid =
    Inv_AeX_AC_Identification._ResourceGuid Inner Join
  vHWComputerSystem On vComputer.Guid = vHWComputerSystem._ResourceGuid
Where
  Inv_AddRemoveProgram.DisplayName Like '%Microsoft Office%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%proof%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%web%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%MUI%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%primary%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%security%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%runtime%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%activation%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%update%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%live%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%components%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%viewer%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%programs%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%connector%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%service pack%' And
  Inv_AddRemoveProgram.DisplayName Not Like '%trial%'
Order By
  vUser.[Office Location],
  vUser.[Display Name]
 

+1
Login to vote
Andrey Shipov's picture

 

Hi

Your quire will work well when all your computers are active and you don’t have assets management. If you have assets management or you retire your PCs, your quire will return information for all PC in data base, even for not active ones.

You need to filter the results against table vcomuter, this table contains only active PCs.

Add the code below to your quire to have more realistic results

AND wrkstaid in
    (SELECT wrkstaid
    FROM dbo.Wrksta
    where guid in (select guid from vcomputer))

I have modified your quire to include the additional code and have changed the format slightly,so quire will look like this:

SELECT AeXInv_AeX_OS_Add_Remove_Programs.Name, Count(*) AS Total
FROM AeXInv_AeX_OS_Add_Remove_Programs
WHERE
((
    (AeXInv_AeX_OS_Add_Remove_Programs.Name) Like 'Microsoft Office%'
and (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Service%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Security%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Project%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Visio%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Disc 2%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Communicator%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Viewer%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Visual%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Resource%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Share%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Publisher%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%proof%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%Powerpoint%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%outlook%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%onenote%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%live%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%infopath%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%frontpage%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%converter%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%access%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%web%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%excel%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%groove%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%word%'
And (AeXInv_AeX_OS_Add_Remove_Programs.Name) Not Like '%interop%')
AND (AeXInv_AeX_OS_Add_Remove_Programs.Publisher) Like 'Microsoft%')
AND wrkstaid in
    (SELECT wrkstaid
    FROM dbo.Wrksta
    where guid in (select guid from vcomputer))

GROUP BY AeXInv_AeX_OS_Add_Remove_Programs.Name
HAVING (((Count(*))>10))
ORDER BY AeXInv_AeX_OS_Add_Remove_Programs.Name

Andrey Shipov
IS Infrastructure Senior Engineer
Manchester, UK

0
Login to vote
TeleFragger's picture

I am now embarking down an OFFICE report path.. this helped me get a base report...

but thought I would share my modification on this report... cleaned up a bit... and removed retired machines..

I do not know much about SQL code.. just enough to be dangerous to get the job done... but I like short and cleaned up code.... hope this helps someone in the future...

added 3rd line to join table that has active, retired - Join  [vFixedAssetResourceStatus] t1 on t0._ResourceGuid = t1.Guid

added line 18 to target active only - and t1.Status = 'Active'

We do not have asset management

 

SELECT  t0.DisplayName, Count(*) AS Total
FROM  Inv_AddRemoveProgram t0
Join  [vFixedAssetResourceStatus] t1 on t0._ResourceGuid = t1.Guid
WHERE t0.DisplayName Not Like '%Service%' And t0.DisplayName Like 'Microsoft Office%' 
And t0.DisplayName Not Like '%Security%' And t0.DisplayName Not Like '%Project%' 
And t0.DisplayName Not Like '%Visio%' And t0.DisplayName Not Like '%Disc 2%' 
And t0.DisplayName Not Like '%Communicator%' And t0.DisplayName Not Like '%Viewer%' 
And t0.DisplayName Not Like '%Visual%' And t0.DisplayName Not Like '%Resource%' 
And t0.DisplayName Not Like '%Share%' And t0.DisplayName Not Like '%Publisher%' 
And t0.DisplayName Not Like '%proof%' And t0.DisplayName Not Like '%Powerpoint%' 
And t0.DisplayName Not Like '%outlook%' And t0.DisplayName Not Like '%onenote%' 
And t0.DisplayName Not Like '%live%' And t0.DisplayName Not Like '%infopath%' 
And t0.DisplayName Not Like '%frontpage%' And t0.DisplayName Not Like '%converter%' 
And t0.DisplayName Not Like '%access%' And t0.DisplayName Not Like '%web%' 
And t0.DisplayName Not Like '%excel%' And t0.DisplayName Not Like '%groove%' 
And t0.DisplayName Not Like '%word%' And t0.DisplayName Not Like '%interop%' 
AND t0.Publisher Like 'Microsoft%'
and t1.Status = 'Active'
 
GROUP BY  t0.DisplayName
ORDER BY  t0.DisplayName

 

Did we help you? Please Mark As Solution those posts which resolve your problem,

0
Login to vote
BlitzAdler's picture

Hi Colleagues,

Do you have any idea if it possible to make a report to show if the office 2010 is activated or needs activation?

Thanks in advance!

Mauro

0
Login to vote