topic How can I sort a field alphabetically and then by total? in Splunk Search

How can I sort a field alphabetically and then by total?

jwalzerpitt — Mon, 14 Aug 2017 19:36:32 GMT

I have the following search in which I'm trying to sort first alphabetically and then by total, but the Processes field is not sorting alphabetically.

index=sysmon | stats count by process,ParentImage | sort -count | stats dc(process) as Total, list(process) as Processes by ParentImage | sort +str(Processes), -total

Any idea as to what I'm missing or not doing correctly?
Thx

Re: How can I sort a field alphabetically and then by total?

jwalzerpitt — Mon, 14 Aug 2017 19:39:47 GMT

Modified search to as below, but still no luck

index=sysmon | stats count by process,ParentImage | sort +str(process),-count | stats dc(process) as Total, list(process) as Processes by ParentImage | sort +str(Processes), -total

Re: How can I sort a field alphabetically and then by total?

richgalloway — Mon, 14 Aug 2017 19:54:30 GMT

What sort order are you getting?

Re: How can I sort a field alphabetically and then by total?

jwalzerpitt — Mon, 14 Aug 2017 20:00:04 GMT

I get the correct sort order based on Total, but the Processes field is all over the place. Here's from the first listing:

    cmd.exe
notepad++.exe
Update.exe
aruser.exe
firefox.exe
runonce.exe
Box Edit.exe
Box Local Com Service.exe
CCleaner64.exe
DellSystemDetect.exe
IAStorIconLaunch.exe
ImageTray.exe
ONENOTEM.EXE
OUTLOOK.EXE
OneDrive.exe
RDCMan.exe
SnippingTool.exe
WINWORD.EXE
chrome.exe
explorer.exe
lync.exe
netsession_win.exe
vmtoolsd.exe

Re: How can I sort a field alphabetically and then by total?

somesoni2 — Mon, 14 Aug 2017 20:38:49 GMT

The Processes field in the end is a multivalued field hence doesn't get sorted using sort command. Give this a try

index=sysmon | stats count by process,ParentImage | sort -count | stats dc(process) as Total, list(process) as Processes by ParentImage | eval Processes=mvsort(Processes) | sort -total

Please note that Processes are sorted lexicographically (upper case are sorted first then lower case)

Re: How can I sort a field alphabetically and then by total?

jwalzerpitt — Mon, 14 Aug 2017 20:49:34 GMT

Thx as that worked (as well as the explanation on sorting lexicographically). With that knowledge, I modified my search as below which allowed for sorting alphabetically on the Processes field.

index=sysmon | eval process=lower(process) | stats count by process,ParentImage | sort -count | stats dc(process) as Total, list(process) as Processes by ParentImage | eval Processes=mvsort(Processes) | sort -total

Please copy your reply into the answer field so I can mark it as such, and thx again for the help!

Re: How can I sort a field alphabetically and then by total?

somesoni2 — Mon, 14 Aug 2017 20:53:29 GMT

Here you go.

Re: How can I sort a field alphabetically and then by total?

jwalzerpitt — Mon, 14 Aug 2017 20:57:28 GMT

Thx again!