Splunk Search

How to group by multiple fields?

erikschubert
Engager

Hi everyone,

I'm kinda new to splunk.

I have two indizes:

  1. 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.

Labels (3)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma

tej57
Contributor

Hello @erikschubert ,

You can try below search:

index=events
| fields hostname,destPort
| rename hostname as host
| join type=outer host
    [| search index=infrastructure
     | fields os]
| table host destPort os
0 Karma

erikschubert
Engager

@tej57 wrote:

Hello @erikschubert ,

You can try below search:

 

index=events
| fields hostname,destPort
| rename hostname as host
| join type=outer host
    [| search index=infrastructure
     | fields os]
| table host destPort os

 


Hi,

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

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...