Skip to main content

 

Splunk Lantern

Shipping delay detection

 

This article is designed to show you how to detect shipping delays by analyzing EDI transactions to monitor carrier performance and ensure on-time deliveries, which is important for any organization looking to optimize their inventory. By leveraging EDI 214 (Transportation carrier shipment status message) and EDI 856 (Advance ship notice), this article enables real-time tracking of shipment statuses, identifies delays, and triggers corrective actions such as rerouting shipments or escalating issues with carriers.

Key features of the procedures in this article include:

  • Real-time delay detection: Monitor carrier status updates (EDI 214) for delays.
  • Automated alerts: Send notifications to logistics teams when delays are detected.
  • Proactive rerouting: Enable quick rerouting of shipments to minimize delays.

Benefits of these procedures include:

  • Reduced delivery delays: Proactive detection cuts delays by up to 30%.
  • Cost savings: Minimize penalties, expedited shipping fees, and customer compensations.
  • Improved customer satisfaction: Ensure on-time deliveries, boosting customer trust.
  • Data-driven logistics: Empower teams to prioritize reliable carriers and optimize shipping strategies.

Data sources

  • Primary data sources:
    • EDI 214 (Transportation carrier shipment status message) for real-time shipment status updates
    • EDI 856 (Advance ship notice) for shipment details and estimated delivery dates
  • Supporting data sources:
    • EDI 210 (Freight invoice) for shipping cost analysis
    • EDI 850 (Purchase order) for order priority context

Sample EDI events

EDI 856 (ASN)

BSN*00*SHIP123*20231015*1200  
DTM*011*20231020 (Promised delivery: 2023-10-20)  
MAN*ABCD1234*XYZ  

EDI 214 (Status update)

BSN*00*SHIP123*20231022*1500  
DTM*011*20231025 (Actual delivery: 2023-10-25)  
STC*DL*DELAYED 

Step-by-step analysis instructions

This section provides a quick overview of the process you'll use to ingest EDI data, process it, and analyze shipping delays in the Splunk platform.

  1. Set up EDI integration:
    1. Ingest EDI 214 and EDI 856 data into the Splunk platform.
    2. Map critical fields: edi_shipment_id, edi_est_delivery_date, edi_act_delivery_date, edi_carrier.
  2. Calculate delivery delays: Use eval to compute the delay duration (actual vs. estimated delivery dates).
  3. Filter delayed shipments: Apply search delay_duration > 0 to isolate delayed shipments.
  4. Aggregate carrier performance: Use stats to calculate average and maximum delays per carrier.
  5. Assign performance ratings: Use eval with case() to categorize carriers as Excellent/Good/Poor.
  6. Visualize results:
    1. Create bar charts to compare carrier performance ratings.
    2. Build timecharts to monitor delay trends over time.

Base search

index=supply_chain_edi sourcetype="edi:x12" edi_code IN (214 856) source=edi_quantumline_customer_full 
| strcat "EDI:" edi_code " -- " _raw edi_code_raw 
| stats first(edi_requestor) AS edi_requestor, first(edi_responder) AS edi_responder, values(edi_carr_id) AS edi_carr_id values(edi_code_raw) AS evt_combined, dc(edi_code) AS edi_code_dc, range(_time) AS time_took, earliest(_time) AS time_begin, latest(_time) AS time_last BY edi_tr_id 
| eval _time=time_last 
| eval time_took_days=time_took/86400 
| search edi_code_dc>1 
| eval performance=case(time_took_days <= 2, "Excellent", time_took_days <= 3, "Good", time_took_days <= 4, "Poor", time_took_days <= 6, "Red") 
| table _time * 

Search explanation

Splunk search Explanation

index=supply_chain_edi sourcetype="edi:x12" edi_code IN (214 856) source=edi_quantumline_customer_full

Retrieves EDI transactions (shipment status 214 and shipment notifications 856) from the supply_chain_edi index, specifically from edi_quantumline_customer_full.

strcat "EDI:" edi_code " -- " _raw edi_code_raw

Creates a formatted string combining "EDI:", the edi_code, and _raw event data into edi_code_raw for better tracking.

| stats first(edi_requestor) AS edi_requestor, first(edi_responder) AS edi_responder, values(edi_carr_id) AS edi_carr_id values(edi_code_raw) AS evt_combined, dc(edi_code) AS edi_code_dc, range(_time) AS time_took, earliest(_time) AS time_begin, latest(_time) AS time_last BY edi_tr_id 

