How to count events sorting by count in a multivalue fields

[cindygibbs_08]

Hello guys I have this SPL

| stats count(events) by type process

and it gives me something CORRECT like this:

PROCESS	TYPE OF ALERT	COUNT
A	RED FLAG	458
A	ISJD	5245
A	IOO	21452
A	XCNCNC	125
B	LPOLSSS	21
B	SSSSSS	584
B	RED FLAG	284
B	ISJD	455
C	RED FLAG	255214
C	ISJD	55551
C	IOO	8569

but when I do this:

| stats count(events) by type process
| stats values(*) as * by process

I get something incorrect because the type or erros do not correspond witht he count field next to them because splunk seems to order the m in anotehr fashion, like this for example which is not correct

PROCESS	TYPE OF ALERT	COUNT
A	IOO                        ISJD                      RED FLAG    XCNXNX	125             5245              458         21452

and so the rows for B and C will also be mixed up


I will like to have them showm like this: WHICH is correct

is there  a proper way to do that guys THANK you so much in advance!

kindly
C

[ITWhisperer]

values will sort the multi-values lexicologically (and remove duplicates) whereas list will maintain the order and preserve all values

| stats count(events) by type process
| stats list(*) as * by process

[cindygibbs_08]

@ITWhisperer  hello  dearest! First thanks for the explanation between list and values I actually did use list before but for some reason this command always returns some "gibberish" values in the list field such as:

"Splusk_x67383_ap_73828828383" 

But I know this is something from splunk or maybe is something with my version of splunk because this weird things do not appear when I use values... Is it because I have splunk 8?

[ITWhisperer · Accepted solution]

Try just listing the fields that you are interested in.

| stats count(events) as count by type process
| stats list(type) as type list(count) as count by process

[kamlesh_vaghela]

@cindygibbs_08 

Can you please try this?

YOUR_SEARCH
| sort PROCESS - COUNT
| autoregress PROCESS p=1 as pre_PROCESS
| eval PROCESS=case(isnull(pre_PROCESS),PROCESS,PROCESS!=pre_PROCESS,PROCESS,1=1,null()) | fields - pre_PROCESS
| eval PROCESS=if(isnull(PROCESS),TYPE_OF_ALERT,PROCESS)
| eval TYPE_OF_ALERT=if(PROCESS=TYPE_OF_ALERT,COUNT,TYPE_OF_ALERT)
| eval COUNT=if(COUNT=TYPE_OF_ALERT,null(),COUNT)

My Sample Search :

| makeresults | eval _raw="PROCESS	TYPE OF ALERT	COUNT
A	RED FLAG	458
A	ISJD	5245
A	IOO	21452
A	XCNCNC	125
B	LPOLSSS	21
B	SSSSSS	584
B	RED FLAG	284
B	ISJD	455
C	RED FLAG	255214
C	ISJD	55551
C	IOO	8569"| multikv forceheader=1
|table PROCESS TYPE_OF_ALERT COUNT
| sort PROCESS - COUNT
| autoregress PROCESS p=1 as pre_PROCESS
| eval PROCESS=case(isnull(pre_PROCESS),PROCESS,PROCESS!=pre_PROCESS,PROCESS,1=1,null()) | fields - pre_PROCESS
| eval PROCESS=if(isnull(PROCESS),TYPE_OF_ALERT,PROCESS)
| eval TYPE_OF_ALERT=if(PROCESS=TYPE_OF_ALERT,COUNT,TYPE_OF_ALERT)
| eval COUNT=if(COUNT=TYPE_OF_ALERT,null(),COUNT)

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[cindygibbs_08]

@kamlesh_vaghela  thank you so much this definitely works but I feel like I will get a llot of use of this in other problems I have to Solve thank you so much