GuardDuty logs queries

Rank all GuardDuty alerts by severity

GuardDuty is a valuable source of visibility into threats against your infrastructure. However, it can produce a large number of findings. This query shows the distribution of findings which be used to assess the posture of an account.

Snowflake

SELECT
 severity,
 count(1) AS total_rows
FROM panther_logs.public.aws_guardduty
WHERE p_occurs_between('2021-01-01', '2021-02-01')
GROUP BY severity
ORDER BY total_rows DESC

Athena

SELECT
 severity,
 count(1) AS total_rows
FROM panther_logs.aws_guardduty
WHERE p_occurs_between('2021-01-01', '2021-02-01')
GROUP BY severity
ORDER BY total_rows DESC

Rank all GuardDuty alerts by affected resources

Similar to the above example, but in this example the query characterizes the findings by ranking affected resources.

Snowflake

SELECT
 resource:resourceType AS resource_type,
 count(1) AS total_rows
FROM panther_logs.public.aws_guardduty
WHERE p_occurs_between('2021-01-01', '2021-02-01')
GROUP BY resource:resourceType
ORDER BY total_rows DESC

Athena

SELECT
 json_extract(resource, '$.resourcetype') AS resource_type,
 count(1) AS total_rows
FROM panther_logs.aws_guardduty
WHERE p_occurs_between('2021-01-01', '2021-02-01')
GROUP BY json_extract(resource, '$.resourcetype')
ORDER BY total_rows DESC