Skip to Content
0

Audit Database 3.1 query - updating to work on 4.1

Jul 14, 2017 at 09:28 AM

289

avatar image
Former Member

I had a query that ran against BOXI_AUDIT to search where a specific object in a universe had been used. It ran just using 2 tables, AUDIT_DETAIL and AUDIT_EVENT.

It seems the equivalent database in 4.1 is BOBI_AUDIT and equivalent tables are ADS_EVENT and ADS_EVENT_DETAIL.

However it seems the whole logic of the database has changed. Am I going to have to reinvent the wheel for the same query against 4.1?

/* RUN AGAINST BOXI_AUDIT. NOTE DATA IS RESTRICTED FOR REPORTS REFRESHED IN 2013 ENTER OBJECT NAME AND UNIVERSE NAME TO SEARCH IN WHERE CLAUSE */ select reportname,Report_Location,object1,uni.universe_name ,max(report.maxdate)as Last_Run_Date from -----------distinct reports refreshed in 2013 (Select case when a.Detail_Type_ID=8 then cast(Detail_text as nvarchar(255)) else '' end as reportname, b.Event_ID, MAX(b.Start_Timestamp)as maxdate from AUDIT_DETAIL a inner join AUDIT_EVENT b on a.Event_id = b.event_id and a.server_cuid = b.server_cuid and a.event_id in (select event_id from AUDIT_DETAIL where Detail_type_id = 2 and cast(Detail_text as nvarchar(255)) like('%')) and b.event_type_id in (19) and year(b.Start_Timestamp)=2013 and case when a.Detail_Type_ID=8 then cast(Detail_text as nvarchar(255)) else '' end<>'' group by case when a.Detail_Type_ID=8 then cast(Detail_text as nvarchar(255)) else '' end, b.Event_ID --order by MAX(b.Start_Timestamp) desc )report inner join -----------distinct obejcts refreshed in 2013 (Select case when a.Detail_Type_ID=3 then cast(Detail_text as nvarchar(255)) else '' end as object1, b.Event_ID, MAX(b.Start_Timestamp) as maxdate /*case when a.Detail_Type_ID=2 then cast(Detail_text as nvarchar(255)) else '' end as universe, case when a.Detail_Type_ID=3 then cast(Detail_text as nvarchar(255)) else '' end as object1, case when a.Detail_Type_ID=8 then cast(Detail_text as nvarchar(255)) else '' end as reportname*/ from AUDIT_DETAIL a inner join AUDIT_EVENT b on a.Event_id = b.event_id and a.server_cuid = b.server_cuid and a.event_id in (select event_id from AUDIT_DETAIL where Detail_type_id = 2 and cast(Detail_text as nvarchar(255)) like('%')) and b.event_type_id in (19) and year(b.Start_Timestamp)=2013 and case when a.Detail_Type_ID=3 then cast(Detail_text as nvarchar(255)) else '' end <>'' group by case when a.Detail_Type_ID=3 then cast(Detail_text as nvarchar(255)) else '' end, b.Event_ID --order by MAX(b.Start_Timestamp) desc )obj on report.Event_ID=obj.Event_ID inner join ---------------distinct universe (select distinct event_id,cast(Detail_text as nvarchar(255)) as universe_name from AUDIT_DETAIL where Detail_type_id = 2) uni on report.Event_ID=uni.Event_ID inner join (Select case when a.Detail_Type_ID=43 then cast(Detail_text as nvarchar(255)) else '' end as Report_Location, b.Event_ID, MAX(b.Start_Timestamp) as maxdate from AUDIT_DETAIL a inner join AUDIT_EVENT b on a.Event_id = b.event_id and a.server_cuid = b.server_cuid and a.event_id in (select event_id from AUDIT_DETAIL where Detail_type_id = 2 and cast(Detail_text as nvarchar(255)) like('%')) and b.event_type_id in (19) and year(b.Start_Timestamp)=2013 and case when a.Detail_Type_ID=43 then cast(Detail_text as nvarchar(255)) else '' end <>'' group by case when a.Detail_Type_ID=43 then cast(Detail_text as nvarchar(255)) else '' end, b.Event_ID)Rep_loc on report.Event_ID=Rep_loc.Event_ID where --reportname='DF 20130221 Meta Data Test.wid' object1='period' and uni.universe_name='Ceramics Sales' group by reportname,object1,universe_name,Report_Location

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers