Which type of host is talking to which type of host on which port

[erikschubert]

Hey everyone,

I want to create a search that gives me the following information in a structured way: Which type of host sends data to which type of host using which port? In a table it would basically look like this: typeOfSendingHost|typeOfReceivingHost|destPort

At the moment I have the following search, which shows me which type of host is listening on which port. The subsearch is used to provide the type of system based on splunkname. Therefore, the field splunkname is created in the main search.

(index="_internal" group=tcpin_connections)
|rename host AS splunknames
|join type=left splunkname
[|search index=index2]
|stats values(destPort) by type

Example Output:

type	values(destPort)
Indexer	9995, 9997
Intermediate Forwarder	9996, 9997

In the _internal index, the sending system is stored in the field "hostname" and the receiving system is stored in "host". The field "destPort" is the port to which data is sent. Information about our systems is stored in index2. An event in index2 has the field "splunkname" and "type". The field "splunkname" in index2 contains the hostname of the system (e.g. fields hostname/host). The field "type" stores the type of the system (Forwarder, Indexer, Search Head...).

My question is, how can I make the results look like this?

Sending System Type	Receiving System Type	destPort
Intermediate Forwarder	Indexer	9997

Thank you so much in advance

[bowesmana]

What you're effectively trying to do is use the host information in index2 as a lookup to determine type from the host name.

It would make sense if that is static data, to create a lookup of that information and simply use the lookup function to get the type, e.g. to create the lookup

index=index2
| stats count by splunkname type
| fields - count
| outputlookup host_lookup.csv

and then in your search, do

(index="_internal" group=tcpin_connections)
| lookup host_lookup splunkname as hostname OUTPUT type as sendingType
| lookup host_lookup splunkname as host OUTPUT type as receivingType
| stats values(destPort) as destPorts by sendingType receivingType

[erikschubert]

Thanks for your reply. Is it not possible to achieve it without using a lookup? Similiar to what I've used in the search above?

[bowesmana]

If you use join, you will have to join twice, first to get the type of the sending host and secondly to get the type of the receiving host.

You can also do it with some fiddly eventstats.

Here is a pseudo example of using eventstats 

| makeresults count=20
| eval hostname="host-".mvindex(split("ABCDEFGHIJKLMNOPQRSTUVWXYZ", ""), random() % 26)
| eval host="host-".mvindex(split("ABCDEFGHIJKLMNOPQRSTUVWXYZ", ""), random() % 26)
| eval destPort=mvindex(split("9995,9996,9997", ","), random() % 3)
| append [
  | makeresults count=26
  | streamstats c
  | eval splunkname="host-".mvindex(split("ABCDEFGHIJKLMNOPQRSTUVWXYZ", ""), c - 1)
  | eval type=mvindex(split("Intermediate Forwarder,Indexer", ","), random() % 2)
  | fields - c
]
``` The above sets up a data set of 20 rows of send/receive events 
    and the append makes the 'library' of the 26 possible host types.
    Now this logic will 'join' the types to the events ```
| eval host=coalesce(host, splunkname)
| eval hostname=coalesce(hostname, splunkname)
``` join the type to receiving host ```
| eventstats values(type) as receivingType by host
``` join the type to sending hostname ```
| eventstats values(type) as sendingType by hostname
| where isnotnull(destPort)
| stats values(destPort) as destPorts by sendingType receivingType

and a similar example using a double join

| makeresults count=20
| eval hostname="host-".mvindex(split("ABCDEFGHIJKLMNOPQRSTUVWXYZ", ""), random() % 26)
| eval host="host-".mvindex(split("ABCDEFGHIJKLMNOPQRSTUVWXYZ", ""), random() % 26)
| eval destPort=mvindex(split("9995,9996,9997", ","), random() % 3)
| join hostname [
  | makeresults count=26
  | streamstats c
  | eval splunkname="host-".mvindex(split("ABCDEFGHIJKLMNOPQRSTUVWXYZ", ""), c - 1)
  | eval type=mvindex(split("Intermediate Forwarder,Indexer", ","), random() % 2)
  | fields - c
  | rename splunkname as hostname 
]
| rename type as sendingType
| join host [
  | makeresults count=26
  | streamstats c
  | eval splunkname="host-".mvindex(split("ABCDEFGHIJKLMNOPQRSTUVWXYZ", ""), c - 1)
  | eval type=mvindex(split("Intermediate Forwarder,Indexer", ","), random() % 2)
  | fields - c
  | rename splunkname as host
]
| rename type as receivingType
| fields - _time
| stats values(destPort) as destPorts by sendingType receivingType

Note that both of these involve append or join, which are not the best commands to use, as they can have subsearch data limitations.