topic Re: How to group by multiple fields? in Splunk Search

How to group by multiple fields?

erikschubert — Mon, 30 Jan 2023 16:39:49 GMT

Hi everyone,

I'm kinda new to splunk.

I have two indizes:

Stores events (relevant fields: hostname, destPort)

2. Stores information about infrastructure (relevant fields: host, os)

I need to show which Ports are used by which os.

From the first index I need to know which host is using which ports.

From the second index I want to know which host is using which os. I then want to see, which OS uses which ports. To make it even more difficult, the host is called hostname in index 1 and host in index 2.

I basically have to group by hostname and then by OS.

How can i achieve this? What concepts can I use?

I have two working searches for step 1 and step2, I just dont know how to "join" them together.

Any help is greatly appreciated, thanks.

Re: How to group by multiple fields?

tej57 — Mon, 30 Jan 2023 16:46:52 GMT

Hello @erikschubert ,

You can try below search:

Re: How to group by multiple fields?

erikschubert — Mon, 30 Jan 2023 17:02:04 GMT

@tej57 wrote:
Hello @erikschubert ,
You can try below search:

Hi,

this displays which host is using which Port, but the column OS stays empty 😞

Re: How to group by multiple fields?

bowesmana — Mon, 30 Jan 2023 23:34:00 GMT

Avoid join in Splunk where possible - it has limitations and is not really the Splunk way to do things, as joining can easily be done with stats

search <dataset_1> OR <dataset_2> | eval host=if(index=dataset_1, hostname, host) | stats values(destPort) as ports values(os) as os by host | stats values(ports) as ports by os

Assuming that there is only 1 os per host, then the first stats will give all the ports for the host along with its os and then the second stats will give all the ports by os.