Parse Nested JSON

Go to solution
Sam121
Tera Expert

‎04-16-2025 06:51 PM

Hello,

 

I am looking for a script to get values of Security Zone attribute from Security Zone Cache node and  CI attribute from Configuration Items node in below sample JSON payload.

 

Based on below sample JSON payload, script should return Security Zone as COR and CI should be dcscapengMID001.

 

 

{"1.3.1.1":{"Configuration Items":{"table_name":"cmdb_ci_hardware","cmdb_ci_hardware-query":"ip_address=10.39.145.142^ORsys_idIN8bafe2e4db072150966c676ed396194c,8bafe2e4db072150966c676ed396194c^install_status!=7","results":[{"CI":"dcscapengMID001","Serial Number":"ee11ff7f-e6ff-49bb-857a-1175883319e8","Reporting Realm":"Core","Status":"Installed","sys_id":"8bafe2e4db072150966c676ed396194c"}]},"Network Adapters":{"table_name":"cmdb_ci_network_adapter","cmdb_ci_network_adapter-query":"ip_address=10.39.145.142^install_status!=7","results":[{"Nic":"Ethernet 1","CI":"dcscapengMID001","Reporting Realm":"Core","CI Status":"Installed","sys_id":"d3afe2e4db072150966c676ed396195e"}]},"IP Addresses":{"table_name":"cmdb_ci_ip_address","cmdb_ci_ip_address-query":"ip_address=10.39.145.142^install_status!=7","results":[{"IP":"10.39.145.142","CI":"dcscapengMID001","Reporting Realm":"Core","CI Status":"Installed","sys_id":"ad842bf71bbc0650f3fa2066b04bcb06"}]},"NCM Load Balancer VIP":{"table_name":"u_ncm_cache_lbc_virtual_ip","u_ncm_cache_lbc_virtual_ip-query":"u_address=10.39.145.142/32","results":[]},"IPAM Cache":{"table_name":"u_ipam_cache","u_ipam_cache-query":"u_starting_ip_int<=1.70365326E8^u_ending_ip_int>=1.70365326E8","results":[{"CIDR":"10.0.0.0/8","Type":"Aggregate","Sub Type":"","Reporting Realm":"","Security Zone":"","sys_id":"93f9167b1bfcee94c5c320e4604bcb98"},{"CIDR":"10.0.0.0/8","Type":"Prefix","Sub Type":"Active","Reporting Realm":"Core","Security Zone":"","sys_id":"7a61d3331b78aa944accec21604bcb20"},{"CIDR":"10.32.0.0/12","Type":"Prefix","Sub Type":"Container","Reporting Realm":"Core","Security Zone":"","sys_id":"ba13d33b1b78aa944accec21604bcbd3"},{"CIDR":"10.38.0.0/15","Type":"Prefix","Sub Type":"Container","Reporting Realm":"Core","Security Zone":"","sys_id":"96835f7b1b78aa944accec21604bcb4f"},{"CIDR":"10.39.128.0/18","Type":"Prefix","Sub Type":"Active","Reporting Realm":"Core","Security Zone":"COR","sys_id":"1293df7b1b78aa944accec21604bcb12"},{"CIDR":"10.39.144.0/22","Type":"Prefix","Sub Type":"Active","Reporting Realm":"Core","Security Zone":"None","sys_id":"a293df7b1b78aa944accec21604bcb15"}]},"Security Zone Cache":{"table_name":"u_subnet_nsz","u_subnet_nsz-query":"u_starting_ip_int<=1.70365326E8^u_ending_ip_int>=1.70365326E8","results":[{"CIDR":"10.39.128.0/18","Security Zone":"COR","sys_id":"7c02c775dbd0d81049217a8eaf9619ed"}]},"Unknown IP From Scan":{"table_name":"u_unknown_ip","u_unknown_ip-query":"u_ip_address=10.39.145.142","results":[{"IP":"10.39.145.142","Found":"Yes","Reporting Realm":"Core","sys_id":"6adab2711b32d294a5db0d03604bcbfb"}]},"Unknown CIDR Reason":{"table_name":"u_unknown_cidr_exclusion","u_unknown_cidr_exclusion-query":"u_starting_ip_int<=1.70365326E8^u_ending_ip_int>=1.70365326E8","results":[]}}}

 

