checking if all values from lookup exists

[dorHerbesman]

Hey, im trying to do something relative easy and for some reason can't make it..

i have a lookup named tableq_lookyp with only one column tableq with the values:

1,2,4,5,7,8,10,11,12,13,14,15,16,20,21,22 (each value is different row)

and i have this search:

index=myidnex sourcetype=mysourcetype source=mysource

| table ACCUM_CODE LOCK_CODE PERIOD_KEY TABLEQ UPD_DATE UPD_TIME USER_NAME

i want to check if all of the values from the tableq lookup exists in my search 

so i should get 16 rows (as the amount of different values in tableq) and a new column with yes/no options that tell me if the value appear in the search/lookup or not 
what is the best way of doing it?

thanks !

[gcusello]

Hi @dorHerbesman,

try something like this:

index=myidnex sourcetype=mysourcetype source=mysource
| stats
   count
   BY TABLEQ
| append [ | inputlookup your_lookup | eval count=0 | rename tableq  AS TABLEQ | fields TABLEQ count ]
| stats
   sum(count) AS total
   BY TABLEQ
| where total=0

Ciao.

Giuseppe

[dorHerbesman]

OK the calculation is correct but now i've lost all other fields 

| table ACCUM_CODE LOCK_CODE PERIOD_KEY TABLEQ UPD_DATE UPD_TIME USER_NAME

how can i regain them but keep the calulation code you provide?

thanks again for the help!

[gcusello · Accepted solution]

Hi @dorHerbesman ,

if you want to list all the values from the lookup that aren't present in the search results, you will not have these fields!

Anyway, you can add all the other fields adding them  and using the values option in stats command:

index=myidnex sourcetype=mysourcetype source=mysource
| stats
   count
   values(ACCUM_CODE) AS ACCUM_CODE
   values(LOCK_CODE) AS LOCK_CODE
   values(PERIOD_KEY) AS PERIOD_KEY
   values(UPD_DATE) AS UPD_DATE
   values(UPD_TIME) AS UPD_TIME
   values(USER_NAME) AS USER_NAME
   BY TABLEQ
| append [ | inputlookup your_lookup| eval count=0 | rename tableq  AS TABLEQ | fields TABLEQ count ]
| stats
   sum(count) AS total
   values(ACCUM_CODE) AS ACCUM_CODE
   values(LOCK_CODE) AS LOCK_CODE
   values(PERIOD_KEY) AS PERIOD_KEY
   values(UPD_DATE) AS UPD_DATE
   values(UPD_TIME) AS UPD_TIME
   values(USER_NAME) AS USER_NAME
   BY TABLEQ
| where total=0

 Ciao.

Giuseppe

[dorHerbesman]

that's exectly what i wanted! thanks!

[gcusello]

Hi @dorHerbesman ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[kiran_panchavat]

@dorHerbesman I don't see the lookup command in your search. 

index=elbit_hr sourcetype=synerionDB source=retromng 

| table ACCUM_CODE LOCK_CODE PERIOD_KEY TABLEQ UPD_DATE UPD_TIME USER_NAME

[kiran_panchavat]

@dorHerbesman Hi, you must upload the lookup first, then use the |inputlookup tableq_lookyp to check. Upon successfully viewing your lookup data, you can access Splunk by using the lookup command. And the only way to find out is to use your initial search query along with the lookup command. Kindly review the Splunk documents listed below for your reference.

lookup - Splunk Documentation