My understanding is that there a two types of audit logging occurring in TRIM. The first type is the Audit Logs which are set up in the System Options and create a daily log file as a text file. I understand that the data from these logs is stored in a table called TSEVENTDAT.
The other type of logging is set on the audit tab of each record type. I am trying to establish the date and time and the user who attached the elctronic document. The event is recorded on the record type and I can see it is the active audit events on each individual document. My problem is that I need this information for 90,000 records. I was able to get the information by doing a print merge and using the 'audit events' field and then finding the information I want in excel. I'm sure there is a quicker way to do this - but I'm no DBA!
Anyway, print merge is not full proof, as it only extracted a limited number of events per record, and if the 'document attach' event was older, then it was not extracted - hence I have about 5000 records, where I still need this information.
What is the name of the table that stores this data? I have a DBA who can extract the data for me, and then I'll be set :)
What would be really awesome is if I understood the entire table structure and where data was stored, as it could save me heaps of time pulling out countless information through print merge (and it would give my DBA something to do :P lol jk).
The TSEVENTDAT table holds audit entries which need to be written to the server-side audit log (as you've mentioned).
The TSRECLOCHI table holds all of the "online audit" entries. These entries only exist if the audits are enabled on the individual record types (often times over looked during configuration). You have to join this table to the TSRECORD table based on the URI of the record. The rhEventType field indicates the type of event. The enumeration of those types are listed below (listed online here: http://support.openview.hp.com/selfsolve/document/KM507970).
oh well, I only need event 12, so I can live with that :P. The only other thing I think I need to know is, if I have record numbers not URIs, what table is the data that defines that relationship stored in...?
Thanks again, Kate
PS your help is most appreciated
just read your edit...lol
EDIT: Ignore me, you wrote this in your original post TSRECORD
It's just a direct relationship between TSRECORD, TSRECLOCHI.
TSRECORD has the record information. And the rest of the information in the TSRECLOCHI table gives you the events.
So you need all types of event 12 from the system, you'd use this query:
SELECT TSRECORD.recordId AS Expr1, TSRECLOCHI.uri, TSRECLOCHI.rhRecUri, TSRECLOCHI.rhDoneAt, TSRECLOCHI.rhDoneBy, TSRECLOCHI.rhEventType, TSRECLOCHI.rhEventDetails, TSRECLOCHI.rhEventUri, TSRECLOCHI.rhSecureViolation FROM TSRECORD RIGHT OUTER JOIN TSRECLOCHI ON TSRECORD.uri = TSRECLOCHI.uri where rhEventType = 12
It's worth noting that the rhDoneBy field is the NTLOGIN of the user at the time of the event. You can relate that back to TSLOCLOGIN with a join the TSLOCATION, but if a user's name has changed over time (last name change during marriage) then that type of join may exclude items you want.