I need to generate a report on KACE with any server in our system having (Under Services) MSSQLSERVER.

All of our servers are named Sr###  (example) Sr19 or Sr205 ect.

If the report also pulls workstations it won't work because 98% of our machines have SQL.

 

I found something similar here: http://www.itninja.com/question/need-a-kace-report-for-all-sql-server-installations-1

 

But this is ALL sql installations. I only need servers with MSSQLSERVER listed as a service.

SELECT M.NAME, CS_MODEL AS Model, CHASSIS_TYPE as Type, IP, USER_LOGGED,
GROUP_CONCAT(DISTINCT S.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE,
GROUP_CONCAT(DISTINCT S.DISPLAY_VERSION SEPARATOR '\n') AS VERSION
FROM MACHINE M
LEFT JOIN MACHINE_SOFTWARE_JT ON M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
LEFT JOIN SOFTWARE S ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID
WHERE S.DISPLAY_NAME LIKE 'Microsoft SQL Server 2005 Express%'
OR S.DISPLAY_NAME LIKE 'Microsoft SQL Server 2000%'
OR S.DISPLAY_NAME LIKE 'Microsoft SQL Server 2005 Compact%'
GROUP BY M.NAME
ORDER BY NAME

 

Can anyone help?

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

2

Let me know if this works.  I have my server as CHASSIS_TYPE 'server', so change that name if necessary.

Also, none of my servers have SQL on them.

SELECT M.NAME
  , CS_MODEL AS Model
  , CHASSIS_TYPE as Type
  , IP
  , USER_LOGGED
  , GROUP_CONCAT(DISTINCT S.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE
  , GROUP_CONCAT(DISTINCT S.DISPLAY_VERSION SEPARATOR '\n') AS VERSION
FROM MACHINE M
  LEFT JOIN MACHINE_SOFTWARE_JT ON M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
  LEFT JOIN SOFTWARE S ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID
WHERE CHASSIS_TYPE LIKE 'server'
  AND (S.DISPLAY_NAME LIKE 'Microsoft SQL Server 2000%' OR S.DISPLAY_NAME LIKE 'Microsoft SQL Server 2005 Compact%')  
GROUP BY M.NAME
ORDER BY NAME

Answered 11/15/2012 by: gcarpenter
Green Belt

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