GitHub Audit logs queries

Sometimes GitHub contributors bypass branch protection. The GitHub audit logs report that branch protection was bypassed, but do not provide explicit information about what activities took place when bypassing.

This query looks for a user's events in the GitHub audit log that are temporally related to the branch protection bypass events.

Snowflake

WITH rows_i_care_about AS (
SELECT
  actor, repo, at_sign_timestamp as ts, md5(p_row_id) as event_id
FROM panther_logs.public.github_audit
WHERE action like '%override%' 
   AND p_occurs_since('3 week') 
) 
-- collect all the rows AROUND the rows above in time
SELECT
  b.event_id, a.* 
FROM panther_logs.public.github_audit a 
JOIN rows_i_care_about b
    WHERE ( 
    a.actor = b.actor 
    AND 
    a.repo = b.repo
    AND 
    a.at_sign_timestamp > DATEADD(MINUTE, -5, b.ts)
    AND 
    a.at_sign_timestamp < DATEADD(MINUTE, 2, b.ts)
    AND
    p_occurs_since('3 week', a)
  )
 ORDER BY event_id, at_sign_timestamp ASC