Splunk Search

Using lookup values as input for query

pinVie
Path Finder

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

muebel
SplunkTrust
SplunkTrust

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

0 Karma

somesoni2
Revered Legend

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"

jkat54
SplunkTrust
SplunkTrust

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

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...