Need a KACE report for all SQL Server installations
Hi,
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,
Hareesh G
Answers (4)
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
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)
WHERE NTSERVICE.FILE_NAME='sqlservr.exe'
Thanks,
Hareesh G
Comments:
-
This is basically the same thing as I posted above. I've just added the software names and versions.
If you can also comment instead of entering a new answer every time. Keeping up with all of these answers makes things difficult. - dugullett 12 years ago -
Ok...i'll comment the given answer going forward instead writing a new answer. I did realized lately on this. I am testing your query, but it is taking huge time to pull the results. the query is still executing. will update you once i verify this. Thank you very much... - HareeshG 12 years ago
-
Try this one. I usually don't query the process. Just the software display name. For this query to be accurate "SQLSERVER.EXE" will need to be running at the time of check in. If it's not then it will not show.
SELECT M.NAME as Name, CS_MODEL AS Model, CHASSIS_TYPE as Type, IP, USER_LOGGED AS User,
NT.FILE_NAME as 'Process Name', NT.FILE_VERSION as 'Process Version'
FROM MACHINE M
LEFT JOIN MACHINE_NTSERVICE_JT ON M.ID = MACHINE_NTSERVICE_JT.MACHINE_ID
LEFT JOIN NTSERVICE NT ON MACHINE_NTSERVICE_JT.MACHINE_ID = M.ID
WHERE NT.FILE_NAME LIKE '%SQLSERVR.EXE%'
GROUP BY M.NAME
ORDER BY M.NAME
You can always take WHERE S.DISPLAY_NAME LIKE '%SQL SERVER%' from the first query I gave you, and narrow it down. 'Microsoft SQL Server 2005%' will get you less results. If you know the exact version name that you are looking for then use it. You can use something like this. Keep in mind that % is wildcard with SQL.
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%'
So something like this.....
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 - dugullett 12 years ago-
Also be sure to tag K1000 reporting on this question. It helps other reference in the future. - dugullett 12 years ago
Are AD site, user reporting manager, user department info custom fields?
Also are the services captured by Kace, or is the custom as well? I don't have any machines that have the agent w/ SQL is why I ask.
Comments:
-
Something like this will get you started.
SELECT M.NAME, CS_MODEL AS Moder, 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 '%SQL SERVER%'
GROUP BY M.NAME
ORDER BY M.NAME - dugullett 12 years ago
If we search by software title as SQL Server, there will be many other items. hence can you make the query to look for NTSERVICE.FILE_NAME='sqlservr.exe'
Comments:
-
SELECT DISTINCT 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
LEFT JOIN MACHINE_NTSERVICE_JT ON M.ID = MACHINE_NTSERVICE_JT.MACHINE_ID
LEFT JOIN NTSERVICE NT ON MACHINE_NTSERVICE_JT.MACHINE_ID = M.ID
WHERE NT.FILE_NAME LIKE '%SQLSERVR.EXE%'
ORDER BY M.NAME - dugullett 12 years ago