Extracting information from GitHub Audit log exports with DuckDB to create CSV files

[gm3dmo]

Extracting information from GitHub Audit log exports with DuckDB

Having exported git events from my organization and stored them in a file named git-events.json I found that json is not the most pleasant thing to read:

{"@timestamp":1713596671828,"_document_id":"URqi_-__Xjl1qZojMcYnqA==","action":"git.fetch","actor":"datadog-forest-town[bot]","actor_id":111058333,"actor_ip":"44.192.28.48","actor_location":{"country_code":"US"},"business":"gm3dmo-enterprise-cloud-testing","business_id":3082,"external_id":"","hashed_token":"j7TxdJbRhfNhkIOs67EXYceuX0EXL+du673X+6QYSow=","org":"forest-town","org_id":86825428,"programmatic_access_type":"GitHub App server-to-server token","repo":"forest-town/repo-2079435","repository":"forest-town/repo-2079435","repository_public":false,"token_id":0,"transport_protocol":1,"transport_protocol_name":"http","user":"","user_agent":"go-git/5.x","user_id":0}
{"@timestamp":1713596669109,"_document_id":"UcKnmGTmhciMVlkYOGkUaQ==","action":"git.push","actor":"github-actions[bot]","actor_id":41898282,"actor_ip":"20.57.77.4","actor_location":{"country_code":"US"},"business":"gm3dmo-enterprise-cloud-testing","business_id":3082,"external_id":"","hashed_token":"EJmtcbNI26UXIHgh0fnmlmRsDcqsW6y9zLP4xwk9S+s=","org":"forest-town","org_id":86825428,"programmatic_access_type":"GitHub App server-to-server token","repo":"forest-town/repo-2079435","repository":"forest-town/repo-2079435","repository_public":false,"token_id":0,"transport_protocol":1,"transport_protocol_name":"http","user":"","user_agent":"git/2.43.2","user_id":0}

I wanted to be able to do two things quickly:

1. Run SQL type commands to create a report.
2. Provide a CSV file to a data analyst.

Let's use a tool called DuckDB to work through these two requirements.

Install DuckDB

MacOS

brew install duckdb

The version of duckdb installed was:

duckdb --version
v0.10.1 4a89d97db8

Create a DuckDB database and import the git-events.log json file

duckdb github-enterprise-audit.db

Import the git-events.json file into a table called "gitevents"

This will create a table in the database containing all the data from git-events.json.

CREATE TABLE gitevents AS SELECT * FROM read_json_auto('git-events.json');

Select action and group by hour

.mode csv
.headers on
SELECT
  DATE_TRUNC('hour', to_timestamp("@timestamp"/1000)) AS hour,
   action,
   COUNT(*) AS count,
FROM gitevents
GROUP BY DATE_TRUNC('hour', to_timestamp("@timestamp"/1000)), action
ORDER BY hour;

hour,action,count
"2024-04-19 11:00:00+01",git.fetch,8
"2024-04-19 11:00:00+01",git.clone,3
"2024-04-19 14:00:00+01",git.push,2
"2024-04-19 14:00:00+01",git.clone,3
"2024-04-19 14:00:00+01",git.fetch,4
"2024-04-19 15:00:00+01",git.fetch,5
"2024-04-19 15:00:00+01",git.clone,2
"2024-04-20 07:00:00+01",git.push,4
"2024-04-20 07:00:00+01",git.fetch,2
"2024-04-20 07:00:00+01",git.clone,5
"2024-04-20 08:00:00+01",git.fetch,2
"2024-04-20 08:00:00+01",git.clone,2
"2024-04-20 08:00:00+01",git.push,2

Create a table for the audit event

Follow the steps for exporting audit log activity for your enterprise

Save the audit log events to a file calledevents.json.

duckdb github-enterprise-audit.db

CREATE TABLE events AS SELECT * FROM read_json_auto('events.json',  ignore_errors=true);

Output CSV format group by minute

.mode csv
.headers on
SELECT
  DATE_TRUNC('minute', to_timestamp("@timestamp"/1000)) AS minute,
  action,
  COUNT(*) AS count
FROM events
GROUP BY DATE_TRUNC('minute', to_timestamp("@timestamp"/1000)), action
ORDER BY minute;

Create a CSV report file by hour

COPY (
SELECT
  DATE_TRUNC('hour', to_timestamp("@timestamp"/1000)) AS hour,
  action,
  COUNT(*) AS count
FROM events
GROUP BY DATE_TRUNC('hour', to_timestamp("@timestamp"/1000)), action
ORDER BY hour) TO 'events-by-hour.csv' (HEADER, DELIMITER ',');

A file called events-by-hour.csv has been created.

[ezruneko]

good work ! :)