How do I convert a hex string to an Ascii string in Kusto

user_7828805 0
Viewed 650

I have a table that has multiple columns that contain information stored as Ascii strings. It does not appear that there is a built-in function to do that. Here is my sample input:

let T=datatable(Hex1:string, Hex2:string) ["74 65 73 74 31", "74 65 73 74 32", "74 65 73 74 33", "74 65 73 74 34"

I can make an ugly work-around by renaming each column to "Hex" joining it with thethe output of the following function and then renaming the column back to Hex1, Hex2, etc. but that seems very kludgy. Ideally I am looking for a scalar function that accepts a string like "74 65 73 74 31" and returns its Ascii equivalent "test1"

.create-or-alter function with (folder="Test") ['StringHexToAscii'](T:(Hex:string)) {
let Converter = datatable (Hex:string, Ascii: string) [
"0","\x00",  "1","\x01",  "2","\x02",  "3","\x03",  "4","\x04",  "5","\x05",  "6","\x06",  "7","\x07",  "8","\x08",  "9","\t",  "a","\n",  "b","\x0b",  "c","\x0c",  "d","\r",  "e","\x0e",  "f","\x0f",  
"10","\x10",  "11","\x11",  "12","\x12",  "13","\x13",  "14","\x14",  "15","\x15",  "16","\x16",  "17","\x17",  "18","\x18",  "19","\x19",  "1a","\x1a",  "1b","\x1b",  "1c","\x1c",  "1d","\x1d",  "1e","\x1e",  "1f","\x1f",  
"20"," ",  "21","!",  "22","\"",  "23","#",  "24","$",  "25","%",  "26","&",  "27","\'",  "28","(",  "29",")",  "2a","*",  "2b","+",  "2c",",",  "2d","-",  "2e",".",  "2f","/",
"30","0",  "31","1",  "32","2",  "33","3",  "34","4",  "35","5",  "36","6",  "37","7",  "38","8",  "39","9",  "3a",":",  "3b",";",  "3c","<",  "3d","=",  "3e",">",  "3f","?",  
"40","@",  "41","A",  "42","B",  "43","C",  "44","D",  "45","E",  "46","F",  "47","G",  "48","H",  "49","I",  "4a","J",  "4b","K",  "4c","L",  "4d","M",  "4e","N",  "4f","O",  
"50","P",  "51","Q",  "52","R",  "53","S",  "54","T",  "55","U",  "56","V",  "57","W",  "58","X",  "59","Y",  "5a","Z",  "5b","[",  "5c","\\",  "5d","]",  "5e","^",  "5f","_",  
"60","`",  "61","a",  "62","b",  "63","c",  "64","d",  "65","e",  "66","f",  "67","g",  "68","h",  "69","i",  "6a","j",  "6b","k",  "6c","l",  "6d","m",  "6e","n",  "6f","o",  
"70","p",  "71","q",  "72","r",  "73","s",  "74","t",  "75","u",  "76","v",  "77","w",  "78","x",  "79","y",  "7a","z",  "7b","{",  "7c","|",  "7d","}",  "7e","~",  "7f","\x7f",  
"80","\x80",  "81","\x81",  "82","\x82",  "83","\x83",  "84","\x84",  "85","\x85",  "86","\x86",  "87","\x87",  "88","\x88",  "89","\x89",  "8a","\x8a",  "8b","\x8b",  "8c","\x8c",  "8d","\x8d",  "8e","\x8e",  "8f","\x8f",  
"90","\x90",  "91","\x91",  "92","\x92",  "93","\x93",  "94","\x94",  "95","\x95",  "96","\x96",  "97","\x97",  "98","\x98",  "99","\x99",  "9a","\x9a",  "9b","\x9b",  "9c","\x9c",  "9d","\x9d",  "9e","\x9e",  "9f","\x9f",  
"a0","\xa0",  "a1","\xa1",  "a2","\xa2",  "a3","\xa3",  "a4","\xa4",  "a5","\xa5",  "a6","\xa6",  "a7","\xa7",  "a8","\xa8",  "a9","\xa9",  "aa","\xaa",  "ab","\xab",  "ac","\xac",  "ad","\xad",  "ae","\xae",  "af","\xaf",  
"b0","\xb0",  "b1","\xb1",  "b2","\xb2",  "b3","\xb3",  "b4","\xb4",  "b5","\xb5",  "b6","\xb6",  "b7","\xb7",  "b8","\xb8",  "b9","\xb9",  "ba","\xba",  "bb","\xbb",  "bc","\xbc",  "bd","\xbd",  "be","\xbe",  "bf","\xbf",  
"c0","\xc0",  "c1","\xc1",  "c2","\xc2",  "c3","\xc3",  "c4","\xc4",  "c5","\xc5",  "c6","\xc6",  "c7","\xc7",  "c8","\xc8",  "c9","\xc9",  "ca","\xca",  "cb","\xcb",  "cc","\xcc",  "cd","\xcd",  "ce","\xce",  "cf","\xcf",  
"d0","\xd0",  "d1","\xd1",  "d2","\xd2",  "d3","\xd3",  "d4","\xd4",  "d5","\xd5",  "d6","\xd6",  "d7","\xd7",  "d8","\xd8",  "d9","\xd9",  "da","\xda",  "db","\xdb",  "dc","\xdc",  "dd","\xdd",  "de","\xde",  "df","\xdf",  
"e0","\xe0",  "e1","\xe1",  "e2","\xe2",  "e3","\xe3",  "e4","\xe4",  "e5","\xe5",  "e6","\xe6",  "e7","\xe7",  "e8","\xe8",  "e9","\xe9",  "ea","\xea",  "eb","\xeb",  "ec","\xec",  "ed","\xed",  "ee","\xee",  "ef","\xef",  
"f0","\xf0",  "f1","\xf1",  "f2","\xf2",  "f3","\xf3",  "f4","\xf4",  "f5","\xf5",  "f6","\xf6",  "f7","\xf7",  "f8","\xf8",  "f9","\xf9",  "fa","\xfa",  "fb","\xfb",  "fc","\xfc",  "fd","\xfd",  "fe","\xfe"];
T
| extend HexArray = split(Hex, " ")
| mv-expand HexArray
| extend HexArray=tostring(HexArray)
| lookup Converter on $right.Hex == $left.HexArray
| summarize Ascii=make_list(Ascii) by tostring(Hex)
| project Hex, Ascii=strcat_array(Ascii, "")
}
azure-data-explorer azure
2 Answers

This answer builds on the answer given by Yoni Leibowitz for the sake of completeness. The mv-apply function can be used to iterate over multiple dynamic arrays at the same time. They do not need to have the same length. The following can be used to translate multiple columns from Hex to String at the same time:

datatable(Hex1:string, Hex2:string)
[
    "74 65 73 74 2e 31",
    "74 65 73 74 32", 
    "74 65 73 74 33",
    "74 65 73 74 69 6e 67 34",
]
| mv-apply num_as_string1 = split(Hex1, " "), num_as_string2 = split(Hex2, " ")   on (
    summarize nums1 = make_list(tolong(strcat("0x", num_as_string1))),
              nums2 = make_list(tolong(strcat("0x", num_as_string2)))
)
| project Hex1, Str1 = make_string(nums1), Hex2, Str2 = make_string(nums2)
user_78288050

you could try the following alternative:

  1. split() the string into individual hex values
  2. convert each individual hex value to decimal (using mv-apply, tolong())
  3. build a dynamic array with all decimal values, per record (using summarize make_list()
  4. invoke make_string() over that dynamic array
datatable(Hex1:string)
[
    "74 65 73 74 31",
    "74 65 73 74 32", 
    "74 65 73 74 33",
    "74 65 73 74 34",
]
| mv-apply num_as_string = split(Hex1, " ") on (
    summarize nums = make_list(tolong(strcat("0x", num_as_string)))
)
| project Hex1, Str1 = make_string(nums)

-->

| Hex1           | Str1  |
|----------------|-------|
| 74 65 73 74 31 | test1 |
| 74 65 73 74 32 | test2 |
| 74 65 73 74 33 | test3 |
| 74 65 73 74 34 | test4 |
user_78618070
Related