Knowledge Management

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

Satheesh_red
Path Finder

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:-

Satheesh_red_0-1687971460236.png

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:-

Satheesh_red_1-1687972152290.png

 

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 

Labels (4)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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
Path Finder

Thankyou for your reply @ITWhisperer 

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

Thankyou for understanding.

 

 

Regards

Satheesh

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Path Finder

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 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

Satheesh_red
Path Finder

Hi @ITWhisperer 

I hope below screenshot looks more helpful for you.

 

Satheesh_red_0-1688099311326.png

 

Regards,
Satheesh 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
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
Path Finder

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

 

 

Regards

Satheesh

0 Karma

Satheesh_red
Path Finder

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

 

 

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...