ServiceNow – Query Every Asset A user has ever been assigned to

So, an interesting request came across my desk earlier today, to query every asset a user has ever been assigned.

Normally- I would say this should be an easy request… Query sys_audit where tablename=alm_asset, fieldname=assigned_to, filter by the values… and build a report based on this data.

However- data in sys_audit, stays forever, for every single table. So, if you attempt to run that query on any service-now instance that has been up for a few years, You will notice, it crashes.

This is because neither fieldname, nor tablename are indexed columns (on the underlying database)

However- if you look at an asset, and select history, this data is being pulled from sys_audit.

You may ask, well- why does that work when I cannot query the table at all??

The answer is- sys_audit.documentkey is an indexed field. So- when the related query looks for sys_audit where document_key = sys_id of the alm_asset, it returns very quickly, because it is indexed.

Now that we have gotten that part out of the way…. What about the report?

Well- while I am very knowledgeable on the BACKEND of service-now, and its available APIs, I do not know enough knowledge to build the query within service-now. BUT- I did create the logic in c# using a service-now library which I have created.

If you are a experienced service-now individual, you may be able to build a report based on my logic.

var Users = sn.OfType<sys_user>().Where(o => (Insert lamba expression here to locate the specific users)).ToList();

foreach (var rec in sn.OfType<alm_asset>())
{
    var history = sn.OfType<sys_audit>()
        .Where(o => o.documentkey == (object)rec.sys_id)
        .Where(o => o.fieldname == nameof(alm_asset.assigned_to) || o.fieldname == nameof(alm_asset.u_custom_assigned_to))
        //Force query to list.
        .ToList()
        //Local Expression
        .Where(o => Users.Any(usr => usr.sys_id.Equals(o.newvalue)));

    foreach (var match in history)
    {
        //Do Something with you results.
    }
}

The above query is quite inefficient, because it pulls down ALL assigned_to history change records for every asset to evaluate locally. However- it is many times faster then attempting to query sys_audit directly.

sys_history only contains 28 days of data, before any mentions it.

Summary:

1. Query all assets
2. For each asset, query sys_audit where documentkey = asset.sys_id
3. Perform additional filtering on sys_audit, for specific field names, or values.
4. Profit.

Edit- as a last note, this could easily be implement in service-now javascript.