I want to run a report on my KBOX which will give me the SQL version I am running on certan servers.
I need to know which servers are running SQL Enterprise and which are running SQL standard
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
jverbosk
12 years ago
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
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
