Summarise output of auditdb - Unix shell script

From Ingres Community Wiki

Summarise activity: strip_auditdb

This script strips out extra verbosity from the auditdb output

It displays the first x characters of the changed data. You can use this tool to find potential problems then the target specific date/time range to see the whole transaction with the full auditdb output.

strip_auditdb can be used in a pipe directly from auditdb

auditdb ... | strip_auditdb > nice.txt

Or by specifying one or more files.

auditdb ... > lots1.txt

auditdb ... > lots2.txt

strip_auditdb lots*.txt > nice.txt

Written by Paul White

---

Sample

source

sample

And some simple aliases to help

Here are some simple aliases that work on the captured output of an auditdb run. The first alias (tr1) can list the transactions with the highest amount of activity (ie. Inserts, updates, deletes). The second alias (tr2) does much the same, but accumulates the activity over all transactions and hence displays the maximal activity per table. In both cases the output is limited to the top 100 lines.

In a script like ~/.aliases include the lines:

set DP1 = '"$1 $2 $3\n"'
set DP2 = '"$1 $3\n"'
$AUDIT_PAT='/^\s*(\S+)\s*: Transaction Id (\S+).*Table \[(\S+)\]/'
$SORT_PAT='sort | uniq -c | sort -nr | head -100'
alias tr1 "cat \!* | perl -ne '$AUDIT_PAT && print $DP1' | $SORT_PAT"
alias tr2 "cat \!* | perl -ne '$AUDIT_PAT && print $DP2' | $SORT_PAT"

Source the ~/.aliases from your .cshrc/.tcshrc/.bashrc whatever...

Then to use them:

• Generate your auditdb output: auditdb ... > auditdb.log
• Then: tr1 auditdb.log

This could produce output like:
3 Update/Replace 0000465d47ece2d1 pt_detail_audit, biobank
1 Update/Replace 0000465d47ece2d1 pt_raw, biobank
...
ie. Each line shows:
     * The count of a particular type of activity,
     * the type of activity, 
     * the transaction id,
     * and finally the table_name, table_owner.

• And/Or tr2 auditdb.log

This could produce output like:
1240 Insert/Append pt_detail_audit,biobank
...
ie. The transaction detail is stripped from the output and totals per activity/per table are accumulated.

In the above example, it becomes clear that the majority of activity was on the pt_detail_audit table. Combined with the first alias , this tells us that the inserts were done in lots of individual transactions and not one large transaction.

Name: Martin.bowes@***