Aggregates data by edi_tr_id, capturing key transaction details:

  • edi_requestor: First recorded entity that initiated the transaction.
  • edi_responder: First recorded entity that responded.
  • edi_carr_id: List of carrier IDs.
  • evt_combined: Combined event details.
  • edi_code_dc: Distinct count of edi_code values per transaction.
  • time_took: Time range between the first and last event.
  • time_begin: Earliest timestamp of the transaction.
  • time_last: Latest timestamp of the transaction.
| eval _time=time_last 
 
Sets _time to the last recorded event (time_last) for chronological sorting.
eval time_took_days=time_took/86400 Converts the transaction duration (time_took) from seconds to days for performance evaluation.
search edi_code_dc>1 Filters results to only include transactions with more than one distinct edi_code (indicating multiple transaction steps).
eval performance=case(time_took_days <= 2, "Excellent", time_took_days <= 3, "Good", time_took_days <= 4, "Poor", time_took_days <= 6, "Red")

Assigns a performance rating based on transaction duration:

  • ≤ 2 days → Excellent
  • ≤ 3 days → Good
  • ≤ 4 days → Poor
  • ≤ 6 days → Red (indicating significant delay).
| table _time * Displays all fields in a structured table, with _time as the first column for chronological sorting.

 

Extending the base search

The following searches show a few examples of further shipping delay detection that you can perform by using the base search:

Assign delivery performance from delivery duration

By using the chart command with span=1, we can count the number of deliveries based on different time intervals, grouping them into 1-day increments for better analysis.

... Base search omitted (Refer to the base search provided above and add here).

| chart count BY time_took_days span=1

clipboard_e20056b8429d214c341f294b96af8a6cb.png

The base search calculates the number of days per delivery and categorizes each delivery into 1-day intervals to visualize the distribution. Based on the results, we can see that the majority of deliveries fall within a 3 to 5-day timeframe. This exploratory analysis provides valuable insights for operations, helping to establish a threshold for monitoring potential delays.

In the base search, we have also categorized delivery times based on duration, classifying each as "Excellent," "Good," "Poor," or "Red," with "Red" indicating deliveries that require urgent attention. 

Now we can aggregate the statistics by performance category. By using the stats command with performance, we can analyze and visualize different SLA categories that are critical to operations.

... Base search omitted (Refer to the base search provided above and add here).

| stats count BY performance

clipboard_ea9e57977183ff943a41775a8eac0fd94.pngThe results provide a clear breakdown of delivery performance based on operational SLA policies, making it easy to distinguish between different performance categories. Notably, the analysis highlights a high number of "Red" deliveries, indicating areas that require immediate attention.

Identify poor delivery orders

Now let's focus on the "Red" category of delivery activities. The following example further breaks down and analyzes the distribution of delivery duration times within this high-priority category.

... Base search omitted (Refer to the base search provided above and add here).

| search performance="Red"
| sort - time_took_days
| table edi_tr_id edi_carr_id edi_requestor edi_responder  performance time_begin time_last time_took time_took_days

clipboard_eb6a07fa2e438facd69cdaa1053b6d655.png

Based on the results, we can see that most "Red" category deliveries took 4 to 5 days to arrive.  By adding a filter using search performance=Red, we can isolate only the SLA-violated deliveries for further detailed analysis. Applying different attributes allows us to dissect the data more effectively and gain deeper insights.

Isolate “Red” (Immediate attention needed) delivery order cases

Further focusing on "Red" delivery events, we can now drill down into individual delivery activities to gather more details or take necessary action.

... Base search omitted (Refer to the base search provided above and add here).

| search performance="Red"
| sort - time_took_days
| table edi_carr_id edi_requestor edi_responder edi_tr_id performance time_begin time_last time_took time_took_days

clipboard_ecadfb4f745a243eaee657ebe273a2a51.png

Now, all delivery activities that require immediate attention are sorted by the longest delivery times, ensuring that the most critical cases appear at the top of the results table.

Next steps

  • Pilot implementation: Test the solution with a subset of high-priority shipments.
  • Refine alert logic: Adjust delay thresholds and notification rules based on business SLAs.
  • Integrate with logistics systems: Automate rerouting workflows for delayed shipments.
  • Expand analytics: Combine with EDI 210 (Freight invoice) to analyze cost versus performance trade-offs.