topic Re: Timechart duration, with values rolling into next span in Splunk Search

Timechart duration, with values rolling into next span

ALXWBR — Wed, 24 Jul 2019 12:19:38 GMT

I am running the below search to get a sum of starvation per 15 minute period. The problem I am having, is that duration is always attributed to the start time of the event; So if the starvation runs over more than one 15 minutes period, it's still attributing it back to the start time-slice. Ideally I need it to roll over seconds into the next span if they exceed 900 seconds.

Here is the search:

index=idx_sems source="sems_north" sourcetype=SEMSSmartLaneEvents Tag="XRY_STVD" EquipmentID=3
| transaction EquipmentID  startswith=TagBitData="1" endswith=TagBitData="0"
| timechart span=15m sum(duration) as total

Here is an example of the result

_time                  sum
2019-07-22 02:15:00 98.893
2019-07-22 02:30:00 937.92
2019-07-22 02:45:00 1009.674
2019-07-22 03:00:00 2593.638
2019-07-22 03:15:00  
2019-07-22 03:30:00  
2019-07-22 03:45:00 706.153

I need to to show a maximum of 900 in the sum and roll down any difference.

Re: Timechart duration, with values rolling into next span

gcusello — Wed, 24 Jul 2019 12:48:55 GMT

Hi ALXWBR,
did you already seen the option "keeporphans" of transaction command (at https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Transaction )?

Bye.
Giuseppe

Re: Timechart duration, with values rolling into next span

ALXWBR — Wed, 24 Jul 2019 12:54:32 GMT

Hi Giuseppe

There are no orphans, there's only ever a start and an end event which is very consistent.

Re: Timechart duration, with values rolling into next span

dmarling — Wed, 24 Jul 2019 14:14:16 GMT

You can use streamstats to carryover the overflow to the next 15 minute bucket and then perform some evals to make sure you are not overflowing the next one. The below query is making the assumption that your overflow won't exceed two 15 minute buckets. If that happens you'll need to add another streamstats and round of adjusted totals/overflows. Hopefully there's a better way to do this, but this is the only way I could think of. Here's a run anywhere example using the sample data you provided:

| makeresults count=1 
| eval data=" 2019-07-22 02:15:00    98.893
 2019-07-22 02:30:00    937.92
 2019-07-22 02:45:00    1009.674
 2019-07-22 03:00:00    2593.638
 2019-07-22 03:15:00     
 2019-07-22 03:30:00     
 2019-07-22 03:45:00    706.153" 
| rex max_match=0 field=data "(?<data>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}[^\n]+)" 
| mvexpand data 
| rex field=data "(?<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})" 
| rex field=data "(?<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\s+(?<total>[^\s]+)" 
| eval _time=strptime(time, "%Y-%m-%d %H:%M:%S") 
| fields - time data
| eval overflow=if(total>900, total-900, null())
| streamstats window=1 current=false values(overflow) as previousoverflow
| eval adjustedtotal=coalesce(previousoverflow,0)+coalesce(total, 0)
| eval adjustedoverflow=if(adjustedtotal>900, adjustedtotal-900, 0)
| streamstats window=1 current=false values(adjustedoverflow) as previousadjustedoverflow
| eval finalTotal=case(adjustedtotal=0, previousadjustedoverflow, adjustedtotal>900, 900, NOT adjustedtotal=0 AND NOT adjustedtotal>900, adjustedtotal)
| rename total as oldTotal
| table _time oldTotal overflow previousoverflow adjustedtotal adjustedoverflow  previousadjustedoverflow finalTotal

Here's it with the query that you provided:

index=idx_sems source="sems_north" sourcetype=SEMSSmartLaneEvents Tag="XRY_STVD" EquipmentID=3 
| transaction EquipmentID startswith=TagBitData="1" endswith=TagBitData="0" 
| timechart span=15m sum(duration) as total 
| eval overflow=if(total>900, total-900, null()) 
| streamstats window=1 current=false values(overflow) as previousoverflow 
| eval adjustedtotal=coalesce(previousoverflow,0)+coalesce(total, 0) 
| eval adjustedoverflow=if(adjustedtotal>900, adjustedtotal-900, 0) 
| streamstats window=1 current=false values(adjustedoverflow) as previousadjustedoverflow 
| eval finalTotal=case(adjustedtotal=0, previousadjustedoverflow, adjustedtotal>900, 900, NOT adjustedtotal=0 AND NOT adjustedtotal>900, adjustedtotal) 
| rename total as oldTotal 
| table _time oldTotal overflow previousoverflow adjustedtotal adjustedoverflow previousadjustedoverflow finalTotal

