Easy way to create a List report and include the sys_id

[srphayre]

Hi all,

I'm trying to create a simple List report on cmdb_ci_server with about 10 fields but want the report to include the record's sys_id (the report will probably be updated offline and I want a clear way to track back to the correct record).

I know I could create a database view to accomplish this but wondered if there was an easier way.

Still using Berlin.
Streve

[CapaJC]

There are hacks that can add that field to a list or list report (I tried it in System UI > Lists, where I opened the list definition and added a new list element manually in the related list, flushed the instance cache with a cache.do, then viewed the list), but that column will get stripped out if you export it to Excel or CSV. This is a hardcoded behavior.

Exporting to XML will include the sys_id, if you have a way to consume the XML on the other end (I'm definitely no expert at that).

[srphayre]

Yeah - I tried the xml export but it doesn't have the extended fields that I want in the report (just the sys_ids of reference fields)...

[geoffcox]

You can create a new column in the cmdb_ci table called u_sys_id.
Then add an on Insert business rule that copies the sys_id to u_sys_id.
Then write a one-time scheduled job to copy the sys_id to u_sys_id for all existing records.
Now you can show the new column on any list or form you want, including cmdb_ci_server.
I suggest also making it read-only in the dictionary.

On insert business rule:

    current.u_sys_id = current.sys_id;

One time scheduled job:

do_copy_sys_id();

function do_copy_sys_id() {
    var cmdb_ci = new GlideRecord('cmdb_ci');
    cmdb_ci.query();
    while (cmdb_ci.next()) {
        cmdb_ci.u_sys_id = cmdb_ci.sys_id;
        cmdb_ci.update();
    }
}

I hope this helps!

Cheers,
Geoff.

[srphayre]

Geoff - I think this is the most likely solution - its unfortunate that it cant be done without this hack though.
Steve