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

Labels (1)
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
Ultra Champion

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. 

 

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!

Observability | How to Think About Instrumentation Overhead (White Paper)

Novice observability practitioners are often overly obsessed with performance. They might approach ...

Cloud Platform | Get Resiliency in the Cloud Event (Register Now!)

IDC Report: Enterprises Gain Higher Efficiency and Resiliency With Migration to Cloud  Today many enterprises ...

The Great Resilience Quest: 10th Leaderboard Update

The tenth leaderboard update (11.23-12.05) for The Great Resilience Quest is out >> As our brave ...