Here is a query that I believe could help security and systems professionals identify any assets that have high-risk software in their organization. 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
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
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 I’ll be looking for .NET Framework SP2.
SELECT da.ip_address, da.host_name, 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 WHERE ds.name like'%.NET Framework 3.0%' and ds.version like '%SP2'
To know more about our SQL Query Export feature, please check out the introductory blog.