topic Complex lookup table question in Splunk Search

Complex lookup table question

hartfoml — Fri, 23 Aug 2013 20:45:38 GMT

I have a lookup table with two values in the lookup table that I want to use in the end report.

Example: (table with and ip,source) (xxx.xxx.xxx.xxx,incident report)

I want to look at my events and find the IP in the events and then so the event data with the source from the table in the report. this way the report viewer will know what the offending IP and how the IP was added to the table.

Example Results

srcIP dstIP srcPORT dstPORT source x.x.x.x y.y.y.y 5700 80 incident report

I have this type of search that is working:

index="my_summary" [| inputlookup lookup.csv | rename ip as query | fields query ] | top 20 srcIP dstIP srcPORT dstPORT

this search does not work:

index="my_summary" [| inputlookup lookup.csv | rename ip as query | fields query source ] | top 20 srcIP dstIP srcPORT dstPORT source

how do I do a lookup for the ip and tie the ip the the source from the lookup table?
should I be using a join statement rather than a lookup?

Re: Complex lookup table question

linu1988 — Fri, 23 Aug 2013 21:24:57 GMT

index="my_summary" source[| inputlookup lookup.csv | fields source ]

or you should give it a go with proper lookup definition

props.conf
[src]
Lookup-s=Source_Lkp source as srcIP dstIP srcPORT dstPORT

transforms.conf
[Source_Lkp]
filename= lookup.csv

assuming you have source in your eventdata

Re: Complex lookup table question

hartfoml — Mon, 26 Aug 2013 13:52:13 GMT

linu, sorry the source is only in the look-up table

I have an IP and I know where the IP came from.
I want to look for that IP in my events then display a table of the event data. I also want to know where I got the IP i was looking for, (the source) in the table. that way people looking at the report would know why I was looking for this particular IP.

I know how to search events for the IP from the lookup but then how to pass the source to the events so that I can use it in the report on those events I don't know?

Re: Complex lookup table question

linu1988 — Mon, 26 Aug 2013 14:49:07 GMT

It's the same i guess,

change my lookup definition

Lookup-s=Source_Lkp srcIP as source

You can't replace the ip by source in the original event, but you can format and show it in a table

...|Table srcIP dstIP srcPORT dstPORT source|...

Re: Complex lookup table question

hcanivel — Tue, 27 Aug 2013 00:40:52 GMT

Lucky for you, this is a simple syntax question. A join won't work unless the fieldnames match.

Familiarize yourself with the fundamentals of subsearching:
http://docs.splunk.com/Documentation/Splunk/latest/Tutorial/Useasubsearch

and lookups:
http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Addfieldsfromexternaldatasources

Anytime you do a lookup or a subsearch, in order to match/filter, your fields have to match.

Your main search is as follows:

index="my_summary" [| inputlookup lookup.csv | rename ip as query | fields query source ]

In this example, you've queried Splunk, "given values for the fields 'query' and 'source' in lookup table 'lookup.csv,' find me results in index 'my_summary.'"

This is your problem. Since you're looking to filter against any of the following fields srcIP, dstIP, srcPORT, dstPORT, source, those are fields that need to homogenize between your lookup table and event data. Those fields may only exist in your event data, but not your lookup, which is why you renamed the ip field.

Are you looking to match resultant data with a lookup or use the lookup to look for only that matches? Subtle, but distinct difference for your search, depending on size of data we're talking about here.

You can get very similar data either way, but I'll let you decide which between the chicken or egg comes first.

Try something like this:

index="my_summary" | lookup lookup.csv ip as srcIP | top 20 srcIP dstIP srcPORT dstPORT

Re: Complex lookup table question

hartfoml — Thu, 29 Aug 2013 15:16:48 GMT

Thanks for the help I have a different issue with the lookup table. I have two values that I want to pass from the lookup table to the search. the first value "ip" to do a sub-search of the events then the second value "source" to apend to the events found with the "ip" value.

I hope this makes sense?