Getting Data In

multiple input fields for csv lookup

dxw350
Path Finder

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

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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.

View solution in original post

woodcock
Esteemed Legend

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.

dxw350
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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

dxw350
Path Finder

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

dxw350
Path Finder

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????

0 Karma

woodcock
Esteemed Legend

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

dxw350
Path Finder

This is a great explanation. Thank you

0 Karma

woodcock
Esteemed Legend

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

0 Karma

dxw350
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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

Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...