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!
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
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
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
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]