I need to create a report/run a SQL query to show how a specific tag is doing risk score wise compared to the others tags.
Hey jhenry2,
Have you tried the “Risk Scorecard” report for this purpose? You can run one report for each tag for a nice brief comparison. It doesn’t get a whole lot of detail and is geared more for a brief report to say management.
Hello Ben,
Yes, I have looked at this and although it is helpful, I need to be able to show a comparison of how the tag is compared to the rest of the tags
Ahh so you’re looking for something more like a side by side report of all tags and how they all compare to each other?
You would need to use an SQL Query for this, something like below should get you started:
select tag_name, cast(sum(riskscore) as decimal(15,2))
from dim_tag dt
join dim_tag_asset dta using(tag_id)
join fact_asset fa using(asset_id)
group by dt.tag_name
Also just put in a pull request to have this query added to our public github repo. Once approved you will find it with the name “Tags-Calculated-Risk.sql”. I made a few slight changes to make it prettier and add a column for the tag type.
That SQL works great, thanks so much! I just thought about this, is there any way that we can add the number of assets and vulnerabilities that belong to each of the tags as well?
yep, just add to the select statement
select tag_name, cast(sum(riskscore) as decimal(15,2)), count(fa.asset_id), sum(vulnerabilities)
from dim_tag dt
join dim_tag_asset dta using(tag_id)
join fact_asset fa using(asset_id)
group by dt.tag_name
1 Like