Timechart to start from the most recent time where a condition is met

[sssignals]

Hi all

I have "my search | timechart avg(Throughput) span=5m by id".

For each id, the throughput fluctuates and and drop to 0 several times. I want to show the user the throughput from the most recent time + 1hour earlier. I do not need to show the older events where Throughput=0.

Thank you in advance for your suggestions.

[micahkemp]

Try this:

my search
| bin span=5min _time
| stats avg(Throughput) AS Throughput span=5m by id _time
| eval zero_throughput_time=if(Throughput=0, _time, NULL)
| eventstats latest(zero_throughput_time) AS latest_zero_throughput_time BY id
| eval latest_zero_throughput_time=if(isnull(latest_zero_throughput_time), 0, latest_zero_throughput_time)
| where _time>=latest_zero_throughput_time

[mayurr98]

by the way you can not run | eval zero_throughput_time=if(Throughput=0, _time, NULL) as there is no field generated Throughput by | timechart avg(Throughput) AS Throughput span=5m by id command.

I hope you understand what I am trying to say!

[micahkemp]

You're right. I edited my search to use bin and stats instead. That's what I get for trying to type out a search without actually running it like I usually do.

Good catch!

[sssignals]

Thanks mayurr98 for your suggestion. Tried but did not get the expected results.

For example, I am looking at 1 id only. The throughput for this 1 id fluctuates and drops to zero a few times over say a week, after which it will rise and fluctuate at a non-zero value. The most recent event that the throughput of this id drop to zero was eg. yesterday 4pm.

So I want to show my users the throughout for this id from the most recent event (where throughput drops to zero) in a timechart starting from yesterday 3pm and time range for 12hrs.

Thanks for your thoughts and suggestions on my problem in advance.
I really appreciate it!

[mayurr98]

You can try something like

 my search | bucket span=5m _time | stats avg(Throughput) as Throughput by _time id| where Throughput!=0 | xyseries _time,id,Throughput | sort- _time

Let me know if this helps!

[mayurr98]

Following your comment try this: first try for particular id and check the results and then do this for all

index=<your_index> id=<your_id> | bucket span=5m _time 
 | stats avg(Throughput) AS Throughput by id _time  | eval Throughput=round(Throughput)   | eval zero_throughput_time=case(Throughput=0, _time) | eventstats latest(zero_throughput_time) AS latest_zero_throughput_time BY id| eval latest_zero_throughput_time=if(isnull(latest_zero_throughput_time), 0, latest_zero_throughput_time)  | where   _time<=latest_zero_throughput_time+39600  | where _time>=latest_zero_throughput_time-3600 |where Throughput!=0 | chart values(Throughput) over _time by id

I hope this helps you!

Also you can try running above query in parts so that you can see what is happening after each |

[p_gurav]

You can use "my search earliest=-h | timechart avg(Throughput) span=5m by id"