/*
** Audit Trail Collector for SEK
*/

-----------------------------------------------------------
-- Install some stuff in 'sybsecurity'
-- If the database doesnt exists, then terminate the session.
-----------------------------------------------------------
use sybsecurity
go
if (db_name() != 'sybsecurity')
begin
	print ''
	print '############################################################################'
	print '## ERROR: database ''sybsecurity'' is not available... exiting...'
	print '############################################################################'
	print ''
end
go
if (db_name() != 'sybsecurity')
begin
	select syb_quit()
end
go

-----------------------------------------------------------
-- Drop table if we want to *reinstall* the table.
-----------------------------------------------------------
--drop table audit_collector_config
go

-----------------------------------------------------------
-- Create a Config table if it dosn't exists
-----------------------------------------------------------
if not exists (select * from sysobjects where type = 'U' and name = 'audit_collector_config')
begin
	print ' *** Create table: audit_collector_config'
	exec('
		create table audit_collector_config
		(
			 last_poll_ts              datetime    not null  -- when did we last poll data
			,truncate_after_x_hours    int         not null  -- truncate the sysaudits_## after X hours
			,check_data_size_x_minute  int         not null  -- Check data size every x minute, and truncate if below data_size_free_mb_th
			,check_xlog_size_x_minute  int         not null  -- Check xlog size every x minute, and truncate if below xlog_size_free_mb_th
			,th_data_size_free_mb      int         not null  -- if data usage is below 100 MB free, do truncate 
			,th_xlog_size_free_mb      int         not null  -- if xlog usage is below 100 MB free, do truncate
			,last_truncate_ts          datetime    not null  -- last time the sysaudits_## was truncated
			,last_th_truncate_ts       datetime        null  -- last time the sysaudits_## was truncated from the threshold actions procedure
			,last_data_size_check_ts   datetime    not null  -- last time we checked for data size
			,last_xlog_size_check_ts   datetime    not null  -- last time we checked for log size
		)
	')
end
go

-----------------------------------------------------------
-- Insert default CONFIG values if no rows exists in table
-----------------------------------------------------------
if not exists (select * from audit_collector_config)
begin
	print ' *** Insert table: audit_collector_config, adding DEFAULT data...'
	
	insert into audit_collector_config (
		 last_poll_ts
		,truncate_after_x_hours
		,check_data_size_x_minute
		,check_xlog_size_x_minute
		,th_data_size_free_mb
		,th_xlog_size_free_mb
		,last_truncate_ts
		,last_th_truncate_ts
		,last_data_size_check_ts
		,last_xlog_size_check_ts
	)
	select
		 '2000-01-01 00:00:00'   -- last_poll_ts                -- this makes us get ALL records on first execution
		,6                       -- truncate_after_x_hours      -- 6 hours between truncate
		,15                      -- check_data_size_x_minute    -- if data usage is below 100 MB free, do truncate 
		,15                      -- check_xlog_size_x_minute    -- if xlog usage is below 100 MB free, do truncate 
		,200                     -- th_data_size_free_mb        -- if data usage is below 100 MB free, do truncate 
		,100                     -- th_xlog_size_free_mb        -- if xlog usage is below 100 MB free, do truncate 
		,getdate()               -- last_truncate_ts            -- do not start with truncate, instead do it in X hours
		,null                    -- last_th_truncate_ts         -- if threshold_procs etc truncates the table
		,getdate()               -- last_data_size_check_ts     -- last time we checked for data size
		,getdate()               -- last_xlog_size_check_ts     -- last time we checked for log size
end
go

-----------------------------------------------------------
-- DROP Procedure: audit_collector
-----------------------------------------------------------
if exists (select * from sysobjects where type = 'P' and name = 'audit_collector')
begin
	print ' *** Drop   proc: audit_collector'
	drop procedure audit_collector
end
go

-----------------------------------------------------------
-- CREATE Procedure: audit_collector
-----------------------------------------------------------
print ' *** Create proc: audit_collector'
go
/*=====================================================================** 
** PROCEDURE: audit_collector
**---------------------------------------------------------------------** 
** Description: 
** 
** Internal flow of the procedure.
**
** Poll phase:
**   - Select new audit data from table 'sysaudits_##' transfer to client
**     Note: Only records that hasn't been sent to the client will be sent.
**     Note: Only one client will be supported the "sek_sek_auditing" system
** Cleanup phase:
**   - every X hour truncate records in 'sysaudits_##' (configurable)
**   - remove already fetched records from 'audit_trail' (based on poll time)
**
** NOTE: For the moment this uses a "Single audit trail table"
**       In the future we might switch to many "trails".
**       Switching betweed the trail# will be done inside this proc.
**
**       Doing it like this means: that the "interface" between sek_sek_auditing 
**       and sybase do NOT have to change.
**       sek_sek_auditing will just have to call this procedure... which should handle
**       whatever is needed to be done.
** 
**---------------------------------------------------------------------** 
** Input parameters: 
** 
**---------------------------------------------------------------------** 
** output parameters: 
** - none
** 
**---------------------------------------------------------------------** 
** output select: 
** - Basically: select audit_event_name(event) as event_name, *
**              from sysaudits_0#
** 
**---------------------------------------------------------------------** 
** Return codes: 
** 
** 0 - ok. 
** 1 - error 
** 
**---------------------------------------------------------------------** 
** Error codes: 
**  - none
** 
**---------------------------------------------------------------------** 
** History: 
** 
** 2020-05-20  1.0.0  Goran.Schwarz@b3.se
**                    Created 
** 2023-06-01  2.0.0  Goran.Schwarz@executeit.se
**                    Simplifying it due to 'sek_sek_auditing' collector
**                    sek_sek_auditing will send data to "subscribers"
**                    sek_sek_auditing also collects data from MDA tables
**---------------------------------------------------------------------*/ 
create proc audit_collector
with execute as owner
as
begin
	set nocount on

	-- declare som vars
	declare @i_last_poll_ts             datetime
	declare @i_truncate_after_x_hours   int
	declare @i_th_data_size_free_mb     int
	declare @i_th_xlog_size_free_mb     int
	declare @i_check_data_size_x_minute int
	declare @i_check_xlog_size_x_minute int
	declare @i_last_truncate_ts         datetime
	declare @i_last_data_size_check_ts  datetime
	declare @i_last_xlog_size_check_ts  datetime


	declare @s_last_poll_ts             datetime

	declare @do_truncate                int
	declare @now                        datetime
	declare @err                        int
	declare @rc                         int

	declare @msg varchar(255)

	--------------------------------------------
	-- Check Settings
	--------------------------------------------
	if (@@trancount > 0)
	begin
		raiserror 99999 'You can NOT execute procedure ''audit_collector'' within a transaction. @@trancount=%1!', @@trancount
		return 1
	end

	--------------------------------------------
	-- Check input
	--------------------------------------------
	/* no input parameters to this procedure */


	-- initialize the vars for: INTERNAL collector
	select
		 @i_last_poll_ts             = last_poll_ts
		,@i_truncate_after_x_hours   = truncate_after_x_hours
		,@i_th_data_size_free_mb     = th_data_size_free_mb
		,@i_th_xlog_size_free_mb     = th_xlog_size_free_mb
		,@i_check_data_size_x_minute = check_data_size_x_minute
		,@i_check_xlog_size_x_minute = check_xlog_size_x_minute
		,@i_last_truncate_ts         = last_truncate_ts
		,@i_last_data_size_check_ts  = last_data_size_check_ts
		,@i_last_xlog_size_check_ts  = last_xlog_size_check_ts
	from audit_collector_config

	-- Set default values if not found in config
	if (@i_last_poll_ts             is null) set @i_last_poll_ts             = '2000-01-01 00:00:00'
	if (@i_truncate_after_x_hours   is null) set @i_truncate_after_x_hours   = 6
	if (@i_th_data_size_free_mb     is null) set @i_th_data_size_free_mb     = 100
	if (@i_th_xlog_size_free_mb     is null) set @i_th_xlog_size_free_mb     = 100
	if (@i_check_data_size_x_minute is null) set @i_check_data_size_x_minute = 15
	if (@i_check_xlog_size_x_minute is null) set @i_check_xlog_size_x_minute = 15
	if (@i_last_data_size_check_ts  is null) set @i_last_data_size_check_ts  = '2000-01-01 00:00:00'
	if (@i_last_xlog_size_check_ts  is null) set @i_last_xlog_size_check_ts  = '2000-01-01 00:00:00'
	if (@i_last_truncate_ts         is null) set @i_last_truncate_ts         = '2000-01-01 00:00:00'

	
	if (@s_last_poll_ts is null)
		set @s_last_poll_ts = '2000-01-01 00:00:00'

	-- initialize other vars
	select @do_truncate              = 0
	select @now                      = getdate()

	-- set some fields in sysprocesses
--	set clientname @subscriber
	set clienthostname 'at: sysaudit_##->client'


	--------------------------------------------
	-- SELECT Records from: sysaudits_01 -->> audit_trail
	-- Sample records since "previous-internal-sample"
	-- if records are added to the audit trail *while* we are doing the select, those records will be "moved" on next "collect"
	--------------------------------------------
	select 
		 event_name = audit_event_name(event)
		,event
		,eventmod
		,spid
		,eventtime
		,sequence
		,suid
		,dbid
		,objid
		,xactid
		,loginname
		,dbname
		,objname
		,objowner
		,extrainfo
		,nodeid
	from dbo.sysaudits_01 
	where eventtime between @i_last_poll_ts and @now

	set @err = @@error, @rc = @@rowcount
	--print 'DEBUG: sysaudits_01-->>audit_trail: rowcount=%1!, i_last_poll_ts=%2!, now=%3!', @rc, @i_last_poll_ts, @now

	--------------------------------------------
	-- ERROR of moving data from: sysaudits_01 -->> audit_trail
	-- give an EMPTY resultset back
	--------------------------------------------
	if (@err != 0)
	begin
		set @msg = 'at: sysaudit_##->client::error=' + convert(varchar(10), @err)
		set clienthostname @msg
		
/*<--*/	return 1
	end

	--------------------------------------------
	-- SUCCESS of moving data from: sysaudits_01 -->> caller
	-- now: possibly cleanup
	--------------------------------------------

	--------------------------------------------
	-- update INTERNAL: 'last_poll_ts'
	--------------------------------------------
	update audit_collector_config 
	   set last_poll_ts = @now


	--------------------------------------------
	-- POST WORK
	--------------------------------------------

	--------------------------------------------
	-- If it's time to check **xlog** usage
	--------------------------------------------
	if (datediff(mi, @i_last_xlog_size_check_ts, getdate()) > @i_check_xlog_size_x_minute)
	begin
		-- Check if the transaction log is starting to get full...
		-- then do action: "try to truncate tran log"
		declare @logSizeFreeInMb int
		set @logSizeFreeInMb = convert(int, (lct_admin('logsegment_freepages',db_id('sybsecurity'))-lct_admin('reserved_for_rollbacks',db_id('sybsecurity'))) / (1024.0*1024.0/@@maxpagesize))
		if (@logSizeFreeInMb < @i_th_xlog_size_free_mb)
		begin
			-- set some fields in sysprocesses
			set clienthostname 'at: syslogs: dump tran'
	
			set @msg = 'WARNING: Free XLOG size is below ' + convert(varchar(20), @i_th_xlog_size_free_mb) + ' MB (now at: ' + convert(varchar(20), @logSizeFreeInMb) + ' MB) in database sybsecurity. ACTION: TRUNCATING transaction log'
			dbcc printolog(@msg)
	
			-- Also send the message to the client
			print @msg
	
			-- now dump/truncate the transaction log
			dump tran sybsecurity with truncate_only
		end
		
		-- Update the timestamp when we did the check
		update audit_collector_config 
		   set last_xlog_size_check_ts = getdate()
	end


	--------------------------------------------
	-- If it's time to check **data** usage
	--------------------------------------------
	if (datediff(mi, @i_last_data_size_check_ts, getdate()) > @i_check_data_size_x_minute)
	begin
		-- Check if all data segments (except xlog) is starting to get full...
		-- then request action: @do_truncate = 1
		declare @dataSizeFreeInMb int
		select @dataSizeFreeInMb = convert(int, sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)/(1024.0*1024.0/@@maxpagesize)))	from master.dbo.sysusages u readpast where u.dbid = db_id('sybsecurity') and (segmap & (2147483647-4)) > 0
		if (@dataSizeFreeInMb < @i_th_data_size_free_mb)
		begin
			set @do_truncate = 1

			set @msg = 'WARNING: Free DATA size is below ' + convert(varchar(20), @i_check_data_size_x_minute) + ' MB (now at: ' + convert(varchar(20), @dataSizeFreeInMb) + ' MB) in database sybsecurity. ACTION: @do_truncate = 1'
			dbcc printolog(@msg)
	
			-- Also send the message to the client
			print @msg
	
		end
	
		-- Update the timestamp when we did the check
		update audit_collector_config 
		   set last_data_size_check_ts = getdate()
	end


	--------------------------------------------
	-- If it's time to TRUNCATE sysaudits_01
	--------------------------------------------
	if (datediff(hh, @i_last_truncate_ts, getdate()) > @i_truncate_after_x_hours)
	begin
		set @do_truncate = 1
	end

	
	--------------------------------------------
	-- truncate the audit trail if it's time for that
	--------------------------------------------
	if (@do_truncate = 1)
	begin
		-- set some fields in sysprocesses
		set clienthostname 'at: trunc: sysaudits'

		-- In here we could change sysaudits_## table if we DO NOT want to loose ANY data...
		-- That is data written between 'below insert' and 'truncate'
		-- But for now... lets asume that it's OK to loose a "few" records
		
		-- Move LAST part from: sysaudits_01 -->> client
		select 
			 event_name = audit_event_name(event)
			,event
			,eventmod
			,spid
			,eventtime
			,sequence
			,suid
			,dbid
			,objid
			,xactid
			,loginname
			,dbname
			,objname
			,objowner
			,extrainfo
			,nodeid
		from dbo.sysaudits_01 
		where eventtime > @now

		print 'INFO: Truncating table: sysaudits_01'

		-- Truncate is preferred (but we might loose a few records, that comes in while the select is executing)
		-- If we do NOT want to loose any data... Then use more than one audit trail table!
		truncate table dbo.sysaudits_01

		-- Remember when we last did a "truncate"
		update audit_collector_config 
		   set last_truncate_ts = getdate()
	end

	-- set some fields in sysprocesses
	set clienthostname 'at: end'

	-- OK
	return 0
