How to change my stats avg(x) search to an hourly timechart ?

[pavanae]

Hi

I have the following search which displays the Average of a field, but I am trying to put a time chart in hourly which shows the average of that particular hour.

…..My Search……|rex "<Total_Amount_Due>(?<amount>\d+.\d+)</Total_Amount_Due>" | stats count by amount | where amount > 0 | stats avg(amount) as average
How to modify my search to display the hourly average count?

Any help or Suggestions?

[tedwroks]

It sounds like all you want is:

... My Search ...|rex "<Total_Amount_Due>(?<amount>\d+.\d+)</Total_Amount_Due>" | timechart span=1h avg(amount)

[martin_mueller · Accepted solution]

You could use this:

... | rex ... | timechart span=1h count dc(amount) as dc | eval average = count / dc | fields - count dc

This will count the events per hour and the number of different amount values to then compute the average.

Alternatively, you could do this:

... | rex ... | bin span=1h _time | stats count by _time amount | timechart span=1h avg(count) as average

Note, I've changed avg(amount) to avg(count), not sure if that was intentional in your question or not.

[pavanae]

Hi everything seems good but it was giving the wrong average.

[martin_mueller]

If both don't produce results you like then please do post sample data along with intended results.

[pavanae]

Thanks Martin both searches worked great my mistake.

[somesoni2]

Try to use Martin's 2nd query with avg(amount) in the timechart.