Query cmdb_rel_ci table and get parent records which contains terminated on field

[rambo1]

Hi,

I am query cmdb_rel_ci table, with filter conditions as below:

child.class = aws_datacenter or azuredatacenter

I also want to add one more condition :

child.terminated_on is in last 24 hours.

Problem is, terminated on field is not there on cmdb_ci table, it is there only on few tables and I want only those record which have that field.

How do I write query?

I am trying some script as below :

var query = 'child.sys_class_name=cmdb_ci_aws_datacenter^ORchild.sys_class_name=cmdb_ci_azure_datacenter';
var gr = new GlideRecord('cmdb_rel_ci');
gr.addEncodedQuery(query);
gr.query();
while(gr.next())
{
var cs = new GlideRecord('cmdb_ci_vm_object');
cs.addQuery('sys_id',gr.parent.sys_id);
cs.query();
while(cs.next())
{
if(cs.terminated_on.isValid() == true)
{
gs.print(cs.name);
}
}
}

getting the below result:

ompacting large row block (file.write: cmdb_rel_ci 10000 rows 160000 saveSize)

Any suggestions?

[rambo1]

any suggestions Ankur?

[Muralidharan BS]

HI Rambo,

you can use that in script aswell, once you have created the query, you can run the script on that specific table like below,

var gr = new GlideRecord('u_cmdb_qb_result_49ddb993c44d86e8779f961f00381569067902a4'); // name of the query table you can find in url
gr.query();
while(gr.next()){
gs.info(gr.u_application_1); // name of the applicaiton field
}

the beauty is you can dot walk to the application fields. 

Kindly mark my response correct and helpful if my suggestion helped you,

Thanks

Murali

[rambo1]

Hi Murali

Thanks for your response, but I couldnt understand on how to use the script for my problem ...it will be helpful if you can explain a little more . Thanks in advance.

[Muralidharan BS]

Hi Rambo,

You can try this,

1. Go to cmdb query and below class, 

2. click on the link and setup something like below 

3. click each class and apply filter like (terminated in last 24 hours etc)

4. Click run, you will see the result matching condition. 

5. copy the table name from url - https://inst.service-now.com/u_cmdb_qb_result_0e1871693950ff885f792f90f1bac46bd7b7afa4_list.do?

6. use in script - 

var gr = new GlideRecord('u_cmdb_qb_result_49ddb993c44d86e8779f961f00381569067902a4'); // name of the query table you can find in url
gr.query();
while(gr.next()){
gs.info(gr.'field name'); // name of the applicaiton field
}

Kindly mark my response correct and helpful if my suggestion helped you,

Thanks

Murali

[rambo1]

Sorry to bother, I am new to this query builder ...I dragged both classes to center but how do i add relationship?