Splunk Search

How to extend a dataset to add a column that represents a numerical column as text?

LeeMoe
Path Finder

I have a dataset which has a column "Port" that contains (limited) numerical values.  I want to make these values display as text (e.g. 443 == HTTPS).  I could do this in Excel but I'm a Splunk newbie and frankly in need of a nudge in the right direction....I assume it would be some kind of lookup?

I would then pull the text values into a pivot for a dashboard to replace my current one with the port numbers.

Kudos and virtual shiny things for anyone who can help 🙂

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Use the same answer I gave earlier, except change the file name and output field name.

BTW, lookup files typically don't contain timestamps.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

LeeMoe
Path Finder

Thank you, this looks awesome:

OC-dash-namedports.png

0 Karma

LeeMoe
Path Finder

Rich, thank you.  I now have meaningful things in my dashboard and I am grateful.

OC-dash-namedports.png

 Splunk is brilliant and I think you are pretty darn cool too 🙂

LeeMoe
Path Finder

Rich, thanks, you shook my head enough to go and load the file into a Lookup (duh).

So now I parse my thing and get two columns for Port and Protocol based on my lookup - which is cool.  Thank you so much.  If I wanted to mix the fields from my index and my lookup, I am sure that's simple to you too (but not to me, sorry).  Can you point me in the right direction there too?  

index="myindex"
| rex "message=\"(?<message>{.+})\" +path="
| eval message = replace(message, ".\"", "\"")
| spath input=message
| lookup ports_protocols.csv Port OUTPUT Protocol | table Port Protocol

0 Karma

LeeMoe
Path Finder

Background: this builds on a previous question.

I've now imported a CSV file into a new index and stored it in a new table/dataset (ports_protocols)

index=”myindex” | rex "message=\"(?<message>{.+})\" +path="
| eval message = replace(message, ".\"", "\"")
| spath input=message
| lookup ports_protocols Port OUTPUT Protocol | table Port Protocol

Without the lookup, it works perfectly but of course my Port field is numeric and I would like it to be more human-readable.

Clearly I did something wrong up to now.....advice? Thanks 🙂

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Why did you index the lookup file?

The lookup command is used with lookup table files, not indexes or datasets.  Use the command with your ports_protocols.csv file.

---
If this reply helps you, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Yes, you would use a lookup table.  Start by creating a CSV file something like this:

PortService
22

ssh

80HTTP
443HTTPS

 

Call it "ports.csv", for example.  Then use the lookup command to map port number to service.

 

 

...
| lookup ports.csv Port OUTPUT Service
| table Port Service

 

 

---
If this reply helps you, Karma would be appreciated.

LeeMoe
Path Finder

Thanks, Rich.  My mind was on the right approach.

I have two datasets now, one is my main log source and the other I created as a lookup source (I hope that's right?) called "Lookup_Ports_Protocols" (_time, Port, Protocol columns).  I assume I want to add a column to my main dataset that looks this up, I don't quite know how to bring that into being.  Or do I need to construct a new dataset from these two?

Thanks and sorry for being a bit average at this.  I excel at many other things, trust me!

Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Use the same answer I gave earlier, except change the file name and output field name.

BTW, lookup files typically don't contain timestamps.

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...