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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...