Here is the problematic part of the script.
It is the table 'MAP_inc_parent_incident' which doesn't work but the others never have the value by default 'N/A'.
/* Récupérer le nombre total de ligne */
TotalTable:
SQL SELECT
"X-Total-Count" as "TotalCount"
FROM JSON (wrap on) "_response_header" PK "__KEY__response_header"
/*recharge tout*/
WITH CONNECTION (Url "$(SOURCE)/api/now/table/incident_sla?sysparm_display_value=true&sysparm_query=taskslatable_slaSTARTSWITHOLA^inc_state=7^ORinc_state=6^taskslatable_stage!=cancelled&sysparm_fields=inc_number,inc_short_description,inc_cmdb_ci,inc_u_second_configuration_item,inc_priority,inc_opened_by.u_group_creator,inc_opened_at,inc_resolved_by.u_group_creator,inc_resolved_at,taskslatable_sla,taskslatable_stage,taskslatable_duration,assignment_group,taskslatable_business_duration,taskslatable_sla,inc_state,CMDB_CI_Link,__FK_cmdb_ci,__FK_u_second_configuration_item,CI2,inc_category,inc_subcategory,inc_urgency,inc_impact,inc_u_vendor,inc_reassignment_count,inc_assigned_to,inc_opened_by,inc_sys_id,inc_closed_at,inc_assignment_group,inc_u_group_creator,inc_cmdb_ci.model_id.u_nna,inc_cmdb_ci.sys_id,inc_cmdb_ci.name,inc_cmdb_ci.u_criticality,inc_cmdb_ci.u_incidents_service_range,inc_cmdb_ci.u_environment,inc_cmdb_ci.model_id,inc_cmdb_ci.category,inc_sys_updated_on,inc_parent_incident") ;
Let Total = peek('TotalCount', 0, TotalTable);
Drop Table TotalTable;
Let StartAt = 0;
Let PageSize = 500;
Trace "Total Incident SLA : " $(Total);
for StartAt = 0 to Total
Trace "StartAt : " $(StartAt);
RestConnectorMasterTable:
SELECT
"taskslatable_stage",
"taskslatable_business_duration",
"inc_impact",
"taskslatable_duration",
"inc_number",
"inc_category",
"inc_opened_by.u_group_creator",
"inc_state",
"inc_priority",
"inc_opened_at",
"inc_resolved_at",
"inc_u_vendor",
"inc_urgency",
"inc_subcategory",
"inc_short_description",
"inc_reassignment_count",
"inc_assigned_to",
"inc_u_second_configuration_item",
"__KEY_result",
"inc_closed_at",
"inc_sys_id",
"inc_cmdb_ci.model_id.u_nna",
"inc_cmdb_ci.category",
"inc_cmdb_ci.sys_id",
"inc_cmdb_ci.name",
"inc_cmdb_ci.u_criticality",
"inc_cmdb_ci.u_environment",
"inc_sys_updated_on",
"inc_parent_incident",
(SELECT
"display_value",
"link",
"__FK_inc_cmdb_ci"
FROM "inc_cmdb_ci" FK "__FK_inc_cmdb_ci"),
(SELECT
"display_value" AS "display_value_u0",
"link" AS "link_u0",
"__FK_taskslatable_sla"
FROM "taskslatable_sla" FK "__FK_taskslatable_sla"),
(SELECT
"display_value" AS "display_value_u1",
"link" AS "link_u1",
"__FK_inc_resolved_by.u_group_creator"
FROM "inc_resolved_by.u_group_creator" FK "__FK_inc_resolved_by.u_group_creator"),
(SELECT
"display_value" AS "display_value_u2",
"link" AS "link_u2",
"__FK_inc_opened_by"
FROM "inc_opened_by" FK "__FK_inc_opened_by"),
(SELECT
"display_value" AS "display_value_u3",
"link" AS "link_u3",
"__FK_inc_assigned_to"
FROM "inc_assigned_to" FK "__FK_inc_assigned_to"),
(SELECT
"display_value" AS "display_value_u4",
"link" AS "link_u4",
"__FK_inc_u_second_configuration_item"
FROM "inc_u_second_configuration_item" FK "__FK_inc_u_second_configuration_item"),
(SELECT
"display_value" AS "display_value_u5",
"link" as "link_u5",
"__FK_inc_assignment_group"
FROM "inc_assignment_group" FK "__FK_inc_assignment_group"),
(SELECT
"display_value" AS "display_value_u6",
"link" AS "link_u6",
"__FK_inc_u_group_creator"
FROM "inc_u_group_creator" FK "__FK_inc_u_group_creator"),
(SELECT
"display_value" AS "display_value_u7",
"link" AS "link_u7",
"__FK_inc_cmdb_ci.u_incidents_service_range"
FROM "inc_cmdb_ci.u_incidents_service_range" FK "__FK_inc_cmdb_ci.u_incidents_service_range"),
(SELECT
"display_value" AS "display_value_u8",
"link" AS "link_u8",
"__FK_inc_cmdb_ci.model_id"
FROM "inc_cmdb_ci.model_id" FK "__FK_inc_cmdb_ci.model_id"),
(SELECT
"display_value" AS "display_value_u9",
"link" AS "link_u9",
"__FK_inc_parent_incident"
FROM "inc_parent_incident" FK "__FK_inc_parent_incident")
FROM JSON (wrap off) "result" PK "__KEY_result"
WITH CONNECTION (
//tous les jours
Url "$(SOURCE)/api/now/table/incident_sla?sysparm_display_value=true&sysparm_limit=500&sysparm_query=taskslatable_slaSTARTSWITHOLA^inc_state=7^ORinc_state=6^taskslatable_stage!=cancelled&sysparm_fields=inc_number,inc_short_description,inc_cmdb_ci,inc_u_second_configuration_item,inc_priority,inc_opened_by.u_group_creator,inc_opened_at,inc_resolved_by.u_group_creator,inc_resolved_at,taskslatable_sla,taskslatable_stage,taskslatable_duration,assignment_group,taskslatable_business_duration,taskslatable_sla,inc_state,CMDB_CI_Link,__FK_cmdb_ci,__FK_u_second_configuration_item,CI2,inc_category,inc_subcategory,inc_urgency,inc_impact,inc_u_vendor,inc_reassignment_count,inc_assigned_to,inc_opened_by,inc_sys_id,inc_closed_at,inc_assignment_group,inc_u_group_creator,inc_cmdb_ci.model_id.u_nna,inc_cmdb_ci.sys_id,inc_cmdb_ci.name,inc_cmdb_ci.u_criticality,inc_cmdb_ci.u_incidents_service_range,inc_cmdb_ci.u_environment,inc_cmdb_ci.model_id,inc_cmdb_ci.category,inc_sys_updated_on,inc_parent_incident",
QUERY "sysparm_offset" "$(StartAt)");
StartAt = StartAt+PageSize;
Next;
[MAP_cmdb_ci]:
LOAD
[display_value] as [CMDB_CI],
SubField([link], '/', 😎 as CMDB_CI_ID,
[__FK_inc_cmdb_ci] AS [__KEY_result_Incident]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_inc_cmdb_ci]);
[MAP_taskslatable_sla]:
Mapping LOAD
// [link_u0],
[__FK_taskslatable_sla] AS [__KEY_result_Incident],
[display_value_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_taskslatable_sla]);
[MAP_assignment_group]:
Mapping LOAD
[__FK_inc_assignment_group] AS [__KEY_result_Incident],
[display_value_u5]
// [link_u1],
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_inc_assignment_group]);
[MAP_inc_u_group_creator]:
Mapping LOAD
//[link_u6],
[__FK_inc_u_group_creator] AS [__KEY_result],
[display_value_u6]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_inc_u_group_creator]);
[MAP_CI2]:
Mapping LOAD
[__FK_inc_u_second_configuration_item] AS [__KEY_result_Incident],
[display_value_u4] as CI2
// [link_u4],
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_inc_u_second_configuration_item]);
[MAP_assigned_to]:
MAPPING LOAD
// [link_u3],
[__FK_inc_assigned_to] AS [__KEY_result_Incident],
[display_value_u3]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_inc_assigned_to]);
[MAP_opened_by]:
Mapping LOAD
// [link_u2],
[__FK_inc_opened_by] AS [__KEY_result_Incident],
[display_value_u2] as [Opened_by]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_inc_opened_by]);
[MAP_inc_service_range]:
MAPPING LOAD
//[link_u7],
[__FK_inc_cmdb_ci.u_incidents_service_range] AS [__KEY_result],
[display_value_u7]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_inc_cmdb_ci.u_incidents_service_range]);
[MAP_inc_cmdb_ci.model_id]:
mapping LOAD
//[link_u8],
[__FK_inc_cmdb_ci.model_id] AS [__KEY_result],
[display_value_u8]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_inc_cmdb_ci.model_id]);
[MAP_inc_parent_incident]:
Mapping LOAD
// [link_u2],
[__FK_inc_parent_incident] AS [__KEY_result],
[display_value_u9] as [PARENT_INCIDENT]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_inc_parent_incident]);
[Result_incident_SLA]:
LOAD
[taskslatable_stage],
[taskslatable_business_duration],
[inc_impact] as impact,
[taskslatable_duration],
[inc_number] AS [INCIDENT_ID],
[inc_category] AS [Domaine],
ApplyMap('MAP_inc_u_group_creator',__KEY_result, '$(NON_TROUVE)') AS [Groupe_Createur],
ApplyMap('MAP_inc_parent_incident',__KEY_result, '$(NON_TROUVE)') AS [inc_parent_incident],
[inc_state] AS [STATUS],
[inc_priority],
[inc_opened_at] AS [DATE_CREATION],
[inc_resolved_at] AS [DATE_RESOLUTION],
[inc_u_vendor]AS [Fournisseur_Affecte],
[inc_urgency]as urgency,
[inc_subcategory] AS [Sous_Domaine],
[inc_short_description] AS [Titre],
[inc_reassignment_count],
[inc_assigned_to],
[inc_closed_at] AS [DATE_CLOTURE],
[inc_cmdb_ci.model_id.u_nna] as NNA,
[inc_cmdb_ci.category] AS [Domaine_Applicatif],
[inc_cmdb_ci.sys_id] ,
[inc_cmdb_ci.name] AS [CMDB_CI_Name],
[inc_cmdb_ci.u_criticality] AS [CRITICITE_SOURCE],
[inc_cmdb_ci.u_environment] AS ENV,
[__KEY_result],
ApplyMap('MAP_CI2',__KEY_result, '$(NON_TROUVE)') AS [CMDB_CI_2],
if( ((left([inc_urgency],1)=1 or left([inc_urgency],1)=2) and left([inc_impact],1)=1) or (left([inc_urgency],1)=1 and left([inc_impact],1)=2), 'P1', 'P2') as PRIORITE,//Groupe d'affectation
lookup('CMDB_CI', '__KEY_result_Incident', __KEY_result, 'MAP_cmdb_ci') AS [CMDB_CI],
lookup('CMDB_CI_ID', '__KEY_result_Incident', __KEY_result, 'MAP_cmdb_ci') AS [CMDB_CI_ID],
ApplyMap('MAP_opened_by',__KEY_result, '$(NON_TROUVE)') AS [Nom_Createur],
ApplyMap('MAP_taskslatable_sla',__KEY_result, '$(NON_TROUVE)') AS [SLA_Name],
ApplyMap('MAP_assignment_group',__KEY_result, '$(NON_TROUVE)') AS [Groupe_Affectation] ,
ApplyMap('MAP_assigned_to',__KEY_result, '$(NON_TROUVE)') AS [Assigned_to],
ApplyMap('MAP_inc_service_range',__KEY_result, '$(NON_TROUVE)') AS [PLAGE_SOURCE] ,
ApplyMap('MAP_inc_cmdb_ci.model_id',__KEY_result, '$(NON_TROUVE)') AS [APP],
[inc_sys_id] AS [sys_incident_id],
inc_sys_updated_on as INC_DATE_UPDATE
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_result]);
DROP TABLE RestConnectorMasterTable;
/* Store dans le fichier QVD */
STORE Result_incident_SLA INTO 'lib://$(SOURCE_QVD)/Incident_SLA.qvd';
DROP TABLE Result_incident_SLA ,MAP_cmdb_ci;
