For auditing purposes, I need to extract user specific data in Agile 8.5 CM database.
This query is related to Oracle Agile 8.5 CM database which is Oracle 8i.
Here are the basic queries I would want to run.
- How many times did a user access the system? I would want to know date and time.
- What objects did a specific user access? I would want to know date and time.
- What users viewed a specific object? I would want to know date and time.
Can you please help me out how can I query this into Agile 8.5 CM database.
Thanks
The Usage_History table contains the user names, computer where logged in from, login time and logout time. There isn’t a record of what objects were viewed, but there is for getting attachments. The Change_History and Item_History tables contain that info. User_ID is linked to the Nodetable id for the user name in the history tables and the Event_Type you’re looking for is 38 (GetFile.)
You can see when a user logged in and logged out using USAGE_HISTORY with the user information being in NODETABLE and PROPERTYTABLE.
But you cannot determine when or which objects are accessed/viewed. That information is not stored in the database. Given the login/logout date/time values, you could search CHANGE_HISTORY and ITEM_HISTORY to see what activities (if any) the user performed, but these will only be create/modify/delete actions, not what they looked at.
As for what a user looked at or accessed in attachments, the codes that Keith provided are for CHANGE_HISTORY. The codes for ITEM_HISTORY.ACTION would be : 0 – Print Attachment, 1 – Get Attachment, 13 – View Attachment, 15 – Get File Attachment, 16 – Get Image Attachment, 21 – Checkout Image Attachment, 22 – Checkout File Attachment, 29 – View File Attachment, 30 – View Image Attachment, 36 – Open Attachment.