topic Re: How to do a for loop in Splunk query? in Dashboards & Visualizations

How to do a for loop in Splunk query?

nick405060 — Tue, 29 Sep 2020 20:36:58 GMT

Hi there,

I have users of my dashboard specify integer N in a dropdown menu. In a query that has results displayed on my dashboard in a table, I'd like to outer join "mycsv_1.csv", "mycsv_2.csv", "mycsv_3.csv," ... "mycsv_N.csv" as part of that query.

The inputcsv/join syntax is easy for me, but how do I do the iteration?

Re: How to do a for loop in Splunk query?

nick405060 — Tue, 29 Sep 2020 20:37:12 GMT

I was able to implement this in a slightly convoluted way - if anyone can figure out how to do it better please let me know.

I set up a scripted input that takes in $n$ and copies mycsv_1.csv ... mycsv_n.csv to temp_1.csv ... temp_n.csv. Then, my Splunk query looks like:

| script MyPythonScript $n$ | {attemps to outer join temp_1.csv, temp_2.csv, temp_3.csv, ...temp_MAX.csv (I used 20 as MAX), and since the temp files that are between $n$ and MAX were never copied over by the Python script, they don't exist and aren't joined. Only mycsv_1 to mycsv_N.csv are joined}

Python code (the scenario this code addresses is slightly different than the scenario described in the initial question. Using dates in csv filenames instead of integers):

# Takes an integer N and then saves 'C:\Program Files\Splunk\var\run\splunk\ABC_machines_pings-yyyy-mm-dd.csv' files
# (where yyyy-mm-dd is 0...N days before now()) as temp_(0...N).csv files

# Since you can't iterate in a Splunk query (ideally a user would specify N and we could inputcsv ABC_machines_pings_0...N only), I
# instead created this script to just copy and rename files 0...N so that selecting temp_* in a Splunk query will give csv files 0...N only

from datetime import datetime, timedelta
import os
import sys

n = int(sys.argv[1])
dir='C:\\Program Files\\Splunk\\var\\run\\splunk\\'

# Removes former temp_n files
for file in os.listdir(dir):
    if 'temp_' in file:
        os.remove(dir+file)

# Creates new temp_n files
for i in range(n):
    path = dir + 'ABC_machines_pings-' + (datetime.today() - timedelta(days=i)).strftime('%Y-%m-%d') + '.csv'
    os.system('copy \"' + path + '\" \"' + dir + 'temp_' + str(i) + '.csv\"')

Re: How to do a for loop in Splunk query?

woodcock — Thu, 26 Jul 2018 23:47:40 GMT

Like this:

| makeresults
| eval count=$YourIndexValueTokenNameHere$
| eval filename=mvrange(1,count+1, 1)
| mvexpand filename
| rex field=filename mode=sed "s/(.*)/mycsv_\1.csv/"
| map maxsearches=99 search="| inputlookup $filename$"

You can test by running this in your search bar and using | eval count = 11

Re: How to do a for loop in Splunk query?

nick405060 — Fri, 27 Jul 2018 20:14:54 GMT

That "2" on line 3 needs to be a 1 and you need to use mvexpand. But, besides that thank you for this approach! Here is my code (note that it is for a slight variation on the question):

| makeresults 
| eval count=$n$ 
| eval count=mvrange(1,count+1, 1) 
| mvexpand count 
| eval filename=strftime(now()-count*86400,"abc_machines_pings-%Y-%m-%d.csv")
| map maxsearches=28 search="| inputcsv $filename$ | where pingdelay != 10000000  and pingdelay != \"\""

Re: How to do a for loop in Splunk query?

woodcock — Fri, 27 Jul 2018 21:14:37 GMT

Quite right; I pasted wrong version. I re-edited and fixed it.