Folder Contents Stats

Looking for a Livereport that will a) list the users who visited the folder and b) list of documents in the folder and sub-folders and the users who viewed/fetched/downloaded those documents.Is this possible?

Find more posts tagged with

Comments

Appu_Nair

AFAIK a folder visit is not audited,but a well known hack exists where you put a small white jpg image in a folder and keep it hidden.When a user visits LL will record a hit on the white image object should work for any hidden object as well.

ray_chance

How would you audit the image file? Do you already have an existing Livereport?My customer also wants a report that shows users who have downloaded/viewed/fetched files from their folders and subfolders

Appu_Nair

add image file into folder you want.Note its objid which I call 12345 here.now create a basic LR like this set it for autolivereport and put the sql in like thisselect count(auditstr) "Folder Hit, well not exactly" from dauditnew where dataid=12345and auditstr in ('Fetch')For your other events run something like select distinct auditstr form dauditnewand then you can go on adding likeselect count(auditstr) "Folder Hit, well not exactly" from dauditnew where dataid=12345and auditstr in ('Fetch','Download')

John W. Simon, Jr.

Nair's solution assumes you have specific audit events turned on. You would need to go to the admin pages and check your settings to be sure this solution would work.Cheers.

Appu_Nair

John's statement is very true as the event reporting is completely dependent on event auditing turned on.Usually a document audit is turned on in default installs.There are two more ways another way a folder hit can be calculated IMHO.One is by the use of summarytimings logs which can be turned on each livelink server by inserting the key WantSummaryTimings=TRUE under the options pref of opentext.ini.This results in a csv file which can be analyzed using excel and inside you will find the nodeid aka objid aka dataid of the folder object.OT sells a tool called performance analyzer whihc can give you some good data using summarytimings.The second way it can be captured is probably using the freebie called Microsoft log parser.Again if you know the folder id that info is in the web server logs(not sure if it can be turned off).I used that in a project to get some web server/livelink info.If your organization's we b department has some fancy analytical tools then you could probably use that as well.For the second part of your qn you could devise a query that walks the hierarchy using connect by clause for oracle,a function for sql server as the case for your DB may be.There are a lot of examples in the LR discussions including a new approach that I see using dtreeancestors table.

ray_chance

Thanks to all for your helpfulf tips. Appu's LR is on the right track.Unfortunately I have a customer who is very "picky". He wants a count, but also the names (by email address) of those users who visited the folder and the documents those users downloaded or fetched.Sincerely, SQL Newbie

Appu_Nair

Not sure if the folder hack is needed because ultimately the aim is to figure out for a given folder who visted the contents of it.If it is a Oracle livelink the familar connect by can be used between the tables dtree,kuaf and dauditnew.A small eg howver I do not posess time/knowledge to count/group by each set.On my limited testing with this query I am able to show the level,name,email,whet was done etc like this.see if you can understand it and see if anything useful can be done with itThe logic I used is very simple dtree dataid to parentid using connect by will run a hierarchical query,to that I added the condition dtree dataid is dauditnew dataid and dauditnew performerid=kuaf id.I am not sure if the query is correct or not at ll just ran on a very simple data set.The dataid/parentid may have to be ABS(dataid) abs(parentid) if the folder contains discussions,projects,cd's etcFor a smaple folderid 2771 who visted and did what under it*******************************************8select level,a.dataid ,a.parentid ,a.name ,b.auditstr,b.performerid ,c.name,c.mailaddress from dtree a, dauditnew b ,kuaf c where upper(b.auditstr)='FETCH' and c.id=b.performerid and a.dataid=b.dataid connect by prior a.dataid=a. parentid start with a.dataid=2771 order by a.dataid****************************