charts: How can I calculate median for each type on the hourly aggregation?

[belts]

Dear all,

There are three columns with data: time (time scale in steps of 10 minutes) , val (amount of transactions) and type (type of automated system - 3 different types only).

I need to aggregate data for each type at the hour level - and calculate median(val) for each type on the hourly aggregation. As the answer should be 3 time series of the same length.

What I did:

source="data.txt" | chart median(val) by type, date_hour

But X-axis contains not all hours, they aggregate into "OTHER" tab.

Thanks in advance for the help.

[mstjohn_splunk]

Hi @belts,

Did the answer below solve your question? If yes, please click “Accept” directly below the answer to resolve the post. If not, please comment with more information if you are still having issues. Thanks!!

[DalJeanis]

tl;dr

Before looking at the below stuff, try adding limit=0 to your chart command.

---

If your original data were coming from an index (we know it's not, but go with us here). This gives you records that have a_time value in 10 minute increments, the val, and the type.

index=foo type=*
| bin _time span=10m
| stats count as val by _time type

Then this gives you median 10m period for each hour of each day for each type.

 | bin _time as Hour
 | stats median(val) as med_val by Hour type

On the other hand, if you wanted the median 10m period for each hour over ALL days, then you could do something like this instead of the above...

 | eval Hour=strftime(_time,"%H")
 | stats median(val) as med_val by Hour type