topic Re: Display Values not found in search results but is in lookup table in Splunk Search

Why are Display Values not found in search results but is in lookup table?

nandhiniG — Fri, 05 Aug 2022 14:30:41 GMT

I Have a look up file called dataset.csv which will have one field,

dataset_name

dataset1

dataset2

dataset3

I need to display the dataset_names in a table which are not present in the search results but present in lookup , i use the below query to retrieve all the datasets . but using lookup it does not show the missing names.

QUERY :

I want the results like,how can i achieve this

DatasetName	FilesMoved	Time
dataset1	YES
dataset2	NO

Re: Display Values not found in search results but is in lookup table

kamlesh_vaghela — Wed, 01 Sep 2021 15:43:55 GMT

@nandhiniG

Try something

index = dataIntegration source=piedpiper lambda_events | search event.proc_stat_cd = "SCSS" AND event.s3_location = "*"s3:*//lake/depositescrow*"*" AND "event.module_name"=EMR | rename event.dataset_id as "Id" | rename event.regrd_dataset_nm as dataset_name | rename event.proc_ts as "DateTime" | table "Id" , "Name","DateTime" | lookup dataset.csv dataset_name output dataset_name as dataset_name_lp | eval FilesMoved = if(isnull(dataset_name_lp),"NO","YES")

Re: Display Values not found in search results but is in lookup table

nandhiniG — Thu, 02 Sep 2021 09:20:11 GMT

It is not working , as expected.

It displays the matching search results - which means the files are moved .But not displaying the files missed .

ex: i have two datasets --> first.dat ,second.dat

lets assume first.dat is the only file processed,and the splunk search results have only one log

datasetId	dataset_name	FilesMoved
1	first.dat	YES
2	second.dat	NO

Re: Display Values not found in search results but is in lookup table

kamlesh_vaghela — Thu, 02 Sep 2021 16:35:34 GMT

@nandhiniG

Can you please try this?

index = dataIntegration source=piedpiper lambda_events | search event.proc_stat_cd = "SCSS" AND event.s3_location = "*"s3:*//lake/depositescrow*"*" AND "event.module_name"=EMR | rename event.dataset_id as "Id" | rename event.regrd_dataset_nm as dataset_name | rename event.proc_ts as "DateTime" | table "Id" , "Name","DateTime" | rename Name as dataset_name | append [| inputlookup dataset.csv] | stats count values(*) as * by dataset_name | eval FilesMoved = if(count==1,"NO","YES")

My Sample Search :

Re: Display Values not found in search results but is in lookup table

PickleRick — Thu, 02 Sep 2021 18:37:40 GMT

Well... you can use |set diff to check for the values not present in a common subset of two searches https://docs.splunk.com/Documentation/Splunk/8.2.1/SearchReference/Set

Unfortunately it doesn't show which search it is from.

So in order to compare two sets I usually use a little trick

<search one> | eval setsource=1 | append [ <search two> | eval setsource=2 ] | stats sum(setsource) by <all important fields>

This way in the resulting table you have a setsource field showing whether the line comes from first search (1), second (2) or is the line included in both results (3).

Re: Display Values not found in search results but is in lookup table

nandhiniG — Fri, 03 Sep 2021 09:25:03 GMT

Anyway , i tried to add the input lookup first like below and retrieved the missing dataset names only. and it worked .

| inputlookup data.csv | fields "dataset_name" | search NOT [search index = asvdataintegration source=piedpiper sts_asvdataintegration_symphony_lambda_clewriter_events | search event.proc_stat_cd = "SCSS" OR event.proc_stat_cd = "FAIL" AND event.s3_location = "*"s3://cof-*/"*"/lake/depositescrow*"*" AND "event.module_name"=EMR | rename event.regrd_dataset_nm as dataset_name | table dataset_name | format]

Re: Display Values not found in search results but is in lookup table

Taruchit — Fri, 05 Aug 2022 12:56:07 GMT

Hi @nandhiniG,

Thank you for sharing your inputs and the SPL that helped you get the results which are present in lookup table and not in search results.

I also have a similar usecase, and I tried following your approach, but I am still getting the outputs which are present in lookup table and in search results.

Thus, please help by sharing your inputs to correct the SPL.

Thank you