Need a KACE report for all SQL Server installations
I am looking for a query which can report me all the SQL Server Installations in my environment. Below are required details which i need in the report.
1. Machine Name, Machine Model, Machine Type, IP Address, AD site, Last logged on User in the Machine, User Reporting Manager, User Department Info.
2. SQL Server version, Installed Instances, Installed services like Database, Reporting, Analysis, Notification, Integration or Workstation components.
Please help me!!!
Thanks in Advance,
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
Community Chosen Answer
By going through the forum i have found a similar query and modified it to pull the sql server installed machines info. Can you please verify the query and let me know whether this correct or not. I got the results displayed without any error, but still need know whether the results i have queried are correct or not?.
Please find the below Query
SELECT DISTINCT MACHINE.NAME AS Machine, NTSERVICE.DISPLAY_NAME AS 'Service Name', NTSERVICE.FILE_NAME AS 'Process Name', NTSERVICE.STARTUP_TYPE AS 'Startup Type', NTSERVICE.STATUS AS Status, NTSERVICE.PRODUCT_VERSION AS 'PRODUCT VERSION' FROM MACHINE
JOIN MACHINE_SOFTWARE_JT ON (MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID)
JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID)
JOIN MACHINE_NTSERVICE_JT ON (MACHINE.ID = MACHINE_NTSERVICE_JT.MACHINE_ID)
JOIN NTSERVICE ON (NTSERVICE.ID = MACHINE_NTSERVICE_JT.NTSERVICE_ID)