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 | 1 | 56 |
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 | 5 |
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 !!!
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.
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.