I have the following report that shows deleted users, but how how can I show the date the user was deleted? Using Content Server 10.
select name from kuaf where deleted=1
You need to access the DAUDITNEW. We’re in 9.7.1, but I would think the DB hasn’t changed that much.
DAUDITNEW
AUDITID = 1 <create> or 2 <delete>
AUDITDATE
USERID = KUAF.ID
DATAID is NULL
SUBTYPE is NULL
If you have a large installation, this will take a while to run through. There ought to be (DBA)ways of making the DAUDITNEW more quickly traversed.
Oracle SQL could look like this, but there’s always room for improvement. This puts one date per line, instead of both on the same. You can verify the dates, by adding “da.eventid” to it and then you can retrieve individual records by a key for verification.
select DA.USERID, k.name "UserName", case da.auditid when 1 then 'Created: '||da.auditdate when 2 then 'Deleted: '||da.auditdate end Timed
from dauditnew da
join kuaf k on da.userid = k.id and k.type = 0
where da.auditid in (1, 2) and da.userid > 0 and da.userid > 0 and da.dataid is NULL and da.subtype is NULL
order by k.name
Colin J. Schmidt
MSLive/Livelink Admin
Tulsa, OK 74172
Colin.Schmidt@williams.com
If you have received this message in error, please reply to advise the sender of the error and then immediately delete this message.
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Wednesday, February 06, 2013 11:11 PMTo: eLink RecipientSubject: Show Date a User was Deleted?
Show Date a User was Deleted?
Posted by karen.morley@goodrich.com (Morley, Karen) On 02-07-2013 00:09
[To post a comment, use the normal reply function]
Forum:
Content Server LiveReports Forum
Content Server:
Knowledge Center
Another way to get the date a user was deleted:
1. Login to Livelink as the Admin user.
2. Go to the Livelink Administration pages using the ?func=admin.index URL extension.
3. In the "System Administration" section, click the "Administer Event Auditing" hyperlink.
4. Click the "Query Audit Log" hyperlink.
5. In the "Target Items" section, select "By Type" and choose "User or Group" from the associated dropdown list..
6. In the "Event Type" section choose "Delete"
7. Click the "Submit Query" button.
As for a LiveReport, the following is from a colleague, Eric Saavedra:
/* USERS WHO HAVE BEEN DELETED AND WHEN */
SELECT * FROM DAUDITNEW WHERE AUDITSTR = 'Delete' AND USERID IN (SELECT ID FROM KUAF WHERE DELETED = 1 AND TYPE = 0)
