Skip to main content

Splunk Lantern turns 5 on May 28th. Thank you for being one of our 750,000 annual users!
Click here to join our Slack channel to tell us what you love about the site or what content you'd like to see more of.
Lantern Home

 

Splunk Lantern

Current inventory status visibility

  1. Last updated
  2. Save as PDF

 

This article is designed to show you how to achieve real-time visibility into inventory levels by analyzing electronic data interchange (EDI) transactions, which is important for any organization looking to optimize their inventory. By leveraging EDI 846 (Inventory inquiry/advice) and EDI 856 (Advance ship notice), this article provides a centralized view of inventory levels across multiple locations, enabling proactive decision-making and alignment with demand.

Key features of the procedures in this article include:

  • Real-time inventory tracking: Monitor inventory levels using EDI 846 and EDI 856.
  • Multi-location visibility: Aggregate inventory data across warehouses and distribution centers.
  • Low-stock alerts: Trigger notifications when inventory falls below predefined thresholds.
  • Integration with procurement systems: Automatically generate replenishment orders when needed.

Benefits of these procedures include:

  • Reduced stock-outs: Proactive alerts prevent inventory shortages.
  • Improved inventory accuracy: Real-time updates eliminate manual tracking errors.
  • Lower holding costs: Optimize stock levels to avoid overstocking.
  • Enhanced decision-making: Centralized visibility enables faster responses to demand changes.

Data required

  • Primary data sources:
    • EDI 846 (Inventory inquiry/advice) for real-time inventory levels.
    • EDI 856 (Advance ship notice) for inbound/outbound shipment updates.
  • Supporting data sources:
    • EDI 850 (Purchase order) for demand signals.

Sample EDI events

EDI 850 (Purchase order)

EG*00*NE*PO12345*20231015
LIN*1*VP*SKU1001*25 (Ordered 25 units of SKU1001)

EDI 856 (ASN)

BSN*00*SHIP789*20231016  
LIN*1*VP*SKU1001*25 (Shipped 25 units of SKU1001 to Warehouse A)

EDI 846 (Inventory update)

LIN*1*VP*SKU1001*20 (Warehouse A reports 20 units in stock)  
DTM*007*20231017

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 inventory status in the Splunk platform.

  1. Set up EDI integration:
    1. Ingest EDI 846 and EDI 856 data into the Splunk platform.
    2. Map fields: edi_item_code, edi_location, edi_quantity, edi_transaction_type.
  2. Calculate inventory changes: Use eval to adjust inventory levels based on inbound/outbound transactions.
  3. Aggregate current stock: Use stats to calculate net inventory by item and location.
  4. Set low-stock thresholds: Define thresholds (for example, 100 units) using eval.
  5. Generate alerts: Configure automated alerts for items flagged as low stock.
  6. Visualize results:
    1. Build a dashboard with real-time inventory levels across locations.
    2. Use gauges to display stock levels and tables to list low stock items.

Base search

This base search combines EDI 856 ASN records with EDI 846 inventory update events. Next, you'll extract and process product IDs along with the associated quantities to be shipped from the EDI 856 records. Similarly, you'll retrieve the actual inventory updates, including product IDs and quantities, from the EDI 846 records.

These two events will then be merged into a single record using a common transaction ID. As a result, each record will provide a unified view of the transaction, including both the ASN shipment details and the corresponding inventory update status. This aggregated event will support various analyses, including shipment volumes, inventory statuses across products and warehouses, and the broader impact on customers.

