SQL Query to get policy compliance percentage for each host /computer

InsightVM SQL Queries
1

Hi There ,

Can someone please help me with creating a query to get policy compliance percentage for each host in excel sheet using SQL Export?

Thank you for all your help.

2

@holly_wilsey and @tyler_schmidtke , Anything you can do to help will be great ?

Thank you in advance.
Manny

3

You can use something like this.

SELECT da.ip_address, da.host_name, fasp.scope, fasp.date_tested, fasp.compliant_rules, fasp.noncompliant_rules, fasp.not_applicable_rules, fasp.rule_compliance, dp.title
FROM fact_asset_scan_policy fasp
LEFT JOIN dim_policy dp using (policy_id)
LEFT JOIN dim_asset da using (asset_id)
WHERE fasp.date_tested>‘20210801’
order by dp.title

3 Likes
4

Thank you for sharing! I just tested this one in my lab instance and it looked good.

@manny_singh You could give @Adrian’s query a try and see if it works for you. The date_tested in the WHERE clause may need updating, but otherwise it seems to fit what you’re looking for.

Also for reference, here’s the schema for the fact_asset_scan_policy table where a bunch of that data is being pulled from.

1 Like
6

Hi @Adrian ,
You are awesome !! :100: Thank you so much for the query it is working as needed. Ignore my last comment. I have updated the query with OS info and fixed the Date tested field.

SELECT da.ip_address, da.host_name, fasp.scope, dos.description, fasp.date_tested, fasp.compliant_rules, fasp.noncompliant_rules, fasp.not_applicable_rules, fasp.rule_compliance, dp.title
FROM fact_asset_scan_policy fasp
LEFT JOIN dim_policy dp using (policy_id)
LEFT JOIN dim_asset da using (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
WHERE fasp.date_tested>'2021-08-01'
order by dp.title

I noticed that if the none of rules are applicable to the OS from policy title , Nexpose indicate the total precentage of compliance to be 100% rather 0% , this is same in console and the excel sheet , Is this a bug ?

Thanks ,
Manny

1 Like
7

You should only scan using the right CIS policy. For instance for 2k12 OS assets you use the 2012 CIS policy only.

8

Thank you @holly_wilsey … I’m looking forward to raise my rank here :grin:

1 Like
9

I am aware of this Adrian , I think regardless, this still needs to be addressed by R7 .

10

oh, I see … first you need to create a scanning template and assign a policy then you’ll have to scan the assets. Ensure that you use for instance [CIS Microsoft Windows Server 2012 R2 Domain Controller *] policy only for domain controllers and [CIS Microsoft Windows Server 2012 R2 Member Server *] for other types of servers. You can also see the compliance score in the asset page under policies fieldpolicies If you don’t see Policies in the Asset Page, you can go to Items in the top right corner of asset page and select Policies.

11

I have updated the Query again to Round the decimal numbers (fasp.rule_compliance) , Although this can be easily done with Excel however just thought to do it with the Query : The updated query is below :slight_smile:

SELECT da.ip_address, da.host_name, fasp.scope, dos.description, fasp.date_tested, fasp.compliant_rules, fasp.noncompliant_rules, fasp.not_applicable_rules, round(CAST (fasp.rule_compliance AS NUMERIC)* 100) AS "Compliance Percentage", dp.title
FROM fact_asset_scan_policy fasp
LEFT JOIN dim_policy dp using (policy_id)
LEFT JOIN dim_asset da using (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
WHERE fasp.date_tested>'2021-08-01'
order by dp.title

Thank you.