HomeInvestigating Data Updates in IBM ICM

Investigating Data Updates in IBM ICM

 

IBM ICM users frequently need to investigate data updates, especially backdated changes that cause prior period adjustments.   Both, the Audit Log and a lesser known tool, Table History, can be used to investigate data updates, identify the user who made the change, and check the values prior to the update.

 

Audit Log

The audit log will also show details for the following events:

    • Data Import Concluded and
    • Data Saved

The audit log is a good place to start when scheduled data imports inadvertently cause a prior period adjustment:

  1. Open the Audit tab
  2. Select the event in the audit log and
  3. Right click on the row to display the show details menu
  4. Select ; a popup will display all the rows updated by the event
  5. Additional data details are available by selecting a row and right clicking
  6. Select the Show Detail menu and another popup will appear
      • The popup will display the previous row and the current row;  any changes will be highlighted, as illustrated in figure 1

 

 

Table History

Table History stores all the data changes on a hidden system table and can be accessed using the View/Edit Data Ribbon.  The history table displays all changes to the record.  It will display the current and previous historical values in addition to user and date of the change.  Table History is a good place to start if prior period adjustments are caused by a manual data load.

  1. First select the table and View/Edit Data
  2. Next select a row of Data
  3. Then click on the History icon(blue book)
  4. From the popup, right click on the data row and right click
  5. Select the show history menu and the current popup will be updated with all the history for that row(figure 2)

 

 

 

There are two tools available in IBM ICM to investigate data updates, the date of the update and the user that made the changes.  Both the tools can provide historical data value, which is often required for investigative purposes.Consider using Table History when multiple changes need to be reviewed, or the Audit Log when investigating scheduled data imports.  Use the Table History when investigating multiple updates to the same data row.

 

How to Investigate Data Deletions

Data saved events in the Audit Table also track the deletion of data from a table. 

  1. Open the Audit tab
  2. Select the event in the audit log and
  3. Right click on the row to display the Show Details menu
  4. Select ; a popup will display all the rows updated by the event
      • All the deleted rows will be highlighted in red and
      • The text will be crossed out
  5. Additional data details are available by selecting a row and right clicking
  6. Select the Show Detail menu and another popup will appear
      • The popup will display the previous row and the current row will display null as illustrated in figure 3

 

 

 

Because the row no longer exists, it cannot be pulled up using the row history button.  However, it is possible to display deletions using SQL. There are a couple of SQL options to use, depending on the host:

    • On-Premise:  Software like SQL Server Management Studio
    • On-Premise and Cloud:  Data import wizard embedded within IBM ICM

To use the Data Import wizard, right click on a table and chose the data, import data menu item.  From there set up a server import from an ODBC compliant database.  Connect to the database using the SQL Server driver and the appropriate connection string.  A query window will appear in step 5 of the Data Import wizard after connecting successfully.

    • Write a SQL query against the HistoryMap table using a restriction on the table you are investigating and then preview the results as Illustrated in figure 4. You are looking to identify the HistoryID.

 

 

    • Write another SQL query using the identified HistoryID. The preview will display the rows as illustrated in figure 5.

 

The example above shows all the rows for Payee 50, 51 and 52.  These rows have an“_End” event without a subsequent “_Start” event, indicating that the row was deleted and not changed.  The difference between deleted rows and updated rows is illustrated in figure 6 which displays multiple updates to Payee 1 over time.

 

In addition to the results displayed, “UserID_” and “Time_” can be added by creating an inner join to the Audit_ table on the “_Start” or “_End fields”.  SQL has a couple of advantages over the audit log:

  • multiple updates can be displayed at once
  • “like”, “not like”, “in”, “not in” and wildcard searches can frame an unstructured search

This approach, however, does require some basic SQL knowledge.

 

Conclusion

 Through the Audit tab, Table History and SQL, both data updates and row deletions can be investigated. 

Audit Tab 

    • Simple to use
    • Works for updates and deletions
    • Displays all changes during an event
    • Row detail can provide user and time information

Table History

    • Simple to use
    • Works for updates
    • Displays all changes to the row over time
    • Detail can provide user and time information

SQL

    • Requires an understanding of SQL
    • Works for updates and deletions
    • Can display multiple events
    • Can display user and time through inner join

These tools can be powerful aids to tracking down the root cause of any data changes that may not have been tracked via external auditing.

 

 

About Author:

This article is authored by a team of IBM ICM Professionals at Spectrum Technologies LLC. For further information on this topic, please reach us at   This email address is being protected from spambots. You need JavaScript enabled to view it. , or visit us at www.spectrumbiztech.com