index=supply_chain_edi sourcetype="edi:x12" edi_code IN (846 856) source=edi_quantumline_customer 
| rex field=_raw max_match=2 "HL.*[\r\n]+(?<edi_asn_item>LIN\|.*[\r\n]+.*)" 
| rex field=_raw max_match=2 "N1.*[\r\n]*(?<edi_inv_update>LIN\|.*[\r\n]+.*[\r\n]+.*[\r\n]+.*)" 
| rex field=edi_inv_update max_match=2 "(?<edi_inv_update>LIN\|.*[\r\n]+.*)" 
| stats list(edi_asn_item) AS edi_asn_item, list(edi_inv_update) AS edi_inv_update, values(edi_code) AS edi_code, latest(_time) AS _time BY edi_tr_id 
| table _time, edi_code, edi_tr_id, edi_asn_item, edi_inv_update 
| eval zip_item=mvzip(edi_asn_item,edi_inv_update) 
| mvexpand zip_item 
| table _time, edi_tr_id, zip_item 
| eval edi_asn_item=mvindex(split(zip_item,","),0), edi_inv_update=mvindex(split(zip_item,","),1) 
| table _time, edi_tr_id, edi_asn_item, edi_inv_update 
| rex field=edi_asn_item "LIN\|[^\|]*\|BP\|(?<asn_prod_name>\w+)" 
| rex field=edi_asn_item "QTY|SN1\|[^\|]*\|(?<asn_cnt>\w+)" 
| rex field=edi_inv_update "LIN\|[^\|]*\|BP\|(?<inv_prod_name>\w+)" 
| rex field=edi_inv_update "(QTY|SN1)\|[^\|]*\|(?<inv_cnt>\w+)"

Search explanation

Splunk search Explanation

index=supply_chain_edi sourcetype="edi:x12" edi_code IN (846 856) source=edi_quantumline_customer

Filters EDI X12 data for inventory updates (EDI 846) and shipment records (EDI 856) from the specified data source.

rex field=_raw max_match=2 "HL.*[\r\n]+(?<edi_asn_item>LIN|.*[\r\n]+.*)"

Extracts ASN (Advance Shipment Notice) line items from raw EDI data.

rex field=_raw max_match=2 "N1.*[\r\n]*(?<edi_inv_update>LIN|.*[\r\n]+.*[\r\n]+.*[\r\n]+.*)"

Extracts inventory update records from raw EDI data.

rex field=edi_inv_update max_match=2 "(?<edi_inv_update>LIN|.*[\r\n]+.*)"

Refines extracted inventory update records by ensuring correct formatting.

stats list(edi_asn_item) AS edi_asn_item, list(edi_inv_update) AS edi_inv_update, values(edi_code) AS edi_code, latest(_time) AS _time BY edi_tr_id

Groups shipment and inventory update events by transaction ID, preserving the latest timestamp for each transaction.

table _time, edi_code, edi_tr_id, edi_asn_item, edi_inv_update

Displays key fields for analysis, including timestamps, transaction IDs, and extracted shipment/inventory data.

eval zip_item=mvzip(edi_asn_item,edi_inv_update)

Merges ASN shipment and inventory update data into a single field for easier processing.

mvexpand zip_item

Expands the merged field into multiple rows to ensure each shipment-inventory pair is treated separately.

table _time, edi_tr_id, zip_item

Displays the expanded dataset, focusing on transaction timestamps, IDs, and merged shipment-inventory data.

eval edi_asn_item=mvindex(split(zip_item,","),0), edi_inv_update=mvindex(split(zip_item,","),1)

Splits the merged data back into separate shipment (ASN) and inventory update fields.

table _time, edi_tr_id, edi_asn_item, edi_inv_update

Organizes and presents the cleaned dataset with shipment and inventory details.

rex field=edi_asn_item "LIN|[^|]*|BP|(?<asn_prod_name>\w+)"

Extracts product names from ASN shipment records.
| rex field=edi_asn_item "QTY|SN1\|[^\|]*\|(?<asn_cnt>\w+)"

Extracts the quantity of items in ASN.

rex field=edi_inv_update "LIN|[^|]*|BP|(?<inv_prod_name>\w+)"

Extracts product names from inventory update records.
| rex field=edi_inv_update "(QTY|SN1)\|[^\|]*\|(?<inv_cnt>\w+)" Extracts updated inventory count from ASN shipment records.

Extending the base search

The following searches show a few examples of further inventory analysis that you can perform:

Current inventory volumes by products

Here, the dedup command is used to retrieve the most recent inventory status event. Next, we calculate the count reported in EDI 846 based on the inventory product name, as shown below.

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

