topic Re: Replace letters with numbers in Splunk Search

Replace letters with numbers

timrich66 — Fri, 03 Sep 2021 14:18:06 GMT

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.

Re: Replace letters with numbers

maciep — Fri, 03 Sep 2021 15:29:57 GMT

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,"")

Re: Replace letters with numbers

timrich66 — Fri, 03 Sep 2021 15:42:53 GMT

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 🙂

Re: Replace letters with numbers

cmtri — Fri, 03 Sep 2021 15:55:45 GMT

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"

Re: Replace letters with numbers

PickleRick — Fri, 03 Sep 2021 20:35:39 GMT

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 😉

Re: Replace letters with numbers

ITWhisperer — Sat, 04 Sep 2021 09:51:36 GMT

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

Re: Replace letters with numbers

PickleRick — Sat, 04 Sep 2021 18:57:05 GMT

This solution is prone to collision.

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

Re: Replace letters with numbers

ITWhisperer — Sat, 04 Sep 2021 19:08:06 GMT

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.

Re: Replace letters with numbers

PickleRick — Sat, 04 Sep 2021 20:05:43 GMT

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 .

Re: Replace letters with numbers

timrich66 — Mon, 06 Sep 2021 10:24:14 GMT

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.