SQL example - software counts and listing

A common challenge that security professionals often face is bringing a large (and confusing) amount of vulnerabilities to their systems teams, who may only care to know about the assets and software listings that require remediation.

This query will provide you with the following:

  • Count of assets
  • Software Vendor
  • Software Name
  • Software Family
  • Software Version
sql
1
SELECT count(da.asset_id) as asset_count, ds.vendor, ds.name as software_name,  ds.family, ds.version
2
FROM dim_asset_software das
3
  JOIN dim_software ds using (software_id)
4
  JOIN dim_asset da on da.asset_id = das.asset_id
5
GROUP BY ds.vendor, ds.name, ds.family, ds.version, ds.cpe
6
ORDER BY asset_count DESC

Now that you have a count of how many assets are running the particular software, it is time to find out which assets these are. By using the WHERE clause you can filter your results for the software in question. In this example we’ll be looking for .NET Framework SP2.

sql
1
SELECT da.ip_address, da.host_name, ds.vendor, ds.name as software_name,  ds.family, ds.version
2
FROM dim_asset_software das
3
  JOIN dim_software ds using (software_id)
4
  JOIN dim_asset da on da.asset_id = das.asset_id
5
WHERE ds.name like'%.NET Framework 3.0%' and ds.version like '%SP2'