| dedup inv_prod_name
| stats sum(inv_cnt) as inv_cnt by inv_prod_name
| sort - inv_cnt

clipboard_e3436d628362c0c27abf26d77d653826c.png

The results quickly highlight the products with the highest stock in the warehouse, those that are out of stock, and the total quantity of products available in the warehouse.

Comprehensive warehouse activity summary

For this analysis, we want to assess the quantity of products stored in different warehouses. Since multiple SKUs are distributed across various warehouse locations globally, our goal is to analyze inventory levels across these sites.

The following search retrieves the current inventory status from the warehouse and then appends priority data, backfilled by manufacturing. Additionally, it incorporates the quantity of orders sent to the warehouse in the form of ASN (Advanced Shipping Notices), which indicate the amount of products that need to be deducted from the available stock.

In these examples, the appendcols command is used to first retrieve replenishment quantities from the factory and then track the corresponding orders scheduled for shipment.

| inputlookup warehouse_inventory.csv 
| strcat edi_warehouse_loc ":" item_id combo 
| appendcols 
    [ search index=supply_chain_edi sourcetype="edi:x12" edi_code=940 
    | rex field=_raw max_match=1 "N1\|WH\|(?<edi_warehouse_loc>[^\|]*)" 
    | rex field=_raw max_match=10 "LX.*[\r\n]+(?<edi_warehouse_item>W\d{2}\|.*[\r\n])" 
    | table _time, edi_code, edi_tr_id, edi_warehouse_loc, edi_warehouse_item 
    | mvexpand edi_warehouse_item 
    | rex field=edi_warehouse_item "W\d+\|(?<item_qty>\d*)\|[^\|]*\|[^\|]*\|(?<item_id>[^\|]*)~" 
    | stats sum(item_qty) AS item_qty_add BY edi_warehouse_loc item_id 
    | strcat edi_warehouse_loc ":" item_id combo 
    | table combo item_qty_add ] 
| appendcols 
    [ search index=supply_chain_edi sourcetype="edi:x12" edi_code=856 source=edi_quantumline_customer_full 
    | dedup edi_tr_id 
    | rex field=_raw max_match=10 "(?<edi_ship_item>HL.*[\r\n]+LIN.*[\r\n]+SN.*)" 
    | table _time, edi_code, edi_tr_id, edi_ship_item 
    | mvexpand edi_ship_item 
    | rex field=edi_ship_item max_match=10 "LIN\|\w*\|\w*\|(?<item_id>\w*)~[\r\n]+\w*\|\w*\|(?<item_qty_ship>\w*)" 
    | appendcols 
        [ search index=supply_chain_edi sourcetype="edi:x12" edi_code=940 earliest=-200d latest=+5d 
        | dedup edi_tr_id 
        | rex field=_raw max_match=1 "N1\|WH\|(?<edi_warehouse_loc>[^\|]*)" 
        | rex field=_raw max_match=10 "LX.*[\r\n]+(?<edi_warehouse_item>W\d{2}\|.*[\r\n])" 
        | table _time, edi_code, edi_tr_id, edi_warehouse_loc ] 
    | stats sum(item_qty_ship) AS item_qty_ship BY edi_warehouse_loc item_id 
    | strcat edi_warehouse_loc ":" item_id combo
    | table combo item_qty_ship ] 
| eval item_inv_tot=item_qty+item_qty_add-item_qty_ship 
| eval item_inv_diff=item_inv_tot-item_qty

Search explanation

Splunk Search Explanation

inputlookup warehouse_inventory.csv

Loads the warehouse inventory dataset from a CSV lookup file.

strcat edi_warehouse_loc ":" item_id combo

Creates a unique key (combo) by concatenating the warehouse location and item ID for correlation.

