Create audit trail for Kepion apps and data – Kepion Support Center

Introduction

An audit trail allows businesses to track changes to Kepion apps and data. This article shows application modelers and systems admins how to create an audit trail for your fact tables, generate audit reports, and track single changes.

Tip: Learn how to create an audit trail for your forms in our Review Form Changes article.

Fact vs. fact history tables

All your current data is contained in fact tables (F_), but you can have a record of updated and/or deleted data in fact history tables (FH_). The FH_ table provides the information you need to follow an audit trail. Let's dive deeper to help you interpret your FH_ tables.

Structures

F_ and FH_ tables share the same structure, except the latter has an additional column: InitiatedActivityID. This column holds the unique values (assigned in numerical order) for sets of data that have been updated or deleted and posted.

F_ table

FH_table

ActivityID, on the other hand, is the unique value (assigned in numerical order) for data sets that have been newly added or updated and posted.

Here's an example to help distinguish ActivityID from InitiatedActivityID. When you post new or updated data to the F_ table, a new ActivityID would be assigned for all those records. If you delete a record with an existing 38 ActivityID as part of that same post, that record will be moved to the FH_ table with an InitiatedActivityID of 55.

Note: Posting multiple data changes at the same time will result in the changed records sharing an ActivityID and InitiatedActivityID.

Examples

Let's see how adding, updating, and deleting values affect F_ and FH_ tables.

Add

In our form, we've added a value of 50 to the empty cell and posted the change.

Querying the F_ table in SSMS shows the record and its ActivityID.

Adding a record to a blank cell will not update the FH_ table as you are not changing or deleting an existing data.

Update

We then change the value from 50 to 100 and post the change.

The F_ table does not show the previous record. It displays the updated value, which has a new ActivityID.

The FH_ table, however, shows the original record.

Delete

Let's delete the value and post the change.

The F_table does not have the original record or the updated record. It reflects the current data present in your model.

The FH_ table has tracked the original record and the deleted record.

Create audit trail

With F_ and FH_ tables defined, let's create an audit trail.

1. Open Kepion and go to the Modeler module.

2. From the left navigation, go to Models.

3. Locate your target model(s) and select its corresponding checkbox in the Audit column.

An FH_ table will be generated for any model with the Audit option enabled. You can find the fact history table in SQL Server Management Studio (SSMS) by expanding the Tables node of your target application.

You can now generate an audit report or track single changes.

Generate audit report

1. Open SSMS and connect to your SQL Server.

2. Select New Query.

3. Copy and paste the following into the query window:

USE [<Application Database>]
GO

SELECT 
 DA.[MemberLabel] [Account]
,SA.[MemberLabel] [Scenario]
,F.[TimeID]
,F.[Value]
,F.[Comment]
,CA.ActedOn [Created On]
,CU.FullName [Created By]
,MA.ActedOn [Modified On]
,MU.FullName [Modified By] 
FROM
(
SELECT 
 [AccountID]
,[TimeID]
,[ScenarioID]
,[Value]
,[Comment]
,[ActivityID]
,-1 [InitiatedActivityID]
FROM [dbo].[F_Expense_CoreMG_Writeback]

UNION ALL

SELECT 
 [AccountID]
,[TimeID]
,[ScenarioID]
,[Value]
,[Comment]
,[ActivityID]
,[InitiatedActivityID]
FROM [dbo].[FH_Expense_CoreMG_Writeback]
) F

LEFT OUTER JOIN [dbo].[Activities] CA
ON F.[ActivityID] = CA.[ActivityID]
LEFT OUTER JOIN [dbo].[Users] CU
ON CA.[UserID] = CU.[UserID]
LEFT OUTER JOIN [dbo].[Activities] MA
ON F.[InitiatedActivityID] = MA.[ActivityID]
LEFT OUTER JOIN [dbo].[Users] MU
ON MA.[UserID] = MU.[UserID]
LEFT OUTER JOIN [dbo].[D_Account] DA
ON F.[AccountID] = DA.[MemberId]
LEFT OUTER JOIN [dbo].[D_Scenario] SA
ON F.[ScenarioID] = SA.[MemberId]

GO

4. Replace the following:

<Application Database> with the name of your own application database.
References to F_ and FH_ tables (e.g., [dbo].[FH_Expense_CoreMG_Writeback]) with the name of your own fact and fact history tables.
Column names with those in your own tables.

5. Select Execute.

You should have a full audit report in the Results tab:

Note: The NULL values in the Modified On and Modified By columns come from the current values in your F_table. They haven't been updated or deleted, hence the NULL values in those columns.

Track a single change

1. Open SSMS and connect to your SQL Server.

2. In a new query, enter a SELECT * FROM statement to see your target FH_ table and select Execute.

3. Locate the target record and take note of its ActivityID.

4. In a new query, enter a JOIN statement that pulls the user responsible for the change and the exact date and time when the change occurred from dbo.Activities and dbo.Users.

SELECT A.[ActivityID], U.[FullName], A.[ActedOn]
FROM [dbo].[Activities] A
INNER JOIN [dbo].[Users] U
ON A.[UserID] = U.[UserID]
WHERE A.[ActivityID] = <ActivityID of record>

If we want to find out who deleted the 100 value shown in the FH_ table in the previous section and when the change was posted, we could use the following query:

SELECT A.[ActivityID], U.[FullName], A.[ActedOn]
FROM [dbo].[Activities] A
INNER JOIN [dbo].[Users] U
ON A.[UserID] = U.[UserID]
WHERE A.[ActivityID] = 40