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
1SELECT count(da.asset_id) as asset_count, ds.vendor, ds.name as software_name, ds.family, ds.version2FROM dim_asset_software das3JOIN dim_software ds using (software_id)4JOIN dim_asset da on da.asset_id = das.asset_id5GROUP BY ds.vendor, ds.name, ds.family, ds.version, ds.cpe6ORDER 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
1SELECT da.ip_address, da.host_name, ds.vendor, ds.name as software_name, ds.family, ds.version2FROM dim_asset_software das3JOIN dim_software ds using (software_id)4JOIN dim_asset da on da.asset_id = das.asset_id5WHERE ds.name like'%.NET Framework 3.0%' and ds.version like '%SP2'
Did this page help you?