topic Re: checking if all values from lookup exists in Splunk Search

checking if all values from lookup exists

dorHerbesman — Wed, 02 Jul 2025 11:44:04 GMT

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 !

Re: checking if all values from lookup exists

kiran_panchavat — Tue, 05 Mar 2024 15:08:07 GMT

@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

Re: checking if all values from lookup exists

kiran_panchavat — Tue, 05 Mar 2024 15:09:13 GMT

@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

Re: checking if all values from lookup exists

gcusello — Thu, 03 Jul 2025 09:49:08 GMT

Hi @dorHerbesman,

try something like this:

Ciao.

Giuseppe

Re: checking if all values from lookup exists

dorHerbesman — Wed, 06 Mar 2024 07:02:37 GMT

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!

Re: checking if all values from lookup exists

gcusello — Thu, 03 Jul 2025 07:02:46 GMT

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

Re: checking if all values from lookup exists

dorHerbesman — Wed, 06 Mar 2024 07:33:12 GMT

that's exectly what i wanted! thanks!

Re: checking if all values from lookup exists

gcusello — Wed, 06 Mar 2024 07:38:24 GMT

Hi @dorHerbesman ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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