Match lookup field values with Splunk results and display over lookup field value yes/no

[Satheesh_red]

Hi,

The lookup field values must match the field values returned by the query, and the results must be shown as yes/no depending on whether the match happens. but we are unable to match and are unable to publish all of the information from the lookup fields in the results. Please assist.

My lookup file:-

My query:-
index = * sourcetype=* host=* | rex field=source "\/u02\/logs\/patch_(?<domain_name>.+).log"| rex field=_raw max_match=0 "\s(?<Patch_num>[^ ]+);" | dedup host | mvexpand Patch_num | lookup soa_nonprod_Q_patches.csv Patch_num | table domain_name host Patch_num patchlist | eval match_status=if(match(Patch_num,patchlist),"Yes","No")
| table domain_name host Patch_num match_status

Result output:-

18387355 value is missing in the Patch_num output and it should be 'No'  in matching_status field as thsi value is not available in the Search result field. 

Regards,
Satheesh 

[ITWhisperer]

Rather than looking up the patch_num, you could set a field to 2, append the .csv with the same field set to 1, and then sum the field by patch_num using eventstats. Where the sum is 1, the patch_num only exists in the csv.

[Satheesh_red]

Thankyou for your reply @ITWhisperer 

Would please share me the sample/reference syntax will be really helpful.

Thankyou for understanding.

Regards

Satheesh

[ITWhisperer]

Try something like this

index = * sourcetype=* host=* 
| rex field=source "\/u02\/logs\/patch_(?<domain_name>.+).log"
| rex field=_raw max_match=0 "\s(?<Patch_num>[^ ]+);" 
| dedup host 
| mvexpand Patch_num 
| eval from=2
| append [
  | inputlookup soa_nonprod_Q_patches.csv
  | eval from=1]
| eventstats sum(from) as from by Patch_num
| where isnotnull(domain_name) OR from=1  
| eval match_status=case(from==1,"Not in events",from%2==0,"Not in list",1==1,"In events and list")
| table domain_name host Patch_num match_status

[Satheesh_red]

Hi @ITWhisperer 

While performing more validations, I noticed an issue.

When I put host=* it's not giving accurate results, and if I remove host=* and checking with index=abc and source type=xyz also isn't giving correct results, any reason? The command is operating just fine with just one host every time, meaning each time we have to pass against with one host.

index=abc and source type=xyz host=abc - working fine

index=abc and source type=xyz host=* - not working

index=abc and source type=xyz - not working.

Kindly suggest.

Regards,
Satheesh 

[ITWhisperer]

Sounds like an issue with your data. You haven't provided enough detail as to why some hosts are not working.

[Satheesh_red]

Hi @ITWhisperer 

I hope below screenshot looks more helpful for you.

Regards,
Satheesh 

[ITWhisperer · Accepted solution]

index = * sourcetype=* host=* 
| rex field=source "\/u02\/logs\/patch_(?<domain_name>.+).log"
| rex field=_raw max_match=0 "\s(?<Patch_num>[^ ]+);" 
| dedup host 
| append [
  | inputlookup soa_nonprod_Q_patches.csv]
| eventstats values(Patch_num) as Patch_list
| where isnotnull(host)
| mvexpand Patch_list
| eval match_status=if(isnotnull(mvfind(Patch_num, Patch_list)), "Yes", "No")
| table domain_name host Patch_list match_status

[Satheesh_red]

Thankyou @ITWhisperer , it's working as expected. Appreciated your time and support

Regards

Satheesh

[Satheesh_red]

My apologies @ITWhisperer 

I'm looking for, matching case of Patch_num in the events for each and every host that are existed in lookup list.

Ex:- if lookup list is having 25 patch numbers row list and host A Patch_num resulted 24 in the events and host B resulted 25 in the events and host C resulted 20 events and so on for N number of hosts. The output match case should be with list 25 patches for each host.

Domain_name   Host  patchlist   Match_case

  xyz1                     ABC   Row 1 num in list  Yes/no

xyz1                  ABC  Row 2 number in list Yes/no

xyzN                ABC. .....RowN(like this for all the rows in the list,ex 25)      Yes/No

xyz2            DEF    Row1 num in list  Yes/no

xyz2           DEF.   Row1. num in list.   Yes/no

xyzN          DEF.   RowN.        Yes/No

xyz3.          GHI.   Row1 num in list.   Yes/No

xyz3.          GHI.   Row2 num in list Yes/No

Like this, Yes/No match case with Patch_num are available in the events for each host.

Each host should be having some list of patch numbers and available in Patch_num events filled.

Hope this explanation helps, please let me know if required additional details.

Thankyou for helping.

Regards,

Satheesh