Db2 audit setup

Db2auditsetup

###########################################################################################
### make a directory to store audit and archievd audcit file 
###########################################################################################
export BASE_PATH=/home  ###### THIS HAS TO BE SET where the auidt data and archived audit stuff goes 
###### THIS HAS TO BE SET 
export DB2_HOME=/home/db2inst1  
###### THIS HAS TO BE SET :: SCHEMA onto which data will be loaded 
export DB2_SCHEMA=AUDIT  
###### THIS HAS TO BE SET:: Set the Extract location
echo -e "\n\nDB2_HOME: $DB2_HOME\nDB2_SCHEMA: $DB2_SCHEMA\nEXTRACT_LOC_PATH: $EXTRACT_LOC_PATH\n\n"
######  This can be modified 
export LOG_PATH=$BASE_PATH/$USER
AUDIT_DATA_PATH=$LOG_PATH/db2audit

AUDIT_DATA_ARCH_PATH=$LOG_PATH/db2auditarch
mkdir -pv $AUDIT_DATA_PATH        #auditdata location
mkdir -pv $AUDIT_DATA_ARCH_PATH    #auditarchive
###### Print the locations
echo -e "\n\nAUDIT_DATA_PATH: $AUDIT_DATA_PATH\nAUDIT_DATA_ARCH_PATH: $AUDIT_DATA_ARCH_PATH\n\n"

###########################################################################################
# set the datapath and archibe path 
###########################################################################################
db2audit configure datapath $AUDIT_DATA_PATH  archivepath  $AUDIT_DATA_ARCH_PATH

###########################################################################################
						#testingn starts here 
###########################################################################################
db2 CONNECT to SAMPLE 
db2 "CREATE AUDIT POLICY ALTPOLICY CATEGORIES AUDIT STATUS BOTH, OBJMAINT STATUS BOTH,  CHECKING STATUS BOTH, EXECUTE STATUS BOTH ERROR TYPE NORMAL"
db2 "CREATE AUDIT POLICY ALTPOLICY CATEGORIES AUDIT STATUS BOTH, OBJMAINT STATUS BOTH,  EXECUTE STATUS BOTH ERROR TYPE NORMAL"

db2 "CREATE AUDIT POLICY AUD_OBJMAINT CATEGORIES AUDIT STATUS BOTH, OBJMAINT STATUS BOTH"

db2 +oz "AUDIT DATABASE USING POLICY ALTPOLICY" > $HOME/auditpolicy.out
#### create a sample table 
db2 +oz "create table babu (col1 char(100))" >> $HOME/auditpolicy.out


echo -e "\n\n############################# AUDIT Policy create output log : @ $HOME/auditpolicy.out \n $(cat $HOME/auditpolicy.out )\n#############################\n\n"


#################################################################################################
##########   TABLE setup copy from $DB2_HOME to local 

echo "CREATE SCHEMA AUDIT; "   > $HOME/db2audit.ddl
echo "SET CURRENT SCHEMA = 'AUDIT'; " >> $HOME/db2audit.ddl

cat $DB2_HOME/sqllib/misc/db2audit.ddl >> $HOME/db2audit.ddl

#more $HOME/db2audit.ddl

db2 +oz -tf $HOME/db2audit.ddl > $HOME/auditschema.out

echo -e "\n\n############################# AUDIT Policy Schema object output log: @ $HOME/auditschema.out \n 
DDL file location : $HOME/db2audit.ddl \n 
$(cat $HOME/auditschema.out )\n#############################\n\n"

##################################################################################################
### LOADING  LOAD FROM $EXTRACT_LOC_PATH/audit.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE  INSERT INTO AUDIT.AUDIT
#### verify db2 "select name,creator From sysibm.systables where name = 'AUDIT'"
##################################################################################################c
############### EXTRACT and load to tables in audit schema defined in variable DB2_SCHEMA

############### EXTRACT 
mkdir -v $EXTRACT_LOC_PATH

############### archive 
db2audit archive database SAMPLE  to $AUDIT_DATA_ARCH_PATH/   # db2audit archive database DTDWHS01  to $AUDIT_DATA_ARCH_PATH/ ## devd551a

##### Extract
db2audit extract delasc to $EXTRACT_LOC_PATH from files $AUDIT_DATA_ARCH_PATH/

############### Verify
echo $(grep -i babu  $EXTRACT_LOC_PATH/*.del | wc -l | awk '{if($0 > 0 )print "Audit setup:: db2audit extract is working and exracted data is located @:"}' ) $EXTRACT_LOC_PATH


##### LOAD THE DATA to SCHEMA AUDIT 
ls -a  $EXTRACT_LOC_PATH/*.del| xargs -n 1 basename > $HOME/audit_filenames.out

while read line; do echo -e " db2 LOAD FROM $EXTRACT_LOC_PATH/$line of DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE  INSERT INTO $DB2_SCHEMA."$(awk -F '.' '{print $1}' <<< $line); done < $HOME/audit_filenames.out > $HOME/db2load.out
while read line;do eval  "$line" ; done  < $HOME/db2load.out



#####################################################################################################
### verify if everything is working 

 db2 -x  "select count(*) From AUDIT.objmaint" | awk '{if($0 > 0 )print "\n\n############################# \n Audit setup is working AUDIT.objmaint has"$0}'