Database view of catalog items where related SCTASK there's no SLA attached

Carol2
Tera Contributor

Hi All 

 

I created a database view using the task_sla and sc_task table. Both tables have the fields that I want to report on. I am stuck on joining the tables together to get the information I am looking for. Where the related SCTASK must give me the name of the catalog item and report on whether the task SLA field is populated or not. Please help i am new to creating database views 

 

Carol2_0-1734442214128.png

 

 

Carol2_2-1734442279196.png

 

 

Carol2_1-1734442505984.png

 

 

 

24 REPLIES 24

@Carol2 

did you add logs and see what came in that while loop?

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

@Ankur Bawiskar 

yes i did, and still nothing 

 

function getRecords() {
    var catItemArr = [];
    var taskGR = new GlideRecord('sc_task');
    taskGR.query();
    while (taskGR.next()) {
        var slaGR = new GlideRecord('task_sla');
        slaGR.addQuery('task', taskGR.sys_id);
        slaGR.query();
        if (!slaGR.hasNext()) {
            var catItemSysId = taskGR.request_item.cat_item.sys_id.toString();
            catItemArr.push(catItemSysId);
            gs.log('Catalog Item sys_id: ' + catItemSysId);
        } else {
            gs.log('SLA record found for task: ' + taskGR.sys_id);
        }
    }
    return catItemArr.toString();
}

 

 

@Carol2 

try running in background script

use gs.info() instead of gs.log()

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

Carol2
Tera Contributor

@Ankur Bawiskar 

Still nothing gs.info() doesn't give me any records

 

so, I created a database view eventually, please check where clause option. This option gives me all the records including incidents and the sla is attached. How do I filter this further to give me records which are catalog task with no SLA attached. 

 

Carol2_0-1734536177490.png

 

 

 

@Carol2 

add this in where clause

sla.task != item.sys_id && sla.task.sys_class_name == 'sc_task'

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader