Good Morning Community Members!
I am having an issue parsing a JSON result and import the records into an import table. Using Postman my GET query executes and returns the following result.
{
"totalSize": 3,
"size": 3,
"responseCode": "200.00.1000",
"nextRecordsUrl": null,
"messageHeader": {
"transactionSequenceId": "TRANS-SFDC-0001",
"transactionDateTime": "2023-01-18T11:42:00Z",
"transactionCode": "SFDC_QUERY_API",
"sourceSystem": "CES"
},
"isSuccess": true,
"interfaceLiteral": "GET_CASES_FOR_CES",
"done": true,
"data": [
{
"recordInfo": "{\"questionAnswers\":\"\",\"type\":\"Enterprise Case\",\"contractualETA\":null,\"arrivalTime\":null,\"lastActivityType\":\"Initial Troubleshooting\",\"oldSiteId\":null,\"reasonForChange\":null,\"actualIssue\":\"Under Investigation\",\"parentCaseNumber\":null,\"escalationReason\":null,\"timeInCurrentStatus\":\"0d 0h 0m\",\"oldVADBNumber\":null,\"nextUpdateETA\":\"2022-12-03T04:53:00Z\",\"createdDate\":\"2022-12-02T22:58:18Z\",\"activityDescription\":\"Custumer is mentioning the primary service is intermittent and customer just noticed back up is also Offnine.\",\"startTime\":null,\"item\":null,\"serialNumber\":null,\"owner\":\"ESC Frontline\",\"caseRequestSubCategory\":\"Service Out/Intermittent\",\"customerLocationId\":\"07534\",\"newVADBNumber\":null,\"eventType\":\"Service Out/Intermittent\",\"customerTicketNumber\":null,\"serviceImpacted\":null,\"impactedProductSerialNumber\":null,\"newPortAddress\":null,\"billOfLading\":null,\"departureTime\":null,\"siteIdDeviceId\":null,\"lastModifiedDate\":\"2022-12-02T22:58:22Z\",\"oldPortAddress\":null,\"oldLanIPAddress\":null,\"childCompanyId\":null,\"nextSteps\":null,\"caseRequestCategory\":\"Technical\",\"newSiteId\":null,\"caseNumber\":\"128459011\",\"impactedProduct\":\"HR4860W\",\"caseDescription\":\"Working with custumer at location\",\"lastModifiedBy\":\"Tag Train2\",\"action\":\"Actively Investigating\",\"activitiesAffected\":null,\"newLanIPAddress\":null,\"quantity\":null,\"endTime\":null,\"parentCompanyId\":\"MUR\",\"status\":\"In Progress\"}",
"uniqueCRMId": "a6m54000001IrvLAAS",
"objectType": "Case"
},
{
"recordInfo": "{\"questionAnswers\":\"\",\"type\":\"Enterprise Case\",\"contractualETA\":null,\"arrivalTime\":null,\"lastActivityType\":\"Initial Troubleshooting\",\"oldSiteId\":null,\"reasonForChange\":null,\"actualIssue\":\"Under Investigation\",\"parentCaseNumber\":null,\"escalationReason\":null,\"timeInCurrentStatus\":\"0d 0h 5m\",\"oldVADBNumber\":null,\"nextUpdateETA\":\"2022-12-03T04:53:00Z\",\"createdDate\":\"2022-12-02T22:58:18Z\",\"activityDescription\":\"After T1 troubleshooting with customer, craddle point from NAP still has connectivity issues. Contacting NAP (verizon) to dispatch technichian.\",\"startTime\":null,\"item\":null,\"serialNumber\":null,\"owner\":\"ESC Frontline\",\"caseRequestSubCategory\":\"Service Out/Intermittent\",\"customerLocationId\":\"07534\",\"newVADBNumber\":null,\"eventType\":\"Service Out/Intermittent\",\"customerTicketNumber\":null,\"serviceImpacted\":null,\"impactedProductSerialNumber\":null,\"newPortAddress\":null,\"billOfLading\":null,\"departureTime\":null,\"siteIdDeviceId\":null,\"lastModifiedDate\":\"2022-12-02T23:03:30Z\",\"oldPortAddress\":null,\"oldLanIPAddress\":null,\"childCompanyId\":null,\"nextSteps\":\"NAP Truck Roll\",\"caseRequestCategory\":\"Technical\",\"newSiteId\":null,\"caseNumber\":\"128459011\",\"impactedProduct\":\"Standard Wireless\",\"caseDescription\":\"Working with custumer at location\",\"lastModifiedBy\":\"Tag Train2\",\"action\":\"Actively Investigating\",\"activitiesAffected\":null,\"newLanIPAddress\":null,\"quantity\":null,\"endTime\":null,\"parentCompanyId\":\"MUR\",\"status\":\"In Progress\"}",
"uniqueCRMId": "a6m54000001It5kAAC",
"objectType": "Case"
},
{
"recordInfo": "{\"questionAnswers\":\"\",\"type\":\"Enterprise Case\",\"contractualETA\":null,\"arrivalTime\":null,\"lastActivityType\":\"Initial Troubleshooting\",\"oldSiteId\":null,\"reasonForChange\":null,\"actualIssue\":\"Under Investigation\",\"parentCaseNumber\":null,\"escalationReason\":null,\"timeInCurrentStatus\":\"0d 0h 0m\",\"oldVADBNumber\":null,\"nextUpdateETA\":\"2022-12-20T19:00:00Z\",\"createdDate\":\"2022-12-19T17:23:14Z\",\"activityDescription\":\"FRONTLINE INTERACTION\\n• Inbound Task Type: Web Case\\n• Original Request: Site Hard Down\\n\\nOverview:\\n• Background Info: Received Ticket 150340890 today 12/19/2022 for Monitoring SIte, it has been intermittent trough the day.\\n• Steps Taken: Run HNS Tools, confirmed that the issue is with the Primary Transport (Cable), called the NAP, NAP Confirmed Outage, Estimated time of Resolution Wednesday 21st by 3:00Pm.\\n• Next Step: Keep Monitoring Site, For the next representative, call NAP on Wednesday 21st by 3:00pm. to confirm if outage is resolved.\\n\\nTimeline:\\n[11:23 AM] Received Ticket Number 150340890.\\n[11:23 AM] Case Created\\n[11:23 AM] Checking background info.\\nSite keeps going into hard down throughout the day. Tier 1 performed. Please monitor for 48 hours. Dispatch is approved if needed.\\nTier 1 performed\\nCS-HON-Tech-IP\\n[11:24 AM] Running Tools.\\n[11:29 AM] Site is up at the moment, Running Tools.\\n[11:32 AM] Jitter for WIreless is out of threshold range \\n[11:32 AM] Running Tools\\n[11:36 AM] Confirmed that at th emoment site is up and running.\\n[11:36 AM] One of the findings, is that Core 2 is showing critical error.\\n[11:40 AM] Found out that there are issues with the Primary Transport (Cable), Active QoS results show that backup transport took over today.\\n[11:41 AM] Creating NAPOO.\\n[11:44 AM] Calling NAP.\\n[11:48 AM] Waiting for NAP\\n[11:50 AM] Ricardo Milos from Spectrum Answered, he mentions that there's a current outage, having a degradation due to the congestion in the area, the estimated time for resolution is wednesday around 3 pm, they will be working.\\n[11:53 AM] Updating Case Notes\\n[11:57 AM] Case set to Pending Monitoring,.\\n\\n\\nNAP Interaction Information:\\n• Provider: Spectrum\\n• Contact: 877-892-4662\\n• Agent: Ricardo Milos\\n• Key: 8783300012584785\\n• Circuit Health: Packet Loss / Intermittency Confirmed\\n• Resolution: Network Outage Confirmed\\n• Ticket: CR420619\\n• Next Follow up: Tuesday, December 20 at 00:52 AM.\\n• Escalation: No Escalation Required\",\"startTime\":null,\"item\":null,\"serialNumber\":null,\"owner\":\"ESC Frontline\",\"caseRequestSubCategory\":\"Service Out/Intermittent\",\"customerLocationId\":\"7221\",\"newVADBNumber\":null,\"eventType\":\"Service Out/Intermittent\",\"customerTicketNumber\":null,\"serviceImpacted\":null,\"impactedProductSerialNumber\":null,\"newPortAddress\":null,\"billOfLading\":null,\"departureTime\":null,\"siteIdDeviceId\":null,\"lastModifiedDate\":\"2022-12-19T17:58:10Z\",\"oldPortAddress\":null,\"oldLanIPAddress\":null,\"childCompanyId\":null,\"nextSteps\":\"None Required\",\"caseRequestCategory\":\"Technical\",\"newSiteId\":null,\"caseNumber\":\"128479324\",\"impactedProduct\":\"Standard Wireless\",\"caseDescription\":\"Working Case\",\"lastModifiedBy\":\"Tag Train1\",\"action\":\"Actively Investigating\",\"activitiesAffected\":null,\"newLanIPAddress\":null,\"quantity\":null,\"endTime\":null,\"parentCompanyId\":\"MUR\",\"status\":\"Pending - Monitoring\"}",
"uniqueCRMId": "a6m54000001NcF3AAK",
"objectType": "Case"
}
],
"responseMessage": "Request processed successfully"
}
I created a business rule on a custom form as a test to verify the result I am getting when executed from ServiceNow.
var s = new sn_ws.RESTMessageV2('Query Hughes Cases', 'GET Cases');
s.setStringParameter('lastPollDateTime', current.u_lastpolldatetime);
s.setStringParameter('transactionDateTime', current.u_transactiondatetime);
var response = s.execute();
var responseBody = response.getBody();
var status = response.getStatusCode();
//Parse json result
var cases = JSON.parse(responseBody);
for (var i=0; i < cases.data.length; i++){
current.u_steps_to_reproduce += cases.data[i].recordInfo;
}
The BR executes and returns the result successfully. The field is set with the following JSON Result with all the escape characters removed as I expected.
{
"questionAnswers":"","type":"Enterprise Case","contractualETA":null,"arrivalTime":null,"lastActivityType":"Initial Troubleshooting","oldSiteId":null,"reasonForChange":null,"actualIssue":"Under Investigation","parentCaseNumber":null,"escalationReason":null,"timeInCurrentStatus":"0d 0h 0m","oldVADBNumber":null,"nextUpdateETA":"2022-12-19T19:21:00Z","createdDate":"2022-12-19T17:23:14Z","activityDescription":"Working Case","startTime":null,"item":null,"serialNumber":null,"owner":"ESC Frontline","caseRequestSubCategory":"Service Out/Intermittent","customerLocationId":"7221","newVADBNumber":null,"eventType":"Service Out/Intermittent","customerTicketNumber":null,"serviceImpacted":null,"impactedProductSerialNumber":null,"newPortAddress":null,"billOfLading":null,"departureTime":null,"siteIdDeviceId":null,"lastModifiedDate":"2022-12-19T17:23:18Z","oldPortAddress":null,"oldLanIPAddress":null,"childCompanyId":null,"nextSteps":null,"caseRequestCategory":"Technical","newSiteId":null,"caseNumber":"128479324","impactedProduct":"HR4860W","caseDescription":"Working Case","lastModifiedBy":"Tag Train1","action":"Actively Investigating","activitiesAffected":null,"newLanIPAddress":null,"quantity":null,"endTime":null,"parentCompanyId":"MUR","status":"In Progress"}
{"questionAnswers":"","type":"Enterprise Case","contractualETA":null,"arrivalTime":null,"lastActivityType":"Initial Troubleshooting","oldSiteId":null,"reasonForChange":null,"actualIssue":"Under Investigation","parentCaseNumber":null,"escalationReason":null,"timeInCurrentStatus":"0d 0h 19m","oldVADBNumber":null,"nextUpdateETA":"2022-12-19T19:00:00Z","createdDate":"2022-12-19T17:23:14Z","activityDescription":"Calling NAP to check Primary Transport.","startTime":null,"item":null,"serialNumber":null,"owner":"ESC Frontline","caseRequestSubCategory":"Service Out/Intermittent","customerLocationId":"7221","newVADBNumber":null,"eventType":"Service Out/Intermittent","customerTicketNumber":null,"serviceImpacted":null,"impactedProductSerialNumber":null,"newPortAddress":null,"billOfLading":null,"departureTime":null,"siteIdDeviceId":null,"lastModifiedDate":"2022-12-19T17:42:43Z","oldPortAddress":null,"oldLanIPAddress":null,"childCompanyId":null,"nextSteps":"Escalate to NAP Office Only","caseRequestCategory":"Technical","newSiteId":null,"caseNumber":"128479324","impactedProduct":"Standard Wireless","caseDescription":"Working Case","lastModifiedBy":"Tag Train1","action":"Actively Investigating","activitiesAffected":null,"newLanIPAddress":null,"quantity":null,"endTime":null,"parentCompanyId":"MUR","status":"In Progress"}
{"questionAnswers":"","type":"Enterprise Case","contractualETA":null,"arrivalTime":null,"lastActivityType":"Initial Troubleshooting","oldSiteId":null,"reasonForChange":null,"actualIssue":"Under Investigation","parentCaseNumber":null,"escalationReason":null,"timeInCurrentStatus":"0d 0h 0m","oldVADBNumber":null,"nextUpdateETA":"2022-12-20T19:00:00Z","createdDate":"2022-12-19T17:23:14Z","activityDescription":"FRONTLINE INTERACTION\n• Inbound Task Type: Web Case\n• Original Request: Site Hard Down\n\nOverview:\n• Background Info: Received Ticket 150340890 today 12/19/2022 for Monitoring SIte, it has been intermittent trough the day.\n• Steps Taken: Run HNS Tools, confirmed that the issue is with the Primary Transport (Cable), called the NAP, NAP Confirmed Outage, Estimated time of Resolution Wednesday 21st by 3:00Pm.\n• Next Step: Keep Monitoring Site, For the next representative, call NAP on Wednesday 21st by 3:00pm. to confirm if outage is resolved.\n\nTimeline:\n[11:23 AM] Received Ticket Number 150340890.\n[11:23 AM] Case Created\n[11:23 AM] Checking background info.\nSite keeps going into hard down throughout the day. Tier 1 performed. Please monitor for 48 hours. Dispatch is approved if needed.\nTier 1 performed\nCS-HON-Tech-IP\n[11:24 AM] Running Tools.\n[11:29 AM] Site is up at the moment, Running Tools.\n[11:32 AM] Jitter for WIreless is out of threshold range \n[11:32 AM] Running Tools\n[11:36 AM] Confirmed that at th emoment site is up and running.\n[11:36 AM] One of the findings, is that Core 2 is showing critical error.\n[11:40 AM] Found out that there are issues with the Primary Transport (Cable), Active QoS results show that backup transport took over today.\n[11:41 AM] Creating NAPOO.\n[11:44 AM] Calling NAP.\n[11:48 AM] Waiting for NAP\n[11:50 AM] Ricardo Milos from Spectrum Answered, he mentions that there's a current outage, having a degradation due to the congestion in the area, the estimated time for resolution is wednesday around 3 pm, they will be working.\n[11:53 AM] Updating Case Notes\n[11:57 AM] Case set to Pending Monitoring,.\n\n\nNAP Interaction Information:\n• Provider: Spectrum\n• Contact: 877-892-4662\n• Agent: Ricardo Milos\n• Key: 8783300012584785\n• Circuit Health: Packet Loss / Intermittency Confirmed\n• Resolution: Network Outage Confirmed\n• Ticket: CR420619\n• Next Follow up: Tuesday, December 20 at 00:52 AM.\n• Escalation: No Escalation Required","startTime":null,"item":null,"serialNumber":null,"owner":"ESC Frontline","caseRequestSubCategory":"Service Out/Intermittent","customerLocationId":"7221","newVADBNumber":null,"eventType":"Service Out/Intermittent","customerTicketNumber":null,"serviceImpacted":null,"impactedProductSerialNumber":null,"newPortAddress":null,"billOfLading":null,"departureTime":null,"siteIdDeviceId":null,"lastModifiedDate":"2022-12-19T17:58:10Z","oldPortAddress":null,"oldLanIPAddress":null,"childCompanyId":null,"nextSteps":"None Required","caseRequestCategory":"Technical","newSiteId":null,"caseNumber":"128479324","impactedProduct":"Standard Wireless","caseDescription":"Working Case","lastModifiedBy":"Tag Train1","action":"Actively Investigating","activitiesAffected":null,"newLanIPAddress":null,"quantity":null,"endTime":null,"parentCompanyId":"MUR","status":"Pending - Monitoring"
}
What I would like to do is import this data to have incidents created. When I run the script from a scheduled job the GET query runs successfully but it only creates 3 empty records in the import table.
var r = new sn_ws.RESTMessageV2('Query Hughes Cases', 'GET Cases');
r.setStringParameterNoEscape('lastPollDateTime', '2022-12-15T11:42:00Z');
r.setStringParameterNoEscape('transactionDateTime', '2023-01-23T10:02:00Z');
var response = r.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
var crImpSet = new GlideRecord('sys_import_set');
crImpSet.initialize();
crImpSet.mode = 'synchronous';
crImpSet.table_name = 'u_hughes_cases';
crImpSet.state = 'loading';
var ImpSetId = crImpSet.insert();
var cases = JSON.parse(responseBody);
for (var i=0; i < cases.data.length; i++){
var imp = new GlideRecord('u_hughes_cases');
imp.initialize();
imp.u_category = cases.data[i].recordInfo.caseRequestCategory;
imp.u_case_number = cases.data[i].recordInfo.caseNumber;
imp.u_location = cases.data[i].recordInfo.customerLocationId;
imp.sys_import_set = ImpSetId;
imp.insert();
}I have used this approach with a different vendor before with and XML result, but I am struggling with this JSON issue. I am not sure why I get the proper number of records but all the records are empty with no field data set to transform.
I greatly appreciate any assistance this wonderful community can provide.
Thanks,
David Whaley
1 ACCEPTED SOLUTION
Hi,
Are you sure the cases.data[i].recordInfo is also a JSON and not a string?
Can you try: JSON.parse(responseBody.data[0].recordInfo).caseRequestCategory
Regards,
Hayo
Hi,
Are you sure the cases.data[i].recordInfo is also a JSON and not a string?
Can you try: JSON.parse(responseBody.data[0].recordInfo).caseRequestCategory
Regards,
Hayo
Hello Hayo,
Thanks for your response. I have ran the result of cases.data[i].recordInfo through jslint to validate it is correct JSON and it validated. In my original post I tested the response I was getting by setting a field with a business rule with the cases.data[i].recordInfo this is the result I am getting,
I will try your suggestion though and update the thread.
Thanks for your help.
