Finding the lastest audit dates for documents and users

I am trying to find the latest audit date and last audit user of every document within a folder. Our goal is to archive older documents. I believe I would need a LiveReport like this but need to get the user name.SELECT dataid, dname, MAX(to_char(auditdate,'YYYY-MM-DD')) as "Audit Date" FROM daudit GROUP BY dataid, dname order by dataidAlso, I would need a similar report but this time listing the most current (the lastest) audit date of all our users. We are trying to determine who has not logged on in awhile.We are using an Oracle Database and LL 9.1 sp4Any help would be greatly appreciatedRegards,Ann

Find more posts tagged with

Comments

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

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

I don't have a Livelink 9.1.x system to test this, but I would imagine that to get the username, you would do something like this:

SELECT k.name, d.dataid, d.dname, MAX(to_char(d.auditdate,'YYYY-MM-DD')) as "Audit Date" FROM daudit.d, kuaf k WHERE d.UserID=k.ID GROUP BY d.dataid, d.dname order by d.dataid

To determine who has not logged in for a while:

For users who have never logged in, you can use the following query provided by a colleague of mine:

SELECT firstname || ' ' || lastname "Name", mailaddress "eMail", name "Username" FROM kuaf k WHERE not exists (select 'x' from DAUDIT a where event = 'LOGIN' and k.id = a.userid) and type=0 and deleted=0 ORDER BY name

Format: AutoLiveReport

For users that haven't logged in since date "X", where you specify the date, the following is from Dave Ebels of Johnson Controls:

SELECT b.logons, a.firstname || ' ' || a.lastname "Name", a.mailaddress "eMail", a.name "Username", b.maxdate "Last Access" FROM kuaf a, (SELECT count(userid) logons, userid, max(auditdate) maxdate FROM daudit WHERE event = 'LOGIN' GROUP BY userid) b WHERE a.id = b.userid and to_char(b.maxdate,'yyyy mm dd') < to_char(%1,'yyyy mm dd') and a.type=0 and a.deleted=0 ORDER BY lastname

Inputs --

Type: Date

Prompt: Enter Date

Param%1: User Input 1

Format: AutoLiveReport

The above reports have been written for Livelink 9.2 SP1 on Oracle and should work on earlier versions down to 9.0 at least. They would need to be revised for Livelink 9.5, since a new audit table is used in that version. Also, the 2nd LiveReport is very Oracle-specific, since it uses || for the concatenation (whereas SQL Server uses + ) and uses a to_char function, which I am pretty sure is Oracle-specific.

Let me know if they help...

- Bhupinder----------------------------------------------
Bhupinder Singh, B.Math, B.Ed.
Senior Systems Analyst, Information Technology
Open Text, Waterloo, Ontario, Canada
----------------------------------------------

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, June 10, 2009 5:54 PM
To: eLink Recipient
Subject: Finding the lastest audit dates for documents and users

Finding the lastest audit dates for documents and users

Posted by unilev02user2 (Song, Ann) on 2009/06/10 17:51

I am trying to find the latest audit date and last audit user of every document within a folder. Our goal is to archive older documents. I believe I would need a LiveReport like this but need to get the user name.

SELECT dataid, dname, MAX(to_char(auditdate,'YYYY-MM-DD')) as "Audit Date" FROM daudit GROUP BY dataid, dname order by dataid

Also, I would need a similar report but this time listing the most current (the lastest) audit date of all our users. We are trying to determine who has not logged on in awhile.

We are using an Oracle Database and LL 9.1 sp4

Any help would be greatly appreciated

Regards,
Ann

Ann_Song

Thanks Bhupinder,The first report didn't work: "not a Group By expression" but the other two work really well...the last one is closer to what I am looking for. How difficult would it be to find the last activity date on a document?Regards,Ann

Tim_Hunter

Ann, this should fix the first query SELECT k.name, d.dataid , d.dname , MAX(TO_CHAR(d.auditdate,'YYYY-MM-DD')) AS "Audit Date" FROM daudit d, kuaf k WHERE d.UserID=k.IDGROUP BY k.name, d.dataid , d.dnameORDER BY d.dataidTo find the latest activity date on a document SELECT d.dataid, MAX(to_char(d.auditdate,'YYYY-MM-DD')) as "Audit Date" FROM daudit d where d.dataid = %1 group by d.dataidPrompt for the document ID

Ann_Song

Thanks Tim....both of these scripts work but how would I further customize the second script to provide the latest activity date of all documents in a folder. I think it would be something like this:SELECT d.dataid, MAX(to_char(d.auditdate,'YYYY-MM-DD')) as "Audit Date" , t.nameFROM daudit d, dtree tstart with d.dataid = %1 connect by t.parentid = PRIOR d.dataidHowever this script doesn't work....Thank you for your assistance.Ann

