Dashboards & Visualizations

Why does this search not sort

timrich66
Communicator

I am running this search to return batch job run times.

index=sr_prd sourcetype=batch_roylink earliest=-7d@d
| eval s=strptime(Scheduled_Batch_StartTime, "%Y-%m-%d %H:%M:%S.%Q")
| eval e=strptime(Scheduled_Batch_Endtime, "%Y-%m-%d %H:%M:%S.%Q")
| eval s=round(s,2)
| eval e=round(e,2)
| eval r=tostring(e-s, "duration")
| rename "Scheduled_Batch_StartTime" as "Start Time", "Scheduled_Batch_Endtime" as "End Time", r as "Runtime (H:M:S)"
| stats list(s) as "s", list("Start Time") as "Start Time",list("End Time") as "End Time", list("Runtime (H:M:S)") as "Runtime (H:M:S)" by Task_Object
| search Task_Object = Roylink_Upload
| sort s

Even though 's' is a numeric string, the results are not returning in search order - 

timrich66_0-1641297015475.png

Any ideas why this is happening?  Thanks

1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

By the time you are sorting, s is already a multivalue field. Try:

index=sr_prd sourcetype=batch_roylink earliest=-7d@d
| eval s=strptime(Scheduled_Batch_StartTime, "%Y-%m-%d %H:%M:%S.%Q")
| eval e=strptime(Scheduled_Batch_Endtime, "%Y-%m-%d %H:%M:%S.%Q")
| eval s=round(s,2)
| eval e=round(e,2)
| eval r=tostring(e-s, "duration")
| rename "Scheduled_Batch_StartTime" as "Start Time", "Scheduled_Batch_Endtime" as "End Time", r as "Runtime (H:M:S)"
| sort s
| stats list(s) as "s", list("Start Time") as "Start Time",list("End Time") as "End Time", list("Runtime (H:M:S)") as "Runtime (H:M:S)" by Task_Object
| search Task_Object = Roylink_Upload

View solution in original post

inventsekar
SplunkTrust
SplunkTrust

As suggested on the previous reply, | stats list(s) as "s" this will list out all values of "s" and assign it "s"... simply making it the field "s" as a multivalue field. 

so, run the sort command before doing this stats list operation. 

 

thanks and best regards,
Sekar

PS - If this or any post helped you in any way, pls consider upvoting, thanks for reading !
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

By the time you are sorting, s is already a multivalue field. Try:

index=sr_prd sourcetype=batch_roylink earliest=-7d@d
| eval s=strptime(Scheduled_Batch_StartTime, "%Y-%m-%d %H:%M:%S.%Q")
| eval e=strptime(Scheduled_Batch_Endtime, "%Y-%m-%d %H:%M:%S.%Q")
| eval s=round(s,2)
| eval e=round(e,2)
| eval r=tostring(e-s, "duration")
| rename "Scheduled_Batch_StartTime" as "Start Time", "Scheduled_Batch_Endtime" as "End Time", r as "Runtime (H:M:S)"
| sort s
| stats list(s) as "s", list("Start Time") as "Start Time",list("End Time") as "End Time", list("Runtime (H:M:S)") as "Runtime (H:M:S)" by Task_Object
| search Task_Object = Roylink_Upload

timrich66
Communicator

Thank you, that is perfect.  (I've removed 's' from the final table as not required).

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...