EDI: Transmission success rates

KPI search example

A critical Key Performance Indicator (KPI) here is the EDI Transmission Success Rate, which monitors the success of acknowledgements. Monitoring fluctuations in success rate—such as sudden spikes or drops—is crucial for identifying potential issues within supply chain systems. For this critical KPI, deviations can be detected using anomaly detection models in Splunk software or by leveraging Splunk ITSI for KPI change monitoring.

A high transmission success rate ensures that critical supply chain documents are delivered to the correct recipient systems without errors. Monitoring this KPI helps prevent operational delays due to missing or failed transmissions. Specifically, it looks at the percentage of successfully transmitted EDI documents (for example, 850 Purchase Orders, 856 Advance Shipment Notices, or 810 Invoices) compared to the total number of documents sent.

To calculate the success rate, we first need to count the total number of requests and the number of successful requests, then apply a formula to determine the success rate. This requires aggregating both the total requests and the successful events. By using the timechart command, we can generate a statistical trend with the following SPL.

index=supply_chain_edi sourcetype="edi:x12" 
| table _time edi_ack_status edi_buyer edi_code edi_code_ack edi_cont_num edi_date edi_flag edi_requestor edi_responder edi_seller edi_sequence edi_time edi_tr_id edi_type 
| eval edi_ack_status_combo=edi_code+"-"+edi_code_ack+"-"+edi_ack_status 
| eval edi_code_groupby=if(isnull(edi_code_ack), edi_code, edi_code_ack) 
| stats last(_time) AS _time, last(edi_requestor) AS edi_requestor, last(edi_responder) AS edi_responder, first(edi_code) AS edi_type, values(edi_code) AS edi_code, last(edi_ack_status) AS edi_ack_status BY edi_tr_id edi_code_groupby
| eval edi_ack_success=if(isnull(edi_ack_status), "No_Respond" , "Respond")
| timechart count BY edi_ack_success
| eval edi_ack_request_total=No_Respond+Respond

Use a line graph visualization to clearly show responses, no responses, and total requests.

We can calculate the EDI Transmission Success Rate by adding two eval commands to apply the appropriate formula.

index=supply_chain_edi sourcetype="edi:x12" 
| table _time edi_ack_status edi_buyer edi_code edi_code_ack edi_cont_num edi_date edi_flag edi_requestor edi_responder edi_seller edi_sequence edi_time edi_tr_id edi_type 
| eval edi_ack_status_combo=edi_code+"-"+edi_code_ack+"-"+edi_ack_status 
| eval edi_code_groupby=if(isnull(edi_code_ack), edi_code, edi_code_ack) 
| stats last(_time) AS _time, last(edi_requestor) AS edi_requestor, last(edi_responder) AS edi_responder, first(edi_code) AS edi_type, values(edi_code) AS edi_code, last(edi_ack_status) AS edi_ack_status BY edi_tr_id edi_code_groupby
| eval edi_ack_success=if(isnull(edi_ack_status), "No_Respond" , "Respond")
| timechart count BY edi_ack_success
| eval edi_ack_request_total=No_Respond+Respond
| eval edi_ack_success_rate=(Respond/edi_ack_request_total)*100

By using a line chart for visualization, it's easier to observe and track trends over time.

To proactively receive alerts for fluctuations in the success rate, we can either set a fixed threshold for the rate to trigger an alert or use adaptive thresholding to monitor deviations from the normal range. The follow example search applies a fixed threshold for monitoring. An additional search condition, edi_ack_success_rate<80, is applied to identify instances where the transmission success rate falls below 80 percent.

index=supply_chain_edi sourcetype="edi:x12" 
| table _time edi_ack_status edi_buyer edi_code edi_code_ack edi_cont_num edi_date edi_flag edi_requestor edi_responder edi_seller edi_sequence edi_time edi_tr_id edi_type 
| eval edi_ack_status_combo=edi_code+"-"+edi_code_ack+"-"+edi_ack_status 
| eval edi_code_groupby=if(isnull(edi_code_ack), edi_code, edi_code_ack) 
| stats last(_time) AS _time, last(edi_requestor) AS edi_requestor, last(edi_responder) AS edi_responder, first(edi_code) AS edi_type, values(edi_code) AS edi_code, last(edi_ack_status) AS edi_ack_status BY edi_tr_id edi_code_groupby
| eval edi_ack_success=if(isnull(edi_ack_status), "No_Respond" , "Respond")
| timechart count BY edi_ack_success
| eval edi_ack_request_total=No_Respond+Respond
| eval edi_ack_success_rate=(Respond/edi_ack_request_total)*100
| search edi_ack_success_rate<80

Search explanations

Splunk search	Explanation
index=supply_chain_edi sourcetype="edi:x12"	Select EDI X12 data by selecting sourcetype ofedi:x12.
| table _time edi_ack_status edi_buyer edi_code edi_code_ack edi_cont_num edi_date edi_flag edi_requestor edi_responder edi_seller edi_sequence edi_time edi_tr_id edi_type	Format the relevant fields into a table.
| eval edi_ack_status_combo=edi_code+"-"+edi_code_ack+"-"+edi_ack_status | eval edi_code_groupby=if(isnull(edi_code_ack), edi_code, edi_code_ack)	Create a new evaluated field edi_ack_status_combo that combines edi_code, edi_code_ack, edi_ack_statusfield. Use another evalcommand to create edi_code_groupby field to join all EDI events with EDI 997 acknowledgment events.
| stats last(_time) AS _time, last(edi_requestor) AS edi_requestor, last(edi_responder) AS edi_responder, first(edi_code) AS edi_type, values(edi_code) AS edi_code, last(edi_ack_status) AS edi_ack_status BY edi_tr_id edi_code_groupby	Join all EDI events with EDI 997 (acknowledgment) events into a single event. A successfully joined event will have EDI edi_ack_status field that indicates there was a matching pair of EDI with EDI acknowledgment. Events with null values in edi_ack_status means that there's no matching pair of EDI acknowledgments. Events with successfully joined EDIs have “A”, “E”, “R” flags indicating their status.
| eval edi_ack_success=if(isnull(edi_ack_status), "No_Respond" , "Respond")	For data cleansing purposes, assign the value of edi_ack_status as “Not Received” if the value is null.
| timechart count BY edi_ack_success	Create trend aggregation stats using timechart and count the amount over time.
| eval edi_ack_request_total=No_Respond+Respond	Calculate the sum of total events to put them in the trend timeline.
| eval edi_ack_success_rate=(Respond/edi_ack_request_total)*100	Calculate the ratio of edi_ack_success_rate to put the results in the trend timeline.

A sudden drop in transmission success rate could indicate system malfunctions or connectivity issues, potentially leading to gaps in communication with suppliers or customers.

Next steps

When you have this search running in your Splunk platform, return to the Monitoring electronic data interchange transmission and acknowledgement use case to learn how to share the results with stakeholders and to find other KPIs you might want to measure. You can also review the Solution Accelerator for Supply Chain Optimization for more great use cases to help you use the Splunk platform to be successful in your supply chain operations.