appendcols [ search index=supply_chain_edi sourcetype="edi:x12" edi_code=940

Retrieves EDI 940 (warehouse shipment order) transactions from the supply chain EDI index.

rex field=_raw max_match=1 "N1|WH|(?<edi_warehouse_loc>[^|]*)"

Extracts the warehouse location (edi_warehouse_loc) from the EDI transaction data.

rex field=_raw max_match=10 "LX.*[\r\n]+(?<edi_warehouse_item>W\d{2}|.*[\r\n])"

Extracts multiple warehouse item details from the EDI 940 records.

table _time, edi_code, edi_tr_id, edi_warehouse_loc, edi_warehouse_item

Displays key fields relevant to warehouse inventory tracking.

mvexpand edi_warehouse_item

Expands multi-value fields to process each item separately.

rex field=edi_warehouse_item "W\d+|(?<item_qty>\d*)|[^|]*|[^|]*|(?<item_id>[^|]*)~"

Extracts item quantities and item IDs from the warehouse shipment details.

stats sum(item_qty) AS item_qty_add BY edi_warehouse_loc item_id

Aggregates the total quantity of received items per warehouse location and item ID.

strcat edi_warehouse_loc ":" item_id combo

Recreates the combo key for alignment with the initial dataset.

table combo item_qty_add ]

Organizes data for received inventory, showing item quantities added.

appendcols [ search index=supply_chain_edi sourcetype="edi:x12" edi_code=856 source=edi_quantumline_customer_full

Retrieves EDI 856 (shipment notifications) transactions.

dedup edi_tr_id

Removes duplicate transaction IDs to avoid redundancy.

rex field=_raw max_match=10 "(?<edi_ship_item>HL.*[\r\n]+LIN.*[\r\n]+SN.*)"

Extracts shipment item details from the EDI records.

table _time, edi_code, edi_tr_id, edi_ship_item

Displays relevant fields for shipment tracking.

mvexpand edi_ship_item

Expands multi-value fields for processing individual items.

rex field=edi_ship_item max_match=10 "LIN|\w*|\w*|(?<item_id>\w*)~[\r\n]+\w*|\w*|(?<item_qty_ship>\w*)"

Extracts item quantities and IDs from shipment records.

appendcols [ search index=supply_chain_edi sourcetype="edi:x12" edi_code=940 earliest=-200d latest=+5d

Searches historical EDI 940 transactions within a broad time range (-200 to +5 days).

dedup edi_tr_id

Removes duplicate transactions to ensure data integrity.

rex field=_raw max_match=1 "N1|WH|(?<edi_warehouse_loc>[^|]*)"

Extracts warehouse location from past transactions.

rex field=_raw max_match=10 "LX.*[\r\n]+(?<edi_warehouse_item>W\d{2}|.*[\r\n])"

Extracts warehouse item details from historical shipment orders.

table _time, edi_code, edi_tr_id, edi_warehouse_loc ]

Displays key fields relevant to past warehouse shipments.

stats sum(item_qty_ship) AS item_qty_ship BY edi_warehouse_loc item_id

Aggregates the total quantity of shipped items per warehouse and item ID.

strcat edi_warehouse_loc ":" item_id combo

Recreates the combo key for data alignment.

table combo item_qty_ship ]

Organizes shipment inventory data, showing item quantities shipped.

eval item_inv_tot=item_qty+item_qty_add-item_qty_ship

Computes the updated total inventory by adding received stock and subtracting shipped stock.

eval item_inv_diff=item_inv_tot-item_qty

Calculates the inventory difference, highlighting discrepancies between expected and actual inventory levels.

The results display a color-coded table showing the inventory status across warehouses for each individual SKU. This table can be further refined for in-depth analysis, allowing us to drill down into key details such as the total replenished quantities, SKUs lacking replenishment, current demand levels, and potential future demand for each SKU.

Comprehensive warehouse current stock and activity summary

Using the base search, let's dive deeper into a more detailed analysis of the examples we discussed, this time from the product SKU perspective.

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

| stats sum(item_qty_ship) AS item_qty_ship, sum(item_qty) AS item_qty, sum(item_qty_add) AS item_qty_add BY item_id
| eval item_inv_tot=item_qty+item_qty_add-item_qty_ship 
| eval item_inv_diff=item_inv_tot-item_qty
| table item_id item_qty item_qty_add item_qty_ship item_inv_diff item_inv_tot

