How do I export a list of non-compliant AWS resources into a CSV file in Panther?

Last updated
Save as PDF

QUESTION

ANSWER

To do this:

Go to Investigate > Data Explorer, and copy/paste the query below into the query field. Note: This query will fail if you run it as-is; it must be customized for your system.
Edit the query, according to your Panther system and the data you want to export.
Run the query.
Once the query completes, use the Download CSV button to export the data. This button sits on the right-hand side, below the query field and above the data viewing area.

Note: This query is designed to deliver the same information that can be seen on the Cloud Resources page in the Panther Console. For more information about cloud security scanning in Panther, see our documentation here.

select t1.id, t2.resourceType, ZEROIFNULL(t3.fail_count) as Failures, t2.integrationLabel, concat('https://<DOMAIN>.runpanther.net/investigate/resources/', replace(t2.id, '/', '%2F')) as uri, t2.changetype from
-- Replace <DOMAIN> with the domain name of your Panther system. This will match the URL in your browser.

(select id, max(p_parse_time) as p_parse_time FROM panther_cloudsecurity.public.resource_history where
p_occurs_since('2 days') group by id) as t1 inner join
(select id, changetype, resourceType, integrationLabel, p_parse_time FROM panther_cloudsecurity.public.resource_history where
-- Enter your Filters here by uncommenting the line and adding your specifications
  -- resourcetype = 'AWS.IAM.Role' and
  -- integrationLabel = 'panther-account' and
  -- id like '%resource-name%' and

  p_occurs_since('2 days')) as t2
on t1.id = t2.id and t1.p_parse_time = t2.p_parse_time
left join

(select info.resourceId, count(*) as fail_count from
(select resourceId, policyId, policySeverity, status, p_parse_time FROM panther_cloudsecurity.public.compliance_history
where p_occurs_since('2 days')) as info inner join
(select resourceId, policyId, max(p_parse_time) as p_parse_time FROM panther_cloudsecurity.public.compliance_history
where p_occurs_since('2 days') group by resourceId, policyId
) as max_time
on info.resourceId = max_time.resourceId and info.policyId = max_time.policyId and info.p_parse_time = max_time.p_parse_time
where info.status = 'FAIL' group by info.resourceId order by info.resourceId asc) as t3
on t1.id = t3.resourceId
where changetype != 'DELETED'

-- Uncomment and edit this last line to get only failing or only passing resources
-- and failures > 0