Stats command with latest values listing

[jerinvarghese]

Hi Team,

I have a splunk query that am testing for Service Now data extract.

index=snow "INC783"
| search dv_state="In Progress" OR dv_state="New" OR dv_state="On Hold"
| stats max(_time) as Time latest(dv_state) as State by number, dv_priority
| fieldformat Time=strftime(Time,"%Y-%m-%d %H:%M:%S")
| table number,Time, dv_priority, State

The challenge with the code is, above output is listing all the states for the particular Incidnet, even when i tried to filter for only the latest and max time.

number	Time	dv_priority	State
INC783	2024-11-13 16:56:14	1 - Critical	In Progress
INC783	2024-11-13 17:00:03	3 - Moderate	On Hold

The data must only show the latest one, which must be the one with "On Hold".
Tried multiple method, but failing and showing all.
how can i achieve it.

thanks

Jerin V

[ITWhisperer · Accepted solution]

Your by clause also include dv_priority which is why you are getting multiple results for an incident. Try something like this

index=snow "INC783"
| search dv_state="In Progress" OR dv_state="New" OR dv_state="On Hold"
| stats max(_time) as Time latest(dv_state) as State latest(dv_priority) as Priority by number
| fieldformat Time=strftime(Time,"%Y-%m-%d %H:%M:%S")
| table number,Time, Priority, State