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

[LeeMoe]

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 🙂

[LeeMoe]

Thank you, this looks awesome:

[LeeMoe]

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

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

[LeeMoe]

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

[LeeMoe]

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 🙂

[richgalloway]

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.

[richgalloway]

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

Port	Service
22	ssh
80	HTTP
443	HTTPS

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

[LeeMoe]

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!

[richgalloway · Accepted solution]

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

BTW, lookup files typically don't contain timestamps.