topic Re: Why is the event sort not reflecting in Statistics tab? in Getting Data In

Why is the event sort not reflecting in Statistics tab?

mawomommoh — Wed, 07 Mar 2018 17:10:33 GMT

I have an xml containing steps with timestamps. When I run a search, I am able to sort the events based on the timestamps I have extracted from the xml.

In the Events tab, my xml steps sort properly based on the timestamp, but when I switch to the Statistics tab, my xml steps(events) appear in alphabetical order instead of sorting based on the timestamp.

Why does the sort work when in the Events tab but not when in the Statistics tab when the search query is the same?

Any help will be appreciated.

Thanks

Re: Why is the event sort not reflecting in Statistics tab?

somesoni2 — Wed, 07 Mar 2018 17:26:24 GMT

The Events tab shows the events show in default sorting of reverse chronological (recent events first). The result in Statistics tabs are shown based on SPL that you've written. If your query generates a timestamp in string format, the string sort would be applied. Generally, we'd say to apply sorting in epoch format for accurate results and then convert to string format. More accurate suggestions can be provided if you can share you current search.

Re: Why is the event sort not reflecting in Statistics tab?

mawomommoh — Wed, 07 Mar 2018 17:41:28 GMT

This is my current search:

host=XXXX index=XXXX 
| eval NewTime=strptime(StartTime,"%Y-%m-%dT%H:%M:%S.%3N") | eval _time=NewTime 
| sort _time| eventstats max(_time) AS lastestScan by Description 
| where _time=lastestScan | stats count by Description, StepResult| eval color=case(StepResult=="Passed","green",StepResult=="Skipped","gold", StepResult=="Failed","red")
            | eval StepResult = Description

'StartTime' is the extracted field from the xml. It sorts fine in the Events tab but not in the Statistics tab.

Re: Why is the event sort not reflecting in Statistics tab?

somesoni2 — Wed, 07 Mar 2018 19:07:24 GMT

You're not using _time field in your final result (in stats command). Your result will be sorted by Description and StepResult field values. Could you explain what sorting you're expecting in Statistics tab???

Re: Why is the event sort not reflecting in Statistics tab?

mawomommoh — Wed, 07 Mar 2018 19:12:23 GMT

Oh, I see. I am expecting sorting based on _time field in the Statistics tab. How do I go about that?

Re: Why is the event sort not reflecting in Statistics tab?

somesoni2 — Tue, 29 Sep 2020 18:15:37 GMT

Try this

host=XXXX index=XXXX 
 | eval NewTime=strptime(StartTime,"%Y-%m-%dT%H:%M:%S.%3N") | eval _time=NewTime 
 | sort _time| eventstats max(_time) AS lastestScan by Description 
 | where _time=lastestScan | stats count by _time Description, StepResult| eval color=case(StepResult=="Passed","green",StepResult=="Skipped","gold", StepResult=="Failed","red")
 | eval StepResult = Description

This will give results sorted ascending order of _time. If you want descending order, just add | sort -_time in the end.

Also, your last eval just overwrites value of StepResult field with value of Description field. Do you want to show two field with same value here?

Re: Why is the event sort not reflecting in Statistics tab?

mawomommoh — Wed, 07 Mar 2018 19:37:08 GMT

It worked. Thanks!

Yes, based on what I am working on I need those two fields to have the same value.

You can add your answer so I give you credit.

Thanks for the assistance once again. 🙂

Re: Why is the event sort not reflecting in Statistics tab?

DalJeanis — Wed, 07 Mar 2018 20:02:38 GMT

@mawomommoh - We converted @somesoni2's comment to an answer so you can accept it.