Query a record (CI) where sysid of CI is not in table

[User310379]

Let me try to explain.

I am using an Orchestrator Query activity which takes a table name, encoded query and a Record Limit.

The table I want to query has configuration items that are linked to the Affected CI's in a Change Record (or Change Task) but one of the properties (or columns - not sure of the term) is headed Configuration Item but just shows me the Name of the CI.

I need to pull back the actual CI.  One issue is that we have multiple CI's with the same name.  They are different classes of objects, so I have to do a search at the moment for the name, but that could bring back multiple CI's.  I just want to pull back the actual CI that is affected (and in this task_ci table).

From what I have read, I think this may be some kind of glide and the "Configuration Item" column may be the record, but just displaying the name.

Is there any way, within the encoded query parameter that I can get at the sysid of the actual CI from the column Configuration Item?

[pavani_paluri · Accepted solution]

Hi @User310379 ,

In your Orchestrator Query activity, target the task_ci table.
Use the encoded query to filter by the Change Request or Change Task you’re working with.

When you pull back records, include the ci_item (or configuration_item) field.
Even though it shows the Name in the UI, the actual value returned is the sys_id of the CI.
You can then use that sys_id to query the cmdb_ci table directly and get the exact CI record.

“Configuration Item” column is a reference field. It stores the CI’s sys_id, not just the name. You’ll need to query the task_ci table, grab the sys_id from the reference field, and then use that sys_id to pull the actual CI from cmdb_ci.

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for

Kind Regards,

Pavani P

[User310379]

Thank you Pavani for the reply.

The main issue I have though is HOW do I get at the sysId of the CI when the value being returned to me is the Name.  I am able to query the task_ci table with the query targeting the Task Number and I do get the affected CIs for that task.  So all that is good.

The issue is that when I view the output of the runbook in Runbook Tester, what I see in the results of Configuration Item is the Name.  If I then tag on a Get Records to this and link it to the Configuration Item, if I say where SysId equals this, I get no results.  If I say where Name equals this, I get results but I am getting a list of results matching the name for which there could be several.  Granted that this activity is not a query but a Get Records, so it only asks for the table and then I have a filter where I can choose from a drop down of properties in the table I select and then what I want to search for.  This would be a dynamic link back to "Configuration Item" from the previous activity, which is outputting the Name (or what it would seem).  I don't think with Orchestrator dynamic values I can dot walk to get at the sysId.

I could instead use another "Run Query" activity instead but then how in the encoded query do I tell it to use the SysId, rather than just selecting "Configuration Item".  Again I am not sure I can dot walk with a dynamic variable here.  But I will keep trying different formats to see if I can get at it

I just wish they stored the Id's in the link tables like other SQL.

Most examples I see for "encoded queries" seem to be full scripts but all I enter is a query command such as taskLIKECTASK0071446.  If I was pulling back that task Number from a previous activity (i.e subscribing to Published Data), I can only select the field from the activity, I can't see to tag anything on to specify the sysId of Configuration Item.

I will try to upload some pictures if I can

[User310379]

Hi, I had previously replied but I can't see it.  Maybe it takes a few hours to be moderated.

However, I wanted to say I have managed to get back the sysid now in the results.  In the "Run Query" activity, there is an optional parameter called Display Value.  This seems to default to True and seems to be why I see the Name when the results are changed.

I added in this optional parameter and set it to false and now the Configuration Item returns the sysid.

Now, when I add my next activity of "Get Record" with the table specified as cmdb_ci and filter to SysId Equals {link to previuous activity Configuration Item}, I do indeed just get back the actual CI.

Thank you for your help.

Andrew