while using streamstats avg(daycount) the resulting stats are out of order

[caseygj]

My current search string looks like this:

index=cisco host=cr0* OR host=SC0* | stats count as daycount by date_month, date_mday | streamstats avg(daycount) | sort by date_month,date_mday | fields - date_month

I am looking for a daily average (daycount) and comparing it to actual events over a 30 day period of time.

I thought everything was going well until I got to day 10 of the this month and its clear the daycount value is being sorted incorrectly it's being sorted as txt not as a value. i sense it has something to do with the streamtats but it's not clear to me how to fix it.

My results look like this (any thoughts or suggestions on how to correct the order of the results from the streamstats for avg(daycount) would be appreciated.

The chart should be showing a rapid decline Avg(Daycount)

[jpolvino · Accepted solution]

One way to solve this would be to use a time sorter field, sort by it, do your streamstats, and then throw it away. Does this get you closer?

index=cisco host=cr0* OR host=SC0*
| stats count as daycount first(_time) as timesort by date_month,date_mday
| sort timesort
| streamstats avg(daycount) | fields - date_month timesort

[caseygj]

Thank you, This works excellent.

I see the addition but not entirely clear to me exactly why my original sort on date_month, date_mday did not work. unless it's an order of operations deal where my sort was after the streamstats instead of before it.

I love it though, it works..

I wanted to ward you points for you answer here but it seems I do not have enough Karma to do so (Phhhh, go figure)... I am now on the search for more karma.

🙂 have a nice day jpolvino, you made mine! This is excellent

[jpolvino]

Glad to help out!

Your original sort lost calendar meaning and reverted to lexicographical ordering. If you ran your original search starting in Nov and going to today, you would have seen the months ordered as december, january, november due to this fact. Strip off the very last part of your original search and you'll see this.

[caseygj]

Wow, I had no idea, thank you for pointing that out. I have this problem in a few other areas that I need to fix then... thanks again.