The results provide a comprehensive, single-view snapshot of the entire inventory status. This includes the total units available in the warehouse, the quantity replenished, the number of units shipped, the difference between these values, and finally, the total inventory after replenishment and shipment.

Stock replenishment trends

Here is an example of looking at the amount of inventory being replenished, seeing it in trends. This analysis uses EDI code 940 “Warehouse ship instruction” EDI documents.

​​index=supply_chain_edi sourcetype="edi:x12" edi_code=940 
    | rex field=_raw max_match=1 "N1\|WH\|(?<edi_warehouse_loc>[^\|]*)" 
    | rex field=_raw max_match=10 "LX.*[\r\n]+(?<edi_warehouse_item>W\d{2}\|.*[\r\n])" 
    | table _time, edi_code, edi_tr_id, edi_warehouse_loc, edi_warehouse_item 
    | mvexpand edi_warehouse_item 
    | rex field=edi_warehouse_item "W\d+\|(?<item_qty>\d*)\|[^\|]*\|[^\|]*\|(?<item_id>[^\|]*)~" 
    | timechart span=1d sum(item_qty) AS item_qty_add BY item_id

Stock shipment trends

The following is an example of analyzing shipping trends to fulfill orders using EDI 856 ASNs.

index=supply_chain_edi sourcetype="edi:x12" edi_code=856 source=edi_quantumline_customer_full 
| dedup edi_tr_id 
| rex field=_raw max_match=10 "(?<edi_ship_item>HL.*[\r\n]+LIN.*[\r\n]+SN.*)" 
| table _time, edi_code, edi_tr_id, edi_ship_item 
| mvexpand edi_ship_item 
| rex field=edi_ship_item max_match=10 "LIN\|\w*\|\w*\|(?<item_id>\w*)~[\r\n]+\w*\|\w*\|(?<item_qty_ship>\w*)" 
| timechart sum(item_qty_ship) AS item_qty_ship BY item_id

The analysis reveals a steady increase in order shipping trends day by day, indicating rising demand. This provides valuable insight for planning production to effectively meet customer needs.

Status of warehouse inventory situations

The following analysis focuses on warehouse inventory status KPIs. Here are two key examples:

  • Product variety: Counting the number of different products stored in a warehouse.
  • Total inventory: Measuring the overall quantity of all mixed product stocks.
| inputlookup warehouse_inventory.csv 
| strcat edi_warehouse_loc ":" item_id combo 
| appendcols 
    [ search index=supply_chain_edi sourcetype="edi:x12" edi_code=940 
    | rex field=_raw max_match=1 "N1\|WH\|(?<edi_warehouse_loc>[^\|]*)" 
    | rex field=_raw max_match=10 "LX.*[\r\n]+(?<edi_warehouse_item>W\d{2}\|.*[\r\n])" 
    | table _time, edi_code, edi_tr_id, edi_warehouse_loc, edi_warehouse_item 
    | mvexpand edi_warehouse_item 
    | rex field=edi_warehouse_item "W\d+\|(?<item_qty>\d*)\|[^\|]*\|[^\|]*\|(?<item_id>[^\|]*)~" 
    | stats sum(item_qty) AS item_qty_add BY edi_warehouse_loc item_id 
    | strcat edi_warehouse_loc ":" item_id combo 
    | table combo item_qty_add ]
| eval item_qty_new_tot=item_qty+item_qty_add
| stats sum(item_qty_new_tot) AS item_qty_tot, dc(item_id) AS item_count BY edi_warehouse_loc

The search results display the total quantity of combined product SKUs in a column chart, along with the count of different items per warehouse location. As shown in the results, there are 12 different SKUs available across these four warehouses. Any changes in these numbers can indicate potential stock shortages at a particular warehouse, impacting its ability to fulfill orders from an inventory status perspective.

Next steps

  • Pilot implementation: Test with a subset of high-risk items and locations.
  • Refine thresholds: Adjust low-stock thresholds based on item-specific demand.
  • Scale up: Expand to all inventory items and warehouses.
  • Integrate with procurement: Automate replenishment workflows using EDI 850 (Purchase order).