how to get usage audit report by using liveReport features

Rinku_Puja

We are on process of creating usage Audit report for a document or foder.

in the report we want to see who are all viewed / modified / edited the file / folder.

We are using CS 10.5 and DB SQL server.

Can anyone help me on this in sql query. ( the table name and the keys we have check in the query).

Thank you in advance.

thanks,

Rinku

Find more posts tagged with

Comments

Alok_Varma

Try this SQL Server Query :

Select
DA.DataID,
D.Name,
DA.AuditDate ,
DA.PerformerID,
K.Name 'Performer Name',
DA.AuditStr 'Event' from DAuditNew DA
INNER JOIN KUAF K ON DA.PerformerID =K.ID
INNER JOIN DTree D ON DA.DataID =D.DataID
Where D.SubType IN (0,144) -- Folder/Document
Order By 1,2,4

Colin_Schmidt

This is easy. The table is DAUDITNEW. There is an Admin page that allows you to set or unset auditable events. The effect is immediate and doesn't require a restart. Yuo obviously can not check a new one and expect a backlog od activity to be recorded.
There are a number of activities:

AUDITID is the number of AUDITSTR. AUDITID = 1 means AUDITSTR = "Create".

If you have a DB Tool, you can look at the table to get an understanding of what is recorded. There are a couple of dozen or many more that are User-related that would indicate a User "hit". What you can do to get an idea of what is recorded, in ORACLE:

select distinct da.auditid, da.auditstr from dauditnew da order by da.auditid

That might take a while. I don't know how many entries are in there. There's a bit of more knowledge needed to know whatand why you would check certain actions. The above Query will tell you what you can query for. When a document is EDITED, a "Reserve" and "Unreserve" entry are also created. If neither of these are created, the document was just viewed. Most are fairly self-explanatory.

If your DB is MS SQL Server, I won't be of much help. I learned some by going online.

There is also a manual in KC that is necessary: Schema companion and reference guide. A PDF with codes.