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!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...