topic Re: Result Varies by Including Sort Option in Splunk Search

How to get the latest status of the workflow?

Nithianandan — Thu, 01 Dec 2022 16:43:41 GMT

Hello Splunkers,

Workflows are monitored through splunk. Workflows has different stages like running , paused, cancelled and completed. I have to get the latest status of the workflow. I am using sort - _time option to get the latest data of the status. Along with the search query by using the sort - _time option, data count varies.

For last 7 days

index=... | table _time EXECUTION_NAME STATUS EXECUTION_ID Stage Environment source | dedup EXECUTION_ID | chart count(EXECUTION_ID) as Workflows_Triggered by Environment,STATUS

Environment COMPLETED PAUSED RUNNING

XXX

94498

sort -_time option is used for last 7 days.

index=... | table _time EXECUTION_NAME STATUS EXECUTION_ID Stage Environment source | sort -_time | dedup EXECUTION_ID | chart count(EXECUTION_ID) as Workflows_Triggered by Environment,STATUS

Environment COMPLETED RUNNING

XXX

9735

reason for using sort -_time is get the latest status of the execution_id. completed will be appeared when the dedup is done.

_time STATUS EXECUTION_ID

2022-11-30 12:20:00.492	RUNNING	12345678901
2022-11-30 12:20:18.000	COMPLETED	12345678911

Requesting for support. Thank you !!!

Re: Result Varies by Including Sort Option

yuanliu — Thu, 01 Dec 2022 12:42:45 GMT

Whereas this behavior is confusing, I still do not understand the reason for this sort.

This said, here is an easy confirmation of the mysterious behavior that everyone can run:

index=_internal earliest=-1d@d latest=-0d@d
``` | sort - _time ```
| stats count by eventtype

No sort, my search gives

eventtype	count
splunk-access	5046
splunk-log	59418

index=_internal earliest=-1d@d latest=-0d@d
| sort - _time
| stats count by eventtype

With count, my count becomes

eventtype	count
splunk-access	236
splunk-log	2722

To eliminate variability, this search is limited to the previous day.

I am sure there's an explanation. I just don't have it.

Re: Result Varies by Including Sort Option

PickleRick — Thu, 01 Dec 2022 13:17:13 GMT

Firstly, small technical remark - if you post a search, include it as a code block or a preformatted paragraph - it's easier to read.

But to the point.

There are some things that shouldn't be happening here 😉

index=... 
| table _time EXECUTION_NAME STATUS EXECUTION_ID Stage Environment source
| dedup EXECUTION_ID
| chart count(EXECUTION_ID) as Workflows_Triggered by Environment,STATUS

1. Don't table too early. Table is a transforming command which means that it will move the processing pipeline to search-heads. It's nice to have it at the end of your search for presentation of the results but in the middle of your search you most probably get better results with fields command which is a distributable streamming commands. (if you want to get rid of _raw, just do "| fields - _raw").

2. Your thinking is wrong here. You do a dedup EXECUTION_ID which means that you'll get only the first encountered event with given EXECUTION_ID regardless of other fields. If your events always contain the same Environment and STATUS fields, it could be OK, but otherwise out of every possible combination of Environment and STATUS values you'll only count your EXECUTION_ID into on of those groups. That's most probably not what you meant.

You only get your EXECUTION_ID counted for a single _first encountered_ (which does not have to be the earliest! typically search results are given in reverse chronological order) combination of Environment STATUS (and other fields as well). So if you resort your data, you might pick another event from deduping.

A run-anywhere example to show what I mean

| makeresults count=60
| streamstats count
| eval _time=_time-count 
| eval field1=count % 5
| eval field2=count % 4
| eval field3=count % 3
| fields - count
| sort - _time
| dedup field1

If you cut the search before the dedup, you'll see that you have every possible combination of (0-4), (0-3) and (0-2) ranges. But if you dedup it on one of the fields you'll only get some combinations in results.