I am starting from scratch in building out a SQL Query report that can display/report all unauthorized and unlicensed software’s installed across all assets in my organization. What example would a query that calls this look like? Would I need to call specifically the name of each unauthorized software that may be installed across all assets or is there a function for that built in already? The query will be pulling a report from a platform known as Rapid7.
Any and all guidance is greatly appreciated!
Welcome
What tables are you working with that can help us guide you. Please post here sample schema of tables you will be querying?
Is the backend for Rapid7 data in Microsoft SQL server?
2 Likes
Yes, Rapid7 SQL, so below is two different schemas that are similar but slightly different:
SELECT DISTINCT ON (da.ip_address) da.ip_address AS “IP”, da.host_name AS “Hostname”, da.asset_id AS "Asset ID", ds.name as “SoftwareName”, ds.version AS “SoftwareVersion”, date(da.last_assessed_for_vulnerabilities) AS “LastScan”
JOIN fact_asset fa USING (asset_id)
JOIN dim_software ds using (software_id)
JOIN dim_asset da on da.asset_id = das.asset_id
WHERE ds.name ~* 'steam'
ORDER BY da.ip_address, da.asset_id, ds.version, da.last_assessed_for_vulnerabilities DESC
(or)
SELECT ds.name as software_name, da.asset_id AS "Asset ID", da.ip_address, da.host_name, ds.vendor
FROM dim_asset_software das
JOIN fact_asset fa USING (asset_id)
JOIN dim_software ds using (software_id) JOIN dim_asset da on da.asset_id = das.asset_id
WHERE ds.name ~* 'turbotax'
GROUP BY software_name, ds.vendor, ds.name, da.ip_address, da.host_name, ds.cpe, da.asset_id ORDER BY software_name ASC
Both of these queries work and find the mentioned software under WHERE, but I am confused as to how to generate the query to where I can get it to generate data for several software's, not just one
Looks like you are using a custom sql query tool by rapid7?
Yes, I am, its got custom SQL commands and syntax for Rapid7 sadly so I have to learn a lot of basic things again, if you can help, check out their resource here for their commands and SQL, its called: Rapid7 SQL Export report, its a documentation on their website
If you cant, then thats cool, I understand