Business Objects Audit SQL Report Code

Main SQL

DECLARE @DATEPROMPT VARCHAR(20);

DECLARE @SetDateStart DATETIME;
DECLARE @SetDateEnd DATETIME;
DECLARE @CustomDateEnd DATETIME;
DECLARE @CustomDateStart DATETIME;
DECLARE @LocalStartTime DATETIME;

SET @SetDateStart = DATEADD(DAY, -7 + DATEDIFF(DAY, '2013-02-01', GETDATE()), '2013-02-01');
SET @SetDateEnd = DATEADD(dd, 1,DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0));


SET @CustomDateStart = {?StartDate}; --The date is a parameter in crystal
SET @CustomDateEnd = DATEADD(dd, 1,{?EndDate}); --The date is a parameter in crystal

SET @DATEPROMPT = '{?DateOption}'; --This is a parameter in crystal "DateOption"


SELECT DISTINCT
[Events].[Start_Time] [UTC_Start_Time] 
,[Events].[Duration_ms] 
,[Events].[User_Name] 
,[Events].[Object_Name]
,[Events].[Object_Folder_Path] 
,[ObjectsDesc].Object_Type_Name
,[EventsDesc].Event_Type_Name
,dateadd(hour,-datediff(hour,getdate(),getutcdate()),[Start_Time]) [Local_Start_Time]
-- ,[EventDetailDesc].[Event_Detail_Type_Name]
-- ,[EventDetailDesc].[Event_Detail_Type_ID] 


FROM [BOE_audit].[dbo].[ADS_EVENT] [Events] with(nolock)
INNER JOIN [BOE_audit].[dbo].[ADS_OBJECT_TYPE_STR] [ObjectsDesc] with(nolock)ON [Events].[Object_Type_ID] = [ObjectsDesc].[Object_Type_ID]
INNER JOIN [BOE_audit].[dbo].[ADS_EVENT_TYPE_STR] [EventsDesc] with(nolock)ON [Events].[Event_Type_ID] = [EventsDesc].[Event_Type_ID]
INNER JOIN [BOE_audit].[dbo].[ADS_EVENT_DETAIL] [EventDetail] with(nolock)ON [Events].[Event_ID] = [EventDetail].[Event_ID]
INNER JOIN [BOE_audit].[dbo].[ADS_EVENT_DETAIL_TYPE_STR] [EventDetailDesc] with(nolock)ON [EventDetail].[Event_Detail_Type_ID] = [EventDetailDesc].[Event_Detail_Type_ID]

WHERE [Events].[Event_Type_ID] IN {?EVENT_TYPE}
AND [Events].[Object_Type_ID] IN {?OBJECT_TYPE}
AND [Events].[User_Name] IN {?USER_NAME}
AND [ObjectsDesc].[Language] = 'EN'
AND [EventsDesc].[Language] = 'EN'
AND [EventDetailDesc].[Language] = 'EN'
AND [EventDetailDesc].[Event_Detail_Type_Name] IN {?EVENT_DETAIL}

AND(( @DATEPROMPT = 'Last7Days' AND dateadd(hour,-datediff(hour,getdate(),getutcdate()),[Events].[Start_Time]) >=@SetDateStart 
AND dateadd(hour,-datediff(hour,getdate(),getutcdate()),[Events].[Start_Time])< @SetDateEnd )

OR ( @DATEPROMPT = 'CustomDateRange' AND dateadd(hour,-datediff(hour,getdate(),getutcdate()),[Events].[Start_Time])>= @CustomDateStart 
AND dateadd(hour,-datediff(hour,getdate(),getutcdate()),[Events].[Start_Time]) < @CustomDateEnd ))

ORDER BY [Events].[Start_Time] DESC

 

List of Objects

SELECT [Object_Type_ID]
,[Language]
,[Object_Type_Name]
FROM [BOE_audit].[dbo].[ADS_OBJECT_TYPE_STR]

WHERE [Language] = 'EN'

ORDER BY Object_Type_Name

 

List of Events

SELECT [Event_Type_ID]
,[Language]
,[Event_Type_Name]
FROM [BOE_audit].[dbo].[ADS_EVENT_TYPE_STR]

WHERE [Language] = 'EN' AND Event_Type_ID < 10000

 

List of Usernames

SELECT DISTINCT

[User_Name]


FROM [BOE_audit].[dbo].[ADS_EVENT]

 

List of Event Details

SELECT 
[EventDetailDesc].[Event_Detail_Type_ID]
,[EventDetailDesc].[Event_Detail_Type_Name]

FROM [BOE_audit].[dbo].[ADS_EVENT_DETAIL_TYPE_STR] [EventDetailDesc]
WHERE[EventDetailDesc].[Language] = 'EN'