Re: Timechart duration, with values rolling into next span

dmarling — Wed, 24 Jul 2019 14:18:23 GMT

If the query wasn't clear, the "finalTotal" column is what you would use on your report. I included all of the fields as a way to show the work that the evals are doing.

Re: Timechart duration, with values rolling into next span

ALXWBR — Wed, 24 Jul 2019 14:48:05 GMT

Thanks for this. However it's not quite doing the trick. Its only rolling over twice, but if the number is in excess of 2700, it doesn't continue to keep rolling down until the seconds have all bee accounted for.

Re: Timechart duration, with values rolling into next span

dmarling — Wed, 24 Jul 2019 14:52:15 GMT

Yeah, I called that out that it's limited to two buckets. Can you identify the maximum duration you have observed? Knowing the maximum amount of carryover buckets to deal with can help with what you need to do.

Re: Timechart duration, with values rolling into next span

ALXWBR — Wed, 24 Jul 2019 14:57:34 GMT

oh yeah sorry! Well the machines could be out of use for a few hours at times, for example, the day I am looking at right now has a transaction over 10,000 seconds long, which would roll over into 12 buckets.

Re: Timechart duration, with values rolling into next span

dmarling — Wed, 24 Jul 2019 15:00:58 GMT

Okay, yeah that's doable, but gets irritatingly complicated the more buckets you go down. I'll need to find something that iterates the process automatically without having to have 12 sets of streamstats as that's not really sustainable.

Re: Timechart duration, with values rolling into next span

ALXWBR — Wed, 24 Jul 2019 15:03:50 GMT

I've been racking my brain with streamstats>eval>if combos for two days now and just can't seem to find the combo.

Re: Timechart duration, with values rolling into next span

dmarling — Wed, 24 Jul 2019 19:28:29 GMT

Okay I took this thing back to the drawing board. Due to concurrency we really just need to identify groups where a duration will cause it to go over to another 15 minute bucket and determine how many 15 minute buckets it needs. If something else has multiple buckets inbetween that spill over and need less buckets than one of those 10k seconds ones you found then we don't really care about it as it'll be 900 seconds in this timechart due to that concurrency.

With that in mind if we create a subsearch that assigns groups based on how many buckets are needed and then create some rules around that it should accomplish what we need. I took your example and added some data to it for testing different use cases. Here's the run anywhere example:

| makeresults count=1 
| eval data=" 2019-07-22 02:00:00    
2019-07-22 02:15:00    98.893
  2019-07-22 02:30:00    937.92
  2019-07-22 02:45:00    1009.674
  2019-07-22 03:00:00    2593.638
  2019-07-22 03:15:00      
  2019-07-22 03:30:00     
  2019-07-22 03:45:00    706.153
  2019-07-22 04:00:00    2800.153
  2019-07-22 04:15:00     
  2019-07-22 04:30:00     
  2019-07-22 04:45:00     
  2019-07-22 05:00:00     
  2019-07-22 05:15:00     
  2019-07-22 05:30:00     " 
| rex max_match=0 field=data "(?<data>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}[^\n]+)" 
| mvexpand data 
| rex field=data "(?<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})" 
| rex field=data "(?<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\s+(?<total>[^\s]+)" 
| eval _time=strptime(time, "%Y-%m-%d %H:%M:%S") 
| fields - time data 
| eval amount=coalesce(case((total/900)>1 AND round(total/900,0)>(total/900), round(total/900,0)-1, (total/900)>1 AND round(total/900,0)<(total/900), round(total/900,0)), 0) 
| streamstats current=f count as counter 
| eval carryover=if(total>900, total-900, 0) 
| eval future=if(carryover>0, counter+amount, 0) 
| eval group=case( 
    [| makeresults count=1 
    | eval data=" 2019-07-22 02:00:00   
2019-07-22 02:15:00    98.893
  2019-07-22 02:30:00    937.92
  2019-07-22 02:45:00    1009.674
  2019-07-22 03:00:00    2593.638
  2019-07-22 03:15:00      
  2019-07-22 03:30:00     
  2019-07-22 03:45:00    706.153
  2019-07-22 04:00:00    2800.153
  2019-07-22 04:15:00     
  2019-07-22 04:30:00     
  2019-07-22 04:45:00     
  2019-07-22 05:00:00     
  2019-07-22 05:15:00     
  2019-07-22 05:30:00     " 
    | rex max_match=0 field=data "(?<data>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}[^\n]+)" 
    | mvexpand data 
    | rex field=data "(?<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})" 
    | rex field=data "(?<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\s+(?<total>[^\s]+)" 
    | eval _time=strptime(time, "%Y-%m-%d %H:%M:%S") 
    | fields - time data 
    | eval amount=coalesce(case((total/900)>1 AND round(total/900,0)>(total/900), round(total/900,0)-1, (total/900)>1 AND round(total/900,0)<(total/900), round(total/900,0)), 0) 
    | streamstats current=f count as counter 
    | eval carryover=if(total>900, total-900, 0) 
    | eval future=if(carryover>0, counter+amount, 0) 
    | search NOT future=0 
    | stats min(counter) as counter by future 
    | sort 0 - future 
    | eval future="counter<=".future 
    | eval counter="counter>=".counter 
    | streamstats count as group 
    | eval search="(".counter." AND ".future."), \"".group."\"" 
    | stats list(search) as search 
    | eval search=mvjoin(search, ", ")]) 
| eventstats max(amount) as maxamount max(future) as maxfuture max(total) as maxcarryover count as groupcount by group 
| eval finalTotal=case(isnotnull(group) AND (NOT counter=maxfuture OR groupcount=1), 900, isnotnull(group) AND counter=maxfuture, maxcarryover-(maxamount*900), isnull(group), total)
| rename total as oldTotal
| table _time oldTotal finalTotal

If you add a larger number on there it will automatically account for that since it's mapping groups based on how many buckets the largest duration requires. Here's it mapped to your specific use case:

index=idx_sems source="sems_north" sourcetype=SEMSSmartLaneEvents Tag="XRY_STVD" EquipmentID=3 
| transaction EquipmentID startswith=TagBitData="1" endswith=TagBitData="0" 
| timechart span=15m sum(duration) as total 
| eval amount=coalesce(case((total/900)>1 AND round(total/900,0)>(total/900), round(total/900,0)-1, (total/900)>1 AND round(total/900,0)<(total/900), round(total/900,0)), 0) 
| streamstats current=f count as counter 
| eval carryover=if(total>900, total-900, 0) 
| eval future=if(carryover>0, counter+amount, 0) 
| eval group=case( 
    [ search index=idx_sems source="sems_north" sourcetype=SEMSSmartLaneEvents Tag="XRY_STVD" EquipmentID=3 
    | transaction EquipmentID startswith=TagBitData="1" endswith=TagBitData="0" 
    | timechart span=15m sum(duration) as total 
    | eval amount=coalesce(case((total/900)>1 AND round(total/900,0)>(total/900), round(total/900,0)-1, (total/900)>1 AND round(total/900,0)<(total/900), round(total/900,0)), 0) 
    | streamstats current=f count as counter 
    | eval carryover=if(total>900, total-900, 0) 
    | eval future=if(carryover>0, counter+amount, 0) 
    | search NOT future=0 
    | stats min(counter) as counter by future 
    | sort 0 - future 
    | eval future="counter<=".future 
    | eval counter="counter>=".counter 
    | streamstats count as group 
    | eval search="(".counter." AND ".future."), \"".group."\"" 
    | stats list(search) as search 
    | eval search=mvjoin(search, ", ")]) 
| eventstats max(amount) as maxamount max(future) as maxfuture max(total) as maxcarryover count as groupcount by group 
| eval finalTotal=case(isnotnull(group) AND (NOT counter=maxfuture OR groupcount=1), 900, isnotnull(group) AND counter=maxfuture, maxcarryover-(maxamount*900), isnull(group), total) 
| rename total as oldTotal 
| table _time oldTotal finalTotal

Your only problem at this point would be if the subsearch that generates the groups exceeds 30 seconds which will cause it to timeout if you have the generic timeouts in your confs.

Re: Timechart duration, with values rolling into next span

ALXWBR — Thu, 25 Jul 2019 09:09:37 GMT

Sorry, but still not there. That period between 03:00 and 04:00 isn't right.

_time   oldTotal    finalTotal
2019-07-22 00:00:00      
2019-07-22 00:15:00      
2019-07-22 00:30:00      
2019-07-22 00:45:00      
2019-07-22 01:00:00      
2019-07-22 01:15:00      
2019-07-22 01:30:00      
2019-07-22 01:45:00      
2019-07-22 02:00:00      
2019-07-22 02:15:00 98.893  98.893
2019-07-22 02:30:00 937.92  900
2019-07-22 02:45:00 1009.674    900
2019-07-22 03:00:00 2593.638    900
2019-07-22 03:15:00     900
2019-07-22 03:30:00     793.638
2019-07-22 03:45:00 706.153 706.153
2019-07-22 04:00:00 1015.469    900
2019-07-22 04:15:00 451.892 115.469
2019-07-22 04:30:00 329.411 329.411
2019-07-22 04:45:00 319.438 319.438

Re: Timechart duration, with values rolling into next span

dmarling — Thu, 25 Jul 2019 13:15:05 GMT

Thanks for the additional use cases. I believe I have them accounted for now with this iteration:

| makeresults count=1 
| eval data=" 2019-07-22 00:00:00          
 2019-07-22 00:15:00          
 2019-07-22 00:30:00          
 2019-07-22 00:45:00          
 2019-07-22 01:00:00          
 2019-07-22 01:15:00          
 2019-07-22 01:30:00          
 2019-07-22 01:45:00          
 2019-07-22 02:00:00          
 2019-07-22 02:15:00    98.893
   2019-07-22 02:30:00    937.92
   2019-07-22 02:45:00    1009.674
   2019-07-22 03:00:00    2593.638
   2019-07-22 03:15:00      
   2019-07-22 03:30:00     
   2019-07-22 03:45:00    706.153
   2019-07-22 04:00:00    1015.469
   2019-07-22 04:15:00    451.892
   2019-07-22 04:30:00    329.411
   2019-07-22 04:45:00    319.438
   2019-07-22 05:00:00     
   2019-07-22 05:15:00     
   2019-07-22 05:30:00     " 
| rex max_match=0 field=data "(?<data>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}[^\n]+)" 
| mvexpand data 
| rex field=data "(?<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})" 
| rex field=data "(?<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\s+(?<total>[^\s]+)" 
| eval _time=strptime(time, "%Y-%m-%d %H:%M:%S") 
| fields - time data 
| eval amount=coalesce(case((total/900)>1 AND round(total/900,0)>(total/900), round(total/900,0), (total/900)>1 AND round(total/900,0)<(total/900), round(total/900,0)+1), 0) 
| streamstats current=f count as counter 
| eval carryover=if(total>900, total-900, 0) 
| eval future=if(carryover>0, counter+amount, 0) 
| eval group=case( 
    [| makeresults count=1 
    | eval data=" 2019-07-22 00:00:00          
 2019-07-22 00:15:00          
 2019-07-22 00:30:00          
 2019-07-22 00:45:00          
 2019-07-22 01:00:00          
 2019-07-22 01:15:00          
 2019-07-22 01:30:00          
 2019-07-22 01:45:00          
 2019-07-22 02:00:00          
 2019-07-22 02:15:00    98.893
   2019-07-22 02:30:00    937.92
   2019-07-22 02:45:00    1009.674
   2019-07-22 03:00:00    2593.638
   2019-07-22 03:15:00      
   2019-07-22 03:30:00     
   2019-07-22 03:45:00    706.153
   2019-07-22 04:00:00    1015.469
   2019-07-22 04:15:00    451.892
   2019-07-22 04:30:00    329.411
   2019-07-22 04:45:00    319.438
   2019-07-22 05:00:00     
   2019-07-22 05:15:00     
   2019-07-22 05:30:00     " 
    | rex max_match=0 field=data "(?<data>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}[^\n]+)" 
    | mvexpand data 
    | rex field=data "(?<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})" 
    | rex field=data "(?<time>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\s+(?<total>[^\s]+)" 
    | eval _time=strptime(time, "%Y-%m-%d %H:%M:%S") 
    | fields - time data 
    | eval amount=coalesce(case((total/900)>1 AND round(total/900,0)>(total/900), round(total/900,0), (total/900)>1 AND round(total/900,0)<(total/900), round(total/900,0)+1), 0) 
    | streamstats current=f count as counter 
    | eval carryover=if(total>900, total-900, 0) 
    | eval future=if(carryover>0, counter+amount, 0) 
    | search NOT future=0 
    | stats min(counter) as counter by future 
    | sort 0 - future 
    | eval future="counter<=".future 
    | eval counter="counter>=".counter 
    | streamstats count as group 
    | eval search="(".counter." AND ".future."), \"".group."\"" 
    | stats list(search) as search 
    | eval search=mvjoin(search, ", ")]) 
| eventstats max(amount) as maxamount max(future) as maxfuture max(total) as maxcarryover count as groupcount by group 
| eval finalTotal=case(isnotnull(group) AND (NOT counter=maxfuture OR groupcount=1), 900, isnotnull(group) AND counter=maxfuture, (maxcarryover-((maxamount-1)*900))+coalesce(total,0), isnull(group), total) 
| eval finalcarryover=if(finalTotal>900, finalTotal-900, null()) 
| streamstats window=1 current=f values(finalcarryover) as lastfinalcarryover 
| eval finaltotal=case(finalTotal>=900, 900, finalTotal<900 AND (lastfinalcarryover+finalTotal)<900, lastfinalcarryover+finalTotal, finalTotal<900 AND (lastfinalcarryover+finalTotal)>900, 900, finalTotal<900 AND NOT (lastfinalcarryover+finalTotal)<900 AND NOT (lastfinalcarryover+finalTotal)>900, finalTotal) 
| rename total as oldTotal 
| table _time oldTotal finaltotal

This returns this results:

_time   oldTotal    finaltotal
2019-07-22 00:00:00      
2019-07-22 00:15:00      
2019-07-22 00:30:00      
2019-07-22 00:45:00      
2019-07-22 01:00:00      
2019-07-22 01:15:00      
2019-07-22 01:30:00      
2019-07-22 01:45:00      
2019-07-22 02:00:00      
2019-07-22 02:15:00 98.893  98.893
2019-07-22 02:30:00 937.92  900
2019-07-22 02:45:00 1009.674    900
2019-07-22 03:00:00 2593.638    900
2019-07-22 03:15:00     900
2019-07-22 03:30:00     900
2019-07-22 03:45:00 706.153 900
2019-07-22 04:00:00 1015.469    900
2019-07-22 04:15:00 451.892 900
2019-07-22 04:30:00 329.411 444.880
2019-07-22 04:45:00 319.438 319.438
2019-07-22 05:00:00      
2019-07-22 05:15:00      
2019-07-22 05:30:00

Re: Timechart duration, with values rolling into next span

ALXWBR — Thu, 25 Jul 2019 13:32:30 GMT

I'm getting an error in the | eval group=case(.....

Only works with makeresults but not search

😞

Re: Timechart duration, with values rolling into next span

dmarling — Thu, 25 Jul 2019 13:35:24 GMT

What's the error?

Re: Timechart duration, with values rolling into next span

ALXWBR — Thu, 25 Jul 2019 14:47:38 GMT

Error in 'eval' command: The expression is malformed. An unexpected character is reached at ') )'.

index=idx_sems source="sems_north" sourcetype=SEMSSmartLaneEvents Tag="XRY_STVD" EquipmentID=3 
 | transaction EquipmentID startswith=TagBitData="1" endswith=TagBitData="0" 
 | timechart span=15m sum(duration) as total 
 | eval amount=coalesce(case((total/900)>1 AND round(total/900,0)>(total/900), round(total/900,0), (total/900)>1 AND round(total/900,0)<(total/900), round(total/900,0)+1), 0) 
 | streamstats current=f count as counter 
 | eval carryover=if(total>900, total-900, 0) 
 | eval future=if(carryover>0, counter+amount, 0) 
 | eval group=case( 
     [| search index=idx_sems source="sems_north" sourcetype=SEMSSmartLaneEvents Tag="XRY_STVD" EquipmentID=3 
 | transaction EquipmentID startswith=TagBitData="1" endswith=TagBitData="0" 
 | timechart span=15m sum(duration) as total  
     | eval amount=coalesce(case((total/900)>1 AND round(total/900,0)>(total/900), round(total/900,0), (total/900)>1 AND round(total/900,0)<(total/900), round(total/900,0)+1), 0) 
     | streamstats current=f count as counter 
     | eval carryover=if(total>900, total-900, 0) 
     | eval future=if(carryover>0, counter+amount, 0) 
     | search NOT future=0 
     | stats min(counter) as counter by future 
     | sort 0 - future 
     | eval future="counter<=".future 
     | eval counter="counter>=".counter 
     | streamstats count as group 
     | eval search="(".counter." AND ".future."), \"".group."\"" 
     | stats list(search) as search 
     | eval search=mvjoin(search, ", ")]) 
 | eventstats max(amount) as maxamount max(future) as maxfuture max(total) as maxcarryover count as groupcount by group 
 | eval finalTotal=case(isnotnull(group) AND (NOT counter=maxfuture OR groupcount=1), 900, isnotnull(group) AND counter=maxfuture, (maxcarryover-((maxamount-1)*900))+coalesce(total,0), isnull(group), total) 
 | eval finalcarryover=if(finalTotal>900, finalTotal-900, null()) 
 | streamstats window=1 current=f values(finalcarryover) as lastfinalcarryover 
 | eval finaltotal=case(finalTotal>=900, 900, finalTotal<900 AND (lastfinalcarryover+finalTotal)<900, lastfinalcarryover+finalTotal, finalTotal<900 AND (lastfinalcarryover+finalTotal)>900, 900, finalTotal<900 AND NOT (lastfinalcarryover+finalTotal)<900 AND NOT (lastfinalcarryover+finalTotal)>900, finalTotal) 
 | rename total as oldTotal 
 | table _time oldTotal finaltotal

Re: Timechart duration, with values rolling into next span

dmarling — Thu, 25 Jul 2019 15:30:52 GMT

Thank you. I recreated it on my install as well. This happens when the subsearch that generates the groups returns no results due to nothing exceeding 900 seconds. This accounts for that by making that field null when that use case happens:

index=idx_sems source="sems_north" sourcetype=SEMSSmartLaneEvents Tag="XRY_STVD" EquipmentID=3 
| transaction EquipmentID startswith=TagBitData="1" endswith=TagBitData="0" 
| timechart span=15m sum(duration) as total 
| eval amount=coalesce(case((total/900)>1 AND round(total/900,0)>(total/900), round(total/900,0), (total/900)>1 AND round(total/900,0)<(total/900), round(total/900,0)+1), 0) 
| streamstats current=f count as counter 
| eval carryover=if(total>900, total-900, 0) 
| eval future=if(carryover>0, counter+amount, 0) 
| eval group=case( 
    [ search index=idx_sems source="sems_north" sourcetype=SEMSSmartLaneEvents Tag="XRY_STVD" EquipmentID=3 
    | transaction EquipmentID startswith=TagBitData="1" endswith=TagBitData="0" 
    | timechart span=15m sum(duration) as total 
    | eval amount=coalesce(case((total/900)>1 AND round(total/900,0)>(total/900), round(total/900,0), (total/900)>1 AND round(total/900,0)<(total/900), round(total/900,0)+1), 0) 
    | streamstats current=f count as counter 
    | eval carryover=if(total>900, total-900, 0) 
    | eval future=if(carryover>0, counter+amount, 0) 
    | search NOT future=0 
    | stats min(counter) as counter by future 
    | sort 0 - future 
    | eval future="counter<=".future 
    | eval counter="counter>=".counter 
    | streamstats count as group 
    | eval search="(".counter." AND ".future."), \"".group."\"" 
    | stats list(search) as search 
    | eval search=mvjoin(search, ", ") 
    | append 
        [| makeresults count=1 
        | eval search="\"ERROR\"=\"ERROR\", null()" 
        | table search] 
    | eventstats count 
    | search NOT (count>1 search="\"ERROR\"=\"ERROR\", null()") 
    | fields - count)]) 
| eventstats max(amount) as maxamount max(future) as maxfuture max(total) as maxcarryover count as groupcount by group 
| eval finalTotal=case(isnotnull(group) AND (NOT counter=maxfuture OR groupcount=1), 900, isnotnull(group) AND counter=maxfuture, (maxcarryover-((maxamount-1)*900))+coalesce(total,0), isnull(group), total) 
| eval finalcarryover=if(finalTotal>900, finalTotal-900, null()) 
| streamstats window=1 current=f values(finalcarryover) as lastfinalcarryover 
| eval finaltotal=case(finalTotal>=900, 900, finalTotal<900 AND (lastfinalcarryover+finalTotal)<900, lastfinalcarryover+finalTotal, finalTotal<900 AND (lastfinalcarryover+finalTotal)>900, 900, finalTotal<900 AND NOT (lastfinalcarryover+finalTotal)<900 AND NOT (lastfinalcarryover+finalTotal)>900, finalTotal) 
| rename total as oldTotal 
| table _time oldTotal finaltotal

Re: Timechart duration, with values rolling into next span

ALXWBR — Fri, 09 Aug 2019 10:32:30 GMT

Looks good, thank you 🙂