/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (4)

Answer Summary:
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
Posted by: HareeshG 11 years ago
Senior White Belt
4

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 11 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 11 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 11 years ago
      • Also be sure to tag K1000 reporting on this question. It helps other reference in the future. - dugullett 11 years ago
Posted by: dugullett 11 years ago
Red Belt
2

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 11 years ago
Posted by: HareeshG 11 years ago
Senior White Belt
2

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 11 years ago
Posted by: HareeshG 11 years ago
Senior White Belt
0

Hi Dugullett,

We have AD user info in KACE DB table named USER. Services are captured in KACE, it is not custom.

Thanks,

Hareesh G


Comments:
  • Ok. What is user reporting manager? Also where is the dept info coming from? - dugullett 11 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