[Sybase] 【每天一点SYBASE】- audit

select "exec sp_audit 'exec_procedure','all','" + name +"','off'" AS '--'
  from sysobjects
 where audflags !=0
   and type in ('P','TR')
select "exec sp_audit 'select','all','" + name +"','off'" AS '--'
  from sysobjects
 where audflags !=0
   and type in ('U','V')
select "exec sp_audit 'insert','all','" + name +"','off'" AS '--'
  from sysobjects where audflags !=0
   and type in ('U','V')
select "exec sp_audit 'update','all','" + name +"','off'" AS '--'
  from sysobjects where audflags !=0
   and type in ('U','V')
select "exec sp_audit 'delete','all','" + name +"','off'" AS '--'
  from sysobjects
 where audflags !=0
   and type in ('U','V')

select * into DB..his_audits_01 from sybsecurity..sysaudits_01 where 1=2
select * into DB..his_audits_02 from sybsecurity..sysaudits_02 where 1=2
select * into DB..his_audits_03 from sybsecurity..sysaudits_03 where 1=2

#!/bin/sh
. /etc/.syb_password.sh
. /etc/SYBASE.sh
SERVER=$1
USER=snxsa
ERRLOG='/logs/sybase/audit_logs_archive.log'
Usage()
{
    echo "archive_audit_log.sh SERVER"
    exit 0
}
if [ $# -ne 1 ]
then
   Usage
   exit 1
fi
case $SERVER in
  SERVER1)
  PASSWORD=$SERVER1_PASSWORD
;;
  SERVER2)
  PASSWORD=$SERVER2_PASSWORD
;;
*)
Usage
;;
esac
  isql -S$SERVER -U$USER <<+ > $ERRLOG
$PASSWORD
declare @cur_audit_num int
select @cur_audit_num=cur.value from master..sysconfigures con,master..syscurconfigs cur 
where con.config=cur.config
and con.name='current audit table'
if @cur_audit_num =1
exec sp_configure "current audit table",0,"with truncate"
begin
insert into DB..his_audits_01 select * from sybsecurity..sysaudits_01
truncate table sybsecurity..sysaudits_01
end
if @cur_audit_num =2
begin
insert into DB..his_audits_02 select * from sybsecurity..sysaudits_02
truncate table sybsecurity..sysaudits_02
end
if @cur_audit_num =3
begin
insert into DB..his_audits_03 select * from sybsecurity..sysaudits_03
truncate table sybsecurity..sysaudits_03
end
return
+
num=`grep -c 'Msg|LIB' $ERRLOG`
if [ $num -gt 0 ] 
then
  mailx -r"upboy_mi@163.com" -s"$SERVER: Archive audits logs ended at `date` with errors" "upboy_mi@163.com" <$DUMPLOG
else
  mailx -r"upboy_mi@163.com" -s"$SERVER: Archive audits logs finished at `date` ok" "upboy_mi@163.com" <$DUMPLOG
fi
exit 0

use sybsystemprocs
go
CREATE PROCEDURE dbo.sp_sybsecurity_audseg_used_80    
AS      
declare @dw int, @hour int    
select @dw = datepart(dw,getdate())    
select @hour = datepart(hh,getdate())           
execute xp_cmdshell 'mailx -r"upboy_mi@163.com" -s "SERVER1: sybsecurity aud_segment usage is over 80%!" "upboy_mi@163.com" < /dev/null ' 
go
use sybsecurity
go
exec sp_addthreshold sybsecurity,aud_seg_01,30720,sp_sybsecurity_audseg_used_80
exec sp_addthreshold sybsecurity,aud_seg_02,30720,sp_sybsecurity_logseg_used_80
exec sp_addthreshold sybsecurity,aud_seg_03,30720,sp_sybsecurity_logseg_used_80
--exec sp_addthreshold sybsecurity,logsegment,10240,sp_sybsecurity_logseg_used_80