SELECT
ADS_EVENT_DETAIL.Event_ID,
ADS_EVENT_DETAIL.Event_Detail_ID,
ADS_EVENT_DETAIL.Event_Detail_Type_ID,
ADS_EVENT_DETAIL_TYPE_STR.Event_Detail_Type_Name,
ADS_EVENT_DETAIL.Bunch,
ADS_EVENT_DETAIL.Event_Detail_Value
FROM
ADS_EVENT_DETAIL INNER JOIN ADS_EVENT_DETAIL_TYPE_STR ON (ADS_EVENT_DETAIL.Event_Detail_Type_ID=ADS_EVENT_DETAIL_TYPE_STR.Event_Detail_Type_ID AND ADS_EVENT_DETAIL_TYPE_STR.Language='EN')
I get this output;
Event Id Event Detail Type Id Event Detail Id Event Detail Type Name Event Detail Value Bunch
16871417280893517905 14 11 Universe Name mydwhuniverse 1
14 14 Universe Name mydwhuniverse 1
14 17 Universe Name mydwhuniverse 1
31 12 Universe Object Name mydwhuniverse 1
31 15 Universe Object Name mydwhuniverse 1
31 18 Universe Object Name mydwhuniverse 1
As you see,my Event Detail Type Name and Event Detail Value are the same,is thi a bug ,or something else,and is there a solution for this?
I need to see,for exmp.;
Universe Name= mydwhuniverse
Universe Object Name =City
Universe Object Name = country
Having said that, I don’t think you’ll be able to get the information you’re looking for from the audit database - it doesn’t show the specific universe objects that are being used.
Sorry but the query I wrote was wrong,the true query is here;
SELECT
ADS_EVENT_DETAIL.Event_ID,
ADS_EVENT_DETAIL.Event_Detail_ID,
ADS_EVENT_DETAIL.Event_Detail_Type_ID,
ADS_EVENT_DETAIL_TYPE_STR.Event_Detail_Type_Name,
ADS_EVENT_DETAIL.Bunch,
ADS_EVENT_DETAIL.Event_Detail_Value
FROM
ADS_EVENT_DETAIL INNER JOIN ADS_EVENT_DETAIL_TYPE_STR ON (ADS_EVENT_DETAIL.Event_Detail_Type_ID=ADS_EVENT_DETAIL_TYPE_STR.Event_Detail_Type_ID AND ADS_EVENT_DETAIL_TYPE_STR.Language=‘EN’)
WHERE ADS_EVENT_DETAIL.Event_ID in (select Event_ID from ADS_EVENT t where USER_NAME=userName(Write a user name who made o refresh action) and t.Event_Type_ID=1003)
– Event_Type_ID=1003 --> Refresh
The Record;
ADS_EVENT_DETAIL.Event_Detail_Type_ID=31
ADS_EVENT_DETAIL_TYPE_STR.Event_Detail_Type_Name=Universe Object Name
The audit database does not store the object names, just the universe names. So, you’re not going to be able to use it to get what you’re looking for.
To get this, check in BOB Downloads and see whether there’s been an app posted that will pull this information. If there isn’t, you’ll have to write an app using the SDK that will open each Webi report in your system and pull the information from it.
But in BO XI 3.1 ,when I run this Query,I can get that data I need;(object names-for example city,coutry,amount…etc)
select * from dbo.AUDIT_DETAIL d,dbo.DETAIL_TYPE x
where d.Event_ID in (SELECT [Event_ID]
FROM [BO_Audit].[dbo].[AUDIT_EVENT] t
where t.USER_NAME=‘username’ (which user s actions you wanna report)
and t.Event_Type_ID=41)
and d.Detail_Type_ID=x.Detail_Type_ID
I need to see which user uses which object in Universe when they are making a report.
Also in this Bo Official Document,it s telling that we can hafe that data(on slide 31) Auditing Events.ppt (691.0 KB)
I’m running BI 4.0 sp5 and I’m not seeing this information. What I’ve done:
In the Auditing section of the CMC, Set Events is custom and, in addition to the default values, Retrieve is checked. Under Set Event Details, check Query and Property Value Details.
In BI Launchpad, I created and saved a Webi report called “Webi Test”. I then modified it to change the query. I also just viewed and refreshed it.
I ran the following query to get all of the event details for this report:
Select Distinct
ae.Event_ID,
ae.Object_Name,
ets.Event_Type_Name,
ae.Start_Time,
ed.Bunch,
edt.Event_Detail_Type_Name,
ed.Event_Detail_Value
from ADS_EVENT ae
inner join ADS_AUDITEE aa
on ae.Cluster_ID = aa.Cluster_ID
and ae.Server_ID = aa.Server_ID
and aa.Server_Type_ID = 'webiserver'
inner join ADS_EVENT_TYPE_STR ets
on ae.Event_Type_ID = ets.Event_Type_ID
and ets.Language = 'EN'
inner join ADS_EVENT_DETAIL ed
on ae.EVENT_ID = ed.EVENT_ID
inner join ADS_EVENT_DETAIL_TYPE_STR edt
on ed.Event_Detail_Type_ID = edt.Event_Detail_Type_ID
and edt.language = 'EN'
where ae.USER_NAME = 'Administrator'
and ae.Object_Name = 'Webi Test'
order by
ae.Event_ID,
ed.Bunch,
edt.Event_Detail_Type_Name
I don’t see any values that include individual object names. If I were you I would continue doing this type of test - setting various audit event values in the CMC, edit or view a specific report in BI Launchpad, and then check the audit tables using the above query to see whether you can find the right combination of events to get the data you’re looking for. I’m still not entirely sure that it’s available, regardless of what the documentation says. However, I would love to be proven wrong, so please post your results!
Try to run,refresh and save your “webi test” again,then run your query again,this time you will see the refresh actions and in them you will see the universe_object_name,but you cant see its value ,the result set is attached.
If we can t see this info from audit,for our data security,how can we see(withouut SDK) which user uses which object in universe while they are taking their reports,is there another way to see this information? Book1.xls (20.0 KB)
I did all of that - I’m not seeing any data in the audit database that includes the actual object names that were used in the query. You can get the whole query and extrapolate from there, but, despite what your documentation says, there doesn’t seem to be any way to get the list of dimensions and/or measures used in the report from the auditing database. I may still be wrong, but I’m just not seeing it anywhere in the auditing data.
For ad-hoc, one-off reports that a user may not save, there’s probably no way to get this information. For reports that are saved to the CMS, you would have to open each report in code and see which objects are used.
(BOB member since 2007-04-16)
