Splunk Search

How to search what values are missing in my lookup table?

raomu
Explorer

How to write a search to get a list of items which are not matching.

Example :

I have a list of devices :

A
B
C
D

Now I have a lookup table with a list of devices below :

A
B
D
E

How I can find the list of devices which are missing in my lookup table?
In above case, the results should be C and E

1 Solution

niketn
Legend

@raomu, your question mentions that you need to find devices present in your data but missing from your lookup. However, based on your expected results, device E is present in lookup while missing from your actual data. Please confirm whether that is required or not.

Nevertheless @somesoni2 's answer should still work for you with final pipe as | where mvcount(from)=1.

Following is a run anywhere search based on sample data and output provided

| makeresults
| eval deviceFromData="A,B,C,D"
| makemv deviceFromData delim=","
| mvexpand deviceFromData
| rename deviceFromData as device
| append [ | makeresults
| eval deviceFromLookup="A,B,D,E"
| makemv deviceFromLookup delim=","
| mvexpand deviceFromLookup
| rename deviceFromLookup as device]
| stats count by device
| search count=1
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@raomu, your question mentions that you need to find devices present in your data but missing from your lookup. However, based on your expected results, device E is present in lookup while missing from your actual data. Please confirm whether that is required or not.

Nevertheless @somesoni2 's answer should still work for you with final pipe as | where mvcount(from)=1.

Following is a run anywhere search based on sample data and output provided

| makeresults
| eval deviceFromData="A,B,C,D"
| makemv deviceFromData delim=","
| mvexpand deviceFromData
| rename deviceFromData as device
| append [ | makeresults
| eval deviceFromLookup="A,B,D,E"
| makemv deviceFromLookup delim=","
| mvexpand deviceFromLookup
| rename deviceFromLookup as device]
| stats count by device
| search count=1
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

raomu
Explorer

Excellent.. It worked for me.

Thanks for your time.

0 Karma

niketn
Legend

@raomu, I have converted to answer. Please accept to mark as answered!

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

kdulle
Explorer

You mention that you want the results to show both C and E, but actually the only one that appears to be missing from the lookup is C. So if you are interested in finding out what is missing from both, you may want to try using the diff command.

Something like:

| set diff [ | inputlookup devices.csv ] [ search index=<your_index> devices=* | table devices]

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

The problem with diff is that it does not tell you which way the search is different - an element missing from the first file, or an element missing from the second file, will both be on the output file with no telling where it came from. That's not particularly useful in most scenarios. (Not your fault, I've just never found it particularly useful.)

0 Karma

mayurr98
Super Champion

Hey try this run anywhere search

| makeresults 
| eval devices="A B C D" 
| makemv devices 
| mvexpand devices 
| eval check="csv1" 
| append 
    [| makeresults 
    | eval devices="A B D E" 
    | makemv devices 
    | mvexpand devices 
    | eval check="csv2" ] 
| fields- _time 
| stats dc(check) as count by devices 
| where count=1 
| fields devices 
| rename devices as "missing device list"

In your environment you should try something like this

index=<your_index> devices=* 
| dedup devices 
| table devices 
| eval check="csv1" 
| append 
    [| inputlookup devices.csv 
    | dedup devices 
    | table devices 
    | eval check="csv2"] 
| stats dc(check) as count by devices 
| where count=1 
| fields devices 
| rename devices as "missing device list"

let me know if this helps!

somesoni2
SplunkTrust
SplunkTrust

Try like this

your current search giving list of of devices from your index with field say device | table deviceName | eval from="data"
| append [| inputlookup append=t yourlookup.csv | table deviceName | eval from="lookup"]
| stats values(from) as from by deviceName  | where mvcount(from)=1 AND from="data"

kdulle
Explorer

This worked great for me to identify which devices were missing from a search compared to my lookup table. I just had to change from="data" to from="lookup" in the where statement to reverse the result. Thanks!

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...