How to get the latest record of each month for each specific column?

[urapaveerapan]

Dear guys,
I'm very new in Splunk and I got some work task which still have no idea about the solution. Please kindly give some suggestion.

The purpose of this search is "To track the number of backlog case in each month divided by assignment_group_name"

The number will be count in each assignment_group_name only if the last record of each month was assigned to that assignment group and the state_values of that record must not be "Resolved"
In case, there are no record of the current month, program will get the the last record of the last month instead.

Here is the example of raw data,

index="snow" sourcetype="snow:incident" number="INC0079884" OR number="INC0086062"
| table number sys_updated_on sys_created_on assignment_group_name state_values
|sort number sys_updated_on

The result should be look like this

[niketn]

You can try the following:

index="snow" sourcetype="snow:incident" number="*" state_values="*"
| dedup number state_values
| search state_values!="Resolved"
| timechart span="1mon" count(number) as Count by assignment_group_name 

Same should also be achievable by stats followed by timechart.

PS: If _time does not match with sys_updated_on then you might need to add | eval _time=sys_updated_on before timechart or else use chart over sys_updated_on instead of timechart

| chart span="1mon" count(number) as Count over sys_updated_on by assignment_group_name