I tried below OOB API to parse these values.But, it's not working for me.

 

https://www.servicenow.com/docs/bundle/xanadu-api-reference/page/app-store/dev_portal/API_reference/...

 

Regards,

Sam

0 Helpfuls
  • 1,251 Views
1 ACCEPTED SOLUTION

Go to solution
J Siva
Tera Sage

‎04-16-2025 08:17 PM

Hi @Sam121 
You can try the below script, if the results contain more than one array element.

var str = '{"1.3.1.1":{"Configuration Items":{"table_name":"cmdb_ci_hardware","cmdb_ci_hardware-query":"ip_address=10.39.145.142^ORsys_idIN8bafe2e4db072150966c676ed396194c,8bafe2e4db072150966c676ed396194c^install_status!=7","results":[{"CI":"dcscapengMID001","Serial Number":"ee11ff7f-e6ff-49bb-857a-1175883319e8","Reporting Realm":"Core","Status":"Installed","sys_id":"8bafe2e4db072150966c676ed396194c"}]},"Network Adapters":{"table_name":"cmdb_ci_network_adapter","cmdb_ci_network_adapter-query":"ip_address=10.39.145.142^install_status!=7","results":[{"Nic":"Ethernet 1","CI":"dcscapengMID001","Reporting Realm":"Core","CI Status":"Installed","sys_id":"d3afe2e4db072150966c676ed396195e"}]},"IP Addresses":{"table_name":"cmdb_ci_ip_address","cmdb_ci_ip_address-query":"ip_address=10.39.145.142^install_status!=7","results":[{"IP":"10.39.145.142","CI":"dcscapengMID001","Reporting Realm":"Core","CI Status":"Installed","sys_id":"ad842bf71bbc0650f3fa2066b04bcb06"}]},"NCM Load Balancer VIP":{"table_name":"u_ncm_cache_lbc_virtual_ip","u_ncm_cache_lbc_virtual_ip-query":"u_address=10.39.145.142/32","results":[]},"IPAM Cache":{"table_name":"u_ipam_cache","u_ipam_cache-query":"u_starting_ip_int<=1.70365326E8^u_ending_ip_int>=1.70365326E8","results":[{"CIDR":"10.0.0.0/8","Type":"Aggregate","Sub Type":"","Reporting Realm":"","Security Zone":"","sys_id":"93f9167b1bfcee94c5c320e4604bcb98"},{"CIDR":"10.0.0.0/8","Type":"Prefix","Sub Type":"Active","Reporting Realm":"Core","Security Zone":"","sys_id":"7a61d3331b78aa944accec21604bcb20"},{"CIDR":"10.32.0.0/12","Type":"Prefix","Sub Type":"Container","Reporting Realm":"Core","Security Zone":"","sys_id":"ba13d33b1b78aa944accec21604bcbd3"},{"CIDR":"10.38.0.0/15","Type":"Prefix","Sub Type":"Container","Reporting Realm":"Core","Security Zone":"","sys_id":"96835f7b1b78aa944accec21604bcb4f"},{"CIDR":"10.39.128.0/18","Type":"Prefix","Sub Type":"Active","Reporting Realm":"Core","Security Zone":"COR","sys_id":"1293df7b1b78aa944accec21604bcb12"},{"CIDR":"10.39.144.0/22","Type":"Prefix","Sub Type":"Active","Reporting Realm":"Core","Security Zone":"None","sys_id":"a293df7b1b78aa944accec21604bcb15"}]},"Security Zone Cache":{"table_name":"u_subnet_nsz","u_subnet_nsz-query":"u_starting_ip_int<=1.70365326E8^u_ending_ip_int>=1.70365326E8","results":[{"CIDR":"10.39.128.0/18","Security Zone":"COR","sys_id":"7c02c775dbd0d81049217a8eaf9619ed"}]},"Unknown IP From Scan":{"table_name":"u_unknown_ip","u_unknown_ip-query":"u_ip_address=10.39.145.142","results":[{"IP":"10.39.145.142","Found":"Yes","Reporting Realm":"Core","sys_id":"6adab2711b32d294a5db0d03604bcbfb"}]},"Unknown CIDR Reason":{"table_name":"u_unknown_cidr_exclusion","u_unknown_cidr_exclusion-query":"u_starting_ip_int<=1.70365326E8^u_ending_ip_int>=1.70365326E8","results":[]}}}';

