Splunk Search

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

New Member

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

Table.csv
SERVERA,DATAA
SERVERA,DATAB
SERVERA,DATAC
SERVERB,DATAA
SERVERB,DATAD
SERVERB,DATAE
SERVERC,DATAB
SERVERC,DATAE
SERVERC,DATAC

Data:
SERVER=SERVERA DATA=DATAA
SERVER=SERVERA DATA=DATAC
SERVER=SERVERB DATA=DATAD
SERVER=SERVERB DATA=DATAE
SERVER=SERVERC DATA=DATAB
SERVER=SERVERC DATA=DATAE

Expected result:
SERVERA,DATAB
SERVERB,DATAA
SERVERC,DATAC

Thanks in Advance

0 Karma
1 Solution

Legend

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

0 Karma

Legend

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

0 Karma

New Member

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

Thanks,
Yuvaraj

0 Karma

Legend

you're welcome!
Ciao and next time.
Giuseppe

0 Karma

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, an upvote would be appreciated.
0 Karma