viewed or fetched

Birger_Jacobsen_(danskadmin_-_(deleted))

I'm trying to make a report wich liste all viewed or fetched documents in the actuel folder and who did it Is the following the right way to do thisSELECT DAUDIT.DNAME, DAUDIT.EVENT, KUAF.NAME, DAUDIT.AUDITDATE FROM DAUDIT, KUAF WHERE DAUDIT.USERID = %1 and((DAUDIT.EVENT='FETCH') OR (DAUDIT.EVENT='VIEW'))Thanks

Find more posts tagged with

Comments

eLink User

Message from Marie Lindsay <<A HREF="mailto:marie@opentext.com">marie@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

I think it would be more like:

select daudit.dname, kuaf.name, daudit.event from dtree, daudit, kuaf
where dtree.dataid in
(select dataid from dtree where parentid = %1)
and daudit.dataid = dtree.dataid
and kuaf.id = daudit.userid

and ( (daudit.event = 'FETCH') or (daudit.event = 'VIEW' ))

%1 would be replaced with the ID of the folder whose audit events you want to see. It could be set to a user input of type "container").

-----Original Message-----
From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf Of eLink Discussion: Livelink LiveReports Discussion
Sent: Wednesday, November 14, 2001 8:04 AM
To: eLink Recipient
Subject: viewed or fetched

viewed or fetched Posted by DanskAdmin (Souary, Idriss) on 11/14/2001 08:59 AM

I'm trying to make a report wich liste all viewed or fetched documents in the actuel folder and who did it

Is the following the right way to do this

SELECT DAUDIT.DNAME, DAUDIT.EVENT, KUAF.NAME, DAUDIT.AUDITDATE FROM DAUDIT, KUAF WHERE DAUDIT.USERID = %1 and((DAUDIT.EVENT='FETCH') OR (DAUDIT.EVENT='VIEW'))

Thanks

eLink User

Message from Sean M Alderman via eLinkThat's what I was going to say... :)Aside from that, is there any documentation on what the event key wordsare for the core modules? I realize that there is some information inthe admin page for audit interests but the events that are displayedhave a nice 'english' like presentation, as opposed to the actual datawhich is all caps and possibly abbreviated.eLink Discussion: Livelink LiveReports Discussion wrote:> > ------------------------------------------------------------------------> Name: Message Body> Message Body Type: Hypertext Markup Language (text/html)> Encoding: base64-- Sean M. AldermanITRACK Systems AnalystPACE/NCI - NASA Glenn Research Center(216) 433-2795

Alex_Kowalenko_(akowalen_(Delete)_2285456)

You need to relate DAUDIT to KUAF through an "inner join":SELECT DAUDIT.DNAME, DAUDIT.EVENT, KUAF.NAME, DAUDIT.AUDITDATE FROM DAUDIT, KUAF WHERE DAUDIT.USERID = %1 andDAUDIT.EVENT in ('FETCH','VIEW') andDAUDIT.USERID = KUAF.ID /* Inner Join Relationship */But this only does it for one user and all objects.To do it for all users you can remove the first WHERE clause:SELECT DAUDIT.DNAME, DAUDIT.EVENT, KUAF.NAME, DAUDIT.AUDITDATE FROM DAUDIT, KUAF WHEREDAUDIT.EVENT in ('FETCH','VIEW') andDAUDIT.USERID = KUAF.IDTo do it for documents you have to add the DTree table and filter on documents:SELECT DAUDIT.DNAME, DAUDIT.EVENT, KUAF.NAME, DAUDIT.AUDITDATE FROM DAUDIT, KUAF, DTREE WHERE%1 andDAUDIT.EVENT in ('FETCH','VIEW') andDAUDIT.USERID = KUAF.ID andDAUDIT.DATAID = DTREE.DATAIDParameter %1 is "Filter Documents". You can get a more compact list if you don't want all the details with this query:SELECT COUNT(*) "Times", DAUDIT.DNAME, DAUDIT.EVENT, KUAF.NAMEFROM DAUDIT, KUAF, DTREE WHERE%1 and DAUDIT.EVENT in ('FETCH','VIEW') andDAUDIT.USERID = KUAF.ID andDAUDIT.DATAID = DTREE.DATAIDGROUP BY DAUDIT.DNAME, DAUDIT.EVENT, KUAF.NAMENow if you want to limit the documents to those in a folder then you need to add a WHERE clause which is a subquery:SELECT COUNT(*) "Times", DAUDIT.DNAME, DAUDIT.EVENT, KUAF.NAMEFROM DAUDIT, KUAF, DTREE WHERE%1 and DAUDIT.DATAID IN(SELECT DATAID FROM DTREE WHERE PARENTID = %2) andDAUDIT.EVENT in ('FETCH','VIEW') andDAUDIT.USERID = KUAF.ID andDAUDIT.DATAID = DTREE.DATAIDGROUP BY DAUDIT.DNAME, DAUDIT.EVENT, KUAF.NAMEParameter %2 is User Input 1 for a LiveReport container input type.But this report only does direct children of the folder (or project) container. To get all documents in the folder (or project) container, you have to do a deeper subquery to go all the way down:SELECT COUNT(*) "Times", DAUDIT.DNAME, DAUDIT.EVENT, KUAF.NAMEFROM DAUDIT, KUAF, DTREE WHERE%1 and DAUDIT.DATAID IN(SELECT DATAID FROM DTREESTART WITH DATAID = %2CONNECT BY PRIOR DATAID = PARENTID) andDAUDIT.EVENT in ('FETCH','VIEW') andDAUDIT.USERID = KUAF.ID andDAUDIT.DATAID = DTREE.DATAIDGROUP BY DAUDIT.DNAME, DAUDIT.EVENT, KUAF.NAMEI have attached a LiveReport that does this. If you look at the LiveReport SQL it has more elements to provide the LiveReport format and the proper document mimetype graphic.

Alex_Kowalenko_(akowalen_(Delete)_2285456)

The last query and the attached LiveReport export file only works for the Oracle RDBMS.