Splunk Search

Loop through splunk search for multiple values

anmohan0
Explorer

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?

 

 

Labels (2)
0 Karma
1 Solution

P_vandereerden
Splunk Employee
Splunk Employee

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

Paul van der Eerden,
Breaking software for over 20 years.

View solution in original post

0 Karma

anmohan0
Explorer

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

P_vandereerden
Splunk Employee
Splunk Employee

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

Paul van der Eerden,
Breaking software for over 20 years.
0 Karma
Get Updates on the Splunk Community!

Unlock New Opportunities with Splunk Education: Explore Our Latest Courses!

At Splunk Education, we’re dedicated to providing top-tier learning experiences that cater to every skill ...

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...