#!/usr/bin/env bash
# Purpose: Convert audit.aud (DB2 audit file) to CSV format.
# Author: A-Lang
# Created: 2023/1/12
# Updated: 2023/1/12
#
# The sample of the file audit.aud:
#
#   timestamp=2023-01-08-23.13.02.498212
#   category=CHECKING
#   audit event=CHECKING_OBJECT
#   event correlator=127
#   event status=0
#   database=RPTDB
#   userid=winmfg
#   authid=WINMFG
#   application id=10.8.25.30.64020.230108151301
#   application name=EXCEL.EXE
#   package schema=NULLID
#   package name=SYSSH200
#   package section=4
#   object schema=ISTRPT
#   object name=FRLOT
#   object type=TABLE
#   access approval reason=OBJECT
#   access attempted=SELECT
#   local transaction id=0x00000001b655079c
#   global transaction id=0x0000000000000000000000000000000000000000
#   instance name=istrpt
#   hostname=BSMDB_B
#


Usage() {
    cat <<- EOT
  Usage: ${0##/*/} -c 22 -i audit.aud -o "/path/to/output"

  Options:
  -c, Column Numbers, specify the numbers of the columns. (numbers)
  -i, The file name of the input(.aud), such as <audit.aud>.
  -o, The file name of the output(.csv), if not specified, the result will be displayed on stdout.  
  -h, Help.
EOT
}

Gen_csv() { 
    nfp1=$(( cols_num * 2))
    awk -v nfp2=$nfp1 '
BEGIN {
    FS="=";
    RS="";
}

# Filter for the 22 columns (NF=22x2)
NF==nfp2 {
    # Remove semicolon character
    { gsub(/\;/, "") }

    {
       TIMESTAMP=2; CATEGORY=4; AUDIT_EVENT=6; EVENT_CORRELATOR=8; EVENT_STATUS=10; DATABASE=12; USERID=14; AUTHID=16; APPLICATION_ID=18; APPLICATION_NAME=20; PACKAGE_SCHEMA=22; PACKAGE_NAME=24; PACKAGE_SECTION=26; OBJECT_SCHEMA=28; OBJECT_NAME=30; OBJECT_TYPE=32; ACCESS_APPROVAL_REASON=34; ACCESS_ATTEMPTED=36; LOCAL_TRANSACTION_ID=38; GLOBAL_TRANSACTION_ID=40; INSTANCE_NAME=42; HOSTNAME=44;  
    
       f1=1; f2=3; f3=5; f4=7; f5=9; f6=11; f7=13; f8=15; f9=17; f10=19; f11=21; f12=23; f13=25; f14=27; f15=29; f16=31; f17=33; f18=35; f19=37; f20=39; f21=41; f22=43 
    }
    
    if (! headline)
    {
        headline = sprintf( "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s", $f1, $f2, $f3, $f4, $f5, $f6, $f7, $f8, $f9, $f10, $f11, $f12, $f13, $f14, $f15, $f16, $f17, $f18, $f19, $f20, $f21, $f22 );
        print headline;
    }

    dataline = sprintf("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s", $TIMESTAMP, $CATEGORY, $AUDIT_EVENT, $EVENT_CORRELATOR, $EVENT_STATUS, $DATABASE, $USERID, $AUTHID, $APPLICATION_ID, $APPLICATION_NAME, $PACKAGE_SCHEMA, $PACKAGE_NAME, $PACKAGE_SECTION, $OBJECT_SCHEMA, $OBJECT_NAME, $OBJECT_TYPE, $ACCESS_APPROVAL_REASON, $ACCESS_ATTEMPTED, $LOCAL_TRANSACTION_ID, $GLOBAL_TRANSACTION_ID, $INSTANCE_NAME, $HOSTNAME );
    print dataline;

}

' $input
}

#
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
#
if [ $# -eq 0 ] 
then
    # no arguments
    Usage
    exit 1
fi

while getopts "c:i:o:h" opt
do
    case $opt in
      c) cols_num=$OPTARG ;;
      i) input=$OPTARG ;;
      o) output=$OPTARG ;;
      h) Usage; exit ;;
      \? ) echo -e "\n  Option does not exist : $OPTARG\n"
         Usage; exit 1 ;;
    esac
done

if [ ! -f "$input" ] 
then
    echo "Abort: The INPUT file $input not found!"
    exit 1
fi

if [ -z $cols_num ] 
then
    echo "Abort: The option -c not specified!"
    exit 1
fi

if [ -z $output ]
then
    Gen_csv
else
    echo "Converting the $input to the $output, please be patient..."
    Gen_csv > $output
    echo "Done."
fi