While I am still validating the numbers, when i execute the query it brings up records of previous months, even though i specifically choose (for example from 01/04/2017 to 30/04/2017) it lists events from March 2017 probably because opened_at is in March 2017. Is there a way to eliminate those?
When I execute the query for Year to date the number for April changes. So it is not consistently counting the monthly figures for incidents opened in a month.
Also an incident can be opened at previous month and assigned in current month. For example,
2016/03/28 17:06:55.000 AM 2016-03-28 07:55:41 ServiceDesk INC4752169 Open
2016/04/28 7:07:55.000 PM 2016-04-28 07:55:41 IT CC INC4752169 Assigned
This should not be counted against Service Desk when i run the April period.
I think the scenarios explained by @rmarcum below (1 and 2) is not handled in the query. I tried the below query, which has lot of performance hits plus does not correctly handle the 1 and 2 of @rmarcum which I am looking for.
Please help!
index=....
| transaction dv_number mvlist=assignment_group_name maxspan=30d keepevicted=true
|eval last_state_change = mvindex(assignment_group_name, -1)
| eval dv_opened_at = strptime(dv_opened_at,"%Y-%m-%d %H:%M:%S")
|eval _time=coalesce(dv_opened_at, _time)
|sort 0 - _time
|table _time dv_number assignment_group_name
|rename last_state_change as assignment_group_name
| search assignment_group_name="ServiceDesk"
|stats values(*) as * _time
| table _time dv_number assignment_group_name
|timechart span=1mon dc(dv_number) AS inc BY assignment_group_name limit=20 useother=f
... View more