cancel
Showing results for 
Search instead for 
Did you mean: 

Active Audit Logs / Historical Events

SOLVED
Go to solution
Highlighted
Katherine Birch
Regular Collector

Active Audit Logs / Historical Events

Hi

 

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).

 

Thanks, Kate

 

 

7 REPLIES
Katherine Birch
Regular Collector

Re: Active Audit Logs / Historical Events

PS using TRIM 7.3.0.5210

 

EWillsey
Honored Contributor
Solution

Re: Active Audit Logs / Historical Events

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).

 

I hope this helps!

  

0

currentChanged

1

homeChanged

2

ownerChanged

3

contactAdded

4

contactRemoved

5

erecViewLocationType

6

erecBookoutLocationType

7

erecExtractLocationType

8

erecReplaceLocationType

9

erecNewVersionLocationType

10

erecReturnOnlyLocationType

11

erecRemoveLocationType

12

erecAttachLocationType

13

erecPublishLocationType

14

dispositionChanged

15

signatureCreated

16

signatureAttached

17

signatureVerified

18

signatureFailVerify

19

securityChanged

20

scheduleReviewed

21

madeVital

22

madeNotVital

23

holdAdded

24

holdRemoved

25

containerChanged

26

titleChanged

27

notesChanged

28

createDateChanged

29

scheduleChanged

30

numberChanged

31

accessControlChanged

32

erecPrintLocationType

33

erecExportXMLLocationType

34

erecRenditionExtracted

35

erecRenditionViewed

36

erecRenditionPrinted

37

erecRevisionExtracted

38

erecRevisionViewed

39

erecRevisionPrinted

40

erecLinkExtracted

41

erecLinkViewed

42

erecLinkPrinted

43

erecMailed

44

externalEvent

45

classificationChanged

46

closeDateChanged

47

erecAnnotated

48

erecNotAnnotated

49

erecRedacted

50

erecRevisionRemoved

51

udfChanged

52

erecRenditionAdded

53

createRecordRelationship

54

removeRecordRelationship

55

regDateChanged

56

dueDateChanged

57

datePublishedChanged

58

dateInactiveChanged

59

dateLastActionChanged

60

dateDestructionChanged

61

dateRetentionChanged

62

finalized

63

unfinalized

 

Katherine Birch
Regular Collector

Re: Active Audit Logs / Historical Events

Thanks

This does help!  My only confusion is that there are 73 events that I can select to audit and only 64 on this list.  I will get my DBA on the case, and see what we work out from here! 

Thanks again, Kate

 

EWillsey
Honored Contributor

Re: Active Audit Logs / Historical Events

Awe, well that post is old. Here's a list I just extracted from the enumeration within 7.3:

htCurrentChanged = 0,
htHomeChanged = 1,
htOwnerChanged = 2,
htContactAdded = 3,
htContactRemoved = 4,
htErecViewLocationType = 5,
htErecBookoutLocationType = 6,
htErecExtractLocationType = 7,
htErecReplaceLocationType = 8,
htErecNewVersionLocationType = 9,
htErecReturnOnlyLocationType = 10,
htErecRemoveLocationType = 11,
htErecAttachLocationType = 12,
htErecPublishLocationType = 13,
htDispositionChanged = 14,
htSignatureCreated = 15,
htSignatureAttached = 16,
htSignatureVerified = 17,
htSignatureFailVerify = 18,
htSecurityChanged = 19,
htScheduleReviewed = 20,
htMadeVital = 21,
htMadeNotVital = 22,
htHoldAdded = 23,
htHoldRemoved = 24,
htContainerChanged = 25,
htTitleChanged = 26,
htNotesChanged = 27,
htCreateDateChanged = 28,
htScheduleChanged = 29,
htNumberChanged = 30,
htAccessControlChanged = 31,
htErecPrintLocationType = 32,
htErecExportXMLLocationType = 33,
htErecRenditionExtracted = 34,
htErecRenditionViewed = 35,
htErecRenditionPrinted = 36,
htErecRevisionExtracted = 37,
htErecRevisionViewed = 38,
htErecRevisionPrinted = 39,
htErecLinkExtracted = 40,
htErecLinkViewed = 41,
htErecLinkPrinted = 42,
htErecMailed = 43,
htExternalEvent = 44,
htClassificationChanged = 45,
htCloseDateChanged = 46,
htErecAnnotated = 47,
htErecNotAnnotated = 48,
htErecRedacted = 49,
htErecRevisionRemoved = 50,
htUdfChanged = 51,
htErecRenditionAdded = 52,
htCreateRecordRelationship = 53,
htRemoveRecordRelationship = 54,
htRegDateChanged = 55,
htDueDateChanged = 56,
htDatePublishedChanged = 57,
htDateInactiveChanged = 58,
htDateLastActionChanged = 59,
htDateDestructionChanged = 60,
htDateRetentionChanged = 61,
htFinalized = 62,
htUnfinalized = 63,
htContentAdded = 64,
htContentRemoved = 65,
htCsDeclassifyChanged = 66,
htCsDowngradeChanged = 67,
htActionCompleted = 68,
htActionAssigned = 69,
htActionReassigned = 70,
htActionUndone = 71,
htActionCurrent = 72,
EWillsey
Honored Contributor

Re: Active Audit Logs / Historical Events

Note there was not a 73. Not sure what's up with that.

 

EDIT: forgot this was 0 based, so there are 73 types.

Katherine Birch
Regular Collector

Re: Active Audit Logs / Historical Events

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

EWillsey
Honored Contributor

Re: Active Audit Logs / Historical Events

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.

 

And we work on here for kudos! :)

//Add this to "OnDomLoad" event