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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...