Ivan Camero

CIS compliance SQL query by policy and site

Discussion created by Ivan Camero on May 2, 2017

I'm trying to build a "simple query" that will give me the compliance related data given a specific policy and site.

 

I was able to do the count of the passed/failed/N.A for each rule on each asset with the query below, but I get a row for each status (passed/failed/N.A) on each host. So if a host I has 1, 2 or 3 statutes, I would get 1 to 3 rows. Any ideas on how to transpose the 1-3 rows to the single row with the count for each status in other columns? Something like this:

HostnameIPOSOS versionPolicy# Passed# Failed# Non-Applicable

 

This is the query I wrote:

 

SELECT da.host_name, da.ip_address, dp.title, dos.name, dos.version,

(CASE WHEN dprs.description = 'Passed' THEN 'Passed' WHEN dprs.description = 'Failed' THEN 'Failed' ELSE 'Not Applicable' END) AS status,

count(dprs.description)

FROM fact_asset_policy_rule fpr

   JOIN dim_policy_rule dpr USING (scope, rule_id, policy_id)

   JOIN dim_policy_result_status dprs USING (status_id)

   JOIN dim_policy dp USING (scope, policy_id)

   JOIN dim_asset da USING (asset_id)

   JOIN dim_operating_system dos USING (operating_system_id)

group by da.host_name,da.ip_address,dos.name,dos.version, dp.title,dprs.description

order by da.ip_address

Outcomes