Database view for multiple levels of CMDB

Go to solution
Andrew Linenber
Kilo Expert

‎10-22-2018 05:52 PM

I have a requirement from a member of our team to provide the following:

 A list of all servers that includes a number of fields on the cmdb_ci_server form, along with information pulled from the upstream relationships from a custom table we have, u_cmdb_ci_sub_service.

Essentially, they want to see a bunch of information from the server, along with the support information for the support information on the u_cmdb_ci_sub_service record.

I know this will take a database join, but I'm having a heck of a time with it, figuring in how to get the sub service information to populate.

 

At the moment, I have two view tables on my database view:

find_real_file.png

Those two entries give me a list of servers where I can pull up all of the information requested.  I see a column of "Parent" which is the entry on the u_cmdb_ci_sub_service table I need the information from, but I am not finding a way to add that table correctly to my database view.

Can anyone assist? I've made myself go round in circles looking at this today so hoping a fresh set of eyes will yield better results.

Thank you!

Labels:
Preview file
20 KB
0 Helpfuls
  • 1,724 Views
1 ACCEPTED SOLUTION

Go to solution
Josh Cooper
ServiceNow Employee
ServiceNow Employee

‎10-22-2018 08:11 PM

I haven't really tried this, so I can't guarantee what you'll get... make sure you do it in a dev environment, etc, to make sure it works as expected and all the normal disclaimers

So usually, the cmdb_rel_ci table is a special kind of many-to-many table that lets you link CIs together, so as long as your custom table is set up to use that cmdb_rel_ci in that way, then you can try the suggestion below.  If there's a direct reference from cmdb_ci_server to the new u_cmdb_ci_sub_service, using that parent field you mentioned, then you should just be able to dot-walk to get the fields you need, but it doesn't sound like that's the case.

 

So.

 

Try:

Click on the New table button there, and u_cmdb_ci_sub_service is the service, make the prefix whatever, I'm going to use 'sub'

Then in the Where clause field "sub_sys_id = rel_parent" (if the server is the child, then the sub has to be the parent)

Also, note that it's an underscore not a '.'  the '.' is just for getting a handle on those fields as a scripting object once you hold the record in javascript (kinda like JSON, the glideRecord returns a record object, and all the fields are parameters of that record object), so it works perfectly for scripts, but this is like a direct database query and the dot won't work here.  It feels weird adding the underscore prefix that way, but that's the way the database wants it. 🙂

 

Hopefully that gets you going.  I have a reservation about it because both of the tables are extended from cmdb_ci . . . but I THINK it will be okay.  My concern is that you would somehow end up in an infinite loop situation, but I think we're good here. 

 

Oh, and the order will be important, I'd set the server to 100, relationship to 200, and sub to 300... you have to make sure the relationship is loaded before the sub or it won't know how to join, and if the order is all the same it may or may not work, you could get inconsistent results.  

View solution in original post

1 REPLY 1

Go to solution
Josh Cooper
ServiceNow Employee
ServiceNow Employee

‎10-22-2018 08:11 PM

I haven't really tried this, so I can't guarantee what you'll get... make sure you do it in a dev environment, etc, to make sure it works as expected and all the normal disclaimers

So usually, the cmdb_rel_ci table is a special kind of many-to-many table that lets you link CIs together, so as long as your custom table is set up to use that cmdb_rel_ci in that way, then you can try the suggestion below.  If there's a direct reference from cmdb_ci_server to the new u_cmdb_ci_sub_service, using that parent field you mentioned, then you should just be able to dot-walk to get the fields you need, but it doesn't sound like that's the case.

 

So.

 

Try:

Click on the New table button there, and u_cmdb_ci_sub_service is the service, make the prefix whatever, I'm going to use 'sub'

Then in the Where clause field "sub_sys_id = rel_parent" (if the server is the child, then the sub has to be the parent)

Also, note that it's an underscore not a '.'  the '.' is just for getting a handle on those fields as a scripting object once you hold the record in javascript (kinda like JSON, the glideRecord returns a record object, and all the fields are parameters of that record object), so it works perfectly for scripts, but this is like a direct database query and the dot won't work here.  It feels weird adding the underscore prefix that way, but that's the way the database wants it. 🙂

 

Hopefully that gets you going.  I have a reservation about it because both of the tables are extended from cmdb_ci . . . but I THINK it will be okay.  My concern is that you would somehow end up in an infinite loop situation, but I think we're good here. 

 

Oh, and the order will be important, I'd set the server to 100, relationship to 200, and sub to 300... you have to make sure the relationship is loaded before the sub or it won't know how to join, and if the order is all the same it may or may not work, you could get inconsistent results.