Can be used to query SQL Server Audit Logs that exist on a Storage Account to determine which users logged in and how many times in a given time period

query-audit-log.sh

#!/bin/bash
set -euo pipefail

# Default values for CLI flags
SERVER=""
DATABASE=""

# Use a Service Principal or an Azure AD user with admin rights for your SQL Server
USERNAME=""
PASSWORD=""  # Or use "your_service_principal_client_secret" for Service Principal

AUDIT_LOG_BASE_PATH="https://xxx.blob.core.windows.net/xxx/xxx/xxx/SqlDbAuditing_ServerAudit"
EXPORT_FILENAME="dump.sql.csv"
TEMP_TABLE_NAME="tmp_audit_log"

FROM_DATE="$(gdate "+%Y-%m-%d")"
TO_DATE=$FROM_DATE
DAY_DIFF=$(echo "$(gdate -d "$TO_DATE" +%s)" "$(gdate -d "$FROM_DATE" +%s)" | awk '{print ($1 - $2) / 86400}')

SQL_TEMP_TABLE="CREATE TABLE $TEMP_TABLE_NAME (
    event_time DATETIME,
    server_principal_name VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS,
    statement VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS,
    host_name VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS,
    application_name VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS
);"

# Function to display script usage
display_usage() {
    echo "Usage: $0 -s <server> -d <database> -u <username> -p <password> -x <audit_log_base_path> -e <export_file>"
    echo "Options:"
    echo "  [-f <from_date>]         - YYYY-MM-DD (default: today)"
    echo "  [-t <to_date>]           - YYYY-MM-DD (default: today)"
    exit 1
}

# Parse CLI flags using getopts
while getopts ":s:d:u:p:x:f:t:e:" opt; do
    case $opt in
        s) SERVER="$OPTARG" ;;
        d) DATABASE="$OPTARG" ;;
        u) USERNAME="$OPTARG" ;;
        p) PASSWORD="$OPTARG" ;;
        x) AUDIT_LOG_BASE_PATH="$OPTARG" ;;
        f) FROM_DATE="$OPTARG" ;;
        t) TO_DATE="$OPTARG" ;;
        e) EXPORT_FILENAME="$OPTARG" ;;
        :) echo "Option -$OPTARG requires an argument." >&2; display_usage ;;
        *) echo "Invalid option: -$OPTARG" >&2; display_usage ;;
    esac
done

# Check if all required flags are provided
if [ -z "$SERVER" ] || [ -z "$DATABASE" ] || [ -z "$USERNAME" ] || [ -z "$PASSWORD" ] || [ -z "$AUDIT_LOG_BASE_PATH" ] || [ -z "$EXPORT_FILENAME" ]; then
    echo "Error: Missing required arguments."
    display_usage
fi

# Run SQL Server container using Docker
pre_up ()
{
  stop

  echo "[i] Starting new SQL Container"
  docker run -e 'ACCEPT_EULA=Y' -e "SA_PASSWORD=$PASSWORD" -p 1433:1433 --name sqlserver -d mcr.microsoft.com/mssql/server:latest

  # Wait for the SQL Server container to start
  echo "[i] Waiting for SQL Server container to start..."
  sleep 10
}

# Clean up - remove the temporary table from the original database
clean_up ()
{
  echo "[i] Dropping temporary table from database"
  sqlcmd -S "$SERVER" -d "$DATABASE" -U "$USERNAME" -P "$PASSWORD" -Q "
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[$TEMP_TABLE_NAME]') AND type in (N'U'))
      DROP TABLE [dbo].[$TEMP_TABLE_NAME]
    GO
  "
}

# Import access log file into temporary table using SQLCMD
pull_down ()
{
  CSV_HEADERS="event_time,server_principal_name,statement,host_name,application_name"

  echo "[i] Pushing audit log file into temporary table"
  sqlcmd -S "$SERVER" -d "$DATABASE" -U "$USERNAME" -P "$PASSWORD" -Q "
      $SQL_TEMP_TABLE
      SET NOCOUNT ON;
      INSERT INTO $TEMP_TABLE_NAME
      SELECT
          event_time,
          server_principal_name,
          '\"' + REPLACE(REPLACE(REPLACE(REPLACE(CAST(statement AS VARCHAR(MAX)), CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), ''), CHAR(9), ' ') + '\"' as 'statement',
          host_name,
          application_name
      FROM sys.fn_get_audit_file('$AUDIT_LOG_FILE', NULL, NULL)
      WHERE action_id = 'BCM' and statement LIKE '%FROM%';
      "

  echo "[i] Exporting data into CSV"
  if ! test -f "$HOME/$EXPORT_FILENAME"; then
    echo $CSV_HEADERS > "$HOME/$EXPORT_FILENAME"
  fi

  sqlcmd -S "$SERVER" -d "$DATABASE" -U "$USERNAME" -P "$PASSWORD" -y 0 -s"," -Q "SET NOCOUNT ON; SELECT * FROM [$TEMP_TABLE_NAME]" | cat >> "$HOME/$EXPORT_FILENAME"

  echo "Data exported to $EXPORT_FILENAME successfully."
}

# Import the data from the exported CSV file into the SQL Server container
import_local ()
{
  docker cp "$HOME/$EXPORT_FILENAME" sqlserver:"/var/opt/mssql/$EXPORT_FILENAME"

  echo "[i] Creating local database if it doesnt exist already"
  sqlcmd -S 127.0.0.1,1433 -U sa -P "$PASSWORD" -Q "IF NOT EXISTS (
    SELECT * FROM sys.databases WHERE name = '$DATABASE'
  )
  BEGIN
    CREATE DATABASE [$DATABASE];
  END"

  sqlcmd -S 127.0.0.1,1433 -U sa -d "$DATABASE" -P "$PASSWORD" -Q "$SQL_TEMP_TABLE"

  echo "[i] Importing CSV file to local database"
  echo "BULK INSERT $TEMP_TABLE_NAME FROM '/var/opt/mssql/$EXPORT_FILENAME' WITH (FORMAT = 'CSV');" > .tmp.tsql

  sqlcmd -S 127.0.0.1,1433 -U sa -d "$DATABASE" -P "$PASSWORD" -i .tmp.tsql

  rm .tmp.tsql
}

# Stop and remove the SQL Server container
stop ()
{
  echo "[i] Stopping and removing existing containers"
  docker stop sqlserver || true && docker rm sqlserver || true
}

# Update the last execution time file
end ()
{
  echo "$AUDIT_LOG_BASE_PATH" > ".last-executed"
  exit 0
}

start ()
{
  pre_up
  clean_up
}

iterate ()
{
  pull_down
  import_local
}

start

while [ "$DAY_DIFF" -gt -1 ]
do
  DATE=$(gdate -d "$FROM_DATE +$DAY_DIFF days" "+%Y-%m-%d")
  AUDIT_LOG_FILE="$AUDIT_LOG_BASE_PATH/$DATE"
  DAY_DIFF=$((DAY_DIFF - 1))

  iterate
done;

stop