Gauge Data Visualization UI Builder loads too slow

[Hari1]

Hi,

I have added the Gauge Data Visualization component in UI builder which is taking long time to load (25~30 Seconds) when i preview it. 

Below is the customization that i have done in UI Builder

/**
  * @Param {params} params
  * @Param {api} params.api
  * @Param {TransformApiHelpers} params.helpers
  */
function evaluateProperty({api, helpers}) {
	var data = [{
        "data": [{
            "value": api.data.cmdb_ci_governance_server_1.output.Coverage[0].Percentage,
            "change": "0",
            "changePercent": "0"
        }],
        "metadata": {
            "eventData": {
                "indicatorSysid": ""
            },
            "dataSourceLabel": "",
            "filterQuery": "",
            "aggregate": {
                "fieldType": "decimal"
            },
            "format": {
                "unitFormat": "{0}",
                "frequency": "daily",
                "precision": 0
            }
        }
        
    }];
    return data;
}

Data Resource - Transform:

[
{
    "name": "ciType",
    "label": "CI Type",
    "description": "Type of the CI",
    "readOnly": "false",
    "fieldType": "string",
    "mandatory": true,
    "defaultValue": ""
},
{
    "name": "usedByOrg",
    "label": "Used By Org",
    "description": "Used By Org",
    "readOnly": "false",
    "fieldType": "string",
    "mandatory": true,
    "defaultValue": ""
}
]

Script:

function transform(input) {

	var start = new Date().getTime();

    var usedByOrgVal = input.usedByOrg;
    var arrFinCount = [];

    if (usedByOrgVal == "All") {
        arrFinCount = [filterList("operational_status!=6"), filterList("operational_status!=6^owned_by!=null"), filterList("operational_status!=6^u_in_coverage=Yes"), filterList("operational_status!=6^u_complete=Yes"), filterList("operational_status!=6^u_correct=Yes", "operational_status!=6^u_in_coverage=No"), filterList("operational_status!=6^u_complete=No"), filterList("operational_status!=6^u_correct=No"), filterList("nameISNOTEMPTY^operational_status!=6^u_resp_orgISNOTEMPTY^NQnameISNOTEMPTY^operational_status!=6^u_resp_orgISEMPTY^assetISEMPTY")];
    } else {
        arrFinCount = [filterList("operational_status!=6^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^owned_by!=null^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_in_coverage=Yes^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_complete=Yes^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_correct=Yes^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_in_coverage=No^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_complete=No^owned_by.u_program=" + usedByOrgVal), filterList("operational_status!=6^u_correct=No^owned_by.u_program=" + usedByOrgVal), filterList("nameISNOTEMPTY^operational_status!=6^u_resp_orgISNOTEMPTY^NQnameISNOTEMPTY^operational_status!=6^u_resp_orgISEMPTY^assetISEMPTY^owned_by.u_program=" + usedByOrgVal)];
    }

    function filterList(arrVal) {
        var cal = new GlideRecord("cmdb_ci_server");
        cal.addEncodedQuery(arrVal);
        cal.query();
        return cal.getRowCount();
    }

    var obj = {
        Ownership: [{
            Value: arrFinCount[1],
            Percentage: isNaN(((arrFinCount[1] / arrFinCount[0]) * 100).toFixed(2)) ? "0.00" : ((arrFinCount[1] / arrFinCount[0]) * 100).toFixed(2),
        }],
        Coverage: [{
            InCoverage: arrFinCount[2],
            OutOfCoverage: arrFinCount[5],
            Percentage: isNaN(((arrFinCount[2] / arrFinCount[0]) * 100).toFixed(2)) ? "0.00" : ((arrFinCount[2] / arrFinCount[0]) * 100).toFixed(2),
        }],
        Completeness: [{
            Complete: arrFinCount[3],
            NotComplete: arrFinCount[6],
            Percentage: isNaN(((arrFinCount[3] / arrFinCount[0]) * 100).toFixed(2)) ? "0.00" : ((arrFinCount[3] / arrFinCount[0]) * 100).toFixed(2),
        }],
        Correctness: [{
            Correct: arrFinCount[4],
            NotCorrect: arrFinCount[7],
            Percentage: isNaN(((arrFinCount[4] / arrFinCount[0]) * 100).toFixed(2)) ? "0.00" : ((arrFinCount[4] / arrFinCount[0]) * 100).toFixed(2),
        }],
        ActiveServerCount: [{
            Count: arrFinCount[8]
        }],
    };

    var end = new Date().getTime();
    var time = end - start;

    gs.info('CMDB: Execution time Server: ' + time);

    return obj;
}

[Hari1]

1. We cannot leave it as it is because the leadership teams do not want it to be so slow.

2. How do i perform a big query. I cannot use the GlideRecord in the client script. Any suggestions on that would be helpful

3. How do i trigger a schedule job from the UI Builder? I need more details on how can we achieve this step 3 functionality

Can you please help!

[IronPotato · Accepted solution]

1. create new field on table of type JSON

2. read how to create scheduled job (https://www.servicenow.com/docs/bundle/xanadu-platform-administration/page/administer/reference-page...)

3. store the response object from the script into that JSON field within scheduled job

4. on page load query that field, parse the response and use it in your compoentns

[Hari1]

I have created a scheduled job and a custom table to store the JSON value. But i am not able to fine the data type JSON on the table field data type. Can you help me on how to create a field to store the JSON data?

NOTE: The JSON data is quite huge.

Some of the references i lookded at over the community but nothing helped.

https://www.servicenow.com/community/developer-forum/how-to-create-a-table-with-a-json-field-in-serv...

[IronPotato]

make it type string with 4000 max length. Don't forget to run both if and else queries and store the result into that field.

[Hari1]

Thank you for the response, I am able to get this working, I was also looking at a approach were in i wanted to pass the object value to the system property instead of the custom table field. I am able to pass the value to the system property but when i access the system property in UI Builder i am able to see the output but with a backslash "\" in each object array value.
Below is the system property:

Below is the accessed system property in UI Builder:

Sorry i am asking for too much