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

[Carol2]

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]

@Ankur Bawiskar 

I've actually realised that the Task SLA table only has records with sla attached, no wonder I am not getting any records when I run the code.

The TASK SLA table won't work for this, so the question is where are all these records with no sla saved?

[Ankur Bawiskar]

@Carol2 

no other table

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

[Carol2]

@Ankur Bawiskar 

I am lost, which table? 

[Ankur Bawiskar]

@Carol2 

ideally the way how script include is created should help you

try this which is the other way round

function getRecords() {
    var catItemArr = [];
    // Initialize GlideRecord for Catalog Task (sc_task)
    var taskGR = new GlideRecord('sc_task');
    taskGR.query();
    while (taskGR.next()) {
        // Initialize GlideRecord for Task SLA (task_sla)
        var slaGR = new GlideRecord('task_sla');
        slaGR.addQuery('task', taskGR.sys_id);
        slaGR.query();
        // Check if there are no SLA records for the task
        if (slaGR.hasNext()) {
            catItemArr.push(taskGR.request_item.cat_item.sys_id.toString());
        }
    }
    var finalArr = [];
    var itemRec = new GlideRecord('sc_cat_item');
    itemRec.addQuery('sys_id', 'NOT IN', catItemArr.toString());
    itemRec.query();
    while (itemRec.next()) {
        finalArr.push(itemRec.getUniqueValue());
    }

    return finalArr.toString();
}

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

[Carol2]

@Ankur Bawiskar 

So nothing really worked for this issue. I decided to create a table and it works the correct records without sla are populating. Looking at my script include below. the state field is empty and not populating. I've checked the logs and the code run as if its text it doesn't return the task state. Check the script below what did i miss. 

var CatalogTasksWithoutSLA = Class.create();

CatalogTasksWithoutSLA.prototype = {

    initialize: function() {

    },

    getTasksWithoutSLA: function() {

        var tasksWithoutSLA = [];

        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();

                var catItemName = taskGR.request_item.cat_item.name.toString();

                var taskState = taskGR.state.toString();

                tasksWithoutSLA.push(catItemSysId);

                gs.log('Catalog Task without SLA: ' + catItemSysId);

                // Insert record into custom table

                var customGR = new GlideRecord('u_catalog_tasks_without_sla');

                customGR.initialize();

                customGR.u_task_sys_id = taskGR.sys_id;

                customGR.u_task_number = taskGR.number;

                customGR.u_task_short_description = taskGR.short_description;

                customGR.u_cat_item_sys_id = catItemSysId;

                customGR.u_cat_item_name = catItemName;

                customGR.u_task_state = taskGR.taskState;

                customGR.insert();

            }

        }

        return tasksWithoutSLA;

    },

    type: 'CatalogTasksWithoutSLA'

};