How can I create a query to list all devices running a process
05/24/2016 799 views
How can I create a query to look for processes and display the list of computers having the process running?
For querying software catalog, I found this query which I customize for my uses:
SELECT SC.NAME AS SOFTWARE_NAME, M.NAME as MACHINE_NAME, M.LAST_USER as LASTUSER
FROM SAM_MACHINE_JT JOIN CATALOG.SAM_CATALOG SC ON SC.ID = SAM_MACHINE_JT.SAM_CATALOG_ID
JOIN SAM_COUNT ON SAM_COUNT.SAM_CATALOG_ID = SC.ID
LEFT JOIN SAM_METER_DATA SMD ON SMD.MACHINE_ID = SAM_MACHINE_JT.MACHINE_ID
AND SMD.TITLED_APPLICATION_ID = SC.ID
LEFT JOIN SAM_METER_TITLED_APPLICATION SMTA ON SMTA.TITLED_APPLICATION_ID = SC.ID
LEFT JOIN SAM_NOT_ALLOWED SNA ON SNA.TITLED_APPLICATION_ID = SC.ID
LEFT JOIN MACHINE M ON M.ID = SAM_MACHINE_JT.MACHINE_ID
LEFT JOIN KBSYS.SAM_TITLE_REQUEST STR ON STR.SAM_APPLICATION_FILE_ID = SC.ID
LEFT JOIN USER U on REPLACE(M.USER_LOGGED,'DOMAIN\\','') = U.USER_NAME
WHERE (SC.SAM_TYPE IN ('TITLED_APPLICATION', 'TITLED_SUITE') AND (SC.SOFTWARE_CATEGORY_ID <> 32)) AND SC.NAME like 'SQL Server%'
How can I modify the above to query against Processes instead of Software Catalog as the above is doing? I would like to be able to join the data with the computer name and the last known user.