Timechart column statistics: Average of monthly project hours from index containing time sheet data

[doug_hall]

I'm searching an index that contains time sheet data. Each event is a charge by an employee of some number of hours (hours_charged) against a project. I used the following to get a table of total hours by project per month

index=XXX | ... | timechart span=1month sum(hours_charged) by project_name

This produces a table like the following.

_time     ProjectA ProjectB ProjectC ProjectD
2013-07   12.3     45.6     78.9     12.3
2013-08   12.7     44.1     77.1     13.2
...

This is useful information but what I'm really after is the average of the monthly project hours. So I'm looking for a way to generate an average of the project columns in the timechart. Ideally the result would look like the following.

_time     AvgHoursPerProject
2013-07   37.3
2012-08   36.8  

Is there a way to do this?

Thanks,
Doug

[martin_mueller · Accepted solution]

You could use this:

... | timechart span=1month sum(hours_charged) as hours dc(project_name) as projects | eval average_hours = hours / projects | fields - hours projects

Note, this will count the number of hours for each month individually... so if one month has four projects it'll build an average over four projects, and if another month only has one project it'll build an average over one project. Depending on your requirements that may be correct or not. If you need the number of projects to be the overall number of projects you can do this:

... | eventstats dc(project_name) as projects | timechart span=1month sum(hours_charged) as hours values(projects) as projects | eval average_hours = hours / projects | fields - hours projects

That'll compute the number of projects over all months. Unwanted behaviour depending on your requirement could be a large number of short projects driving up the project count, lowering the averages for all months.

[doug_hall]

I didn't include it in the question but you are correct that I only wanted to average over the number of projects that actually had charges in the month so your first suggestion is exactly what I was looking for. Thanks so much for your help.