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!

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, ...

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...