Splunk Search

Replace letters with numbers

timrich66
Communicator

Hi all,

I have an alert that looks for a specific message that includes the record ID.

I would like to be able to create a numeric value for that ID that could be used to create a unique ID when raising a ServiceNow ticket.  Therefore, all alerts for the same record ID would write to the same SNow ticket.

The record ID is a string of 7 alphanumerics - e.g. abc4efg

I would like to be able to change "abc4efg" to "1234567".  Thus the number does not change, but the letters are all the equivalent 1-26 number.

The only constant is the length of the record ID which is 7 characters.

I've looked at many answers, sadly none provide exactly what I am looking for.

Is this something that could be achieved using SED?  

Thanks in advance.

Labels (5)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

Why '"generate" if you can use the value directly?

| eval myID=tonumber(yourID,36)

 Unless you want to have 7 digits at most. In this case good luck avoiding colisions 😉

View solution in original post

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Why '"generate" if you can use the value directly?

| eval myID=tonumber(yourID,36)

 Unless you want to have 7 digits at most. In this case good luck avoiding colisions 😉

0 Karma

timrich66
Communicator

Thanks @PickleRick , I had tried using  "eval myId=tonumber(recordId,26)" not realising I had to use base 36 to include 0-9.

I am using strftime on the date and combining the date and recordId to give me one SNow ID per day for each record.

This gives me the solution I require.

0 Karma

cmtri
Explorer

Try this rex command out, you'll need to go up to z of course:

 

| makeresults 1
| eval your_field="abc4ef"
| rex mode=sed field=your_field "s/a/1/g s/b/2/g s/c/3/g s/e/5/g s/f/6/g"

0 Karma

maciep
Champion

if you're looking to do this in search, then something like this might work - wouldn't be surprised if there was a better way.  But essentially an array for each letter, then loop over the recordid and replace letters with associated index of that letter in the letters array

| stats count 
| eval recordId="abc4efg"
| eval mvRecordId = split(recordId,"")
| eval letters=split("|,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z",",")
| eval serviceNowId = mvmap(mvRecordId,if(mvfind(letters,lower(mvRecordId))>0,mvfind(letters,lower(mvRecordId)),mvRecordId))
| eval serviceNowId = mvjoin(serviceNowId,"")
0 Karma

timrich66
Communicator

Thanks @maciep , unfortunately our install is still on v7.3.2 so I cannot use 'mvmap'.  I will have to push the provider for an upgrade 🙂

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Based on @maciep  solution, here is one that doesn't use mvmap

| makeresults
| eval recordId="abc4efg"
| eval mvRecordId = split(recordId,"")
| eval letters=split("|,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z",",")
| eval serviceNowId=""
| foreach 1 2 3 4 5 6 7
    [| eval letter=mvindex(mvRecordId,<<FIELD>>-1)
    | eval serviceNowId=mvappend(serviceNowId,if(mvfind(letters,lower(letter))>0,tostring(mvfind(letters,lower(letter))),letter))]
| fields - letter letters mvRecordId
| eval serviceNowId = mvjoin(serviceNowId,"")
0 Karma

PickleRick
SplunkTrust
SplunkTrust

This solution is prone to collision.

How do you distinguish "akakaka" from "kakakaa"? Or "111aaa1" from "1a1aa1a"?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Correct - but the OP doesn't specify how this should be avoided. There are a couple of ways this might be done. The indexes could be prefixed with a leading zero or two, the values could be joined with zeroes or nines or some other digit.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Sure, he didn't. But taking into account his desired usage, collisions should be avoided. Otherwise there's no point in this conversion.

As I wrote before, tonumber() gives a very straightforward conversion .

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...