Splunk Search

How to display what values are missing in my lookup table comparing to actual data?

yuvarajvelu
New Member

How to display what values are missing in my lookup table comparing to actual data?

Table.csv
SERVER_A,DATA_A
SERVER_A,DATA_B
SERVER_A,DATA_C
SERVER_B,DATA_A
SERVER_B,DATA_D
SERVER_B,DATA_E
SERVER_C,DATA_B
SERVER_C,DATA_E
SERVER_C,DATA_C

Data:
SERVER=SERVER_A DATA=DATA_A
SERVER=SERVER_A DATA=DATA_C
SERVER=SERVER_B DATA=DATA_D
SERVER=SERVER_B DATA=DATA_E
SERVER=SERVER_C DATA=DATA_B
SERVER=SERVER_C DATA=DATA_E

Expected result:
SERVER_A,DATA_B
SERVER_B,DATA_A
SERVER_C,DATA_C

Thanks in Advance

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi yuvarajvelu,
try this:

index=my_index
| eval SERVER=upper(SERVER), DATA=upper(DATA)
| stats count BY SERVER DATA
| append [ | inputlookup Table.csv | eval SERVER=upper(SERVER), DATA=upper(DATA), count=0 | fields SERVER DATA count ]
| stats sum(count) AS total BY SERVER DATA
| where total=0

Ciao.
Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi yuvarajvelu,
try this:

index=my_index
| eval SERVER=upper(SERVER), DATA=upper(DATA)
| stats count BY SERVER DATA
| append [ | inputlookup Table.csv | eval SERVER=upper(SERVER), DATA=upper(DATA), count=0 | fields SERVER DATA count ]
| stats sum(count) AS total BY SERVER DATA
| where total=0

Ciao.
Giuseppe

yuvarajvelu
New Member

Hi Giuseppe , Thank you so much for your query. It worked well and got the expected answer.

Thanks,
Yuvaraj

0 Karma

gcusello
SplunkTrust
SplunkTrust

you're welcome!
Ciao and next time.
Giuseppe

0 Karma

richgalloway
SplunkTrust
SplunkTrust

This should get you started.

| inputlookup Table.csv NOT [ search index=foo SERVER=* DATA=* | stats count by SERVER, DATA | fields SERVER DATA | format ]
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

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