Splunk Search

How to do Lookup single field comparison?

Aleksey_18
New Member

I apologize for the banal question on the lookup.
Not so long ago, I began to learn how to filter events by lists through lookup.
The task of comparing a couple of fields )) but it is not clear how to solve.

There is a search (input restype), the result of which gives a JSON format event with a field ( result{} ) containing IP.
This field should be compared with the list lookup ip ( blacklist_get ) that I have already created.
The result of the query should be output IP that are not in the list blacklist_get .
Attached a screenshot with the events of this field with IPalt text

In the query itself, I do a conversion with a field result{} as it contains (JSON) many values, then I give this field a new name IP.
The request works correctly, but how to filter through the lists is unclear.

index="main" sourcetype=.........
| spath input=result{} | mvexpand result{}
| rename result{} as IP
0 Karma
1 Solution

harsmarvania57
SplunkTrust
SplunkTrust

Hi,

Here I am assuming that you have column header ip in your blacklist_get.csv file. In that case try below query

index="main" sourcetype=.........
| spath input=result{} | mvexpand result{}
| rename result{} as IP
| lookup blacklist_get.csv ip as IP OUTPUT ip as lookup_ip
| where isnull(lookup_ip)
| table IP

View solution in original post

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

Hi,

Here I am assuming that you have column header ip in your blacklist_get.csv file. In that case try below query

index="main" sourcetype=.........
| spath input=result{} | mvexpand result{}
| rename result{} as IP
| lookup blacklist_get.csv ip as IP OUTPUT ip as lookup_ip
| where isnull(lookup_ip)
| table IP
0 Karma

Aleksey_18
New Member

Hi harsmarvania57

Thanks for the answer )

This is an imaginary ip field from the list blacklist_get.csv = Column1
Column1 ))
Yes, I did not rename anything when I created the list ))

index="main" sourcetype=......
| spath input=result{} | mvexpand result{}
| rename result{} as IP
| lookup blacklist_get.csv Column1 as IP OUTPUT Column1 as Column1
| where isnull(Column1)
| table IP

I did not think that after OUTPUT you need to specify the same field = Column1 as Column1

Tell me how to expand the query so that this new IP one is added to the same sheet blacklist_get ?

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

If you want to append these IP into blacklist_get.csv then use below query

index="main" sourcetype=......
| spath input=result{} | mvexpand result{}
| rename result{} as IP
| lookup blacklist_get.csv Column1 as IP OUTPUT Column1 as Column1
| where isnull(Column1)
| table IP
| outputlookup append=t blacklist_get.csv
0 Karma

Aleksey_18
New Member

Hi @harsmarvania57

thanks again

Tell me what's wrong with me again.

index="main" sourcetype="..........."
| spath input=result{} | mvexpand result{}
| rename result{} as IP
| lookup blacklist_get Column1 as IP OUTPUT Column1 as Column1
| where isnull(Column1)
| table IP
| outputlookup append=true blacklist_get

An error occurs

Error in 'outputlookup' command: Could not append to file 'blacklist_get': Cannot append to file because none of the fields match.

I tried to determine the field Column1

| fields Column1 | outputlookup append=true blacklist_get
Also does not work

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

Ah my bad, try below query

index="main" sourcetype=......
| spath input=result{} | mvexpand result{}
| rename result{} as IP
| lookup blacklist_get.csv Column1 as IP OUTPUT Column1 as lkp_Column1
| where isnull(lkp_Column1)
| rename IP as Column1
| table Column1
| outputlookup append=t blacklist_get.csv
0 Karma

Aleksey_18
New Member

hi @harsmarvania57
Tell me, how will the team overwrite the list when getting a new value?
A new IP identified and listed in the list of overwriting the previous data in the list to this.

0 Karma
Get Updates on the Splunk Community!

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...