topic Re: multiple input fields for csv lookup in Getting Data In

multiple input fields for csv lookup

dxw350 — Tue, 29 Sep 2020 14:33:58 GMT

if I have a src_ip in a juniper sourcetype and want to match it to a HOST in the csv file and also to a SERVER in the same csv file, is there a way to do that? Here is what I have so far

|lookup data_file HOST as src_ip OR SERVER as src_ip Output src_ip destzone srczone

Re: multiple input fields for csv lookup

woodcock — Thu, 22 Jun 2017 20:10:29 GMT

Do it twice, like this:

|lookup data_file HOST as src_ip Output src_ip destzone srczone
|lookup data_file SERVER as src_ip Output src_ip destzone srczone

Whichever one works last, wins.

Re: multiple input fields for csv lookup

dxw350 — Thu, 22 Jun 2017 22:28:13 GMT

Thanks for the reply. I didn't understand what you meant by "which ever one works last, wins". I need both sets of input in the results as separate src-ip rows. Also, it seems this is doubling the search processsing. Is there a conditional " OR" that can be used as an input portion the the |lookup????

Re: multiple input fields for csv lookup

woodcock — Fri, 23 Jun 2017 08:29:53 GMT

If you need "both sets" then you do not need "OR", you need "AND". My solution assumes that EITHER HOST OR SERVER will match. The comment "whichever matches last wins" means that in the odd case where BOTH match, whichever one matches last will overwrite the existing values (from the previous match). You can control "which one wins" either by swapping the order or by swapping OUTPUTNEW for OUTPUT because the difference between OUTPUT and OUTPUTNEW is if the output field already exists in your event, OUTPUT will overwrite it and OUTPUTNEW won't.

If you really need AND and you need multivalued output fields, then you can do that like this:

| lookup data_file HOST as src_ip Output src_ip destzone srczone
| lookup data_file SERVER as src_ip Output src_ip2 destzone2 srczone2
| eval src_ip=case(isnull(src_ip), src_ip2,
                   isnull(src_ip 2), src_ip,
                   true(), src_ip . "," src_ip2)
| eval src_ip2=null()
| makemv delim="," src_ip
| eval destzone=case(isnull(destzone), destzone2,
                     isnull(destzone2), destzone,
                     true(), destzone . "," destzone2)
| eval destzone2=null()
| makemv delim="," destzone
| eval srczone=case(isnull(srczone), srczone2,
                    isnull(srczone2), srczone,
                    true(), srczone. "," srczone2)
| eval srczone2=null()
| makemv delim="," srczone

Re: multiple input fields for csv lookup

dxw350 — Fri, 23 Jun 2017 12:21:18 GMT

This is a great explanation. Thank you

Re: multiple input fields for csv lookup

woodcock — Fri, 23 Jun 2017 13:09:42 GMT

Don't forget to up-vote and/or click Accept to close the question.

Re: multiple input fields for csv lookup

dxw350 — Fri, 23 Jun 2017 18:04:29 GMT

Thanks for all your help. I tried to give you points, but don't have any yet. I also tried your code and I guess I wasn't too clear on what I exactly needed, although we are close. MY ISSUE: There are always entries for both Host and Server, but if the Server entry is not listed as a separate row in the HOST column I need to add it as a reciprocal row. Is there a way to do that with |outputnew ?
Example:
HOST SERVER
192.168..1.1 192.168.2.10
192.168.2.10 192.168.1.1 This pair is good

192.168.1.3 192.168.2.11
Missing reciprocal This row needs the reverse

Re: multiple input fields for csv lookup

woodcock — Fri, 23 Jun 2017 18:20:51 GMT

It costs you no Karma to Up-Vote or Accept an answer to your question. We all try to help the newbies get around and earn Karma (you get points for Accept, you don't lose them). Watch for my new answer in a bit...

Re: multiple input fields for csv lookup

dxw350 — Fri, 23 Jun 2017 21:43:33 GMT

Yes i accepted what is up-vote???? Also with respect to my issue I think that another reason the reciprocal inputnew lookup is not being populated is because it is not part of the same application dropdown list that I used to filter. (Is there a way to include reciprocL nex t row entry eventhough it is not part of the same dropdown group

For completion example

Host Server dest_zone Appl
192.168.1.1 192.168.10.11 dmz Word
192.168.10.11. 192.168.1.1 Trust. Word
(This pair was good)

192.168.1.2 192.168.10.12 Dmz. Word
But. 192.168.10.12. Not part of word app yet still want the pair as below (the below is missing)
192.168.10.12. 192.168.1.2. Trust. Excel

Re: multiple input fields for csv lookup

woodcock — Sat, 24 Jun 2017 15:57:21 GMT

Up-Vote is clicking the "^" character to the left of an answer above the counter and also in the header of a comment.

Does something like this help?

| makeresults
| eval raw="192.168.1.1 192.168.2.10::192.168.2.10 192.168.1.1::192.168.1.3 192.168.2.11"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<HOST>\S+)\s+(?<SERVER>.*)$"

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval IPs=mvsort(mvappend(HOST, SERVER))
| nomv IPs
| rex field=IPs mode=sed "s/\s+/::/g"
| stats count by IPs
| makemv delim="::" IPs
| lookup data_file IPs as src_ip Output src_ip destzone srczone

Re: multiple input fields for csv lookup

dxw350 — Mon, 26 Jun 2017 11:53:59 GMT

thank you. I will try today and report the results. Your help has been very valuable and I am learning the flexibility and approach.