Additional Information for a Solution Query

Hey all,

My team is trying to create a report that helps our Regional Information Officers to rank the vulnerabilities by a security urgency ranking. For the report we need to combine a large set of fields which include (also explained below):

The recommended fix, remediation summary, asset region, asset location (country), asset hostname, asset OS, Asset IP address, a tag containing the location, vulnerability details, affected services/area, severity, #vulnerabilitis of the host, number of exploits, number of malware kits.

So the basic idea is that we want to group the list by the “recommended fix”-field for a specific region, seperated by every IP/Asset.

The field “vulnerability details” should accumulate all the vulnerabilities (title, description, reference → CVE xyz) which can be fixed via the remediation from the “recommended fix”-field.

The #vulnerabilitis - field should show the number of vulnerabilities on the asset which can be fixed by the “recommended fix”-field.

For severity the highest according the the found CVEs should be taken.

e.g

Fix Region Asset IP Address #Vulnerabilitis …

update windows, region a 192.168.178.1 3 …

patch xyz, region a 192.168.178.1 24 …

update windows, region a 192.168.178.2 1 …

Can you help us building a query?

Thank you very much in advance!

You would need to replace the location, country, and region values but this should get you close. You may want to do some tweaking on the counts or maybe the JOIN statements depending on if you get duplicate lines.

WITH 
region_tags AS (
SELECT dta.asset_id, dt.tag_name AS region
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'LOCATION'
AND (dt.tag_name = 'region1' OR dt.tag_name = 'region2')
),
country_tags AS (
SELECT dta.asset_id, dt.tag_name AS country
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'LOCATION'
AND (dt.tag_name = 'country1' OR dt.tag_name = 'country2')
),
location_tags AS (
SELECT dta.asset_id, dt.tag_name AS location
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'LOCATION'
AND (dt.tag_name = 'location1' OR dt.tag_name = 'location2')
)


SELECT
ds.fix,
ds.summary,
rt.region,
ct.country,
da.host_name,
dos.description,
da.ip_address,
lt.location,
dv.title,
dv.description,
dv.nexpose_id,
COUNT(dv.vulnerability_id), 
dv.exploits,
dv.malware_kits

FROM dim_asset_vulnerability_solution davs

JOIN dim_asset da ON davs.asset_id=da.asset_id
JOIN dim_operating_system dos ON da.operating_system_id=dos.operating_system_id
JOIN region_tags rt ON davs.asset_id=rt.asset_id
JOIN country_tags ct ON davs.asset_id=ct.asset_id
JOIN location_tags lt ON davs.asset_id=lt.asset_id
JOIN dim_vulnerability dv ON davs.vulnerability_id=dv.vulnerability_id
JOIN dim_solution ds ON davs.solution_id=ds.solution_id

GROUP BY
ds.fix,
ds.summary,
rt.region,
ct.country,
da.host_name,
dos.description,
da.ip_address,
lt.location,
dv.exploits,
dv.malware_kits,
dv.title,
dv.description,
dv.nexpose_id
1 Like

How do I create a query for a particular solution using the query builder? Example, you have a list of recommended solutions but sometimes you want to create a query for a particular solution and find our more details of affected assets.

Unfortunately there is no fields within the query builder to focus on a specific solution. For that you would need to use the SQL queries as well.

Alternatively you could essentially create a GOAL to remediate all of a certain type of vulnerability or something to that effect and have it scoped to a global set of assets (for example asset.sites IS NOT NULL). Then create a remediation project of off those vulnerabilities. The remediation project is created by solutions so you could then search based off of them.