Hi,
For several Fact tables, there is a corresponding _date table which holds date based snapshots for that data as per the documentation.
But when I refer to those in SQL, it throws the following error.
Error: relation “fact_asset_policy_date” does not exist
Is there a way to tap into this historic data through SQL export?
Those date tables only exist in the data warehouse, so if you’re trying to query those tables from the console it will not work as they do not exist.
Be sure to use the console schema when writing a query against the console.
https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-dimensions/
Thanks John.
I checked the link you shared and it also mentions these fact tables. Is there a different document that describes tables available to console Vs warehouse?
ah you’re absolutely right. I must have been thinking of the remediaiton_date tables.
Can you show a screenshot of the query you’re trying to run?
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?
but add this:
SELECT policy_id, rule_compliance FROM fact_asset_policy_date(‘2022-01-01’, ‘2023-01-01’, INTERVAL ‘1 month’) WHERE asset_id=123456
Thanks John,
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’)
WHERE asset_id=12345
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.
Any idea what could be going wrong 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.
Hi, I’ve tried this query but it doesn’t work, what am I doing wrong, Error: syntax error at or near “’”
Character: 42
Found that the error is due to the quote type SELECT * FROM fact_asset_date(‘2022-01-01’, ‘2023-01-01’, INTERVAL ‘1 month’) WHERE asset_id = 12345 or for summary asset by month SELECT * FROM fact_asset_policy_date(‘2022-01-01’, ‘2023-01-01’, INTERVAL ‘1 month’) WHERE asset_id = 12345