Need a Live Report for Tracking Activity for Documents in a Folder

I need to write a report (need the SQL statement) that if I input a folder id it will give me a list of the documents in that folder and who viewed/fetched the document and date.Is this possible to do?If anyone has done this or knows how, could you please provide me with the specific statement, etc.Thanks,

Find more posts tagged with

Comments

Martin_Gäckler

Hi,not exactly what you need, but with a little SQL knowledge you should be able to adapt the SQL statement for your requirements:select dname, event , count(*) from daudit where dataid in (select dataid from dtree connect by prior dataid = parentid start with dataid = 9534937) and event in ('Fetch', 'View', 'Menu', 'Browse', 'Clicked') and dname not like '%%.gif' and dname not like '%%.css' and dname not like '%%.jpg' and auditdate >= %1 and auditdate <= %2 group by dname, event order by 3 descMartin

18User3_(Delete)_2154966

Thanks. Unfortunately I don't have any SQL knowledge and we don't have any onsite support for custom Reports from Livelink. I'm not sure what I need to adapt in the above other than the datgaid #. In the past I've used the reports directly from Open Text or relied on someone from the board to help me write the Custom Report. This seems to be a common request to find out activity within a folder. We may have to contract out with Open Text or a partner to help us. Thanks, again.

Dave_Ebels_(jocoadmin_-_(deleted))

I have a nice little Live report that returns the number of fetches and views on the contents of a folder or a single document. This simply returns "hits".use an input type number , mine is named 'Enter Object ID'Param %1 is user input 1Report format is Auto LiveReportthe SQL is as followsselect count(*)"number of hits" from daudit where (EVENT='VIEW' or EVENT='FETCH') and dataid in (select dataid from dtree start with dataid in(%1) connect by prior dataid=parentid)This tests out very nicely. You should be able to plug this right in. I have created a number of these reports and find keeping track of things far easier with them.

OSHA_ICT_team_member_1_(oshauser1_-_(deleted))

This query is interesting, thanks for sharing Dave. What would be very useful is to an extended query, where hits correspond to the fetch or view of a specific group at a defined period (between dd:mm:yy and dd:mm:yy). Anyone can help ?