Skip to main content

Querying the GitHub Audit Log with SQL

Patrick DeVivo

For organizations using GitHub Enterprise, the GitHub audit log is now supported as a table in the MergeStat CLI, enabling SQL queries against audit log events 🎉. Check it out!

The new table-value-function is github_org_audit_log, and may be queried like so:

SELECT * FROM github_org_audit_log('mergestat') -- replace with your org name

and has the following columns (type is TEXT unless otherwise specified):

  • id
  • entry_type
  • action
  • actor_type
  • actor_login
  • actor_ip
  • actor_location (JSON)
  • created_at (DATETIME)
  • operation_type
  • user_login
GitHub Enterprise Only

This feature only works with GitHub Enterprise organizations, since the audit log API is only available to enterprise accounts.

This enables some interesting use-cases for GitHub enterprise users looking to monitor, alert and report on events from the GitHub audit log. We're working with some early users of this table, and will publish some guides and examples in the near future 🔜.

The JSON column (actor_location) can be accessed with the SQLite JSON operators.

SELECT actor_location->>'country' FROM github_org_audit_log('mergestat')
+----------------------------+
| ACTOR_LOCATION->>'COUNTRY' |
+----------------------------+
| United States              |
+----------------------------+
| United States              |
+----------------------------+
| Belgium                    |
+----------------------------+
| ...                        |
+----------------------------+

It's important to note the following from the GitHub API docs as well:

GitHub audit log notes

Join our Slack

Our community Slack is a great place to find help and ask questions. We're always happy to chat about MergeStat there 🎉!