Restore a Field on the User Table Back to its previous Value

[jonathangilbert]

Hi,

 A bulk change was made to user on the sys_user table where a field called "u_principal_name" was cleared.

How can I get all these records to update back to have the previous value in them

I have seen on the forums that you can use a business rule with logic like "current.u_principal_name=previous.u_principal_name", but that does not work.

I also tried using flow designer to look up the history set and then the history line, but this does not work either as I have established that the history set does not get created until you right click the record and then select "History>list", so flow designer cant find a record.

I have around 4000 records to restore

[Vikram Reddy]

Hello @jonathangilbert,

The current.u_principal_name=previous.u_principal_name trick doesn't work because previous is only populated during the same transaction that fired the business rule, it's not a lookup into history. What you actually want is the sys_audit table, since that's the source data sys_history_set and sys_history_line get built from, and it doesn't depend on anyone having opened History > List first.

Before running anything against all 4000 records, check:

• the Audit flag on the u_principal_name dictionary entry (or table-level auditing on sys_user) was actually on, otherwise sys_audit has nothing to restore from
• sys_audit has rows for tablename sys_user, fieldname u_principal_name, with a non-empty oldvalue right before the bulk clear
• documentkey on those rows matches the sys_user sys_id you're fixing

If that checks out, a background script pulling the most recent oldvalue per user will do the restore:

var gr = new GlideRecord('sys_user');
gr.addQuery('u_principal_name', '');
gr.query();
while (gr.next()) {
    var aud = new GlideRecord('sys_audit');
    aud.addQuery('tablename', 'sys_user');
    aud.addQuery('documentkey', gr.sys_id);
    aud.addQuery('fieldname', 'u_principal_name');
    aud.addNotNullQuery('oldvalue');
    aud.orderByDesc('sys_created_on');
    aud.query();
    if (aud.next() && aud.oldvalue) {
        gr.u_principal_name = aud.oldvalue;
        gr.update();
    }
}

Run it against a handful of records first in a sub-prod instance, and export the current sys_user set before touching all 4000, since this only recovers what auditing actually captured, anything not audited is gone for good.

Thank you,
Vikram Karety
Octigo Solutions INC