Improved record change tracking using Audit feature

In a previous post, I demonstrated how the Maximo audit tracking can be used to display the updates made on a record enabling the audit and adding a “history” tab in the application. This technique is quite common but has a major drawback; it displays all the audited field in a row even if only one attribute was changed. When there are many audited fields it is very hard for the user understanding what was changed.

Some years ago i developed the Audit Trail report that can display changes made to any audited table in a certain time window. However, this is not intended to display the changes made to a specific record in the database.

In this post I will describe a simple but powerful solution to display the Maximo audit records in a more human-readable format. Here is how the result will be.

The technique is based on one database table that can store the transposed audit trail and one automation script that populates it scanning the audit tables for changes. It is a vary lightweight solution that relies on the standard Maximo audit feature to track changes.

In this example I will use the Asset application as an example but the technique can be applies to any Maximo object and application. Lets start.

Create database table

Open Database Configuration application and create a new object:

• Name: CX_AUDIT
  • Description: Audit tracking
  • Level: SYSTEM

Do not save the record.

Open the Attributes tab and configure as follows:

• CX_AUDITID – Remove ‘1’ from Primary Key
• DESCRIPTION – Delete this attribute.
• OBJECTNAME – Title: Object – Required: Yes – Primary Key: 1 – Same As: MAXOBJECT.OBJECTNAME
• RECORDID – Title: Record ID- Required: Yes – Primary Key: 2 – Type: BIGINT
• CHANGEDATE – Title: Change Date – Required: Yes – Primary Key: 3 – Type: DATETIME
• ATTRNAME – Title: Attribute – Primary Key: 4 – Required: Yes – Same As: MAXATTRIBUTE.ATTRIBUTENAME – Search Type: WILDCARD
• CHANGEBY – Title: Change By – Required: Yes – Same As: PERSON.PERSONID – Search Type: WILDCARD
• ATTRDESC – Title: Attribute Description – Same As: MAXATTRIBUTE.TITLE – Search Type: WILDCARD
• VALUE1 – Title: Old Value – Type: ALN(1000) – Search Type: WILDCARD
• VALUE2 – Title: New Value – Type: ALN(1000) – Search Type: WILDCARD

Check that your attributes list looks like the following picture and save it.

Now enable admin mode and apply database configuration changes.

Open the ASSET database object and define the following relationship:

• A_ASSET – Child Object: A_ASSET – Where: assetuid=:assetuid
• CX_AUDIT – Child Object: CX_AUDIT – Where: recordid=:assetuid and objectname=’ASSET’

Modify application to display audit data

Open the Application Designer application and export the application XML. Add the following XML fragment just before the </tabgroup> tag and then import the XML.

<tab id="cx_audit" label="History">
  <buttongroup id="cx_audit_btngrp" align="left">
    <pushbutton default="true" id="cx_audit_btn_refresh" label="Refresh Audit Data" mxevent="CX_AUDIT"/>
  </buttongroup>
  <table id="cx_audit_tbl" inputmode="readonly" label="Audit Data" orderby="CHANGEDATE desc" relationship="CX_AUDIT">
    <tablebody id="cx_audit_tb" displayrowsperpage="20">
      <tablecol dataattribute="CHANGEDATE" id="cx_audit_CHANGEDATE"/>
      <tablecol dataattribute="CHANGEBY" id="cx_audit_CHANGEBY"/>
      <tablecol dataattribute="ATTRNAME" id="cx_audit_ATTRNAME"/>
      <tablecol dataattribute="ATTRDESC" id="cx_audit_ATTRDESC"/>
      <tablecol dataattribute="VALUE1" id="cx_audit_VALUE1"/>
      <tablecol dataattribute="VALUE2" id="cx_audit_VALUE2"/>
    </tablebody>
  </table>
</tab>

We also need to configure a signature option to trigger the automation script that will populate the CX_AUDIT table. Open the Add/Modify Signature Option dialog for the ASSET application and define a new sigoption as follows:

• Option: CX_AUDIT
• Description: Audit History
• Option: This is an action that must be invoked by user in the UI

Open the Security Groups application and grant MAXADMIN group (and any other group the has to display audit data) to the “Audit History” option for the Asset application. Logout and login back to ensure Maximo reloads MAXADMIN profile.

Automation script

Now we can create the automation script. Open the Automation Scripts application and create a new script with action launch point.

• Launch Point: CX_AUDIT – Populate CX_AUDIT from audit table
• Object: ASSET
• Action: CX_AUDIT – Populate CX_AUDIT from audit table
• Script: CX_AUDIT – Populate CX_AUDIT from audit table
• Language: python

from psdi.server import MXServer

#-------------------------------------------------------------------------------
def getAuditAttrs(objname):
    attrSet = MXServer.getMXServer().getMboSet("MAXATTRIBUTE", mbo.getUserInfo())
    attrSet.setWhere("objectname='" + objname + "' and eauditenabled=1 and persistent=1")
    attrSet.setOrderBy("primarykeycolseq, attributeno")
    
    attrList = []

    attr = attrSet.moveFirst() 
    while (attr): 
        attrList.append([attr.getString("ATTRIBUTENAME"), attr.getString("TITLE")])
        attr = attrSet.moveNext()

    attrSet.close()
    
    return attrList

#-------------------------------------------------------------------------------

attrList = getAuditAttrs(mbo.getName())

aSet = mbo.getMboSet("CX_AUDIT")
aSet.deleteAll()
aSet.save()

auditSet = mbo.getMboSet("A_" + mbo.getName())

attrValues = {}
for attr in attrList:
    attrValues[attr[0]] = ""
        
audit = auditSet.moveFirst() 
while (audit): 
    # compare values of the current audit record with previous values
    for attr in attrList:
        attrName = attr[0]
        attrTitle = attr[1]
        
        v0 = attrValues.get(attrName)
        v1 = audit.getString(attrName)
        if v1 != v0 and attrName != "CHANGEBY":
            a = aSet.add()
            a.setValue("OBJECTNAME", mbo.getName())
            a.setValue("RECORDID", mbo.getUniqueIDValue())
            a.setValue("ATTRNAME", attrName)
            a.setValue("CHANGEDATE", audit.getDate("EAUDITTIMESTAMP"))
            a.setValue("CHANGEBY", audit.getString("EAUDITUSERNAME"))
            a.setValue("ATTRDESC", attrTitle)
            a.setValue("VALUE1", v0)
            a.setValue("VALUE2", v1)
        attrValues[attrName] = v1

    audit = auditSet.moveNext() 

aSet.save()

Final considerations

The result of the proposed configuration is much more readable that the classic way of displaying audit data. It also allows to search for changes of a specific attribute. However, it requires that the user click the Refresh button to display data. Different solutions can be implemented to improve it.

• Use tabchangeevent tag as described in this IBM TechNote to automatically trigger the CX_AUDIT event. It seems to be the optimal solution but but, at least in my 7.6.1.3 environment, it is not working in a consistent way.
• Use an escalation to launch the CX_AUDIT script at regular intervals. I don’t like this solution because there will always be a delay between the record update and the population of the CX_AUDIT table. It will also fill CX_AUDIT table with a lot of data that may be never displayed.
• Add an After Commit launchpoint on each audited table to launch the CX_AUDIT script.
• Leave the Refresh button as described in this post and eventually implement a cron task to cleanup the CX_AUDIT table every hour. This will leave the CX_AUDIT clean and the user will be able to force a refresh.
• Modify the application to move the CX_AUDIT tab to a dialog. In this way it should be possible to always trigger the re-population of the CX_AUDIT table when the dialog is displayed.