SQL query to identify SCCM agent version

InsightVM SQL Queries
1

Hi! Has someone used a query to identify the SCCM agents on servers - missing/version?

2

SELECT count(da.asset_id) as asset_count, ds.vendor, ds.name as software_name, ds.family, ds.version
FROM dim_asset_software das
JOIN dim_software ds using (software_id)
JOIN dim_asset da on da.asset_id = das.asset_id
GROUP BY ds.vendor, ds.name, ds.family, ds.version, ds.cpe
ORDER BY asset_count DESC

1 Like
3

Thanks for posting this! I personally haven’t used dim_software as much so I’m a little less familiar with it, but I’m going to save this for reference. :slightly_smiling_face:

Mind if I mark your query as the solution here?

1 Like
4

Thank you, sounds good for me. Actually the query will return all the software version and you’ll find there SCCM too, but you can use a filter to show only SCCM.
SELECT da.host_name, da.ip_address, dos.name AS OSName, dos.version AS OSVersion, ds.name, ds.version
FROM dim_asset da
JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id
JOIN dim_asset_software das ON da.asset_id = das.asset_id
JOIN dim_software ds ON das.software_id = ds.software_id
WHERE ds.name LIKE ‘System Center%’
ORDER BY dos.name, dos.version, ds.version

1 Like