Splunk Search

Convert categorical string to number

zeophlite
New Member

I have a field in my events that is a string (but does not translate to a number directly)

Is there a way to convert this string to an integer consistently (value does not matter), such as using a hash function? The functions available, such as md5 convert strings to strings, but is there a way to convert this back to an integer? An example is as follows:

user     favorite_fruit     fruit_number
bob      Apple                   1
jane     Pear                    2
pete     Apple                   1

Where user and favorite_fruit are known at index-time, and fruit_number is calculated at search-time. The actual value of fruit_number is arbitrary and doesn't need to be sequential.

I can't use a lookup, as the list of favorite_fruit's is arbitrary.

0 Karma
1 Solution

renjith_nair
Legend

Try something similar. You can use different by clause in streamstats and eventstats based on requirement.

 |stats count|eval fruit="apple,orange,apple,apple,cherry"|eval user="bob" | makemv delim="," fruit| makemv delim="," user|mvexpand fruit|streamstats count|eventstats first(count) as fruit_number by fruit|fields - count

Just add |streamstats count|eventstats first(count) as fruit_number by fruit|fields - count to your original search

---
What goes around comes around. If it helps, hit it with Karma :slightly_smiling_face:

View solution in original post

0 Karma

renjith_nair
Legend

Try something similar. You can use different by clause in streamstats and eventstats based on requirement.

 |stats count|eval fruit="apple,orange,apple,apple,cherry"|eval user="bob" | makemv delim="," fruit| makemv delim="," user|mvexpand fruit|streamstats count|eventstats first(count) as fruit_number by fruit|fields - count

Just add |streamstats count|eventstats first(count) as fruit_number by fruit|fields - count to your original search

---
What goes around comes around. If it helps, hit it with Karma :slightly_smiling_face:
0 Karma

zeophlite
New Member

Hi Renjith, apologies, I've updated my question to give an example

0 Karma

renjith_nair
Legend

Ok got it.

Try something similar. You can use different by clause in streamstats and eventstats based on requirement.

|stats count|eval fruit="apple,orange,apple,apple,cherry"|eval user="bob" | makemv delim="," fruit| makemv delim="," user|mvexpand fruit|streamstats count|eventstats first(count) as fruit_number by fruit|fields - count

Just add |streamstats count|eventstats first(count) as fruit_number by fruit|fields - count to your original search

---
What goes around comes around. If it helps, hit it with Karma :slightly_smiling_face:

zeophlite
New Member

Works great, please edit this into your answer

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...