Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do you write a audit trail query ?

Reports

How do you write a audit trail query ?

by  appnair  Posted    (Edited  )
How do you write a audit trail query?
********************************************
appnair 09/23/2005
Also whenever you are doing datae comparisons,since livelink stores date information with the time parts,please use trunc functions.For example trunc(auditdate)>=trunc(sysdate).I do not know if this is Oracle specific
This is a recent posting which I made in which I used that methodology
http://www.tek-tips.com/viewthread.cfm?qid=1125357&page=1

*************************************************
appnair 08/25/2005
9.5 and 9.5 sp1 the auditing has considerably changed.If you are doing something important I advise you to get the schema document from OT and do the queries.I do not know on a fresh install whether the DAUDIT table exists.It is called DAUDITNEW and the columns also has changed
************************************************
Sometimes administrators get requests like How can I find
the number of fetches for a particular document at this folder.At least I get this all the time.I looked at many of the canned live reports,the knowledge base,oracle etc but did not exactly find what I was looking for.This query which possibly will run only on Oracle is for sharing,further refinement etc.I run the query, export it as a flat file and send to the user where they probably will
put it into Excel make all kinds of inferences out of it
I am not an Oracle expert so my query may not be well written so take this with a pinch of salt.

select b.FIRSTNAME ,b.LASTNAME,b.MIDDLENAME, a.AUDITDATE, a.DNAME from daudit a,kuaf b
where ( EVENT='VIEW' or EVENT='FETCH' ) AND a.USERID = b.ID and a.AUDITDATE Between '01-OCT-2003' and '31-DEC-2003' and dataid in (select dataid from dtree start with dataid in 77817 connect by prior dataid=parentid) order by a.dname

The dataid 77817 is a folder objID in my system
I am in this example only interested in View and Fetch there are many things possible,rename,delete etc
All my query is doing is getting all the information from duadit table and tying it to user info from the user table.
I put the hard coded dates in the query since I falter always at the oracle date syntax.Run a describe on both tables and see how more creative you can become.I do not know the performance implications of this query so it is upto the Oracle guru's to comment on that aspect

One more thing This is what you get when you hit the audit function of any document folder etc,only thing is the query will probably save you clicking that for different docs.You can run itas a Live Report replacing the hard coding with iput parmeters also.


Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top