Reference Qualifier using gs.sql OR a faster GlideRecord

[salvadormarchan]

Hi SNC Community,

I have a uncommon scenario. I need to create a reference qualifier from an existing Script Include function in Incident table's Configuration Item field. Here the background story:

• There is an existing Override Reference Qualifier to a Script Include Function ,which does a query via GlideRecord to cmdb_rel_group table. When there is no group involved, it returns all possible selection from the cmdb_ci table.
• In a list view of cmdb_ci table, I slected and copied the query: "sys_class_name!=cmdb_ci_service^operational_statusNOT IN0,2,3,4^NQsys_class_name!=service_offering^operational_statusNOT IN0,2,3,4"
• My first thought was to create a GlideRecord query when there is no group involved. the returned query is correct BUT it took more than 3 minutes to show. This becomes a user-experience issue.
• My second thought was execute a gs.sql query BUT is was not recognizing the returned values. It was undefined.

Question:

Is there a way to return the sys_id via gs.sql() and passing them to an array so that it can be used in the reference qualifier? OR is there a way to query faster via GlideRecord?

Here's my gs.sql script and GlideRecord script:

          var sqlString = "SELECT sys_id " +

          "FROM cmdb_ci " +

          "WHERE sys_class_name != 'cmdb_ci_service'   AND operational_status NOT IN (0,2,3,4) OR " +

          "sys_class_name != 'service_offering' AND operational_status NOT IN (0,2,3,4);";

          gs.sql(sqlString);

//------------------------------------------------------------------------------------------------------------------------

configitems = [];

    var qryString = "sys_class_name!=cmdb_ci_service^operational_statusNOT IN0,2,3,4^NQsys_class_name!=service_offering^operational_statusNOT IN0,2,3,4";

    var gr = new GlideRecord('cmdb_ci');

    gr.addEncodedQuery(qryString);

    gr.query();

    while(gr.next()){

          if(configitems.length ==0) {

        configitems = gr.sys_id;

          } else {

        configitems += (',' + gr.sys_id);

  }

    }

    return 'sys_idIN' + configitems;

I appreciate anyone's input, tips or leads.

Thanks,

Dor

[BenPhillipsSNC]

Hi Salvador

I just ran the query from the list view on my instance

/cmdb_ci_list.do?sysparm_query=sys_class_name!=cmdb_ci_service^operational_statusNOT%20IN0,2,3,4^NQsys_class_name!=service_offering^operational_statusNOT%20IN0,2,3,4

And it took less than 1 second. I am not sure you want to look into the advanced questions you have before you first look into why that query is slow.

- How many CI's do you have?

- Exactly how long is the query taking when you run it from the list view directly? ( /cmdb_ci_list.do?sysparm_query=sys_class_name!=cmdb_ci_service^operational_statusNOT%20IN0,2,3,4^NQsys_class_name!=service_offering^operational_statusNOT%20IN0,2,3,4 )

Thanks

[salvadormarchan · Accepted solution]

Hi Ben,

I used the reverse in logic and it works better now:

//------------------------------------------------------------------------

          var qryString = "sys_class_name=cmdb_ci_service^operational_status!=1^NQsys_class_name=service_offering^operational_status!=1";

    var gr = new GlideRecord('cmdb_ci');

    gr.addEncodedQuery(qryString);

    gr.query();

    while(gr.next()){

        configitems.push(gr.sys_id.toString());

    }

    return 'sys_idNOT IN' + configitems;

//------------------------------------------------------------------------

I'm not sure exactly why but I'll take it.

Thanks,

Dor