Tracking Data Changes - Audit Log - 2 of 2

Article

Fabio Goncalves · Apr 19, 2017 5m read

In the previous article, I have demonstrated a simple way to record data changes. At this time I have changed the "Audit Abstract Class" which is responsible for recording audit data and the data structure where the audit log will be recorded.

I have changed the data structure to a parent and child structure where there will be two tables to record the "transaction" and the "fields its values" changed on that transaction.

Take a look at the new data model:

Take a look at the code changed from "Audit Class":

Class Sample.AuditBase [ Abstract ]
{

Trigger SaveAuditAfter [ CodeMode = objectgenerator, Event = INSERT/UPDATE, Foreach = row/object, Order = 99999, Time = AFTER ]
{
#dim %compiledclass As %Dictionary.CompiledClass
#dim tProperty As %Dictionary.CompiledProperty
#dim tAudit As Sample.Audit

Do %code.WriteLine($Char(9)_"; get username and ip adress")
Do %code.WriteLine($Char(9)_"Set tSC = $$$OK")
Do %code.WriteLine($Char(9)_"Set tUsername = $USERNAME")

Set tKey = ""
Set tProperty = %compiledclass.Properties.GetNext(.tKey)
Set tClassName = %compiledclass.Name

Do %code.WriteLine($Char(9)_"Try {")
Do %code.WriteLine($Char(9,9)_"; Check if the operation is an update - %oper = UPDATE")
Do %code.WriteLine($Char(9,9)_"if %oper = ""UPDATE"" { ")

Do %code.WriteLine($Char(9,9,9)_"Set tAudit = ##class(Sample.Audit).%New()")
Do %code.WriteLine($Char(9,9,9)_"Set tAudit.Date = +$Horolog")
Do %code.WriteLine($Char(9,9,9)_"Set tAudit.UserName = tUsername")
Do %code.WriteLine($Char(9,9,9)_"Set tAudit.ClassName = """_tClassName_"""")
Do %code.WriteLine($Char(9,9,9)_"Set tAudit.Id = {id}")
Do %code.WriteLine($Char(9,9,9)_"Set tSC = tAudit.%Save()")
do %code.WriteLine($Char(9,9,9)_"If $$$ISERR(tSC) $$$ThrowStatus(tSC)")
Do %code.WriteLine($Char(9,9,9)_"Set tAuditId = tAudit.%Id()")

While tKey '= "" {
set tColumnNbr = $Get($$$EXTPROPsqlcolumnnumber($$$pEXT,%classname,tProperty.Name))
Set tColumnName = $Get($$$EXTPROPsqlcolumnname($$$pEXT,%classname,tProperty.Name))

If tColumnNbr '= "" {

Do %code.WriteLine($Char(9,9,9)_";")
Do %code.WriteLine($Char(9,9,9)_";")
Do %code.WriteLine($Char(9,9,9)_"; Audit Field: "_tProperty.SqlFieldName)
Do %code.WriteLine($Char(9,9,9)_"if {" _ tProperty.SqlFieldName _ "*C} {")

Do %code.WriteLine($Char(9,9,9,9)_"Set tAuditField = ##class(Sample.AuditField).%New()")
Do %code.WriteLine($Char(9,9,9,9)_"Set tAuditField.Field = """_tColumnName_"""")
Do %code.WriteLine($Char(9,9,9,9)_"Set tAuditField.OldValue = {"_tProperty.SqlFieldName_"*O}")
Do %code.WriteLine($Char(9,9,9,9)_"Set tAuditField.NewValue = {"_tProperty.SqlFieldName_"*N}")
Do %code.WriteLine($Char(9,9,9,9)_"Do tAuditField.AuditSetObjectId(tAuditId)")
Do %code.WriteLine($Char(9,9,9,9)_"Set tSC = tAuditField.%Save()")
do %code.WriteLine($Char(9,9,9,9)_"If $$$ISERR(tSC) $$$ThrowStatus(tSC)")
Do %code.WriteLine($Char(9,9,9)_"}")
}
Set tProperty = %compiledclass.Properties.GetNext(.tKey)
}

Do %code.WriteLine($Char(9,9)_"}")

Do %code.WriteLine($Char(9)_"} Catch (tException) {")

Do %code.WriteLine($Char(9,9)_"Set %msg = tException.AsStatus()")
Do %code.WriteLine($Char(9,9)_"Set %ok = 0")
Do %code.WriteLine($Char(9)_"}")

Set %ok = 1
}

}

By changing data through the Test() classmethod, now you can see the "parent record" from the Audit Class (Sample.Audit) and the "children fields" changed from "Audit Field" class. (Sample.AuditField).

d ##class(Sample.Person).Test(1)
INSERT INTO Sample.Person (Name, Age) VALUES ('TEST PARENT-CHILD', '01')
SQLCODE: 0

ID Age Name
1 01 TEST PARENT-CHILD

1 Rows(s) Affected

UPDATE Sample.Person SET Name = 'INTERSYSTEMS DEVELOPER COMMUNITY', Age = '100' WHERE Name = 'TEST PARENT-CHILD'
SQLCODE:0

ID Age Name
1 100 INTERSYSTEMS DEVELOPER COMMUNITY

1 Rows(s) Affected

Audit classes:

Note that the Sample.AuditField records have references to the Sample.Audit class through Audit field = 1. You can query data by using the relationship from both classes as follows:

That's it. As a result we have a different audit log data structure.

Audit Sample Classes 2

Tirthankar Bachhar · Aug 24, 2017

I'm trying to find the part one - Tracking Data Changes - Audit Log - 1 of 2

Can anyone help me?

0 0

John Murray · Aug 24, 2017

There's a hyperlink at the top of part 2 above. Maybe it wasn't there when you first looked.

Evgeny Shvarov · Oct 5, 2017

Hi, Fabio! Great article! Do you have a github repo or gist for your sample classes too?

Andre Cerri · Nov 13, 2019

Hey Fabio

Nice work on this! I had a long discussion with a customer about data auditing and he pointed me to this article.

We discussed the concepts of auditing specific fields, or just auditing the whole record on update. I think this depends very much on the actual usage of the application. For example, if a user typically only modifies one field out of 50 for an object, then your approach works well, but if they typically modify ten fields it may be better to just store the whole record as in the former case you are writing 11 additional database records, versus just one if you write the whole record. But in turn this also depends on the size of each record - if there is a lot of text then it could become burdensome as well. And remember, all those database writes get journaled.....

We also discussed mapping the audit globals to a separate database. Then you have the option of (say monthly) archiving the audit database to cheaper storage and replacing with a fresh copy. Then retrieving and mounting on a test system as necessary for audit record retrieval purposes. This way you also can just throw away old databases versus having to purge them with all the overhead that entails.

Hope these thoughts help someone!