×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Reports

How do you write a audit trail query ? by appnair
Posted: 22 Jan 04 (Edited 23 Sep 05)

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.



Back to Open Text: Livelink FAQ Index
Back to Open Text: Livelink Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close