I have not yet used this in a larger query but rather just testing to see if I can even pull any information.
SELECT policy_id, rule_compliance FROM fact_asset_policy_date WHERE asset_id=123456
But it seems the way I am referring this table might be incorrect. In the table description, it also mentions 3 arguments although not sure how to refer them when using in a query.
My use-case is to pull previous policy results for assets for comparison. fact_asset_policy table only seems to be holding latest scan data so was hoping to use this _date table and get data for previous scans.
What would be the correct way to connect fact tables that require date arguments?
It did work. I can query the table using those 3 arguments, although the data returned is not what I expected. I tried few different ways, referring to this table directly using asset_id and also by combining with dim_asset. Each time, I only get last three policy scan results, each containing the exact same value as the latest scan.
SELECT dp.title, fapd.date_tested, fapd.day, fapd.scan_id, fapd.policy_id,fapd.scope, fapd.asset_id, fapd.rule_compliance, fapd.not_applicable_rules, fapd.noncompliant_rules, fapd.compliant_rules, da.ip_address, da.host_name, dp.category, dos.description
FROM fact_asset_policy_date(‘2022-09-01’,‘2023-04-23’,INTERVAL ‘1 month’) fapd
JOIN dim_policy dp USING (policy_id)
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
ORDER BY dp.category
SELECT * FROM fact_asset_policy_date(‘2022-09-01’,‘2023-04-23’,INTERVAL ‘1 month’)
Asset is within the scope of the report, and available data is well within the retention period. I also made sure to test an asset where two recent policy scans returned different compliance scores. Per the explanation, query should have returned all scans with their corresponding scores, but instead I get something like shown here.
Is that screenshot sanitized or are those the actual results you got from the query?
The “day” column and “date_tested” column don’t match up unless im misunderstanding the way that column works.
But i would expect to have 7 instances of each policy per asset given the interval you selected. The date tables should be subject to the data retention settings you mentioned as far as I’m aware. The other factor would be that policy scans would have needed to be running that far back as well.