topic Re: Extending a dataset to add a column that represents a numerical column as text in Splunk Search

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

LeeMoe — Tue, 14 Feb 2023 16:48:26 GMT

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 🙂

Re: Extending a dataset to add a column that represents a numerical column as text

richgalloway — Tue, 14 Feb 2023 16:47:19 GMT

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

Re: Extending a dataset to add a column that represents a numerical column as text

LeeMoe — Wed, 15 Feb 2023 12:30:36 GMT

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!

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

LeeMoe — Wed, 15 Feb 2023 17:14:19 GMT

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 🙂

Re: Extending a dataset to add a column that represents a numerical column as text

richgalloway — Wed, 15 Feb 2023 18:46:16 GMT

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

BTW, lookup files typically don't contain timestamps.

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

richgalloway — Wed, 15 Feb 2023 18:50:54 GMT

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.

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

LeeMoe — Wed, 15 Feb 2023 20:46:27 GMT

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

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

LeeMoe — Wed, 15 Feb 2023 21:07:38 GMT

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 🙂

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

LeeMoe — Wed, 15 Feb 2023 21:19:44 GMT

Thank you, this looks awesome: