topic Re: Looping in Splunk... in Splunk Search

Looping in Splunk...

sjringo — Thu, 07 Oct 2021 12:04:32 GMT

Trying to figure out how to loop in Splunk. I have the below query and my end result is to map/chart into a timechart by the percentage over _time.

index=anIndex sourcetype=aSource StringA earliest=-480m latest=-240m | stats count as A

| appendcols [search index=anIndex sourcetype=aSouce StringB earliest=-480m latest=-240m | stats count as B ]

| eval _time = relative_time(now(), "-240m@m")

| eval percentage = round(( A / B) * 100)

| fields + _time, percentage

Variables that need to change with each loop.

Lets assume I want to show percentage starting from 4 hour in the past to the current time by 30 minute increments.

1) Index: the earliest and latest need to increment by +30 minutes starting at (latest=-480, earliest = -240) till I get to 0

2) _time will need to be relative to when I start (beginning @ time now(), -240) and be adjusted on each loop by + 30 mins till I get to 0

I have looked at many examples but do not understand how to apply it to my requirements...

Re: Looping in Splunk...

ITWhisperer — Thu, 07 Oct 2021 12:39:22 GMT

Does this work for you?

Re: Looping in Splunk...

PickleRick — Thu, 07 Oct 2021 12:42:51 GMT

It's again the https://community.splunk.com/t5/Splunk-Search/Timechart-of-a-percentage-using-data-from-X-hours-ago/m-p/567890#M197912 topic? 🙂

I still think you're not telling us what you want to achieve but what you're trying to force splunk to do.

I understand that you want to calculate some stats based on how many times StringA appears in events with sourcetype=A and how many times StringB appears in events with sourcetype=B.

But what is the desired result. Tell us what is this supposed to represent.

Re: Looping in Splunk...

richgalloway — Thu, 07 Oct 2021 12:43:32 GMT

SPL is not a procedural language so we have to think a little differently to get the job done. Fortunately, it can do looping for us.

index=anIndex sourcetype=aSource (StringA OR StringB) earliest=-480m latest=-240m | bucket span=30m _time | stats sum(eval(like(_raw, "%StringA%"))) as A, sum(eval(like(_raw, "%StringB%"))) as B by _time | eval percentage = round(( A / B) * 100) | fields _time, percentage

Re: Looping in Splunk...

sjringo — Thu, 07 Oct 2021 13:10:31 GMT

I added ( | bin _time span=30m) and the results were one percentage calculation. What I am looking for is the percentage calculation over time . I am looking for results like this, lets assume we run the query at 4 AM. Starting @ midnight then moving forward till we get till 4 AM.

_time Percentage earliest latest

2021-10-07 00:00:00 P1 -480 -240

2021-10-07 00:30:00 P2 -450 -210

2021-10-07 01:00:00 P3 -420 -180

2021-10-07 04:00:00 PX -240 -0

Then I would use the timechart on ( _time, Percentage) which would show me how the percentage moves up/down every 30 mins from midnight till 4 AM.

Re: Looping in Splunk...

ITWhisperer — Thu, 07 Oct 2021 13:17:36 GMT

Sorry I missed the by _time out

Re: Looping in Splunk...

sjringo — Thu, 07 Oct 2021 13:27:30 GMT

Pseudo code, mixture of Java and SPL:

int aSpan = 240; <- 4 hours

for (int anInt = -510; anInt > 0; anInt -30) {

index=anIndex sourcetype=aSourceType StringA earliest=-(anInt)m latest=-(anInt+aSpan)m | stats count as A

| appendcols [search index=anIndex sourcetype=aSourceType StringB -(anInt)m latest=-(anInt+aSpan)m | stats count as B ]

| eval _time = relative_time(now(), "-(anInt)m@m")

| eval percentage = round((A / B) * 100)

| + fields _time, percentage

}

Re: Looping in Splunk...

sjringo — Thu, 07 Oct 2021 14:01:00 GMT

Much, Much better... One thing is that _time is the same for each percentage result, which makes sense since the _time eval is:

| eval _time = relative_time(now(), "-240m@m")

How would I make it move in 30 minute intervals which is the bin span ?

Re: Looping in Splunk...

ITWhisperer — Thu, 07 Oct 2021 14:05:41 GMT

Try removing that line

Re: Looping in Splunk...

sjringo — Thu, 07 Oct 2021 14:14:18 GMT

That did the trick. Once again thanks for your help!

My procedural brain was just not seeing the problem correctly...

Re: Looping in Splunk...

ITWhisperer — Thu, 07 Oct 2021 16:50:14 GMT

Yes, SPL is not procedural, although there are ways to do loops of a sort, but they wouldn't have helped in your case.