topic Re: Lookup table help in Splunk Search

Lookup table help

picaresqu3 — Wed, 31 Jul 2019 01:32:43 GMT

Hi All,

Still learning the ropes here, but am making some dashboards and could use some help with a lookup table. I have a panel that gives me the top 10 used ports leaving my router. Would like to match the dest_port with a description for what runs on said port, which is contained in the lookup csv.

I've imported my CSV into Splunk (port_descriptions.csv) and it has 3 colums: protocol, port, description

Here is a quick shot of what it looks like:

protocol    port    description
TCP         0   Reserved
TCP         1   Port Service Multiplexer
TCP         2   Management Utility
TCP         3   Compression Process

Here is my current search,

index=nwk AND action=allowed AND protocol=TCP
| top dest_port
| rename dest_port AS "Destination Port"
| rename count AS "Hits"
| rename percent AS "Percent of Top 10"

I've tried adding the following, but it seems to give me different results than the original search above. Not sure if i should also have the search map the protocol, too?

| lookup port_descriptions.csv port AS dest_port
| top dest_port by description

Anyway, if you've come this far, thanks for reading and trying to help a noobie out.

Re: Lookup table help

gcusello — Wed, 31 Jul 2019 11:16:59 GMT

Hi picaresqu3,
try something like this:

 index=nwk action=allowed protocol=TCP
 | top dest_port
 | lookup port_descriptions.csv port AS dest_port OUTPUT description
 | rename dest_port AS "Destination Port" count AS "Hits" percent AS "Percent of Top 10" description AS Description
 | table "Destination Port" Description Hits "Percent of Top 10"

Bye.
Giuseppe

Re: Lookup table help

picaresqu3 — Wed, 31 Jul 2019 15:05:17 GMT

@gcusello thank you! I was getting duplicates in the description field since the CSV had TCP and UDP entries, but I just broke them out into 2 separate lookup table files to fix. Thank you! 😄

Re: Lookup table help

gcusello — Wed, 31 Jul 2019 17:33:27 GMT

Hi picaresqu3,
if this answer helped you, please accept and/or upvote it.
Bye, see next time.
Giuseppe