topic Re: compare data list in Splunk Search

compare data list

mkrauss1 — Sat, 02 Dec 2017 19:00:16 GMT

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?

Re: compare data list

niketn — Sat, 02 Dec 2017 19:21:11 GMT

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

Re: compare data list

mkrauss1 — Sat, 02 Dec 2017 19:22:40 GMT

can have many stores

Re: compare data list

niketn — Sat, 02 Dec 2017 19:24:49 GMT

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

Re: compare data list

mkrauss1 — Sat, 02 Dec 2017 19:28:23 GMT

But a search for two stores would be great as well

Re: compare data list

niketn — Sat, 02 Dec 2017 19:41:21 GMT

@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.

Re: compare data list

MonkeyK — Sat, 02 Dec 2017 19:52:59 GMT

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.

Re: compare data list

woodcock — Sat, 02 Dec 2017 20:14:57 GMT

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

Re: compare data list

niketn — Sat, 02 Dec 2017 20:16:43 GMT

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 🙂