Auditing Shortcuts within a container

I've written the (Oracle) SQL below, I'm trying to get audit details for a user between dates on a specific folder - This report works for Original items but doesn't for shortcuts.How do I get the report to look at the original items audit history rather than the shortcuts audit history, the shortcut doesn't hold all audit histroy just things like move, perm change etcSQL & Inputs: - User Input %1 - Date FromUser Input %2 - Date ToUser Input %3 - UserUser Input %4 - Containerselect KUAF.FirstName || ' ' || KUAF.LastName "Name", KUAF.Name "Username", DAuditNew.AuditStr "Event", Dtree.name "Item Name", Dtree.dataid "Unique ID", DauditNew.AuditDate "Date" FROM KUAF, dtree, DAuditNew WHERE (DauditNew.AuditDate > %1 AND DauditNew.AuditDate < %2) AND (AuditStr ='ConfigurationChanged' or AuditStr ='AttrChange' or AuditStr ='Edit' or AuditStr ='Alias' or AuditStr ='View' or AuditStr ='Print' or AuditStr ='Recycle' or AuditStr ='Create' or AuditStr ='CategoryAdded' or AuditStr ='AddVersion' or AuditStr ='Fetch' or AuditStr ='Move' or AuditStr ='Rename' or AuditStr ='Reserve' or AuditStr ='PermChange' or AuditStr ='ZipAndDownload' or AuditStr ='DelVersion' or AuditStr ='Delete' or AuditStr ='Unreserve' or AuditStr ='CategoryRemoved') AND (DauditNew.performerID = KUAF.ID) AND (DauditNew.dataID = Dtree.dataID) AND (KUAF.ID = %3) AND DTREE.DATAID IN (SELECT DATAID FROM DTREE START WITH DATAID = %4 CONNECT BY PRIOR DATAID = PARENTID)Any help is welcome.ThanksTom

Find more posts tagged with

Comments

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

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

Hi Thomas,

Livelink does not audit the following of a shortcut.

What it does audit are the actions on the target of the shortcut.

So, you would need substitute the target dataid in your list of DataIIDs for the dataid of the shortcut (alias) target objects.

This is not as hard as it sounds.

The target dataid is stored in OriginDataID, which is 0 if this object is not a reference to another object.

So, you can use the decode function to adjust which column you are actually selecting, as follows.

DTREE.DATAID IN (SELECT decode(origindataid,0,dataid,origindataid) FROM DTREE START

Your connect by prior still returns the original list of DataID values but we are processing the OriginDataID and if it is zero we use the DataID, if not, the OriginDataID (that is, the target of the alias).

You are currently using ! = (not equal) %4 and probably want this to be = %4

Also, as the DAuditNew table is probably very large, you might want to avoid using the massive array of "OR AuditStr =" checks and substitute one

AND AuditID in (14,26,etc,etc ).

I did a group by to find the ones I have in my table ( select auditid,auditstr, count(*) from dauditnew group by auditid,auditstr order by auditid; )

You will need to find out what you have or determine the list another way. Mine list is clearly incomplete as I have not made any Print requests.

   AUDITID AUDITSTR
---------- -----------------------
         1 Create
         2 Delete
         3 Move
         4 Copy
         5 Rename
         6 Reserve
         7 Unreserve
         8 Alias
         9 PermChange
        10 AttrChange
        11 AddVersion
        14 Fetch
        23 Login
        24 Logout
        26 View
        27 OwnerChange
        28 ConfigurationChanged
        29 LoginFailure
        30 MembershipChanged
        31 FunctionExecuted
        33 MembersChanged
        35 CategoryAdded

22 rows selected.

I can see one likely problem - what if the shortcut/alias was to a folder?

My technique is deliberately simple and does not walk down those shortcut folder hierarchies.

If you want to do that, you will have to take a different approach.

It appears rather open-ended as to what you look for - nested shortcuts for folders that could become recursive.

I'd keep clear of that.

Good luck!

Regards

Lindsay Davies

European Escalation Team

Open Text UK

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 13 May 2009 12:16
To: eLink Recipient
Subject: Auditing Shortcuts within a container

Auditing Shortcuts within a container

Posted by thomas.martindale@generaldynamics.uk.com (Martindale, Thomas) on 2009/05/13 07:11

I've written the (Oracle) SQL below, I'm trying to get audit details for a user between dates on a specific folder - This report works for Original items but doesn't for shortcuts.

How do I get the report to look at the original items audit history rather than the shortcuts audit history, the shortcut doesn't hold all audit histroy just things like move, perm change etc

SQL & Inputs: -

User Input %1 - Date From
User Input %2 - Date To
User Input %3 - User
User Input %4 - Container

select KUAF.FirstName || ' ' || KUAF.LastName "Name", KUAF.Name "Username", DAuditNew.AuditStr "Event", Dtree.name "Item Name", Dtree.dataid "Unique ID", DauditNew.AuditDate "Date" FROM KUAF, dtree, DAuditNew WHERE (DauditNew.AuditDate > %1 AND DauditNew.AuditDate < %2) AND (AuditStr ='ConfigurationChanged' or AuditStr ='AttrChange' or AuditStr ='Edit' or AuditStr ='Alias' or AuditStr ='View' or AuditStr ='Print' or AuditStr ='Recycle' or AuditStr ='Create' or AuditStr ='CategoryAdded' or AuditStr ='AddVersion' or AuditStr ='Fetch' or AuditStr ='Move' or AuditStr ='Rename' or AuditStr ='Reserve' or AuditStr ='PermChange' or AuditStr ='ZipAndDownload' or AuditStr ='DelVersion' or AuditStr ='Delete' or AuditStr ='Unreserve' or AuditStr ='CategoryRemoved') AND (DauditNew.performerID = KUAF.ID) AND (DauditNew.dataID = Dtree.dataID) AND (KUAF.ID = %3) AND DTREE.DATAID IN (SELECT DATAID FROM DTREE START WITH DATAID ! = %4 CONNECT BY PRIOR DATAID = PARENTID)

Any help is welcome.

Thanks
Tom