Question / Problem:
Is it possible to get a SQL query to link the column ActionKey number to AuditText text for StatsAdministrative and StatsAuditKey tables from Kofax Capture database?
Answer / Solution:
Starting with Kofax Capture 11 a new tracking tables were added to get information about activity in Kofax Capture Administration.
The StatsAdministrative table contains detail information about what was done in the Administration module such as publishing, deleting users, etc. The StatsAuditKey is basically a legend of the value of StatsAdministrative.
First step is to get the content of each table, start with StatsAdministrative simple query:
SELECT * FROM StatsAdministrative
The column "ActionKey" has numbering which does not mean much unless has text associated. The text associated with ActionKey column from StatsAdministrative table in StatsAuditKey.
The second step simple sql query to get the StatsAuditKey:
SELECT * FROM StatsAuditKey;
Combining and Table/Column: StatsAdministrative/ActionKey and StatsAuditKey/AuditKeyID, to get a more readable results instead of a number. The query below shows some of the values. The variable ACK1 = ActionKey from StatsAdministration and the variable ACK2 = AuditText from StatsAuditKey. Replacing the number with a text:
SELECT ModuleName, ModuleUniqueID, UserID, UserName, AuditText FROM StatsAdministrative ACK1
INNER JOIN StatsAuditKey ACK2 ON ACK2.AuditKeyID = ACK1.ActionKey
WHERE ActionDateTime >= '2019-04-10 12:05:05.000'
The results from the screenshot above in the had a more meaninful output than just a number.