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

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
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

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

 

Answered 08/22/2012 by: HareeshG
Senior White Belt

  • 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.
  • 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...
    • 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
      • Also be sure to tag K1000 reporting on this question. It helps other reference in the future.
Please log in to comment

Answers

1

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.

Answered 08/22/2012 by: dugullett
Red Belt

  • 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
Please log in to comment
1

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'

Answered 08/22/2012 by: HareeshG
Senior White Belt

  • 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
Please log in to comment
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

Answered 08/22/2012 by: HareeshG
Senior White Belt

  • Ok. What is user reporting manager? Also where is the dept info coming from?
Please log in to comment
Answer this question or Comment on this question for clarity