Help on SQL Query ofr Document Reporting

[baraqatax]

Hi guys, I'm developing SQL query to create LiveReport to generate all created and modified documents from 1 folder in a weekly period.

I have succeeded to get all users who created the documents. However, my question is how to get username who modified the documents since there is no "ModifiedBy" field in DTree table. Below is my query.

select d.dataid, d.name, to_char(d.createdate, 'MON DD, YYYY HH24:MI:SS') as CREATEDATE, k.lastname, k.firstname
from DTree d
inner join kuaf k on d.createdby = k.id
where ((d.CreateDate>=(sysdate-7)) and (d.CreateDate<=sysdate))
and d.subtype = 144
connect by prior dataid = parentid start with parentid = <parentid>
order by d.name asc

Does anyone know how to get the users who modified documents from this query?

Thanks!

 

[ggriffit]

what do you mean by modified ? do you mean a new version added or something like permissions changes, category changes etc.

the DTREE.ModifyDate column may give you what you need.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[baraqatax]

Thanks for your prompt response, Greg. Appreciate it!

I mean the DTree table doens't have "ModifiedBy" field, only "CreatedBy" field. Do you know how to get the ModifyBy attribute? What table should I join with DTree and also KUAF?

 

[ggriffit]

baraqatax can you confirm the version of LL you are using ?

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[baraqatax]

I'm using Livelink 9.7.1 with Oracle 10g DB.

 

[appnair]

dtree has a column modifydate in livelink 9.7.1 and any other previous versions.On initial creation createdate and modifydate are set with the same time stamp.modifydate is used to track any changes to the node(it could be anything like new versions,permissions,category changes etc).The version table dversdata has also 4 dates in them to track versionable items.The explanationof the 4 dates are in the KB.

Do a desc dtree in a oracle tool and you will be able to see all the columns

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[appnair]

I now read it more clearer.You are after who is the user who has modified the document after the createdby user has done it.For this if the event is audited you should use dauditnew table.Duaditnew has the same kuaf.id of your original query as performerid there.Change something in a node and filter that on dauditnew for that nodeid and you should be able to see the relation.Also I generally refrain from answering schema qns as it is protected by an NDA by OT.Hence the clues....

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[ggriffit]

As AppNair highlights the DAUDITNEW table will provide the Audit information about specific changes to an Object - assuming that you had the correct configuration of Auditing on at the time.

The Database Schema can be got from your local OT Office once you have signed an NDA, Non Disclosure Agreement, raise a ticket with support to get a copy.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[baraqatax]

Nice tips! Now I got the idea here to join DTREE, KUAF, and DAUDITNEW tables. And I have succeeded to generate the query to get the users who have modified the documents.

Thanks appnair & greg