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?

[Muralidharan BS]

Hi Rambo,

You can use CMDB query to pull those info instead of trying in script, 

Go to CMDB Query and use these two connections, you can add more if needed. click those funnel icon on the class to filter it like - terminated on - true etc. This will fetch you the result required. You can also use this query as report and add to dashboard. 

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

Thanks

Murali

[rambo1]

Hi I need to use the results in one of my script.. could you please help me with the script?

[Ankur Bawiskar]

Hi,

So are you saying you want the name of the child table of cmdb_ci which has that field?

Regards
Ankur

[rambo1]

I want to query cmdb rel table with child class as aws datacenter or azure datacenter and parents which have terminated on field and terminated on value should be 24 hours ago from now.

But when I try to dot walk to parent;s fields I am unable to get terminated on field as it is available only on few tables.

Can we use script instead of encoded query? Isvalidfield api?

Please suggest.