SQL Query Export Example: Software Counts and Listing

Document created by erikc Employee on Jan 7, 2014Last modified by erikc Employee on Jan 7, 2014
Version 4Show Document
  • View in full screen mode

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

 

Example output:

softwarecount.png

 

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'


Example output:

assetlist.jpg


To know more about our SQL Query Export feature, please check out the introductory blog.

5 people found this helpful

Attachments

    Outcomes