Splunk Search

compare data list

mkrauss1
Explorer

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?

Tags (1)
0 Karma
1 Solution

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

woodcock
Esteemed Legend

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
0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

MonkeyK
Builder

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
Legend

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 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

mkrauss1
Explorer

But a search for two stores would be great as well

0 Karma

mkrauss1
Explorer

can have many stores

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...