<pre>
if exists (select name from master..sysdatabases where name='sybsecurity')
	use sybsystemprocs
else
begin
   print "*** THIS NOT A SYBASE 10/11 SERVER OR AUDITING NOT INSTALLED  ***"
   print "***            --- IGNORE FOLLOWING ERRORS ---                ***"
end
go

if exists (select name from master..sysdatabases where name='sybsystemprocs')
	dump tran sybsystemprocs with truncate_only
go

if exists (select * from sysobjects where name = "sp__auditreport")
	drop procedure sp__auditreport
go

create procedure sp__auditreport
	@fromdate		datetime = NULL,
	@todate			datetime = "2100/01/01",
	@name			char(30) = "%",
	@pid      		int = -1,
	@event	 		char(30) = "%",
	@fulltext		char(1) = "N",
	@text			char(1) = "N", /* alternative to @fulltext */
	@help   		char(1) = "N"
as

/*
* @(#)sp__auditreport.sql	1.7 5/19/97 
* This procedure generates truncates the sysaudits table 
*
* USAGE: sp__auditreport [@fromdate][,@todate][,@name]
*        [,@pid][,@event][,@fulltext (Y/N)]
* exec sp__auditreport @help='Y'  for event list
* Author: Michael Chachich <Michael_Chachich_Tk@dev.japan.ml.com> 
*
*/

	declare @maxpid int

	if (@pid <> -1) 
		select @maxpid = @pid
	else
		select @maxpid = 999

	if (@text <> "N") select @fulltext = @text

	create table #events (event int, event_str char(6))

	insert into #events select  1, "AUD ON"
	insert into #events select  2, "AUD OFF"
	insert into #events select  3, "LOGIN"
	insert into #events select  4, "LOGOUT"
	insert into #events select  5, "REBOOT"
	insert into #events select  6, "RPC"
	insert into #events select  7, "ROLE"
	insert into #events select  8, "SSA-ROLE"
	insert into #events select  9, "SSO-ROLE"
	insert into #events select 10, "OP-ROLE"
	insert into #events select 12, "NAV-ROLE"
	insert into #events select 13, "ERROR"
	insert into #events select 14, "AD-HOC"
	insert into #events select 15, "REP-ROLE"
	insert into #events select 100, "DB REF"
	insert into #events select 101, "TBL REF"
	insert into #events select 102, "VW REF"
	insert into #events select 103, "SP REF"
	insert into #events select 104, "TRG REF"
	insert into #events select 105, "TBL ACC"
	insert into #events select 106, "VW ACC"
	insert into #events select 107, "TEXT"

	if ((@fromdate is null and @name="%" and @pid=-1
			and @event="%") or @help <> "N")
	begin
		/* print usage in case that's what was desired */
		print "USAGE: sp__auditreport [@fromdate][,@todate][,@name]"
		print "       [,@pid][,@event][,@fulltext (Y/N)]"
		if @help<>'Y'
			print "exec sp__auditreport @help='Y'  for event list"
		else
			select event_str "EVENTS" from #events
		return 1
	end

	/* default from-date is current date minus 30 minutes */
	if (@fromdate is null)
		select @fromdate = dateadd(mi,-30,getdate())

	if (@fulltext = "N")
		select 
			substring(convert(char,eventtime,1),1,5) + " " +
			substring(convert(char,eventtime,8),1,5) "eventdate",
			event_str "event", 
			convert(char(3),  spid) "pid",
			convert(char(10), loginname) "name", 
			convert(char(12), dbname) "dbname",
			convert(char(12), object_name(objid, dbid)) "objname",
			convert(char(18),extrainfo) "extrainfo"
		from sybsecurity..sysaudits a, #events b
		where eventtime between @fromdate and @todate
		  and a.event = b.event
		  and a.loginname like @name
		  and b.event_str like @event
		  and spid >= @pid
		  and spid <= @maxpid
		order by eventtime, sequence
	else
		select 
			substring(convert(char,eventtime,1),1,5) + " " +
			substring(convert(char,eventtime,8),1,5) "eventdate",
			event_str "event", 
			convert(char(3),  spid) "pid",
			convert(char(10), loginname) "name", 
			convert(char(12), dbname) "dbname",
			convert(char(12), object_name(objid, dbid)) "objname",
			extrainfo
		from sybsecurity..sysaudits a, #events b
		where eventtime between @fromdate and @todate
		  and a.event = b.event
		  and a.loginname like @name
		  and b.event_str like @event
		  and spid >= @pid
		  and spid <= @maxpid
		order by eventtime, sequence
go
set nocount on
if not exists (select name from master..sysdatabases where name='sybsecurity')
begin
	print "***            ---        COMPLETE         ---                ***"
end
go

/* if user isn't granted sso_role, they can't access the audits anyway */

grant execute on sp__auditreport to public
go

