Decimal (base10) numbers into ASCII string format (base36)
In Splunk we can convert any text encoded ASCII string into a decimal number (base10), which is a numeric representation, using an eval function known as “tonumber”. But there are no other options to do the opposite one.
In this blog, we will convert decimal (base10) numbers into ASCII string format (base36).
In this blog, we will demonstrate the two best possible ways to do the above requirements.
Part 1: “Using SPL Query”
We have a field called “OrderNo”, which is in decimal numbers. We will convert that decimal into base36.
index="demo" sourcetype="orderNo"
| table OrderNo,Owner
| eval x=OrderNo,power=mvrange(0,21)
| mvexpand power
| eval base2=pow(36,power)
| where x>=base2
| eval div=floor(x/base2),
bin=div%36,
mybin=case(bin=0,"0",bin=1,"1",bin=2,"2",bin=3,"3",bin=4,"4",bin=5,"5",bin=6,"6",bin=7,"7",bin=8,"8",bin=9,"9",bin=10,"A",bin=11,"B",bin=12,"C",bin=13,"D",bin=14,"E",bin=15,"F",bin=16,"G",bin=17,"H",bin=18,"I",bin=19,"J",bin=20,"K",bin=21,"L",bin=22,"M",bin=23,"N",bin=24,"O",bin=25,"P",bin=26,"Q",bin=27,"R",bin=28,"S",bin=29,"T",bin=30,"U",bin=31,"V",bin=32,"W",bin=33,"X",bin=34,"Y",bin=35,"Z")
| stats max(x) as x, list(bin) as bin, list(mybin) as mybin by OrderNo,Owner
| mvcombine delim="" mybin
| eval M=mvindex(split(mybin," "),0),
N=mvindex(split(mybin," "),1),
O=mvindex(split(mybin," "),2),
P=mvindex(split(mybin," "),3),
Q=mvindex(split(mybin," "),4),
base36=lower("0"."".P."".O."".N."".M)
| fields OrderNo,Owner,base36
Results:
Explanation:
Due to the size of the query, we will explain by the line number (which is available in the above figure) for better understanding.
Line1: we took the data from index “demo” and a sourcetype called “orderNo”.
Line2: then using the table command we tabled “orderNo” and “owner” field
Line3: then we create a field called “x” which is the same as “orderNo ” and using the “mvrange” function we created a field called “power”, which will consist of values from o to 21.
Line4: using “mvexpand” we had expanded the “power” field into a single-valued field.
Line5: then using the “eval” command we created a field called “base2” using the “pow” function. Which will basically consider “power” as a power of 36 and will calculate that value within the “base2” field.
Line6: using where command we are considering only those rows where “X”(i.e. orderNo) is greater than equal to “base2”.
Line7: then in the “div” field we are calculating “x” divided by “base2” and using floor function we are considering the nearest whole integer of that calculated value.
Line8: in the “bin” field we are calculating the modulus of the “div” (dividend) field with 36 (divisor). And creating a field called “binx” with the same value of “bin”.
Line9: then we are creating a field called “mybin”, then using the case function we are converting “bin” which is in decimal, into “base36”.
Line10: then using we grouped all the fields using stats command by “orderNo” and “owner” field.
Line11: using the “mvcombine” command we combined the “mybin” field.
Line12: using eval command we created four fields called “M,N,O and P ” where we are splitting values of “mybin” one by one.
Line13: finally we are creating a field called “base36“, where we are concatenating “” and then “P,O,N and M” respectively. And using the fields command we are only taking required fields.
Hope you have enjoyed this blog, we will come back with new topics of Splunk. Until then goodbye and stay safe and strong.
Happy Splunking !!
