Useful bash scripts for analyzing AWS Aurora DB audit logs.

README.md

Usage

1. Enable RDS logger
2. Run some huge operations
3. Download each audit log one by one in single folder.
4. Run next command to join all logs to single file: cat * | awk -F "'" 'BEGIN{OFS="'\''"} NF-=1 {$1=""; print $0}' | cut -c2- > all.sql
5. Download and run analyze.sh.

Explanation

This command will show all requests by type

cat all.sql | awk '{print $1}' | sort | uniq -ic | tr '[:lower:]' '[:upper:]'

Example:

    302 COMMIT
     87 DELETE
   8365 INSERT
      8 ROLLBACK
  72638 SELECT
   1086 SET
   6672 SHOW
    310 START
   3102 UPDATE
      3 USE

Analyze most used tables

INSERT

cat all.sql | grep 'INSERT INTO' | awk '{print $3}' | sort | uniq -ic | sort -h | tail -n 10

UPDATE

cat all.sql | grep 'UPDATE ' | awk '{print $2}' | sort | uniq -ic | sort -h | tail -n 10

DELETE

cat all.sql | grep 'DELETE FROM' | awk '{print $3}' | sort | uniq -ic | sort -h | tail -n 10

SELECT

cat all.sql | grep 'SELECT' | grep 'FROM' | awk -F " FROM " '{print $2}' | awk '{print $1}' | sort | uniq -ic | sort -h | tail -n 10

analyze.sh

#!/bin/bash

echo "All SQL grouped by type"
cat all.sql | awk '{print $1}' | sort | uniq -ic | tr '[:lower:]' '[:upper:]'
echo "\n"
echo "INSERT statements grouped by table"
cat all.sql | grep 'INSERT INTO' | awk '{print $3}' | sort | uniq -ic | sort -h | tail -n 10
echo "\n"
echo "UPDATE statements grouped by table"
cat all.sql | grep 'UPDATE ' | awk '{print $2}' | sort | uniq -ic | sort -h | tail -n 10
echo "\n"
echo "DELETE statements grouped by table"
cat all.sql | grep 'DELETE FROM' | awk '{print $3}' | sort | uniq -ic | sort -h | tail -n 10
echo "\n"
echo "SELECT statements grouped by table"
cat all.sql | grep 'SELECT' | grep 'FROM' | awk -F " FROM " '{print $2}' | awk '{print $1}' | sort | uniq -ic | sort -h | tail -n 10
echo "\n"