var obj = JSON.parse(str);
var ci_obj = obj['1.3.1.1']['Configuration Items']['results'];
var ci_arr = [];
for (var i = 0; i < ci_obj.length; i++) {
    ci_arr.push(ci_obj[i]['CI']);
}

var sec_obj = obj['1.3.1.1']['Security Zone Cache']['results'];
var sec_arr = [];
for (var j = 0; j < sec_obj.length; j++) {
    sec_arr.push(sec_obj[j]['Security Zone']);
}

gs.print("CI : " + ci_arr.toString());
gs.print("Security Zone: " + sec_arr.toString());

 

Output:

JSiva_0-1744859813952.png

 

Regards,
Siva

 

View solution in original post

5 REPLIES 5

Go to solution
Ankur Bawiskar
Tera Patron
Tera Patron

‎04-16-2025 08:13 PM

@Sam121 

try this and it worked for me

(function extractValues() {
    var jsonPayload = {
        "1.3.1.1": {
            "Configuration Items": {
                "table_name": "cmdb_ci_hardware",
                "cmdb_ci_hardware-query": "ip_address=10.39.145.142^ORsys_idIN8bafe2e4db072150966c676ed396194c,8bafe2e4db072150966c676ed396194c^install_status!=7",
                "results": [{
                    "CI": "dcscapengMID001",
                    "Serial Number": "ee11ff7f-e6ff-49bb-857a-1175883319e8",
                    "Reporting Realm": "Core",
                    "Status": "Installed",
                    "sys_id": "8bafe2e4db072150966c676ed396194c"
                }]
            },
            "Security Zone Cache": {
                "table_name": "u_subnet_nsz",
                "u_subnet_nsz-query": "u_starting_ip_int<=1.70365326E8^u_ending_ip_int>=1.70365326E8",
                "results": [{
                    "CIDR": "10.39.128.0/18",
                    "Security Zone": "COR",
                    "sys_id": "7c02c775dbd0d81049217a8eaf9619ed"
                }]
            }
        }
    };

    // Extract CI from Configuration Items
    var ci = jsonPayload["1.3.1.1"]["Configuration Items"]["results"][0]["CI"];

    // Extract Security Zone from Security Zone Cache
    var securityZone = jsonPayload["1.3.1.1"]["Security Zone Cache"]["results"][0]["Security Zone"];

    // Output the extracted values
    gs.info("CI: " + ci);
    gs.info("Security Zone: " + securityZone);
})();

Output:

AnkurBawiskar_0-1744859613393.png

 

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

Go to solution
Ankur Bawiskar
Tera Patron
Tera Patron
In response to Ankur Bawiskar

‎04-16-2025 09:29 PM

@Sam121 

Thank you for marking my response as helpful.

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
0 Helpfuls

Go to solution
J Siva
Tera Sage

‎04-16-2025 08:17 PM

Hi @Sam121 
You can try the below script, if the results contain more than one array element.

