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!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...