Splunk Search

Sort and Grouping Question

Communicator

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

0 Karma
1 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

View solution in original post

| 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

View solution in original post

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

0 Karma

Communicator

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

0 Karma

Communicator

Perfect! Thank you very much!!!

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

Communicator

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?

0 Karma

Influencer
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!