How to generate a K1000 report for SQL Server installations
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?
Answers (1)
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
