Splunk Search

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

urapaveerapan
Explorer

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

alt text

The result should be look like this

alt text

0 Karma

niketn
Legend

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 
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...