How to compare a search result with a lookup?

[kranthi851]

Hi,

I have to get a result which is not in the lookup file. In the lookup, I have TIME and IP_PN. In the search result, I get the output CURRENTTIME, IP_PN. 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

[jensonthottian · Accepted solution]

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.

[sameera123]

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

[sundareshr]

Try this

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

[kranthi851]

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

[somesoni2]

Post your full search that you tried.

[kranthi851]

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

[somesoni2]

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)

[sundareshr]

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