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!

Security Professional: Sharpen Your Defenses with These .conf25 Sessions

Sooooooooooo, guess what. .conf25 is almost here, and if you're on the Security Learning Path, this is your ...

First Steps with Splunk SOAR

Our first step was to gather a list of the playbooks we wanted and to sort them by priority.  Once this list ...

How To Build a Self-Service Observability Practice with Splunk Observability Cloud

If you’ve read our previous post on self-service observability, you already know what it is and why it ...