I need to know which servers are running SQL Enterprise and which are running SQL standard
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
First, I would try using the report wizard, choosing Computer for the report topic, including Other - Software Titles in the Fields to Display, and later filtering by Software Titles item. If the wizard doesn't get what you want/need, then maybe give this a shot.

Not sure if this is overkill for what you want, but you should be able to use it like a template and simply plug into your own values. This is how I'm doing a report for all of our MS Office installs (including counts).

The first thing I would recommend is refining an Advanced Search in Inventory - Computers until you get all of the hits you want.


via Advanced Search:

Display Name (Title) matches REGEX Microsoft Office

Version Number matches REGEX 11.0.8173.0|12.0.6425.1000|12.0.6612.1000|14.0.4763.1000|14.0.6029.1000

Display Name does not match REGEX Interface|FrontPage|MUI|Proof|engine|module|visio|sharepoint|single|components|project|hybrid

* added to Report to scrub blank entries (no machines listed for software title):

Machines is NOT NULL

Next, build your SQL report and plug the values into the according WHERE entries:

Report Title: MS Office Installs (with Count)

Report Category: Software (Custom)

SELECT DISPLAY_NAME AS 'Display Name',DISPLAY_VERSION AS 'Display Version',COUNT(MACHINE.ID) AS 'Total', GROUP_CONCAT(DISTINCT MACHINE.NAME ORDER BY 1 SEPARATOR '\n') AS 'Machines' FROM SOFTWARE
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID)
LEFT JOIN MACHINE ON (MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID)
WHERE (NOT SOFTWARE.IS_PATCH)
AND ((MACHINE.NAME is not null) AND (DISPLAY_NAME not rlike 'Interface|FrontPage|MUI|Proof|engine|module|visio|sharepoint|single|components|project|hybrid') AND (DISPLAY_VERSION rlike '11.0.8173.0|12.0.6425.1000|12.0.6612.1000|14.0.4763.1000|14.0.6029.1000') AND (DISPLAY_NAME rlike 'Microsoft Office'))
GROUP BY SOFTWARE.ID
ORDER BY DISPLAY_VERSION

I am aware that my column headers at the end are off, not quite sure why (possibly related to the GROUP_CONCAT, which sorts the machine names alphabetically in the report). But good enough for how we use the report.

Hope that helps!

John
Answered 03/20/2012 by: jverbosk
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity