topic Re: Using lookup values as input for query in Splunk Search

Using lookup values as input for query

pinVie — Tue, 29 Sep 2020 10:15:33 GMT

Hi all,

so I built this query

 search index=sey_ips src_ip=10.0.0.1 dest_ip=10.0.0.2
| eval time = _time
| sort - time
| streamstats current=f window=1 first(time) AS lastTime by src_ip, dest_ip, signature_id
| eval diff = lastTime-time
| search lastTime=*
| table _time, src_ip, dest_ip, time, lastTime, signature_id, diff
| stats stdev(diff) by src_ip, dest_ip, signature_id

If I define the IPs manually it works great, but I have a lookup file containing quite a lot of src_ip, dest_ip combination and I'd like to run this query with all the defined IPs. How would I do that? I am basically looking for something like a loop.

Thank you

Re: Using lookup values as input for query

gcusello — Fri, 15 Jul 2016 15:11:34 GMT

Use double quotes on the border of IP.
Bye.
Giuseppe

Re: Using lookup values as input for query

jkat54 — Tue, 29 Sep 2020 10:14:48 GMT

You can work the loop a bit backwards using subsearches:

search index=sey_ips [|inputlookup ip_lookup.csv| fields src_ip, dst_ip | return 0 src_ip dst_ip] 
 | eval time = _time
 | sort - time
 | streamstats current=f window=1 first(time) AS lastTime by src_ip, dest_ip, signature_id
 | eval diff = lastTime-time
 | search lastTime=*
 | table _time, src_ip, dest_ip, time, lastTime, signature_id, diff
 | stats stdev(diff) by src_ip, dest_ip, signature_id

Youll end up with a final search like this

 search index=sey_ips (src_ip=10.0.0.1 OR src_ip=10.0.0.2 OR src_ip=10.0.0.3) OR (dst_ip=10.1.0.1 OR dst_ip=10.2.0.2 OR dst_ip=10.3.0.3) | ...

To change this so that there is an AND instead of OR between the src_ips and dst_ips... you need to use format instead of return:

search index=sey_ips [|inputlookup ip_lookup.csv| fields src_ip, dst_ip |format "(" "(" "OR" ")" "AND" ")"]
 | eval time = _time
 | sort - time
 | streamstats current=f window=1 first(time) AS lastTime by src_ip, dest_ip, signature_id
 | eval diff = lastTime-time
 | search lastTime=*
 | table _time, src_ip, dest_ip, time, lastTime, signature_id, diff
 | stats stdev(diff) by src_ip, dest_ip, signature_id

https://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Format
https://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Return
http://docs.splunk.com/Documentation/Splunk/6.2.2/SearchTutorial/Useasubsearch
https://docs.splunk.com/Documentation/Splunk/6.4.1/Search/Changetheformatofsubsearchresults

Re: Using lookup values as input for query

somesoni2 — Fri, 15 Jul 2016 15:18:24 GMT

Try like this

search index=sey_ips [| inputlookup yourlookup.csv | table src_ip dest_ip]
 | eval time = _time
 | sort - time
 | streamstats current=f window=1 first(time) AS lastTime by src_ip, dest_ip, signature_id
 | eval diff = lastTime-time
 | search lastTime=*
 | table _time, src_ip, dest_ip, time, lastTime, signature_id, diff
 | stats stdev(diff) by src_ip, dest_ip, signature_id

OR (loop method, but try above one first)

| inputlookup yourlookup.csv | table src_ip dest_ip | map maxsearches=1000 search="search index=sey_ips src_ip=$src_ip$ dest_ip=$dest_ip$
 | eval time = _time
 | sort - time
 | streamstats current=f window=1 first(time) AS lastTime by src_ip, dest_ip, signature_id
 | eval diff = lastTime-time
 | search lastTime=*
 | table _time, src_ip, dest_ip, time, lastTime, signature_id, diff
 | stats stdev(diff) by src_ip, dest_ip, signature_id"

Re: Using lookup values as input for query

muebel — Fri, 15 Jul 2016 15:50:29 GMT

as mentioned in some answers below, I like using subsearches for this sort of thing.