Tim_Hunter

Ann, you are closeI don't have any audit in my development Livelink so I can't test how the output will look, but this should work.SELECT d.dataid, MAX(to_char(d.auditdate,'YYYY-MM-DD')) as "Audit Date" , t.nameFROM daudit d, dtree tstart with d.dataid = %1 connect by t.parentid = PRIOR d.dataidgroup by d.dataid, t.name

Ann_Song

Unfortunately, this error occurred:[ORA-01437: cannot have join with CONNECT BY - SELECT d.dataid, MAX(to_char(d.auditdate,'YYYY-MM-DD')) as "Audit Date" , t.name FROM daudit d, dtree t start with d.dataid = :A1 connect by t.parentid = PRIOR d.dataid group by d.dataid, t.name]I've come across this error before but don't know how to correct...any help would be appeciated!Ann

Tim_Hunter

Ann you must be using an old version of Oracle, I think this was "fixed" in 9i.The problem like it states is you cannot use a join when using CONNECT BY. Try this, I am not sure if the results are correct, I don't have auditing turned on in my dev database. SELECT d.dataid , MAX(TO_CHAR(d.auditdate,'YYYY-MM-DD')) AS "Audit Date" , t.name FROM daudit d , (SELECT dataid , name FROM dtree START WITH dataid = :A1 CONNECT BY parentid = PRIOR dataid ) tGROUP BY d.dataid, t.name

Ann_Song

I tried this and it seemed to take a long time for just 23 documents...I didn't let it finish because I thought it might be going in a loop or something.This code makes more sense to me but of course it didn't work at all:SELECT d.dataid, MAX(TO_CHAR(d.auditdate,'YYYY-MM-DD')) AS "Audit Date", t.name,(SELECT t.dataid,t.name FROM dtree t START WITH d.dataid = %1 CONNECT BY t.parentid = PRIOR d.dataid) FROM daudit d GROUP BY d.dataid, t.nameI've had trouble with this before, I guess as you say we're on an older version.I don't think there's an easy solution for this....but thanks very much for all your help...really appreicated..Regards,Ann

Ann_Song

Well, I got it to run in a reasonable time, but for some reason the lastest audit date is always today's date:select name "Name", subtype "Subtype", parentid "ParentID", dataid "DataID", (select MAX(TO_CHAR(d.auditdate,'YYYY-MM-DD')) from d.audit where d.dataid=dataid) "Last AuditDate" from dtree start with dataid = %1 connect by parentid = PRIOR dataidWe're running on an Oracle DB and Livelink version 9.1.0 sp4Any help would be greatly appreciated !!Regards,Ann

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

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

What if you change:

(select MAX(TO_CHAR(d.auditdate,'YYYY-MM-DD')) from d.audit where d.dataid=dataid)

...to:

(select MAX(TO_CHAR(d.auditdate,'YYYY-MM-DD')) from daudit d where d.dataid=dataid)

- Bhupinder

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, June 16, 2009 12:29 PM
To: eLink Recipient
Subject: UPDATE_ Finding the lastest audit dates for documents and users

UPDATE_ Finding the lastest audit dates for documents and users		Posted by unilev02user2 (Song, Ann) on 2009/06/16 12:27
In reply to: I tried this and it seemed to take a long time for just 23 documents...I didn't...		Posted by unilev02user2 (Song, Ann) on 2009/06/15 12:06

Well, I got it to run in a reasonable time, but for some reason the lastest audit date is always today's date:

select name "Name", subtype "Subtype", parentid "ParentID", dataid "DataID", (select MAX(TO_CHAR(d.auditdate,'YYYY-MM-DD')) from d.audit where d.dataid=dataid) "Last AuditDate" from dtree start with dataid = %1 connect by parentid = PRIOR dataid

We're running on an Oracle DB and Livelink version 9.1.0 sp4

Any help would be greatly appreciated !!

Regards,
Ann

Ann_Song

Tried that....there was a typo in my reply...thanks for pointing that out....still returns today's date....I'm stumped !!!Ann

Lindsay_Davies

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

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

Hi Ann,

I had a look at your SQL and tweaked it.

select name "Name", subtype "Subtype", parentid "ParentID", dataid "DataID", (select MAX(TO_CHAR(auditdate,'YYYY-MM-DD')) from daudit da where da.dataid = dt.dataid) "Last AuditDate" from dtree dt start with dataid = %1 connect by parentid = PRIOR dataid

That seems to work for me.

You got today's date back because you did not tell Oracle to compare DAudit.Dataid with DataID from a different table, so it defaulted to the table name in that part of the query which is DAudit. So you always got the max from the whole table.

Anyway, I think this should work for you.

Regards

Lindsay

European Escalations Team

Open Text

Ann_Song

PERFECT !!! This is exactly what I was looking for.Thank you very very much !!!Ann