Splunk Search
Highlighted

How to compare a search result with a lookup?

New Member

Hi,

I have to get a result which is not in the lookup file. In the lookup, I have TIME and IPPN. In the search result, I get the output CURRENTTIME, IPPN. Now I need to get the output result of IP_PN which is not in the lookup. Can you let me know how to do it?

Search I'm using:

index=123|....|rename  _time as currenttime|join IP_PN [|alert.csv] 

Result:

currenttime   IP_PN            time
1465074992.23   10.1.2.3-22   1464988380.57
1465074992.23   10.22.1.1-44e    1464988380.57
Tags (3)
0 Karma
Highlighted

Re: How to compare a search result with a lookup?

Legend

Try this

index=123 NOT [inputlookup alert.csv | table IP_PN] | table CURENTTIME, IP_PN
Highlighted

Re: How to compare a search result with a lookup?

New Member

I tried, i'm getting the results, which are in lookup table.

0 Karma
Highlighted

Re: How to compare a search result with a lookup?

SplunkTrust
SplunkTrust

Post your full search that you tried.

0 Karma
Highlighted

Re: How to compare a search result with a lookup?

New Member
index=main source=qualys IP=10.21.16.195 |rex max_match=0 "UDP_PORT=(?\d+)|TCP_PORT=(?\d+)"|search (PORT=* OR TCP_PORT=* OR UDP_PORT=*)|eval pn=(toString(PORT) + ";" + toString(TCP_PORT) + ";" + toString(UDP_PORT))| makemv delim=";" pn|mvexpand pn |where pn!="Null"|makemv pn|mvexpand pn|eval IP_PN=(toString(IP) + "_" + toString(pn))|dedup IP_PN|rename _time as currenttime| search NOT [|inputlookup qualys_alert.csv | table IP_PN] | table currenttime IP_PN
0 Karma
Highlighted

Re: How to compare a search result with a lookup?

Legend

Try this

index=main source=qualys IP=10.21.16.195 |rex max_match=0 "UDP_PORT=(?\d+)|TCP_PORT=(?\d+)"|search (PORT= OR TCP_PORT= OR UDP_PORT=*)| eval z=mvzip(pn, mvzip(UDP_PORT, TCP_PORT) ) | mvexpand z |where pn!="Null"|eval IP_PN=(toString(IP) + "_" + toString(pn))|dedup IP_PN|rename _time as currenttime| search NOT [|inputlookup qualys_alert.csv | table IP_PN] | table currenttime IP_PN
0 Karma
Highlighted

Re: How to compare a search result with a lookup?

SplunkTrust
SplunkTrust

Is the format of values of field IP_PN same in both your search results and lookup? Lookup is case sensitive (in case your values contain alphabets)

0 Karma
Highlighted

Re: How to compare a search result with a lookup?

Explorer

index=123 |lookup alert.csv IPPN OUTPUTNEW IPPN AS status|search NOT status=*|table CURRENTTIME,IP_PN

0 Karma
Highlighted

Re: How to compare a search result with a lookup?

Contributor
index=main source=qualys |rex max_match=0 "UDP_PORT=(?\d+)|TCP_PORT=(?\d+)"|search (PORT=* OR TCP_PORT=* OR UDP_PORT=*)|eval pn=(toString(PORT) + ";" + toString(TCP_PORT) + ";" + toString(UDP_PORT))| makemv delim=";" pn|mvexpand pn |where pn!="Null"|makemv pn|mvexpand pn|eval IP_PN=(toString(IP) + "_" + toString(pn))|dedup IP_PN|rename _time as currenttime| |lookup qualys_alert.csv IP_PN OUTPUTNEW IP_PN AS status|search NOT status=*| table currenttime IP_PN

The above should work.

View solution in original post

0 Karma