Taking daily "OPEN"/"CLOSED" reports and creating a monthly avg

[Username1]

So suppose that everyday Splunk takes in a report that houses 9 different fields, one of which is called 'status'. Status has the option of being 'New', 'Closed', or 'Open'. I'm trying to show a time-chart that shows the average count per month of reports that have 'Closed' and'Open'. status , along with the difference of the two (everyday). I have found how to do the difference between'Open' and 'Closed'. But I cannot figure out how to find the monthly average for 'Open' and 'Closed', does anyone have any ideas how to query this? 

index=blah... |   timechart span=1mon count(report_date) by status | eval difference=abs(OPEN - CLOSED) 

This is where I'm stuck, how do I get the monthly average of the particular status fields?

[richgalloway]

Try this search.

index=foo 
| timechart span=1mon count(report_date) by status 
| appendpipe [stats avg(*) as * | eval _time="Average" | foreach * [eval <<FIELD>>=round(<<FIELD>>,2)]]
| eval difference=abs(OPEN-CLOSED)

The appendpipe command adds the stats results at the bottom of the events.  I used foreach to round off the averages to 2 digits. 

[Username1]

Hi @richgalloway , thank you for responding to my question. I ran your search query as the code below to see what the results would be. You're query works that it gives me the final average for all of the months, but not the average for each month. How do you think we can modify this to have an average of "OPEN" for every month? Does it have something to do with the span=1mon? Look forward to hearing your thoughts

index=foo 
| timechart span=1mon count(report_date) by status 
| appendpipe [stats avg(OPEN) as avgOPEN | eval _time="Average"]
| eval difference=abs(OPEN-CLOSED)

[to4kawa · Accepted solution]

index=foo 
| bin _time span=1d
| stats count(eval(if(status="OPEN",report_date,NULL))) as OPEN count(eval(if(status="CLOSED",report_date,NULL))) as CLOSED by _time
| bin _time span=1month
| stats sum(OPEN) as OPEN sum(CLOSED) as CLOSED avg(OPEN) as avgOPEN avg(CLOSED) as avgCLOSED by _time
| eval difference=abs(OPEN-CLOSED)

There is not the logs, so I'm not sure.

[Username1]

Hi @to4kawa , Thanks for responding to my question as well. I ran your query and it returns the sum of the numbers of the month (see the picture), which I believe is being caused by the span =1mon. I ran the numbers manually and the average of OPEN for January should be 287. There is a csv everyday of each month that shows what reports are OPEN (so 31 csv in January), so maybe we should be doing span=1d and then doing an average of each month...unsure if thats the best path forward but I'm open for suggestions. 

[to4kawa]

my query is updated. please check latest. @Username1 

[Username1]

@to4kawa That worked amazingly and it returns exactly what I was looking for! I don't fully understand your query, but I'm going to go look into how 'bin' operates and try to reconstruct your logic. Thank you again for assisting me with this, much appreciated.