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!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...