Splunk Search

How to join two searches on a common field and include instances that don't overlap

Traer001
Path Finder

Hello,

I have one search where I am finding inventory details for items going into carts and another search for cart status. The two searches have one common field (named differently in each) for the cart location and I am trying to join them to show how many inventory items have been put into each cart and when the last item was put into the cart.

Both searches have the same index, host and sourcetype.

My search currently looks like this. It's in the works, but I'm stuck. I want it to also show carts that have not had any inventory put into it yet and count the items for each cart (even if it's zero). How can I rewrite this to show the info I want?

 

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE
| rex field=_raw "Cart:\s(?<cart_id>\d+)\sbegin"
| rex field=_raw "Cart:\s(?<cart_id>\d+)\sselect\snew\ssection:\s(?<section>\d+)"
| rex field=_raw "Cart:\s(?<cart_id>\d+)\sset\scurrent\slocation:\s(?<section_name>\w+-\d+)"
| rex field=_raw "Cart:\s(?<cart_id>\d+)\snext\slocation\sis:(?<section_name>\w+-\d+)"
| rex field=_raw "Cart:\s(?<cart_id>\d+)\sset\scurrent\slocation:\s\w+-(?<section_number>\d+)"
| rex field=_raw "Cart:\s(?<cart_id>\d+)\snext\slocation\sis:\w+-(?<section_number>\d+)"
| eval select_time=if(like(_raw, "%select new section%"), _time, null)
| eval selected_at=strftime(select_time, "%Y-%m-%d %H:%M:%S")
| eval start_time=if(like(_raw, "%begin%"), _time, null)
| eval set_time=if(like(_raw, "%set current location%"), _time, null)
| eval next_loc_time=if(like(_raw, "%next location%"), _time, null)
| where isnotnull(cart_id) and ((like(_raw, "%set current location%") and like(section_name, "AA%")) or like(_raw, "%begin%") or (like(_raw, "%select new section%")))
| eventstats latest(section) as section_id latest(section_name) as section_name latest(section_number) as section_number latest(selected_at) as selected_at by cart_id
| where isnotnull(section_name) and (100<=section_id and section_id<=300)
| join type=outer left=L right=R where L.section_number=R.new_loc [search index=INDEX host=HOSTNAME sourcetype=SOURCETYPE
| rex field=_raw "Move\sItem-(?<item>\w+\-\d+\-\d+\-\d+)\sfrom\sBin-(?<bin_id>\d+)-(?<slot_number>\d+)\sto\sAA-(?<new_loc>\d+)"
| where isnotnull(item)
| eval moved_at=strftime(_time, "%Y-%m-%d %H:%M:%S")
| table item, bin_id, slot_number, new_loc, moved_at]
| rename L.cart_id as cart_id, L.section_number as section_number, R.new_loc as new_loc, L.section_name as section_name, R.bin_id as bin_id, R.moved_at as moved_at
| table cart_id, new_loc, section_number, section_name, bin_id, moved_at

Labels (2)
0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...