need report for doc's accessed by user

Hello,I'm asked to create an report that lists the documents that was accessed by an specific user. The Problem is, that the user is already deleted.Any ideas?

Find more posts tagged with

Comments

Lindsay_Davies

Message from Lindsay Davies via eLinkHi Peter,When a user (example JBloggs) is 'deleted', the normal processing is tochange the kuaf.deleted column from 0 to 1 and rename the account from'Jbloggs' to 'JBloggs (Delete) 123456'. The number is the ID (KUAF.ID) that is used in the auditing system toidentify that user.So you will need to determine the ID of the deleted user you areinterested in and then filter the DAuditNew table (or Daudit table forpre Livelink 9.5 systems) on column UserID for this value.You can base your report on standard reports delivered with Livelink,such as the "What's Hot" reports.These query the Daudit(New) table and DTree so that you see the name ofthe item that was accessed.You will just need to apply whatever logic you need to filter for thedeleted user rather than by access date.Good luck.LindsayUK-Support-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: 06 February 2008 09:50To: eLink RecipientSubject: need report for doc's accessed by userneed report for doc's accessed by userPosted by Stegl, Peter on 02/06/2008 04:45 AMHello,I'm asked to create an report that lists the documents that was accessedby an specific user. The Problem is, that the user is already deleted.Any ideas?[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/llisapi.dll/open/Livelink_LiveReports_DiscussionLivelink Server:https://knowledge.opentext.com/knowledge/llisapi.dllTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Steve_McDonough

Hello Peter,Lindsay wrote: ...So you will need to determine the ID of the deleted user you are interested in and then filter the DAuditNew table (or Daudit table for pre Livelink 9.5 systems) on column UserID for this value. Here's that report for identifying all deleted users:SELECT ID, NAME, LASTNAME, FIRSTNAME, OFFICELOCATION FROM KUAF WHERE DELETED > 0 ORDER BY LASTNAMELindsay continued:...query the Daudit(New) table and DTree so that you see the name of the item that was accessed. You will just need to apply whatever logic you need to filter for the deleted user rather than by access date. Here's the report I use:SELECT DT.NAME "Item Name", D.DATAID "Item ID", D.AUDITSTR "Event", K.LASTNAME+ ', ' +K.FIRSTNAME "User Name", D.AUDITDATE "Audit Date" FROM DAUDITNEW D, KUAF K, DTREE DT WHERE D.PERFORMERID = %1 AND D.AUDITID IN (14, 26) AND D.PERFORMERID = K.ID AND DT.DATAID = D.DATAID ORDER BY D.AUDITDATE DESCYou'll need to set up a prompt for User ID number to User Input 1. The ID is the 'filter'. Audit Codes 14 and 26 relate to 'Fetch' and 'Viewed' respectively. Both reports are in Auto LiveReport format. Attached is a spreadsheet of the audit codes if you would like to capture any other user activity.Hope it helps,Leon

Steve_Fedan_(sfedan@acusphere.com_-_(deleted))

Hello Leon,I tried using your 'SELECT DT.NAME' report, but received the following error:Livelink Error: Error processing request. [[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error - SELECT DT.NAME "Item Name", D.DATAID "Item ID", D.AUDITSTR "Event", K.LASTNAME+ ', ' +K.FIRSTNAME "User Name", D.AUDITDATE "Audit Date" FROM DAUDITNEW D, KUAF K, DTREE DT WHERE D.PERFORMERID = ? AND D.AUDITID IN (14, 26) AND D.PERFORMERID = K.ID AND DT.DATAID = D.DATAID ORDER BY D.AUDITDATE DESC] Thoughts?Steve

Lindsay_Davies

Message from Lindsay Davies via eLinkSteve,The "Count field or syntax error" comes from the "where D.PerformerID =?" Component of your statement.Assuming you meant to have a question mark in your query (as shown byoutput from some other select statement) and that you mean the value isnot set, then you should say where D.PerformerID IS NULLHowever, PerformerID is normally set - so you may want to test where ISNOT NULL.Good luck.Lindsay-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: 07 April 2008 16:16To: eLink RecipientSubject: Error running queryError running queryPosted by Fedan, Steve on 04/07/2008 11:15 AMHello Leon,I tried using your 'SELECT DT.NAME' report, but received the followingerror:Livelink Error: Error processing request. [[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntaxerror - SELECT DT.NAME "Item Name", D.DATAID "Item ID", D.AUDITSTR"Event", K.LASTNAME+ ', ' +K.FIRSTNAME "User Name", D.AUDITDATE "AuditDate" FROM DAUDITNEW D, KUAF K, DTREE DT WHERE D.PERFORMERID = ? ANDD.AUDITID IN (14, 26) AND D.PERFORMERID = K.ID AND DT.DATAID = D.DATAIDORDER BY D.AUDITDATE DESC] Thoughts?Steve[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: need report for doc's accessed by userhttps://knowledge.opentext.com/knowledge/llisapi.dll/open/14203330Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/llisapi.dll/open/Livelink_LiveReports_DiscussionLivelink Server:https://knowledge.opentext.com/knowledge/llisapi.dllTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.