Sort and Grouping Question

[lemikg]

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

[Paolo_Prigione · Accepted solution]

| 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

[Paolo_Prigione]

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

[lemikg]

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

[lemikg]

Perfect! Thank you very much!!!

[yannK]

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

[lemikg]

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?

[MHibbin]

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