Dear community,
I faced with issue where I can’t see the context-driven score in sql query reports.
If I sort assets by fa.riskscore I see only Original score. But I have a critical tags with Risk Score Adjustment to help prioritize asset management.
However, I can’t find in documentation any way to see context-driven score.
Do you have any idea how can I do it?
So apparently -Context-driven risk score is not a field in the reporting data model, so to pull that, you’ll need to take the risk_modifier from dim_tag and the value from the tags applied to the asset and calculate that against the original risk score.
With little help of ChatGPT I was able to calculate the context-driven score inside the query like this:
WITH location_tags AS (
SELECT
dta.asset_id,
string_agg(dt.tag_name, ', ') as location_tags
FROM
dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id = dta.tag_id
WHERE
dt.tag_type = ‘LOCATION’
GROUP BY
dta.asset_id
),
owner_tags AS (
SELECT
dta.asset_id,
string_agg(dt.tag_name, ', ') as owner_tags
FROM
dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id = dta.tag_id
WHERE
dt.tag_type = ‘OWNER’
GROUP BY
dta.asset_id
),
criticality_tags AS (
SELECT
dta.asset_id,
string_agg(dt.tag_name, ', ') as criticality_tags,
risk_modifier::double precision – Cast risk_modifier to double precision
FROM
dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id = dta.tag_id
WHERE
dt.tag_type = ‘CRITICALITY’
GROUP BY
dta.asset_id,
dt.risk_modifier
)
SELECT
da.ip_address,
da.host_name,
lt.location_tags AS Application, – Renaming location_tags to Application
ot.owner_tags AS Regulated, – Renaming owner_tags to Regulated
crit.criticality_tags,
fa.riskscore AS “Risk Score”,
CAST(fa.riskscore::double precision * crit.risk_modifier AS NUMERIC(10,0)) AS “Adjusted Risk Score” – rounding the Adjusted Risk Score to 0 decimal places
FROM
dim_asset da
LEFT JOIN location_tags lt USING (asset_id)
LEFT JOIN owner_tags ot USING (asset_id)
LEFT JOIN criticality_tags crit USING (asset_id)
JOIN fact_asset fa ON da.asset_id = fa.asset_id