end
go
-- The procedure should only be executed in 'unchaned' mode (autocommit on)
exec sp_procxmode audit_collector, 'unchained'
go

-----------------------------------------------------------
-- Create 'audit_user' if it do not exists in sybsecurity 
-----------------------------------------------------------
if not exists (select * from sysusers where name = 'audit_user')
begin
	print ' -- Adding user ''audit_user'' in database ''sybsecurity''.'
	exec sp_adduser audit_user 
end
go

-----------------------------------------------------------
-- GRANT exec to Procedure: audit_collector
-----------------------------------------------------------
print ' *** sybsecurity: Granting rights to: audit_user'
grant exec on audit_collector to audit_user
go


exit

-----------------------------------
-- TEST CODE
-----------------------------------

setuser 'audit_user'
setuser
sp_addlogin 'audit_user', 'sybase11'
sp_role 'grant', 'sso_role', 'audit_user'
sp_role 'grant', 'mon_role', 'audit_user'
sybsecurity..sp_adduser 'audit_user'
grant select on master.dbo.sysloginroles to audit_user

select * from sysloginroles
-- Get Audit Records
exec audit_collector
go

use sybsecurity
-- Check config
select * from audit_collector_config

-- reset the last poll time
update audit_collector_config set last_poll_ts     = '2000-01-01 00:00:00' -- force refetch from: sysaudits_01 -->> client
update audit_collector_config set last_truncate_ts = '2000-01-01 00:00:00' -- force truncation

select sysaudits_01 = count(*) from dbo.sysaudits_01
select * from dbo.sysaudits_01 --where eventtime = '2023-05-25 19:11:05.936'
go filter sysindexes

select * from dbo.ch_events




sp_helprotect audit_collector
select suid, role_name(srid) from master.dbo.sysloginroles
select * from sysprocesses
select asehostname()
go 100

-----------------------------------------------------------
-- Drop table if we want to *reinstall* the table.
-----------------------------------------------------------
use sybsecurity;
drop table audit_collector_config;
drop  procedure audit_collector;
select * from audit_collector_config
