IQDoc
ITIQPro Docs Maintenance Connection Everywhere (MCe) · EAM/CMMS manuals
Accessing audit trail history
apsAuditLog, apsTransactionAuditLog

TL;DR

We keep a full, detailed record of everything that is changed, and some additional.

The purpose is to provide an audit trail that can be used by lawyers as needed.

It can be difficult to fully understand, though mostly it can be digested once you get to the apsTransactionAuditLog

Because it is huge, accessing these tables can demand very heavy performance costs on the server and should only be used with Professional Services.

It is critical to use the correct indexes to avoid overburdening, but also the fields with 'JSON' in their name can contain very large amounts of data and should not be accessed without a limit until you are sure you have the record, or a very small number of records, that you need.

Purpose of this document

To guide our Professional Services and customers through finding useful information in the aps Audit Logs. See also: Same SQL for apsAuditLog and apsTransactionAuditLog

Tables

apsAuditLog

There is a table apsAuditLog that must be your starting point.

apsAuditLog discussion

Most important columns for this discussion

For WHERE clause

  • AuditServerUTC - the second most important one usually, you will almost always specify a where clause that is restricted by date range
  • LaborID
  • TargetSyncGUID - the most important one usually, you will almost always be looking for a specific item for the details
  • IsCommitted, never alone in a where clause, normally you'll say "Where isCommitted = 1" to get only the committed records.

Most important for display and moving to the next stage

  • AuditServerUTC - 'when' it happened on the server, UTC means it is is the UTC timezone. This allows exact timing to be done.
  • AuditClientUTC - 'when' it happened on the client, due to offline and flakey connections, this can be considerably before the serverUTC, our customer record we are aware of is 6 months (the iPad fell behind the back seat of the pickup truck and they decided that yes, they still wanted those images, they were excited that it worked even though there had been 2 upgrades since the images were taken.)
  • Module, to verify that you are looking at what you think you are looking at
  • IsCommitted

Note that the JSON and OldJSON are potentially huge and contain all the changes done. Instead, normally you want to

For Linking to get data from other tables

  • LaborID, to see who the user was that made the change, or you might wait until you are looking at the apsTransactionAuditLog file, it also has this value
  • TargetSyncGUID, normally you already know, but if you did a search of changes by users, this or the same value is apsTransactionAuditLog along with Module can tell you what entity was changed

Indexes:

  • ID
  • AuditServerUTC
  • LaborID then AuditServerUTC
  • BatchID
  • TargetSyncGUID then AuditServerUTC

How to use

Searching by BatchID always must be at least the second query, never the first. It is when you find something you are looking for then want to see everything else that was done at the same time, in the same batch.

Searching by ID likely not useful unless you are following up a previous discovery

Searching by LaborID then a date range, very useful and quick.

Searching by TargeSyncGUID, for a date range, very useful

apsTransactionAuditLog

This table has the most useful information in it. HOWEVER ... there are only 2 ways permissible to access it: Where TransaactionID = <value> [optional other restrictions]

Where BatchID = <value> [optional other restrictions]

Anything else can 'bring down the server' with the amount of data involved.

This means you must go to the apsAuditLog first, then come here when you know the Transaction or Batch ID that is interesting to you.

apsAuditLog discussion

Most useful columns for this discussion

For WHERE clause

  • BatchID

Most important for display

  • AuditServerUTC - 'when' it happened on the server, UTC means it is is the UTC timezone. This allows exact timing to be done.
  • AuditClientUTC - 'when' it happened on the client, due to offline and flakey connections, this can be considerably before the serverUTC, our customer record we are aware of is 6 months (the iPad fell behind the back seat of the pickup truck and they decided that yes, they still wanted those images, they were excited that it worked even though there had been 2 upgrades since the images were taken.)
  • ValueJSON
  • OldValueJSON

For linking to get data from other tables

  • Module (you need this to know how to interpret what the TargetKey and TargetSyncGuid is)
  • TargetPK, the PK of the item in the module, such as AssetPK
  • TargetSyncGuid, the SyncGuid of the item in the module, such as the Asset apsSyncGuid
  • LaborID, to see who the user was that made the change
  • Path, what table/item was changed
  • ValueJSON, the value that it was set to
  • OldValueJSON, the value, if known, that it was before the change
  • Operation, what type of change was being made, delete, add, update
  • IsCommitted, if this is false it means that the change was requested, but the commit has not yet happened, and if it is more than a few seconds or minutes ago, means it was requested but it didn't successfully happen, so the change did NOT occur.
  • Session ID - for advanced debugging, needs access to the reg database
  • TransactionID - for advanced debugging

Indexes:

  • TransactionID
  • BatchID

Normal steps for history for an entity like an asset or work order:

  1. Look at the entity, grab it's GUID
  2. Grab the apsAuditLog records for the GUID and usually a date range desc. Grab
  3. Now take the TransactionID or BatchID and look up the details in the apsTransactionAuditLog
  4. Take the LaborID to grab the user name