var str = '{"1.3.1.1":{"Configuration Items":{"table_name":"cmdb_ci_hardware","cmdb_ci_hardware-query":"ip_address=10.39.145.142^ORsys_idIN8bafe2e4db072150966c676ed396194c,8bafe2e4db072150966c676ed396194c^install_status!=7","results":[{"CI":"dcscapengMID001","Serial Number":"ee11ff7f-e6ff-49bb-857a-1175883319e8","Reporting Realm":"Core","Status":"Installed","sys_id":"8bafe2e4db072150966c676ed396194c"}]},"Network Adapters":{"table_name":"cmdb_ci_network_adapter","cmdb_ci_network_adapter-query":"ip_address=10.39.145.142^install_status!=7","results":[{"Nic":"Ethernet 1","CI":"dcscapengMID001","Reporting Realm":"Core","CI Status":"Installed","sys_id":"d3afe2e4db072150966c676ed396195e"}]},"IP Addresses":{"table_name":"cmdb_ci_ip_address","cmdb_ci_ip_address-query":"ip_address=10.39.145.142^install_status!=7","results":[{"IP":"10.39.145.142","CI":"dcscapengMID001","Reporting Realm":"Core","CI Status":"Installed","sys_id":"ad842bf71bbc0650f3fa2066b04bcb06"}]},"NCM Load Balancer VIP":{"table_name":"u_ncm_cache_lbc_virtual_ip","u_ncm_cache_lbc_virtual_ip-query":"u_address=10.39.145.142/32","results":[]},"IPAM Cache":{"table_name":"u_ipam_cache","u_ipam_cache-query":"u_starting_ip_int<=1.70365326E8^u_ending_ip_int>=1.70365326E8","results":[{"CIDR":"10.0.0.0/8","Type":"Aggregate","Sub Type":"","Reporting Realm":"","Security Zone":"","sys_id":"93f9167b1bfcee94c5c320e4604bcb98"},{"CIDR":"10.0.0.0/8","Type":"Prefix","Sub Type":"Active","Reporting Realm":"Core","Security Zone":"","sys_id":"7a61d3331b78aa944accec21604bcb20"},{"CIDR":"10.32.0.0/12","Type":"Prefix","Sub Type":"Container","Reporting Realm":"Core","Security Zone":"","sys_id":"ba13d33b1b78aa944accec21604bcbd3"},{"CIDR":"10.38.0.0/15","Type":"Prefix","Sub Type":"Container","Reporting Realm":"Core","Security Zone":"","sys_id":"96835f7b1b78aa944accec21604bcb4f"},{"CIDR":"10.39.128.0/18","Type":"Prefix","Sub Type":"Active","Reporting Realm":"Core","Security Zone":"COR","sys_id":"1293df7b1b78aa944accec21604bcb12"},{"CIDR":"10.39.144.0/22","Type":"Prefix","Sub Type":"Active","Reporting Realm":"Core","Security Zone":"None","sys_id":"a293df7b1b78aa944accec21604bcb15"}]},"Security Zone Cache":{"table_name":"u_subnet_nsz","u_subnet_nsz-query":"u_starting_ip_int<=1.70365326E8^u_ending_ip_int>=1.70365326E8","results":[{"CIDR":"10.39.128.0/18","Security Zone":"COR","sys_id":"7c02c775dbd0d81049217a8eaf9619ed"}]},"Unknown IP From Scan":{"table_name":"u_unknown_ip","u_unknown_ip-query":"u_ip_address=10.39.145.142","results":[{"IP":"10.39.145.142","Found":"Yes","Reporting Realm":"Core","sys_id":"6adab2711b32d294a5db0d03604bcbfb"}]},"Unknown CIDR Reason":{"table_name":"u_unknown_cidr_exclusion","u_unknown_cidr_exclusion-query":"u_starting_ip_int<=1.70365326E8^u_ending_ip_int>=1.70365326E8","results":[]}}}';

var obj = JSON.parse(str);
var ci_obj = obj['1.3.1.1']['Configuration Items']['results'];
var ci_arr = [];
for (var i = 0; i < ci_obj.length; i++) {
    ci_arr.push(ci_obj[i]['CI']);
}

var sec_obj = obj['1.3.1.1']['Security Zone Cache']['results'];
var sec_arr = [];
for (var j = 0; j < sec_obj.length; j++) {
    sec_arr.push(sec_obj[j]['Security Zone']);
}

gs.print("CI : " + ci_arr.toString());
gs.print("Security Zone: " + sec_arr.toString());

 

Output:

JSiva_0-1744859813952.png

 

Regards,
Siva

 

Go to solution
Sam121
Tera Expert

‎04-16-2025 09:28 PM

Thank You Both for providing this script.

I wish i could mark answers as correct.

 

Regards,

Sam

 

 

0 Helpfuls