Why Doesn't This Work? Or, A Puzzle Explained...

SlightlyLoony — Fri, 21 Oct 2011 13:50:50 GMT

I'm told that just a few weeks ago, our correspondent at right looked like the young man he actually is. Then he tried to write what he thought was a simple little script on his company's ServiceNow instance. After a few days that involved facepalms, banging his head against the wall, banging other people's heads against the wall, screaming, and some possibly licit intoxicants … he looked like this.

He should have contacted me earlier. He was doing almost everything correctly. All he was missing was one key piece of understanding, and the answer would have been obvious.

He needed to understand composite tables.

First, let's look at his code that didn't work. He was trying to write a function which, given the IP address of a computer, would return its name. His function worked fine for workstations and laptops, but not for servers. Here's what he had:


gs.log(findName('10.0.10.69'));

function findName(ip) {
    var gr = new GlideRecord('cmdb_ci_ip_address');
    gr.addQuery('ip_address', ip);
    gr.query();
    if (!gr.next())
        return null;

    var nic_id = '' + gr.nic;
    gr = new GlideRecord('cmdb_ci_network_adapter');
    if (!gr.get(nic_id))
        return null;

    var computer_id = '' + gr.cmdb_ci;
    gr = new GlideRecord('cmdb_ci_computer');
    if (!gr.get(computer_id))
        return null;

    var kind = '' + gr.sys_class_name;
    if (kind == 'cmdb_ci_computer')
        return '' + gr.name;

    return '' + gr.host_name;
}

Most of this is quite straightforward code that navigates through the CMDB database. First it finds the IP address table record with the desired IP address, then it uses that record's reference (nic) to locate the NIC (network adapter) record that is provisioned for that IP address. Once it has the NIC, it uses that record's CI reference (cmdb_ci) to locate the computer that the NIC is installed in. If any of this logic fails, it simply returns a null. So far, this is all just fine.

Right near the end is where our prematurely old friend goes horribly wrong. His code looks at the class name (sys_class_name) of the computer record to see if it's a plain old computer (which would mean it was a workstation or laptop) or something else (which would mean it was some kind of server). If it's a computer, it returns the contents of the name field — this is the part the works just fine. But if it's a server, it tries to return the host name (a field that only servers have). This fails miserably, returning an 'undefined'. Our aged friend can go look at the record for the server with that IP address, and the host name is staring him right in the face. It's there, dang it! What's up with the undefined?

The problem is that his code queried cmdb_ci_computer, not the actual server table. So what? Well, that's where you really need to understand composite tables...

When you query the cmdb_ci_computer table, under the covers the ServiceNow instance is actually joining three tables together to produce the results you find in your GlideRecord instance:

cmdb_ci â‰¡ cmdb_ci_hardware â‰¡ cmdb_ci_computer

All of the columns in all three of those tables are available to your GlideRecord instance. Our wizened friend's problem is that the host_name column is not in any of those tables — hence the undefined. Facepalm time.

The fix is very straightforward — just "re-get" the record in question using a glide record for the actual class:


gs.log(findName('10.0.10.69'));

function findName(ip) {
    var gr = new GlideRecord('cmdb_ci_ip_address');
    gr.addQuery('ip_address', ip);
    gr.query();
    if (!gr.next())
        return null;

    var nic_id = '' + gr.nic;
    gr = new GlideRecord('cmdb_ci_network_adapter');
    if (!gr.get(nic_id))
        return null;

    var computer_id = '' + gr.cmdb_ci;
    gr = new GlideRecord('cmdb_ci_computer');
    if (!gr.get(computer_id))
        return null;

    var kind = '' + gr.sys_class_name;
    if (kind == 'cmdb_ci_computer')
        return '' + gr.name;

    gr = new GlideRecord(kind);
    if (!gr.get(computer_id))
        return null;

    return '' + gr.host_name;
}

Voila! This produces exactly the results that our antique friend wanted. Why? Well, in the case of our example, the computer in question was a Linux server. By using a query against cmdb_ci_linux_server, our GlideRecord joined five tables together to give us the results:

cmdb_ci â‰¡ cmdb_ci_hardware â‰¡ cmdb_ci_computer â‰¡ cmdb_ci_server â‰¡ cmdb_ci_linux_server

The first three tables are the same as before, but the last two hold the key to the mystery: the host_name column is part of the cmdb_ci_server table. By querying (in this case) the Linux server table, the cmdb_ci_server table is automatically included in the query.

How can you tell what attributes are in what table? Very easily! Just navigate to a list or form with examples of the table you're wondering about, right click on the bar, and select Personalize â†’ Dictionary. You'll get a list of all the columns available to that table, including all those columns that are joined together when you query that table through GlideRecord. The Table column tells you which table any particular column is in.

Now we just need to find the fountain of youth for our old goat...

Re: Why Doesn't This Work? Or, A Puzzle Explained...

Sat, 29 Oct 2011 20:24:23 GMT

How about getting from a computer name back down to the (somehow) associated record in cmdb_ci named "eth0"?

Let's say I'm looking for the eth0 record for a computer named X. What table fields need to be walked to connect the dots?

Have looked at the Personalize -> Dictionary page of the Network Adapters tab of a CI in the linux servers class, but somehow still missing the connection.

Thank you.

Re: Why Doesn't This Work? Or, A Puzzle Explained...

SlightlyLoony — Sat, 29 Oct 2011 21:01:30 GMT

If you look at the dictionary, as you described, you'll see that there is a column named "cmdb_ci" that is a reference to a record in the cmdb_ci table. That's the key: each network adapter that belongs to a particular computer has a reference to that computer in that field. Here's some code to demonstrate. The assumption here is that we give our function the computer name and the network adapter name, and it will return either the GlideRecord with the network adapter in it, or null if it couldn't find either the computer or the network adapter. Here you go:

function getNIC(computer, nic) {
var gr = new GlideRecord('cmdb_ci_computer');
gr.addQuery('name', computer);
gr.query();
if (!gr.next())
return null;

var nic_gr = new GlideRecord('cmdb_ci_network_adapter');
nic_gr.addQuery('name', nic);
nic_gr.addQuery('cmdb_ci', gr.sys_id); // <-- the important bit!
nic_gr.query();
return nic_gr.next() ? nic_gr : null;
}

Re: Why Doesn't This Work? Or, A Puzzle Explained...

jesjuar — Tue, 22 Oct 2013 07:54:45 GMT

Instead of 're-get' the CI, you can just use the "ref_" especial prefix to get fields from any sub-table:


...
if (kind == 'cmdb_ci_computer')
        return '' + gr.name;

return '' + gr.ref_cmdb_ci_server.host_name;

😉

post Why Doesn't This Work? Or, A Puzzle Explained... in In other news

Why Doesn't This Work? Or, A Puzzle Explained...

Re: Why Doesn't This Work? Or, A Puzzle Explained...

Re: Why Doesn't This Work? Or, A Puzzle Explained...

Re: Why Doesn't This Work? Or, A Puzzle Explained...