compare data list

[mkrauss1]

Assume i have two stores which must have the same items but one is missing.

My search returns for example

STORE=LONDON ITEM=ORANGE
STORE=LONDON ITEM=APPLE
STORE=PARIS ITEM=ORANGE
STORE=PARIS ITEM=APPLE
STORE=PARIS ITEM=LEMON

How can i display the missing item LEMON visible in store london?

[woodcock]

Like this:

| makeresults 
| eval raw="STORE=LONDON ITEM=ORANGE:STORE=LONDON ITEM=APPLE:STORE=PARIS ITEM=ORANGE:STORE=PARIS ITEM=APPLE:STORE=PARIS ITEM=LEMON" 
| makemv delim=":" raw 
| mvexpand raw 
| rename raw AS _raw 
| kv
| stats dc(STORE) AS num_stores values(STORE) AS stores BY ITEM
| search num_stores=1

[niketn · Accepted solution]

@mkrauss1, please find the following run anywhere search. It mimic three ITEMs and three STORES. You can expand to as many as you want. Obviously the query will be less expensive if there were lookups for unique STORES and ITEMS.

|  makeresults
|  eval data= "STORE=LONDON ITEM=BANANA;STORE=DELHI ITEM=ORANGE;STORE=LONDON ITEM=APPLE;STORE=PARIS ITEM=ORANGE;STORE=PARIS ITEM=APPLE;STORE=PARIS ITEM=LEMON"
|  makemv data delim=";"
|  mvexpand data
|  rename data as _raw
|  KV
|  table ITEM STORE
|  eventstats values(STORE) as AllStores
|  stats count as Match dc(AllStores) as MaxMatch values(STORE) as StoreFound values(AllStores) as AllStores by ITEM
|  search Match<MaxMatch
|  mvexpand AllStores
|  where !(AllStores in (StoreFound))
|  rename AllStores as StoreMissing
|  stats values(StoreFound) as StoreFound  values(StoreMissing) as StoreMissing by ITEM

PS: Commands till | table ITEM STORE create sample data for demo.
Also in command will work on Splunk Enterprise 6.6 onward.

[MonkeyK]

Great solution and I also really like the first part of the query to build a set of data. Both of these should be on some list of solution patterns.

[niketn]

Thanks @MonkeyK 🙂 I learnt KV and extract for mocking up data from @cmerriman 🙂
Most of community members devise these tricks to mock sample data as per question to assist users. Obviously we do not have access to user's data another reason is re-usability by other members and also testing.

You are right that such data generation queries can go to Tips & Tricks section of Splunk Blogs but not sure who can do that 🙂

[niketn]

@mkrauss1, will you always have two stores or can it be more than two as well?

[mkrauss1]

But a search for two stores would be great as well

[mkrauss1]

can have many stores

[niketn]

Do you have lookup file for STORES or can you have a lookup file?