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...
3 Comments
