topic Re: Loop through splunk search for multiple values in Splunk Search

Loop through splunk search for multiple values

anmohan0 — Tue, 16 Jul 2024 21:59:50 GMT

I want to get the below search executed and display the results in a table for all comma separated values that gets passed from dropdown.

index="xxx" source = "yyyyzzz" AND $DropdownValue$ AND Input| eventstats max(_time) as maxTimestamp by desc| head 1 | dedup _time | eval lastTriggered = strftime(_time, "%d/%m/%Y %H:%M:%S %Z")| stats values(lastTriggered) as lastTriggeredTime| appendcols [search index="xxx" source = "yyyyzzz" sourcetype = "mule:rtf:per:logs" AND $DropdownValue$ AND Output| eventstats max(_time) as maxTimestamp by desc| head 1 | dedup_time | eval lastProcessed = strftime(_time, "%d/%m/%Y %H:%M:%S %Z")| stats values(lastProcessed) as lastProcessedTime] | appendcols [search index="xxx" source = "yyyyzzz" sourcetype = "mule:rtf:per:logs" AND $DropdownValue$ AND Error| eventstats max(_time) as maxTimestamp by desc| head 1 | dedup_time | eval lastErrored = strftime(_time, "%d/%m/%Y %H:%M:%S %Z")]|eval "COMPONENT ID"="$DropdownValue$"|eval "Last Triggered Time"=lastTriggeredTime |eval "Last Processed Time"=lastProcessedTime| eval "Last Errored Time"=lastErrored | table "COMPONENT ID", "Last Triggered Time", "Last Processed Time","Last Errored Time" | fillnull value="NOT IN LAST 12 HOURS" "COMPONENT ID","Last Triggered Time", "Last Processed Time","Last Errored Time"

For example if $dropdownValue$ is having ABC,DEV, then the entire above mentioned search should get executed twice and 2 rows od data should be displayed in the table. Can someone guide how this can be achieved?

Re: Loop through splunk search for multiple values

P_vandereerden — Tue, 16 Jul 2024 20:30:47 GMT

This part suggests that the dropdown selections are values in the COMPONENT ID field:
"COMPONENT ID"="$DropdownValue$"

If that's the case, you could filter based on
"COMPONENT ID" IN ($DropdownValue$)
and join the subsearches on COMPONENT ID rather than appending columns.

Joins are not particularly efficient, so instead of that suggestion, I would look at pulling all the data from that index back in a single search, and conditionally evaluating the stats.

Paul

Re: Loop through splunk search for multiple values

yuanliu — Thu, 18 Jul 2024 05:30:57 GMT

Like @P_vandereerden says, SPL is totally different from procedural languages. You need to think differently. One point is: explicit iteration should be used sparsely. There are also lots of other elements in the illustrated code that make it "unSPL" and some unnecessary.

For a problem like this, it is better to follow my four golden rules ("four commandments") of asking answerable questions.

To ask an answerable data analytics question, follow these golden rules; nay, call them the four commandments:

Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search that volunteers here do not have to look at.
Illustrate the desired output from illustrated data.
Explain the logic between illustrated data and desired output without SPL.
If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious.

In your case, you also want to illustrate how desired output change when the token takes different values. One more tip: Use Splunk's auto format feature to format SPL if there are more than a couple pipes. Like this:

index="xxx" source = "yyyyzzz" AND $DropdownValue$ AND Input | eventstats max(_time) as maxTimestamp by desc | head 1 | dedup _time | eval lastTriggered = strftime(_time, "%d/%m/%Y %H:%M:%S %Z") | stats values(lastTriggered) as lastTriggeredTime | appendcols [search index="xxx" source = "yyyyzzz" sourcetype = "mule:rtf:per:logs" AND $DropdownValue$ AND Output | eventstats max(_time) as maxTimestamp by desc | head 1 | dedup _time | eval lastProcessed = strftime(_time, "%d/%m/%Y %H:%M:%S %Z") | stats values(lastProcessed) as lastProcessedTime] | appendcols [search index="xxx" source = "yyyyzzz" sourcetype = "mule:rtf:per:logs" AND $DropdownValue$ AND Error | eventstats max(_time) as maxTimestamp by desc | head 1 | dedup_time | eval lastErrored = strftime(_time, "%d/%m/%Y %H:%M:%S %Z")] | eval "COMPONENT ID"="$DropdownValue$" | eval "Last Triggered Time"=lastTriggeredTime | eval "Last Processed Time"=lastProcessedTime | eval "Last Errored Time"=lastErrored | table "COMPONENT ID", "Last Triggered Time", "Last Processed Time","Last Errored Time" | fillnull value="NOT IN LAST 12 HOURS" "COMPONENT ID","Last Triggered Time", "Last Processed Time","Last Errored Time"

After this formating, you can easily see why some commands are wasteful.

Re: Loop through splunk search for multiple values

anmohan0 — Mon, 20 Jan 2025 06:00:39 GMT

Thanks @P_vandereerden and it worked as the way I wanted.