I need make a query

Hello

I need your help

How I can make a sql query that I report the action exercised by the users within a particular folder?

Name usuer, date, name file and what he did.

I did this, but I only get the owner and lack the kind of action that the user did

select Livelink.DversData.Owner, Livelink.Kuaf.name, Livelink.DversData.DocID, Livelink.DversData.Filename, Livelink.DversData.Version, Livelink.DversData.FileMDate

from Livelink.dversdata, Livelink.DTreeAncestors, Livelink.Kuaf

where Livelink.Dversdata.owner=Livelink.Kuaf.id

and Livelink.DTreeAncestors.dataid=Livelink.dversdata.docid

and Livelink.DTreeAncestors.AncestorID='29823'

Find more posts tagged with

Comments

Carsten_Kulms

Maybe the following helpsselect t.Name, v.DocID as DataID, v.VersionID, v.VersionName, v.FileName, k.Name, a.SubType, a.AuditStr, a.AuditDatefrom DAuditNew a join DTree t on a.DataID=t.DataID join KUAF k on a.PerformerID=k.ID left outer join DVersData v on a.DataID=v.DocIDwhere a.DataID in (select DataID from DTree where ParentID=)order by a.DataID, v.VersionID, a.AuditDateI am not sure if this is the best or even a complete way to query that information.

Hans Stoop

I think you want to query against the DAuditNew and DAuditMore tables to get that kind of information.

Hans

From: eLink Entry: Content Server Development Forum [mailto:development@elinkkc.opentext.com]
Sent: Mittwoch, 4. Mai 2011 18:48
To: eLink Recipient
Subject: Query Audit

Query Audit

Posted by osilva@dclconsultores.com (Silva, Obed) On 2011/05/04 12:47

Hello

I need your help

How I can make a sql query that I report the action exercised by the users within a particular folder?

Name usuer, date, name file and what he did.

I did this, but I only get the owner and lack the kind of action that the user did

select Livelink.DversData.Owner, Livelink.Kuaf.name, Livelink.DversData.DocID, Livelink.DversData.Filename, Livelink.DversData.Version, Livelink.DversData.FileMDate

from Livelink.dversdata, Livelink.DTreeAncestors, Livelink.Kuaf

where Livelink.Dversdata.owner=Livelink.Kuaf.id

and Livelink.DTreeAncestors.dataid=Livelink.dversdata.docid

and Livelink.DTreeAncestors.AncestorID='29823'

[To post a comment, use the normal reply function]
Forum:	Content Server Development Forum
Livelink Server:	knowledge-wlweb01

Hans Stoop

I don’t think it makes sense to query over the versions, because all audit entries are against the node and not against the version.

Hans

From: eLink Entry: Content Server Development Forum [mailto:development@elinkkc.opentext.com]
Sent: Mittwoch, 4. Mai 2011 21:58
To: eLink Recipient
Subject: RE Query Audit

RE Query Audit

Posted by ckulms (Kulms, Carsten) On 2011/05/04 15:58

Maybe the following helps

select t.Name, v.DocID as DataID, v.VersionID, v.VersionName, v.FileName, k.Name, a.SubType, a.AuditStr, a.AuditDate
from DAuditNew a join DTree t on a.DataID=t.DataID join KUAF k on a.PerformerID=k.ID left outer join DVersData v on a.DataID=v.DocID
where a.DataID in (select DataID from DTree where ParentID=<FOLDERID>)
order by a.DataID, v.VersionID, a.AuditDate

I am not sure if this is the best or even a complete way to query that information.

[To post a comment, use the normal reply function]
Topic:	I need make a query
Forum:	Content Server Development Forum
Livelink Server:	knowledge-wlweb01

Carsten_Kulms

The query against DVersData is just to get the versionname (~ version number) and filename. This can be quite interesting if a new version was added.However, I've lost my copy of the schema reference :-) so there could be more wrong with this query. (Also it looks as it would execute rather slow...)Also I missed the DAuditMore stuff ... well, the query was written `ad-hoc´ in the hope it points in the right direction -- improvements are welcome! :-)

Obed Silva

Hello;

Thanks for your answers, you always provided assistance.

I did thit query and it worked:

select Livelink.Kuaf.name as "Nom Ususario", Livelink.DversData.Filename as "Nombre Archivo", Livelink.DAuditNew.AuditStr as "Tipo de Acción", Livelink.DAuditNew.AuditDate as "Fecha de la Acción"

from Livelink.DAuditNew, Livelink.DTreeAncestors, Livelink.Kuaf, Livelink.dversdata

where Livelink.DAuditNew.PerformerID=Livelink.Kuaf.id

and Livelink.DTreeAncestors.dataid=Livelink.dauditnew.dataid

and Livelink.dversdata.DocID=Livelink.DAuditNew.dataid

and Livelink.DTreeAncestors.AncestorID=<IDFolder>

Regards;