Script to convert Kubernetes API Audit jsonline files to CSV for Excel elaboration

audit-to-csv.rb

#!/usr/bin/env ruby
# encoding: UTF-8

# EXAMPLE
#
# {
#   "kind": "Event",
#   "apiVersion": "audit.k8s.io/v1",
#   "level": "Metadata",
#   "auditID": "f6618a15-8e4f-4f82-95b3-7592ed544eb9",
#   "stage": "ResponseComplete",
#   "requestURI": "/apis/node.k8s.io/v1?timeout=15m0s",
#   "verb": "get",
#   "user": {
#     "username": "system:serviceaccount:cattle-system:rancher-webhook",
#     "uid": "9c2d1a92-e9a2-4df3-bd8e-cdcea0428e22",
#     "groups": [
#       "system:serviceaccounts",
#       "system:serviceaccounts:cattle-system",
#       "system:authenticated"
#     ],
#     "extra": {
#       "authentication.kubernetes.io/pod-name": [
#         "rancher-webhook-85bb446df8-n7lgd"
#       ],
#       "authentication.kubernetes.io/pod-uid": [
#         "a8830d81-2803-47f4-b71a-431a7743e020"
#       ]
#     }
#   },
#   "sourceIPs": [
#     "10.30.21.143"
#   ],
#   "userAgent": "webhook/v0.0.0 (linux/amd64) kubernetes/$Format",
#   "responseStatus": {
#     "metadata": {},
#     "code": 200
#   },
#   "requestReceivedTimestamp": "2023-05-28T06:58:43.155675Z",
#   "stageTimestamp": "2023-05-28T06:58:43.160599Z",
#   "annotations": {
#     "authorization.k8s.io/decision": "allow",
#     "authorization.k8s.io/reason": "RBAC: allowed by ClusterRoleBinding \"rancher-webhook\" of ClusterRole \"cluster-admin\" to ServiceAccount \"rancher-webhook/cattle-system\""
#   }
# }

require 'json'
require 'csv'

out = CSV.generate do |csv|
    csv << ["Timestamp", "Duration", "Verb", "Query", "User", "Pod", "IP"]

    raw_events = ARGV.map do |file|
        File.readlines(file).map do |line|
            JSON.parse(line)
        end
    end.flatten

    events = raw_events.map do |event|
        event["requestReceivedTimestamp"] = DateTime.parse(event["requestReceivedTimestamp"]).to_time
        event["stageTimestamp"] = DateTime.parse(event["stageTimestamp"]).to_time
        event
    end.select do |event|
        event["stage"] == "ResponseComplete" && event["requestReceivedTimestamp"]
    end.sort do |e1, e2|
        e1["requestReceivedTimestamp"] <=> e2["requestReceivedTimestamp"]
    end.map do |event|
        duration = if event["verb"] != "watch" then (event["stageTimestamp"] - event["requestReceivedTimestamp"]) else 0 end
        csv << [event["requestReceivedTimestamp"].strftime("%Y-%m-%d %H:%M:%S"), duration, event["verb"], event["requestURI"], event["user"]["username"], (event["user"]["extra"] || {})["authentication.kubernetes.io/pod-name"], event["sourceIPs"]]
    end
end

puts out