Splunk Search

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

Explorer

My current search string looks like this:

index=cisco host=cr0* OR host=SC0* | stats count as daycount by datemonth, datemday | streamstats avg(daycount) | sort by datemonth,datemday | 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)

alt text

0 Karma
1 Solution

Builder

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

View solution in original post

0 Karma

Builder

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

View solution in original post

0 Karma

Explorer

Thank you, This works excellent.

I see the addition but not entirely clear to me exactly why my original sort on datemonth, datemday 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

Builder

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.

0 Karma

Explorer

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.

0 Karma