How do you get an average based on a date field?

[karthi25]

I have a Splunk log in JSON format as follows:

{"SCMSplunkLog":{
"SCMSuccessLog":{
"payload":{
"sourceCount":0,"level":"INFO","duplicateCount":0,"successCount":0,"startTime":"2019-01-19 23:08:31","endTime":"2019-01-19 23:08:34","publishedCount":0
},"appName":"***","eventType":"***"}}}

Now, I want to draw the line chart with the hourly average of publishedCount over starttime. I tried the below query, but it is not working:

 index=*** source=*** | search SCMSplunkLog.SCMSuccessLog.eventType="***"| sort -splunkLogId | eval runDate = SCMSplunkLog.SCMSuccessLog.payload.startTime |eval time=strptime(runDate, "%m-%d-%Y %H") | chart sum(publishedCount) as dailyAvg over time | stats avg(dailyAvg) as TREND  | eval TREND=round(TREND)| strcat "TREND" ":  " TREND TREND

Can anyone please suggest to me the solution for it.

[renjith_nair · Accepted solution]

@karthi25 ,

Try

 index=*** source=*** | search SCMSplunkLog.SCMSuccessLog.eventType="***"| sort -splunkLogId
| eval runDate = SCMSplunkLog.SCMSuccessLog.payload.startTime 
|eval _time=strptime(runDate, "%m-%d-%Y %H") 
|timechart span=1h avg(publishedCount) as TREND