topic Re: Sort and Grouping Question in Splunk Search

Sort and Grouping Question

lemikg — Mon, 28 Sep 2020 13:13:42 GMT

Hi Splunkers,

this might sound stupid. I am trying to query and table host, COMMAND and USER which works fine. But is it possible to group the host? I want to display each COMMAND and USER value under one host name.



Host                      COMMAND   USER

www.off.dus.acompany.com  ini_t     root

                          kthreadt  root

                          ksoftirqd root

                          migration root

                          watdog/0  root

                          mirtion/1 root

                          ksoftqd/1 root

www.off.ber.bcompany.com  ini_t     root

                          kthreadt  root

                          ksoftirqd root

                          migration root

                          watdog/0  root

                          mirtion/1 root

                          ksoftqd/1 root

This is all I got

sourcetype=pu OR sourcetype=tik COMMAND | multikv | table host COMMAND USER

In terms of visuals it is almost the same case as the following link

http://splunk-base.splunk.com/answers/25102/question-regarding-grouping-of-results-into-a-table

Thanks in advance for your help.

Best regards
Mike

Re: Sort and Grouping Question

MHibbin — Mon, 04 Feb 2013 16:08:18 GMT

Perhaps mvcombine could be used? http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Mvcombine

Re: Sort and Grouping Question

yannK — Mon, 04 Feb 2013 16:09:36 GMT

Not very nice, but you can group the COMMAND and USER on a single columns and try the commands values().

mysearch | eval command_user= COMMAND." ".USER | stats values(command_user) AS list_command_user by host

But the duplicates will be removed, so if you really need the count, add some magic.

mysearch | eval command_user= COMMAND." ".USER | stats count by command_user host | eval command_user_count=command_user." [".count."]" | stats values(command_user_count) AS list_command_user_count by host

Re: Sort and Grouping Question

lemikg — Mon, 28 Sep 2020 13:13:50 GMT

thanks, i copy&pasted eval command_user= COMMAND." ".USER
| stats values(command_user) AS list_command_user by host. But it shows only the hosts. Is there anything I need to do else?

Re: Sort and Grouping Question

Paolo_Prigione — Mon, 04 Feb 2013 21:34:18 GMT

| stats list() will keep duplicate user-command tuples.

sourcetype=pu OR sourcetype=tik COMMAND 
| multikv  
| strcat "[" USER "] " COMMAND user_command
| stats list(user_command) by host

Re: Sort and Grouping Question

lemikg — Tue, 05 Feb 2013 08:16:07 GMT

Perfect! Thank you very much!!!

Re: Sort and Grouping Question

lemikg — Tue, 05 Feb 2013 08:43:33 GMT

btw how do I now count the values in the field list(user_command)?

Re: Sort and Grouping Question

Paolo_Prigione — Mon, 28 Sep 2020 13:14:05 GMT

replace "| stats list(user_command) by host" with "| stats list(user_command) count dc(user_command) as distinct_count by host"