How to do Lookup single field comparison?

[Aleksey_18]

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 IP

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

[harsmarvania57 · Accepted solution]

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

[Aleksey_18]

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 ?

[harsmarvania57]

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

[Aleksey_18]

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

[harsmarvania57]

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

[Aleksey_18]

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.