Scalar functions

• 1: abs()
• 2: acos()
• 3: ago()
• 4: around() function
• 5: array_concat()
• 6: array_iff()
• 7: array_index_of()
• 8: array_length()
• 9: array_reverse()
• 10: array_rotate_left()
• 11: array_rotate_right()
• 12: array_shift_left()
• 13: array_shift_right()
• 14: array_slice()
• 15: array_sort_asc()
• 16: array_sort_desc()
• 17: array_split()
• 18: array_sum()
• 19: asin()
• 20: assert()
• 21: atan()
• 22: atan2()
• 23: bag_has_key()
• 24: bag_keys()
• 25: bag_merge()
• 26: bag_pack_columns()
• 27: bag_pack()
• 28: bag_remove_keys()
• 29: bag_set_key()
• 30: bag_zip()
• 31: base64_decode_toarray()
• 32: base64_decode_toguid()
• 33: base64_decode_tostring()
• 34: base64_encode_fromarray()
• 35: base64_encode_fromguid()
• 36: base64_encode_tostring()
• 37: beta_cdf()
• 38: beta_inv()
• 39: beta_pdf()
• 40: bin_at()
• 41: bin_auto()
• 42: bin()
• 43: binary_and()
• 44: binary_not()
• 45: binary_or()
• 46: binary_shift_left()
• 47: binary_shift_right()
• 48: binary_xor()
• 49: bitset_count_ones()
• 50: case()
• 51: ceiling()
• 52: coalesce()
• 53: column_ifexists()
• 54: convert_angle()
• 55: convert_energy()
• 56: convert_force()
• 57: convert_length()
• 58: convert_mass()
• 59: convert_speed()
• 60: convert_temperature()
• 61: convert_volume()
• 62: cos()
• 63: cot()
• 64: countof()
• 65: current_cluster_endpoint()
• 66: current_database()
• 67: current_principal_details()
• 68: current_principal_is_member_of()
• 69: current_principal()
• 70: cursor_after()
• 71: cursor_before_or_at()
• 72: cursor_current()
• 73: datetime_add()
• 74: datetime_diff()
• 75: datetime_list_timezones()
• 76: datetime_local_to_utc()
• 77: datetime_part()
• 78: datetime_utc_to_local()
• 79: dayofmonth()
• 80: dayofweek()
• 81: dayofyear()
• 82: dcount_hll()
• 83: degrees()
• 84: dynamic_to_json()
• 85: endofday()
• 86: endofmonth()
• 87: endofweek()
• 88: endofyear()
• 89: erf()
• 90: erfc()
• 91: estimate_data_size()
• 92: exp()
• 93: exp10()
• 94: exp2()
• 95: extent_id()
• 96: extent_tags()
• 97: extract_all()
• 98: extract_json()
• 99: extract()
• 100: format_bytes()
• 101: format_datetime()
• 102: format_ipv4_mask()
• 103: format_ipv4()
• 104: format_timespan()
• 105: gamma()
• 106: geo_info_from_ip_address()
• 107: gettype()
• 108: getyear()
• 109: gzip_compress_to_base64_string
• 110: gzip_decompress_from_base64_string()
• 111: has_any_ipv4_prefix()
• 112: has_any_ipv4()
• 113: has_ipv4_prefix()
• 114: has_ipv4()
• 115: hash_combine()
• 116: hash_many()
• 117: hash_md5()
• 118: hash_sha1()
• 119: hash_sha256()
• 120: hash_xxhash64()
• 121: hash()
• 122: hll_merge()
• 123: hourofday()
• 124: iff()
• 125: indexof_regex()
• 126: indexof()
• 127: ingestion_time()
• 128: ipv4_compare()
• 129: ipv4_is_in_any_range()
• 130: ipv4_is_in_range()
• 131: ipv4_is_match()
• 132: ipv4_is_private()
• 133: ipv4_netmask_suffix()
• 134: ipv4_range_to_cidr_list()
• 135: ipv6_compare()
• 136: ipv6_is_in_any_range()
• 137: ipv6_is_in_range()
• 138: ipv6_is_match()
• 139: isascii()
• 140: isempty()
• 141: isfinite()
• 142: isinf()
• 143: isnan()
• 144: isnotempty()
• 145: isnotnull()
• 146: isnull()
• 147: isutf8()
• 148: jaccard_index()
• 149: log()
• 150: log10()
• 151: log2()
• 152: loggamma()
• 153: make_datetime()
• 154: make_timespan()
• 155: max_of()
• 156: merge_tdigest()
• 157: min_of()
• 158: monthofyear()
• 159: new_guid()
• 160: not()
• 161: now()
• 162: pack_all()
• 163: pack_array()
• 164: parse_command_line()
• 165: parse_csv()
• 166: parse_ipv4_mask()
• 167: parse_ipv4()
• 168: parse_ipv6_mask()
• 169: parse_ipv6()
• 170: parse_json() function
• 171: parse_path()
• 172: parse_url()
• 173: parse_urlquery()
• 174: parse_user_agent()
• 175: parse_version()
• 176: parse_xml()
• 177: percentile_array_tdigest()
• 178: percentile_tdigest()
• 179: percentrank_tdigest()
• 180: pi()
• 181: pow()
• 182: punycode_domain_from_string
• 183: punycode_domain_to_string
• 184: punycode_from_string
• 185: punycode_to_string
• 186: radians()
• 187: rand()
• 188: range()
• 189: rank_tdigest()
• 190: regex_quote()
• 191: repeat()
• 192: replace_regex()
• 193: replace_string()
• 194: replace_strings()
• 195: reverse()
• 196: round()
• 197: Scalar Functions
• 198: set_difference()
• 199: set_has_element()
• 200: set_intersect()
• 201: set_union()
• 202: sign()
• 203: sin()
• 204: split()
• 205: sqrt()
• 206: startofday()
• 207: startofmonth()
• 208: startofweek()
• 209: startofyear()
• 210: strcat_array()
• 211: strcat_delim()
• 212: strcat()
• 213: strcmp()
• 214: string_size()
• 215: strlen()
• 216: strrep()
• 217: substring()
• 218: tan()
• 219: The has_any_index operator
• 220: tobool()
• 221: todatetime()
• 222: todecimal()
• 223: toguid()
• 224: tohex()
• 225: toint()
• 226: tolong()
• 227: tolower()
• 228: toreal()
• 229: tostring()
• 230: totimespan()
• 231: toupper()
• 232: translate()
• 233: treepath()
• 234: trim_end()
• 235: trim_start()
• 236: trim()
• 237: unicode_codepoints_from_string()
• 238: unicode_codepoints_to_string()
• 239: unixtime_microseconds_todatetime()
• 240: unixtime_milliseconds_todatetime()
• 241: unixtime_nanoseconds_todatetime()
• 242: unixtime_seconds_todatetime()
• 243: url_decode()
• 244: url_encode_component()
• 245: url_encode()
• 246: week_of_year()
• 247: welch_test()
• 248: zip()
• 249: zlib_compress_to_base64_string
• 250: zlib_decompress_from_base64_string()

1 - abs()

Calculates the absolute value of the input.

Syntax

abs(x)

Parameters

Returns

Absolute value of x.

Example

print abs(-5)

Output

2 - acos()

Calculates the angle whose cosine is the specified number. Inverse operation of cos().

Syntax

acos(x)

Parameters

Returns

The value of the arc cosine of x. The return value is null if x < -1 or x > 1.

3 - ago()

Subtracts the given timespan from the current UTC time.

Like now(), if you use ago() multiple times in a single query statement, the current UTC time being referenced is the same across all uses.

Syntax

ago(timespan)

Parameters

Returns

A datetime value equal to the current time minus the timespan.

Example

All rows with a timestamp in the past hour:

T | where Timestamp > ago(1h)

4 - around() function

Creates a bool value indicating if the first argument is within a range around the center value.

Syntax

around(value,center,delta)

Parameters

Returns

Returns true if the value is within the range, false if the value is outside the range. Returns null if any of the arguments is null.

Example: Filtering values around a specific timestamp

The following example filters rows around specific timestamp.

range dt 
    from datetime(2021-01-01 01:00) 
    to datetime(2021-01-01 02:00) 
    step 1min
| where around(dt, datetime(2021-01-01 01:30), 1min)

Output

5 - array_concat()

Concatenates many dynamic arrays to a single array.

Syntax

array_concat(arr [, …])

Parameters

Returns

Returns a dynamic array of all input arrays.

Example

The following example shows concatenated arrays.

range x from 1 to 3 step 1
| extend y = x * 2
| extend z = y * 2
| extend a1 = pack_array(x,y,z), a2 = pack_array(x, y)
| project array_concat(a1, a2)

Output

Related content

• pack_array()

6 - array_iff()

Element-wise iif function on dynamic arrays.

Syntax

array_iff(condition_array, when_true, when_false)

Parameters

Returns

Returns a dynamic array of the values taken either from the when_true or when_false array values, according to the corresponding value of the condition array.

Examples

print condition=dynamic([true,false,true]), if_true=dynamic([1,2,3]), if_false=dynamic([4,5,6]) 
| extend res= array_iff(condition, if_true, if_false)

Output

Numeric condition values

print condition=dynamic([1,0,50]), if_true="yes", if_false="no" 
| extend res= array_iff(condition, if_true, if_false)

Output

Non-numeric and non-boolean condition values

print condition=dynamic(["some string value", datetime("01-01-2022"), null]), if_true=1, if_false=0
| extend res= array_iff(condition, if_true, if_false)

Output

Mismatched array lengths

print condition=dynamic([true,true,true]), if_true=dynamic([1,2]), if_false=dynamic([3,4]) 
| extend res= array_iff(condition, if_true, if_false)

Output

7 - array_index_of()

Searches an array for the specified item, and returns its position.

Syntax

array_index_of(array, value [, start [, length [, occurence ]]])

Parameters

Returns

Returns a zero-based index position of lookup. Returns -1 if the value isn’t found in the array. Returns null for irrelevant inputs (occurrence < 0 or length < -1).

Example

The following example shows the position number of specific words within the array.

let arr=dynamic(["this", "is", "an", "example", "an", "example"]);
print
 idx1 = array_index_of(arr,"an")    // lookup found in input string
 , idx2 = array_index_of(arr,"example",1,3) // lookup found in researched range 
 , idx3 = array_index_of(arr,"example",1,2) // search starts from index 1, but stops after 2 values, so lookup can't be found
 , idx4 = array_index_of(arr,"is",2,4) // search starts after occurrence of lookup
 , idx5 = array_index_of(arr,"example",2,-1)  // lookup found
 , idx6 = array_index_of(arr, "an", 1, -1, 2)   // second occurrence found in input range
 , idx7 = array_index_of(arr, "an", 1, -1, 3)   // no third occurrence in input array
 , idx8 = array_index_of(arr, "an", -3)   // negative start index will look at last 3 elements
 , idx9 = array_index_of(arr, "is", -4)   // negative start index will look at last 3 elements

Output

Related content

Use set_has_element(arr, value) to check whether a value exists in an array. This function will improve the readability of your query. Both functions have the same performance.

8 - array_length()

Calculates the number of elements in a dynamic array.

Syntax

array_length(array)

Parameters

Returns

Returns the number of elements in array, or null if array isn’t an array.

Examples

The following example shows the number of elements in the array.

print array_length(dynamic([1, 2, 3, "four"]))

Output

9 - array_reverse()

Reverses the order of the elements in a dynamic array.

Syntax

array_reverse(value)

Parameters

Returns

Returns an array that contains the same elements as the input array in reverse order.

Example

This example shows an array of words reversed.

print arr=dynamic(["this", "is", "an", "example"]) 
| project Result=array_reverse(arr)

Output

10 - array_rotate_left()

Rotates values inside a dynamic array to the left.

Syntax

array_rotate_left(array, rotate_count)

Parameters

Returns

Dynamic array containing the same elements as the original array with each element rotated according to rotate_count.

Examples

Rotating to the left by two positions:

print arr=dynamic([1,2,3,4,5])
| extend arr_rotated=array_rotate_left(arr, 2)

Output

Rotating to the right by two positions by using negative rotate_count value:

print arr=dynamic([1,2,3,4,5])
| extend arr_rotated=array_rotate_left(arr, -2)

Output

Related content

• To rotate an array to the right, use array_rotate_right().
• To shift an array to the left, use array_shift_left().
• To shift an array to the right, use array_shift_right()

11 - array_rotate_right()

Rotates values inside a dynamic array to the right.

Syntax

array_rotate_right(array, rotate_count)

Parameters

Returns

Dynamic array containing the same elements as the original array with each element rotated according to rotate_count.

Examples

Rotating to the right by two positions:

print arr=dynamic([1,2,3,4,5])
| extend arr_rotated=array_rotate_right(arr, 2)

Output

Rotating to the left by two positions by using negative rotate_count value:

Results

print arr=dynamic([1,2,3,4,5])
| extend arr_rotated=array_rotate_right(arr, -2)

Output

Related content

• To rotate an array to the left, use array_rotate_left().
• To shift an array to the left, use array_shift_left().
• To shift an array to the right, use array_shift_right().

12 - array_shift_left()

Shifts the values inside a dynamic array to the left.

Syntax

array_shift_left(array, shift_count [, default_value ])

Parameters

Returns

Returns a dynamic array containing the same number of elements as in the original array. Each element has been shifted according to shift_count. New elements that are added in place of removed elements have a value of default_value.

Examples

Shifting to the left by two positions:

print arr=dynamic([1,2,3,4,5])
| extend arr_shift=array_shift_left(arr, 2)

Output

Shifting to the left by two positions and adding default value:

print arr=dynamic([1,2,3,4,5])
| extend arr_shift=array_shift_left(arr, 2, -1)

Output

Shifting to the right by two positions by using negative shift_count value:

print arr=dynamic([1,2,3,4,5])
| extend arr_shift=array_shift_left(arr, -2, -1)

Output

Related content

• To shift an array to the right, use array_shift_right().
• To rotate an array to the right, use array_rotate_right().
• To rotate an array to the left, use array_rotate_left().

13 - array_shift_right()

Shifts the values inside a dynamic array to the right.

Syntax

array_shift_right(array, shift_count [, default_value ])

Parameters

Returns

Returns a dynamic array containing the same amount of the elements as in the original array. Each element has been shifted according to shift_count. New elements that are added instead of the removed elements have a value of default_value.

Examples

Shifting to the right by two positions:

print arr=dynamic([1,2,3,4,5])
| extend arr_shift=array_shift_right(arr, 2)

Output

Shifting to the right by two positions and adding a default value:

print arr=dynamic([1,2,3,4,5])
| extend arr_shift=array_shift_right(arr, 2, -1)

Output

Shifting to the left by two positions by using a negative shift_count value:

print arr=dynamic([1,2,3,4,5])
| extend arr_shift=array_shift_right(arr, -2, -1)

Output

Related content

• To shift an array to the left, use array_shift_left().
• To rotate an array to the right, use array_rotate_right().
• To rotate an array to the left, use array_rotate_left().

14 - array_slice()

Extracts a slice of a dynamic array.

Syntax

array_slice(array, start, end)

Parameters

Returns

Returns a dynamic array of the values in the range [start..end] from array.

Examples

The following examples return a slice of the array.

print arr=dynamic([1,2,3]) 
| extend sliced=array_slice(arr, 1, 2)

Output

print arr=dynamic([1,2,3,4,5]) 
| extend sliced=array_slice(arr, 2, -1)

Output

print arr=dynamic([1,2,3,4,5]) 
| extend sliced=array_slice(arr, -3, -2)

Output

15 - array_sort_asc()

Receives one or more arrays. Sorts the first array in ascending order. Orders the remaining arrays to match the reordered first array.

Syntax

array_sort_asc(array1[, …, arrayN][, nulls_last])

If nulls_last isn’t provided, a default value of true is used.

Parameters

Returns

Returns the same number of arrays as in the input, with the first array sorted in ascending order, and the remaining arrays ordered to match the reordered first array.

null is returned for every array that differs in length from the first one.

An array which contains elements of different types, is sorted in the following order:

• Numeric, datetime, and timespan elements
• String elements
• Guid elements
• All other elements

Examples

The examples in this section show how to use the syntax to help you get started.

Sort two arrays

The following example sorts the initial array, array1, in ascending order. It then sorts array2 to match the new order of array1.

let array1 = dynamic([1,3,4,5,2]);
let array2 = dynamic(["a","b","c","d","e"]);
print array_sort_asc(array1,array2)

Output

Sort substrings

The following example sorts a list of names in ascending order. It saves a list of names to a variable, Names, which is then splits into an array and sorted in ascending order. The query returns the names in ascending order.

let Names = "John,Paul,Jane,Kao";
let SortedNames = strcat_array(array_sort_asc(split(Names, ",")), ",");
print result = SortedNames

Output

Combine summarize and array_sort_asc

The following example uses the summarize operator and the array_sort_asc function to organize and sort commands by user in chronological order.

datatable(command:string, command_time:datetime, user_id:string)
[
    'chmod',   datetime(2019-07-15),   "user1",
    'ls',      datetime(2019-07-02),   "user1",
    'dir',     datetime(2019-07-22),   "user1",
    'mkdir',   datetime(2019-07-14),   "user1",
    'rm',      datetime(2019-07-27),   "user1",
    'pwd',     datetime(2019-07-25),   "user1",
    'rm',      datetime(2019-07-23),   "user2",
    'pwd',     datetime(2019-07-25),   "user2",
]
| summarize timestamps = make_list(command_time), commands = make_list(command) by user_id
| project user_id, commands_in_chronological_order = array_sort_asc(timestamps, commands)[1]

Output

Control location of null values

By default, null values are put last in the sorted array. However, you can control it explicitly by adding a bool value as the last argument to array_sort_asc().

The following example shows the default behavior:

print result=array_sort_asc(dynamic([null,"blue","yellow","green",null]))

Output

The following example shows nondefault behavior using the false parameter, which specifies that nulls are placed at the beginning of the array.

print result=array_sort_asc(dynamic([null,"blue","yellow","green",null]), false)

Output

Related content

• Aggregation function types at a glance
• array_sort_desc()
• strcat_array()

16 - array_sort_desc()

Receives one or more arrays. Sorts the first array in descending order. Orders the remaining arrays to match the reordered first array.

Syntax

array_sort_desc(array1[, …, argumentN])

array_sort_desc(array1[, …, argumentN],nulls_last)

If nulls_last isn’t provided, a default value of true is used.

Parameters

Returns

Returns the same number of arrays as in the input, with the first array sorted in ascending order, and the remaining arrays ordered to match the reordered first array.

null is returned for every array that differs in length from the first one.

An array which contains elements of different types, is sorted in the following order:

• Numeric, datetime, and timespan elements
• String elements
• Guid elements
• All other elements

Examples

The examples in this section show how to use the syntax to help you get started.

Sort two arrays

The following example sorts the initial array, array1, in descending order. It then sorts array2 to match the new order of array1.

let array1 = dynamic([1,3,4,5,2]);
let array2 = dynamic(["a","b","c","d","e"]);
print array_sort_desc(array1,array2)

Output

Sort substrings

The following example sorts a list of names in descending order. It saves a list of names to a variable, Names, which is then splits into an array and sorted in descending order. The query returns the names in descending order.

let Names = "John,Paul,Jane,Kayo";
let SortedNames = strcat_array(array_sort_desc(split(Names, ",")), ",");
print result = SortedNames

Output

Combine summarize and array_sort_desc

The following example uses the summarize operator and the array_sort_asc function to organize and sort commands by user in descending chronological order.

datatable(command:string, command_time:datetime, user_id:string)
[
    'chmod',   datetime(2019-07-15),   "user1",
    'ls',      datetime(2019-07-02),   "user1",
    'dir',     datetime(2019-07-22),   "user1",
    'mkdir',   datetime(2019-07-14),   "user1",
    'rm',      datetime(2019-07-27),   "user1",
    'pwd',     datetime(2019-07-25),   "user1",
    'rm',      datetime(2019-07-23),   "user2",
    'pwd',     datetime(2019-07-25),   "user2",
]
| summarize timestamps = make_list(command_time), commands = make_list(command) by user_id
| project user_id, commands_in_chronological_order = array_sort_desc(timestamps, commands)[1]

Output

Control location of null values

By default, null values are put last in the sorted array. However, you can control it explicitly by adding a bool value as the last argument to array_sort_asc().

The following example shows the default behavior:

print result=array_sort_desc(dynamic([null,"blue","yellow","green",null]))

Output

The following example shows nondefault behavior using the false parameter, which specifies that nulls are placed at the beginning of the array.

print result=array_sort_desc(dynamic([null,"blue","yellow","green",null]), false)

Output

Related content

• Aggregation function types at a glance
• array_sort_asc()
• strcat_array()

17 - array_split()

Splits an array to multiple arrays according to the split indices and packs the generated array in a dynamic array.

Syntax

array_split(array, index)

Parameters

Returns

Returns a dynamic array containing N+1 arrays with the values in the range [0..i1), [i1..i2), ... [iN..array_length) from array, where N is the number of input indices and i1...iN are the indices.

Examples

This following example shows how to split and array.

print arr=dynamic([1,2,3,4,5]) 
| extend arr_split=array_split(arr, 2)

Output

print arr=dynamic([1,2,3,4,5]) 
| extend arr_split=array_split(arr, dynamic([1,3]))

Output

18 - array_sum()

Calculates the sum of elements in a dynamic array.

Syntax

array_sum(array)

Parameters

Returns

Returns a double type value with the sum of the elements of the array.

Example

This following example shows the sum of an array.

print arr=dynamic([1,2,3,4]) 
| extend arr_sum=array_sum(arr)

Output

19 - asin()

Calculates the angle whose sine is the specified number, or the arc sine. This is the inverse operation of sin().

Syntax

asin(x)

Parameters

Returns

Returns the value of the arc sine of x. Returns null if x < -1 or x > 1.

Example

asin(0.5)

Output

20 - assert()

Checks for a condition. If the condition is false, outputs error messages and fails the query.

Syntax

assert(condition,message)

Parameters

Returns

Returns true if the condition is true. Raises a semantic error if the condition is evaluated to false.

Examples

The following query defines a function checkLength() that checks input string length, and uses assert to validate input length parameter (checks that it’s greater than zero).

let checkLength = (len:long, s:string)
{
    assert(len > 0, "Length must be greater than zero") and
    strlen(s) > len
};
datatable(input:string)
[
    '123',
    '4567'
]
| where checkLength(len=long(-1), input)

Running this query yields an error: assert() has failed with message: 'Length must be greater than zero'

Example of running with valid len input:

let checkLength = (len:long, s:string)
{
    assert(len > 0, "Length must be greater than zero") and strlen(s) > len
};
datatable(input:string)
[
    '123',
    '4567'
]
| where checkLength(len=3, input)

Output

The following query will always fail, demonstrating that the assert function gets evaluated even though the where b operator returns no data when b is false:

let b=false;
print x="Hello"
| where b
| where assert(b, "Assertion failed")

21 - atan()

Returns the angle whose tangent is the specified number. This is the inverse operation of tan().

Syntax

atan(x)

Parameters

Returns

The value of the arc tangent of x.

Example

atan(0.5)

Output

22 - atan2()

Calculates the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x).

Syntax

atan2(y,x)

Parameters

Returns

Returns the angle in radians between the positive x-axis and the ray from the origin to the point (y, x).

Examples

The following example returns the angle measurements in radians.

print atan2_0 = atan2(1,1) // Pi / 4 radians (45 degrees)
| extend atan2_1 = atan2(0,-1) // Pi radians (180 degrees)
| extend atan2_2 = atan2(-1,0) // - Pi / 2 radians (-90 degrees)

Output

23 - bag_has_key()

Checks whether a dynamic property bag object contains a given key.

Syntax

bag_has_key(bag,key)

Parameters

Returns

True or false depending on if the key exists in the bag.

Examples

datatable(input: dynamic)
[
    dynamic({'key1' : 123, 'key2': 'abc'}),
    dynamic({'key1' : 123, 'key3': 'abc'}),
]
| extend result = bag_has_key(input, 'key2')

Output

Search using a JSONPath key

datatable(input: dynamic)
[
    dynamic({'key1': 123, 'key2': {'prop1' : 'abc', 'prop2': 'xyz'}, 'key3': [100, 200]}),
]
| extend result = bag_has_key(input, '$.key2.prop1')

Output

24 - bag_keys()

Enumerates all the root keys in a dynamic property bag object.

Syntax

bag_keys(object)

Parameters

Returns

An array of keys, order is undetermined.

Example

datatable(index:long, d:dynamic) [
    1, dynamic({'a':'b', 'c':123}), 
    2, dynamic({'a':'b', 'c':{'d':123}}),
    3, dynamic({'a':'b', 'c':[{'d':123}]}),
    4, dynamic(null),
    5, dynamic({}),
    6, dynamic('a'),
    7, dynamic([])
]
| extend keys = bag_keys(d)

Output

25 - bag_merge()

The function merges multiple dynamic property bags into a single dynamic property bag object, consolidating all properties from the input bags.

Syntax

bag_merge(bag1,bag2[,*bag3*, ...])

Parameters

Returns

A dynamic property bag containing the merged results of all input property bags. If a key is present in multiple input bags, the value associated with the key from the leftmost argument takes precedence.

Example

print result = bag_merge(
   dynamic({'A1':12, 'B1':2, 'C1':3}),
   dynamic({'A2':81, 'B2':82, 'A1':1}))

Output

26 - bag_pack_columns()

Creates a dynamic property bag object from a list of columns.

Syntax

bag_pack_columns(column1, column2,... )

Parameters

Returns

Returns a dynamic property bag object from the listed columns.

Examples

The following example creates a property bag that includes the Id and Value columns:

datatable(Id: string, Value: string, Other: long)
[
    "A", "val_a", 1,
    "B", "val_b", 2,
    "C", "val_c", 3
]
| extend Packed = bag_pack_columns(Id, Value)

|C|val_c|3|{
“Id”: “C”,
“Value”: “val_c”
}|

27 - bag_pack()

Creates a dynamic property bag object from a list of keys and values.

Syntax

bag_pack(key1, value1, key2, value2,... )

Parameters

Returns

Returns a dynamic property bag object from the listed key and value inputs.

Examples

Example 1

The following example creates and returns a property bag from an alternating list of keys and values.

print bag_pack("Level", "Information", "ProcessID", 1234, "Data", bag_pack("url", "www.bing.com"))

Results

Example 2

The following example creates a property bag and extract value from property bag using ‘.’ operator.

datatable (
    Source: int,
    Destination: int,
    Message: string
) [
    1234, 100, "AA", 
    4567, 200, "BB",
    1212, 300, "CC" 
]
| extend MyBag=bag_pack("Dest", Destination, "Mesg", Message)
| project-away Source, Destination, Message
| extend MyBag_Dest=MyBag.Dest, MyBag_Mesg=MyBag.Mesg

Results

Example 3

The following example uses two tables, SmsMessages and MmsMessages, and returns their common columns and a property bag from the other columns. The tables are created ad-hoc as part of the query.

SmsMessages

MmsMessages

let SmsMessages = datatable (
    SourceNumber: string,
    TargetNumber: string,
    CharsCount: string
) [
    "555-555-1234", "555-555-1212", "46", 
    "555-555-1234", "555-555-1213", "50",
    "555-555-1212", "555-555-1234", "32" 
];
let MmsMessages = datatable (
    SourceNumber: string,
    TargetNumber: string,
    AttachmentSize: string,
    AttachmentType: string,
    AttachmentName: string
) [
    "555-555-1212", "555-555-1213", "200", "jpeg", "Pic1",
    "555-555-1234", "555-555-1212", "250", "jpeg", "Pic2",
    "555-555-1234", "555-555-1213", "300", "png", "Pic3"
];
SmsMessages 
| join kind=inner MmsMessages on SourceNumber
| extend Packed=bag_pack("CharsCount", CharsCount, "AttachmentSize", AttachmentSize, "AttachmentType", AttachmentType, "AttachmentName", AttachmentName) 
| where SourceNumber == "555-555-1234"
| project SourceNumber, TargetNumber, Packed

Results

28 - bag_remove_keys()

Removes keys and associated values from a dynamic property bag.

Syntax

bag_remove_keys(bag,keys)

Parameters

Returns

Returns a dynamic property bag without specified keys and their values.

Examples

datatable(input:dynamic)
[
    dynamic({'key1' : 123,     'key2': 'abc'}),
    dynamic({'key1' : 'value', 'key3': 42.0}),
]
| extend result=bag_remove_keys(input, dynamic(['key2', 'key4']))

Output

Remove inner properties of dynamic values using JSONPath notation

datatable(input:dynamic)
[
    dynamic({'key1': 123, 'key2': {'prop1' : 'abc', 'prop2': 'xyz'}, 'key3': [100, 200]}),
]
| extend result=bag_remove_keys(input, dynamic(['$.key2.prop1', 'key3']))

Output

29 - bag_set_key()

bag_set_key() receives a dynamic property-bag, a key and a value. The function sets the given key in the bag to the given value. The function overrides any existing value in case the key already exists.

Syntax

bag_set_key(bag,key,value)

Parameters

Returns

Returns a dynamic property-bag with specified key-value pairs. If the input bag isn’t a property-bag, a null value is returned.

Examples

Use a root-level key

datatable(input: dynamic) [
    dynamic({'key1': 1, 'key2': 2}), 
    dynamic({'key1': 1, 'key3': 'abc'}),
]
| extend result = bag_set_key(input, 'key3', 3)

Use a JSONPath key

datatable(input: dynamic)[
    dynamic({'key1': 123, 'key2': {'prop1': 123, 'prop2': 'xyz'}}),
    dynamic({'key1': 123})
]
| extend result = bag_set_key(input, '$.key2.prop1', 'abc')

30 - bag_zip()

Creates a dynamic property-bag from two input dynamic arrays. In the resulting property-bag, the values from the first input array are used as the property keys, while the values from the second input array are used as corresponding property values.

Syntax

bag_zip(KeysArray, ValuesArray)

Parameters

Returns

Returns a dynamic property-bag.

Examples

In the following example, the array of keys and the array of values are the same length and are zipped together into a dynamic property bag.

let Data = datatable(KeysArray: dynamic, ValuesArray: dynamic) [
    dynamic(['a', 'b', 'c']), dynamic([1, '2', 3.4])
];
Data
| extend NewBag = bag_zip(KeysArray, ValuesArray)

More keys than values

In the following example, the array of keys is longer than the array of values. The missing values are filled with nulls.

let Data = datatable(KeysArray: dynamic, ValuesArray: dynamic) [
    dynamic(['a', 'b', 'c']), dynamic([1, '2'])
];
Data
| extend NewBag = bag_zip(KeysArray, ValuesArray)

More values than keys

In the following example, the array of values is longer than the array of keys. Values with no matching keys are ignored.

let Data = datatable(KeysArray: dynamic, ValuesArray: dynamic) [
    dynamic(['a', 'b']), dynamic([1, '2', 2.5])
];
Data
| extend NewBag = bag_zip(KeysArray, ValuesArray)

Non-string keys

In the following example, there are some values in they keys array that aren’t of type string. The non-string values are ignored.

let Data = datatable(KeysArray: dynamic, ValuesArray: dynamic) [
    dynamic(['a', 8, 'b']), dynamic([1, '2', 2.5])
];
Data
| extend NewBag = bag_zip(KeysArray, ValuesArray)

Fill values with null

In the following example, the parameter that is supposed to be an array of values isn’t an array, so all values are filled with nulls.

let Data = datatable(KeysArray: dynamic, ValuesArray: dynamic) [
    dynamic(['a', 8, 'b']), dynamic(1)
];
Data
| extend NewBag = bag_zip(KeysArray, ValuesArray)

Null property-bag

In the following example, the parameter that is supposed to be an array of keys isn’t an array, so the resulting property-bag is null.

let Data = datatable(KeysArray: dynamic, ValuesArray: dynamic) [
    dynamic('a'), dynamic([1, '2', 2.5])
];
Data
| extend NewBag = bag_zip(KeysArray, ValuesArray)
| extend IsNewBagEmpty=isnull(NewBag)

| KeysArray | ValuesArray | NewBag | IsNewBagEmpty | |–|–|–| | a | [1,‘2’,2.5] | | TRUE |

Related content

• zip function

31 - base64_decode_toarray()

Decodes a base64 string to an array of long values.

Syntax

base64_decode_toarray(base64_string)

Parameters

Returns

Returns an array of long values decoded from a base64 string.

Example

print Quine=base64_decode_toarray("S3VzdG8=")  
// 'K', 'u', 's', 't', 'o'

Output

Related content

• To decode base64 strings to a UTF-8 string, see base64_decode_tostring()
• To encode strings to a base64 string, see base64_encode_tostring()

32 - base64_decode_toguid()

Decodes a base64 string to a GUID.

Syntax

base64_decode_toguid(base64_string)

Parameters

Returns

Returns a GUID decoded from a base64 string.

Example

print Quine = base64_decode_toguid("JpbpECu8dUy7Pv5gbeJXAA==")  

Output

If you try to decode an invalid base64 string, “null” will be returned:

print Empty = base64_decode_toguid("abcd1231")

Related content

To encode a GUID to a base64 string, see base64_encode_fromguid().

33 - base64_decode_tostring()

Decodes a base64 string to a UTF-8 string.

Syntax

base64_decode_tostring(base64_string)

Parameters

Returns

Returns UTF-8 string decoded from base64 string.

Example

print Quine=base64_decode_tostring("S3VzdG8=")

Output

Trying to decode a base64 string that was generated from invalid UTF-8 encoding returns null:

print Empty=base64_decode_tostring("U3RyaW5n0KHR0tGA0L7Rh9C60LA=")

Output

Related content

• To decode base64 strings to an array of long values, see base64_decode_toarray()
• To encode strings to base64 string, see base64_encode_tostring()

34 - base64_encode_fromarray()

Encodes a base64 string from a bytes array.

Syntax

base64_encode_fromarray(base64_string_decoded_as_a_byte_array)

Parameters

Returns

Returns the base64 string encoded from the bytes array. Note that byte is an integer type.

Examples

let bytes_array = toscalar(print base64_decode_toarray("S3VzdG8="));
print decoded_base64_string = base64_encode_fromarray(bytes_array)

Output

Trying to encode a base64 string from an invalid bytes array that was generated from invalid UTF-8 encoded string will return null:

let empty_bytes_array = toscalar(print base64_decode_toarray("U3RyaW5n0KHR0tGA0L7Rh9C60LA"));
print empty_string = base64_encode_fromarray(empty_bytes_array)

Output

Related content

• For decoding base64 strings to a UTF-8 string, see base64_decode_tostring()
• For encoding strings to a base64 string see base64_encode_tostring()
• This function is the inverse of base64_decode_toarray()

35 - base64_encode_fromguid()

Encodes a GUID to a base64 string.

Syntax

base64_encode_fromguid(guid)

Parameters

Returns

Returns a base64 string encoded from a GUID.

Example

print Quine = base64_encode_fromguid(toguid("ae3133f2-6e22-49ae-b06a-16e6a9b212eb"))  

Output

If you try to encode anything that isn’t a GUID as below, an error will be thrown:

print Empty = base64_encode_fromguid("abcd1231")

Related content

• To decode a base64 string to a GUID, see base64_decode_toguid().
• To create a GUID from a string, see toguid().

36 - base64_encode_tostring()

Encodes a string as base64 string.

Syntax

base64_encode_tostring(string)

Parameters

Returns

Returns string encoded as a base64 string.

Example

print Quine=base64_encode_tostring("Kusto")

Output

Related content

• To decode base64 strings to UTF-8 strings, see base64_decode_tostring().
• To decode base64 strings to an array of long values, see base64_decode_toarray().

37 - beta_cdf()

Returns the standard cumulative beta distribution function.

If probability = beta_cdf(x,…), then beta_inv(probability,…) = x.

The beta distribution is commonly used to study variation in the percentage of something across samples, such as the fraction of the day people spend watching television.

Syntax

beta_cdf(x, alpha, beta)

Parameters

Returns

The cumulative beta distribution function.

Examples

datatable(x:double, alpha:double, beta:double, comment:string)
[
    0.9, 10.0, 20.0, "Valid input",
    1.5, 10.0, 20.0, "x > 1, yields NaN",
    double(-10), 10.0, 20.0, "x < 0, yields NaN",
    0.1, double(-1.0), 20.0, "alpha is < 0, yields NaN"
]
| extend b = beta_cdf(x, alpha, beta)

Output

Related content

• For computing the inverse of the beta cumulative probability density function, see beta-inv().
• For computing probability density function, see beta-pdf().

38 - beta_inv()

Returns the inverse of the beta cumulative probability density function.

If probability = beta_cdf(x,…), then beta_inv(probability,…) = x.

The beta distribution can be used in project planning to model probable completion times given an expected completion time and variability.

Syntax

beta_inv(probability,alpha,beta)

Parameters

Returns

The inverse of the beta cumulative probability density function beta_cdf()

Examples

datatable(p:double, alpha:double, beta:double, comment:string)
[
    0.1, 10.0, 20.0, "Valid input",
    1.5, 10.0, 20.0, "p > 1, yields null",
    0.1, double(-1.0), 20.0, "alpha is < 0, yields NaN"
]
| extend b = beta_inv(p, alpha, beta)

Output

Related content

• For computing cumulative beta distribution function, see beta-cdf().
• For computing probability beta density function, see beta-pdf().

39 - beta_pdf()

Returns the probability density beta function.

The beta distribution is commonly used to study variation in the percentage of something across samples, such as the fraction of the day people spend watching television.

Syntax

beta_pdf(x, alpha, beta)

Parameters

Returns

The probability beta density function.

Examples

datatable(x:double, alpha:double, beta:double, comment:string)
[
    0.5, 10.0, 20.0, "Valid input",
    1.5, 10.0, 20.0, "x > 1, yields NaN",
    double(-10), 10.0, 20.0, "x < 0, yields NaN",
    0.1, double(-1.0), 20.0, "alpha is < 0, yields NaN"
]
| extend r = beta_pdf(x, alpha, beta)

Output

Related content

• For computing the inverse of the beta cumulative probability density function, see beta-inv().
• For the standard cumulative beta distribution function, see beta-cdf().

40 - bin_at()

Returns the value rounded down to the nearest bin size, which is aligned to a fixed reference point.

In contrast to the bin() function, where the point of alignment is predefined, bin_at() allows you to define a fixed point for alignment. Results can align before or after the fixed point.

Syntax

bin_at (value,bin_size,fixed_point)

Parameters

Returns

The nearest multiple of bin_size below the given value that aligns to the specified fixed_point.

Examples

In the following example, value is rounded down to the nearest bin_size that aligns to the fixed_point.

print bin_at(6.5, 2.5, 7)

Output

In the following example, the time interval is binned into daily bins aligned to a 12 hour fixed point. The return value is -12 since a daily bin aligned to 12 hours rounds down to 12 on the previous day.

print bin_at(time(1h), 1d, 12h)

Output

In the following example, daily bins align to noon.

print bin_at(datetime(2017-05-15 10:20:00.0), 1d, datetime(1970-01-01 12:00:00.0))

Output

In the following example, bins are weekly and align to the start of Sunday June 6, 2017. The example returns a bin aligned to Sundays.

print bin_at(datetime(2017-05-17 10:20:00.0), 7d, datetime(2017-06-04 00:00:00.0))

Output

In the following example, the total number of events are grouped into daily bins aligned to the fixed_point date and time. The fixed_point value is included in one of the returned bins.

datatable(Date:datetime, NumOfEvents:int)[
datetime(2018-02-24T15:14),3,
datetime(2018-02-24T15:24),4,
datetime(2018-02-23T16:14),4,
datetime(2018-02-23T17:29),4,
datetime(2018-02-26T15:14),5]
| summarize TotalEvents=sum(NumOfEvents) by bin_at(Date, 1d, datetime(2018-02-24 15:14:00.0000000)) 

Output

Related content

• bin()

41 - bin_auto()

Rounds values down to a fixed-size bin, with control over the bin size and starting point provided by a query property.

Syntax

bin_auto (value)

Parameters

To control the bin size and starting point, set the following parameters before using the function.

Returns

The nearest multiple of query_bin_auto_size below value, shifted so that query_bin_auto_at will be translated into itself.

Examples

set query_bin_auto_size=1h;
set query_bin_auto_at=datetime(2017-01-01 00:05);
range Timestamp from datetime(2017-01-01 00:05) to datetime(2017-01-01 02:00) step 1m
| summarize count() by bin_auto(Timestamp)

Output

42 - bin()

Rounds values down to an integer multiple of a given bin size.

Used frequently in combination with summarize by .... If you have a scattered set of values, they’ll be grouped into a smaller set of specific values.

Syntax

bin(value,roundTo)

Parameters

Returns

The nearest multiple of roundTo below value. Null values, a null bin size, or a negative bin size will result in null.

Examples

Numeric bin

print bin(4.5, 1)

Output

Timespan bin

print bin(time(16d), 7d)

Output

Datetime bin

print bin(datetime(1970-05-11 13:45:07), 1d)

Output

Pad a table with null bins

When there are rows for bins with no corresponding row in the table, we recommend to pad the table with those bins. The following query looks at strong wind storm events in California for a week in April. However, there are no events on some of the days.

let Start = datetime('2007-04-07');
let End = Start + 7d;
StormEvents
| where StartTime between (Start .. End)
| where State == "CALIFORNIA" and EventType == "Strong Wind"
| summarize PropertyDamage=sum(DamageProperty) by bin(StartTime, 1d)

Output

In order to represent the full week, the following query pads the result table with null values for the missing days. Here’s a step-by-step explanation of the process:

1. Use the union operator to add more rows to the table.
2. The range operator produces a table that has a single row and column.
3. The mv-expand operator over the range function creates as many rows as there are bins between StartTime and EndTime.
4. Use a PropertyDamage of 0.
5. The summarize operator groups together bins from the original table to the table produced by the union expression. This process ensures that the output has one row per bin whose value is either zero or the original count.

let Start = datetime('2007-04-07');
let End = Start + 7d;
StormEvents
| where StartTime between (Start .. End)
| where State == "CALIFORNIA" and EventType == "Strong Wind"
| union (
    range x from 1 to 1 step 1
    | mv-expand StartTime=range(Start, End, 1d) to typeof(datetime)
    | extend PropertyDamage=0
    )
| summarize PropertyDamage=sum(DamageProperty) by bin(StartTime, 1d)

Output

43 - binary_and()

Returns a result of the bitwise AND operation between two values.

Syntax

binary_and(value1,value2)

Parameters

Returns

Returns logical AND operation on a pair of numbers: value1 & value2.

44 - binary_not()

Returns a bitwise negation of the input value.

Syntax

binary_not(value)

Parameters

Returns

Returns logical NOT operation on a number: value.

Example

binary_not(100)

Output

45 - binary_or()

Returns a result of the bitwise or operation of the two values.

Syntax

binary_or(value1, value2 )

Parameters

Returns

Returns logical OR operation on a pair of numbers: value1 | value2.

46 - binary_shift_left()

Returns binary shift left operation on a pair of numbers.

Syntax

binary_shift_left(value,shift)

Parameters

Returns

Returns binary shift left operation on a pair of numbers: value « (shift%64). If n is negative, a NULL value is returned.

Example

binary_shift_left(1,2)

Output

47 - binary_shift_right()

Returns binary shift right operation on a pair of numbers.

Syntax

binary_shift_right(value,shift)

Parameters

Returns

Returns binary shift right operation on a pair of numbers: value » (shift%64). If n is negative, a NULL value is returned.

Examples

binary_shift_right(1,2)

Output

48 - binary_xor()

Returns a result of the bitwise xor operation of the two values.

Syntax

binary_xor(value1,value2)

Parameters

Returns

Returns logical XOR operation on a pair of numbers: value1 ^ value2.

Examples

binary_xor(1,1)

Output

binary_xor(1,2)

Output

49 - bitset_count_ones()

Returns the number of set bits in the binary representation of a number.

Syntax

bitset_count_ones(value)

Parameters

Returns

Returns the number of set bits in the binary representation of a number.

Example

// 42 = 32+8+2 : b'00101010' == 3 bits set
print ones = bitset_count_ones(42) 

Output

50 - case()

Evaluates a list of predicates and returns the first result expression whose predicate is satisfied.

If none of the predicates return true, the result of the else expression is returned. All predicate arguments must be expressions that evaluate to a boolean value. All then arguments and the else argument must be of the same type.

Syntax

case(predicate_1, then_1, [predicate_2, then_2, …] else)

Parameters

Returns

The value of the first then_i whose predicate_i evaluates to true, or the value of else if neither of the predicates are satisfied.

Example

range Size from 1 to 15 step 2
| extend bucket = case(Size <= 3, "Small", 
                       Size <= 10, "Medium", 
                       "Large")

Output

51 - ceiling()

Calculates the smallest integer greater than, or equal to, the specified numeric expression.

Syntax

ceiling(number)

Parameters

Returns

The smallest integer greater than, or equal to, the specified numeric expression.

Examples

print c1 = ceiling(-1.1), c2 = ceiling(0), c3 = ceiling(0.9)

Output

52 - coalesce()

Evaluates a list of expressions and returns the first non-null (or non-empty for string) expression.

Syntax

coalesce(arg,arg_2,[arg_3,...])

Parameters

Returns

The value of the first arg whose value isn’t null (or not-empty for string expressions).

Example

print result=coalesce(tolong("not a number"), tolong("42"), 33)

Output

53 - column_ifexists()

Displays the column, if the column exists. Otherwise, it returns the default column.

Syntax

column_ifexists(columnName,defaultValue)

Parameters

Returns

If columnName exists, then returns the column. Otherwise, it returns the defaultValue column.

Example

This example returns the default State column, because a column named Capital doesn’t exist in the StormEvents table.

StormEvents | project column_ifexists("Capital", State)

Output

This output shows the first 10 rows of the default State column.

54 - convert_angle()

Convert an angle value from one unit to another.

Syntax

convert_angle(value,from,to)

Parameters

Conversion units

• Arcminute
• Arcsecond
• Centiradian
• Deciradian
• Degree
• Gradian
• Microdegree
• Microradian
• Millidegree
• Milliradian
• Nanodegree
• Nanoradian
• NatoMil
• Radian
• Revolution
• Tilt

Returns

Returns the input value converted from one angle unit to another. Invalid units return null.

Example

print result = convert_angle(1.2, 'Degree', 'Arcminute')

Output

55 - convert_energy()

Convert an energy value from one unit to another.

Syntax

convert_energy(value,from,to)

Parameters

Conversion units

• BritishThermalUnit
• Calorie
• DecathermEc
• DecathermImperial
• DecathermUs
• ElectronVolt
• Erg
• FootPound
• GigabritishThermalUnit
• GigaelectronVolt
• Gigajoule
• GigawattDay
• GigawattHour
• HorsepowerHour
• Joule
• KilobritishThermalUnit
• Kilocalorie
• KiloelectronVolt
• Kilojoule
• KilowattDay
• KilowattHour
• MegabritishThermalUnit
• Megacalorie
• MegaelectronVolt
• Megajoule
• MegawattDay
• MegawattHour
• Millijoule
• TeraelectronVolt
• TerawattDay
• TerawattHour
• ThermEc
• ThermImperial
• ThermUs
• WattDay
• WattHour

Returns

Returns the input value converted from one energy unit to another. Invalid units return null.

Example

print result = convert_energy(1.2, 'Joule', 'BritishThermalUnit')

Output

56 - convert_force()

Convert a force value from one unit to another.

Syntax

convert_force(value,from,to)

Parameters

Conversion units

• Decanewton
• Dyn
• KilogramForce
• Kilonewton
• KiloPond
• KilopoundForce
• Meganewton
• Micronewton
• Millinewton
• Newton
• OunceForce
• Poundal
• PoundForce
• ShortTonForce
• TonneForce

Returns

Returns the input value converted from one force unit to another. Invalid units return null.

Example

print result = convert_force(1.2, 'Newton', 'Decanewton')

Output

57 - convert_length()

Convert a length value from one unit to another.

Syntax

convert_length(value,from,to)

Parameters

Conversion units

• Angstrom
• AstronomicalUnit
• Centimeter
• Chain
• DataMile
• Decameter
• Decimeter
• DtpPica
• DtpPoint
• Fathom
• Foot
• Hand
• Hectometer
• Inch
• KilolightYear
• Kilometer
• Kiloparsec
• LightYear
• MegalightYear
• Megaparsec
• Meter
• Microinch
• Micrometer
• Mil
• Mile
• Millimeter
• Nanometer
• NauticalMile
• Parsec
• PrinterPica
• PrinterPoint
• Shackle
• SolarRadius
• Twip
• UsSurveyFoot
• Yard

Returns

Returns the input value converted from one length unit to another. Invalid units return null.

Example

print result = convert_length(1.2, 'Meter', 'Foot')

Output

58 - convert_mass()

Convert a mass value from one unit to another.

Syntax

convert_mass(value,from,to)

Parameters

Conversion units

• Centigram
• Decagram
• Decigram
• EarthMass
• Grain
• Gram
• Hectogram
• Kilogram
• Kilopound
• Kilotonne
• LongHundredweight
• LongTon
• Megapound
• Megatonne
• Microgram
• Milligram
• Nanogram
• Ounce
• Pound
• ShortHundredweight
• ShortTon
• Slug
• SolarMass
• Stone
• Tonne

Returns

Returns the input value converted from one mass unit to another. Invalid units return null.

Example

print result = convert_mass(1.2, 'Kilogram', 'Pound')

Output

59 - convert_speed()

Convert a speed value from one unit to another.

Syntax

convert_speed(value,from,to)

Parameters

Conversion units

• CentimeterPerHour
• CentimeterPerMinute
• CentimeterPerSecond
• DecimeterPerMinute
• DecimeterPerSecond
• FootPerHour
• FootPerMinute
• FootPerSecond
• InchPerHour
• InchPerMinute
• InchPerSecond
• KilometerPerHour
• KilometerPerMinute
• KilometerPerSecond
• Knot
• MeterPerHour
• MeterPerMinute
• MeterPerSecond
• MicrometerPerMinute
• MicrometerPerSecond
• MilePerHour
• MillimeterPerHour
• MillimeterPerMinute
• MillimeterPerSecond
• NanometerPerMinute
• NanometerPerSecond
• UsSurveyFootPerHour
• UsSurveyFootPerMinute
• UsSurveyFootPerSecond
• YardPerHour
• YardPerMinute
• YardPerSecond

Returns

Returns the input value converted from one speed unit to another. Invalid units return null.

Example

print result = convert_speed(1.2, 'MeterPerSecond', 'CentimeterPerHour')

Output

60 - convert_temperature()

Convert a temperature value from one unit to another.

Syntax

convert_temperature(value,from,to)

Parameters

Conversion units

• DegreeCelsius
• DegreeDelisle
• DegreeFahrenheit
• DegreeNewton
• DegreeRankine
• DegreeReaumur
• DegreeRoemer
• Kelvin
• MillidegreeCelsius
• SolarTemperature

Returns

Returns the input value converted from one temperature unit to another. Invalid units return null.

Example

print result = convert_temperature(1.2, 'Kelvin', 'DegreeCelsius')

Output

61 - convert_volume()

Convert a volume value from one unit to another.

Syntax

convert_volume(value,from,to)

Parameters

Conversion units

• AcreFoot
• AuTablespoon
• BoardFoot
• Centiliter
• CubicCentimeter
• CubicDecimeter
• CubicFoot
• CubicHectometer
• CubicInch
• CubicKilometer
• CubicMeter
• CubicMicrometer
• CubicMile
• CubicMillimeter
• CubicYard
• Decaliter
• DecausGallon
• Deciliter
• DeciusGallon
• HectocubicFoot
• HectocubicMeter
• Hectoliter
• HectousGallon
• ImperialBeerBarrel
• ImperialGallon
• ImperialOunce
• ImperialPint
• KilocubicFoot
• KilocubicMeter
• KiloimperialGallon
• Kiloliter
• KilousGallon
• Liter
• MegacubicFoot
• MegaimperialGallon
• Megaliter
• MegausGallon
• MetricCup
• MetricTeaspoon
• Microliter
• Milliliter
• OilBarrel
• UkTablespoon
• UsBeerBarrel
• UsCustomaryCup
• UsGallon
• UsLegalCup
• UsOunce
• UsPint
• UsQuart
• UsTablespoon
• UsTeaspoon

Returns

Returns the input value converted from one volume unit to another. Invalid units return null.

Example

print result = convert_volume(1.2, 'CubicMeter', 'AcreFoot')

Output

62 - cos()

Returns the cosine function value of the specified angle. The angle is specified in radians.

Syntax

cos(number)

Parameters

Returns

The cosine of number of radians.

Example

print cos(1)

Output

63 - cot()

Calculates the trigonometric cotangent of the specified angle, in radians.

Syntax

cot(number)

Parameters

Returns

The cotangent function value for number.

Example

print cot(1)

Output

64 - countof()

Counts occurrences of a substring in a string. Plain string matches may overlap; regex matches don’t.

Syntax

countof(source, search [, kind])

Parameters

Returns

The number of times that the search value can be matched in the source string. Plain string matches may overlap; regex matches don’t.

Examples

65 - current_cluster_endpoint()

Returns the network endpoint (DNS name) of the current cluster being queried.

Returns the network endpoint (DNS name) of the current Eventhouse being queried.

Syntax

current_cluster_endpoint()

Returns

The network endpoint (DNS name) of the current cluster being queried, as a value of type string.

The network endpoint (DNS name) of the current Eventhouse being queried, as a value of type string.

Example

print strcat("This query executed on: ", current_cluster_endpoint())

66 - current_database()

Returns the name of the database in scope (database that all query entities are resolved against if no other database is specified).

Syntax

current_database()

Returns

The name of the database in scope as a value of type string.

Example

print strcat("Database in scope: ", current_database())

67 - current_principal_details()

Returns details of the principal running the query.

Syntax

current_principal_details()

Returns

The details of the current principal as a dynamic. The following table describes the returned fields.

Example

print details=current_principal_details()

Example output

68 - current_principal_is_member_of()

Checks group membership or principal identity of the current principal running the query.

Syntax

current_principal_is_member_of(group)

Parameters

Returns

The function returns true if the current principal running the query is successfully matched for at least one input argument. If not, the function returns false.

Examples

print result=current_principal_is_member_of(
    'aaduser=user1@fabrikam.com', 
    'aadgroup=group1@fabrikam.com',
    'aadapp=66ad1332-3a94-4a69-9fa2-17732f093664;72f988bf-86f1-41af-91ab-2d7cd011db47'
    )

Output

Using dynamic array instead of multiple arguments:

print result=current_principal_is_member_of(
    dynamic([
    'aaduser=user1@fabrikam.com', 
    'aadgroup=group1@fabrikam.com',
    'aadapp=66ad1332-3a94-4a69-9fa2-17732f093664;72f988bf-86f1-41af-91ab-2d7cd011db47'
    ]))

Output

69 - current_principal()

Returns the current principal name that runs the query.

Syntax

current_principal()

Returns

The current principal fully qualified name (FQN) as a string.
The string format is:
PrinciplaType=PrincipalId;TenantId

Example

print fqn=current_principal()

Example output

70 - cursor_after()

A predicate run over the records of a table to compare their ingestion time against a database cursor.

IngestionTime policy enabled.

Syntax

cursor_after(RHS)

Parameters

Returns

A scalar value of type bool that indicates whether the record was ingested after the database cursor RHS (true) or not (false).

Related content

• database cursors

71 - cursor_before_or_at()

A predicate function run over the records of a table to compare their ingestion time against the database cursor time.

IngestionTime policy enabled.

Syntax

cursor_before_or_at(RHS)

Parameters

Returns

A scalar value of type bool that indicates whether the record was ingested before or at the database cursor RHS (true) or not (false).

Related content

• database cursors

72 - cursor_current()

Retrieves the current value of the cursor of the database in scope.

Syntax

cursor_current()

Returns

Returns a single value of type string that encodes the current value of the cursor of the database in scope.

Related content

• database cursors

73 - datetime_add()

Calculates a new datetime from a specified period multiplied by a specified amount, added to, or subtracted from a specified datetime.

Syntax

datetime_add(period,amount,datetime)

Parameters

Possible values of period:

• Year
• Quarter
• Month
• Week
• Day
• Hour
• Minute
• Second
• Millisecond
• Microsecond
• Nanosecond

Returns

A datetime after a certain time/date interval has been added.

Examples

Period

print  year = datetime_add('year',1,make_datetime(2017,1,1)),
quarter = datetime_add('quarter',1,make_datetime(2017,1,1)),
month = datetime_add('month',1,make_datetime(2017,1,1)),
week = datetime_add('week',1,make_datetime(2017,1,1)),
day = datetime_add('day',1,make_datetime(2017,1,1)),
hour = datetime_add('hour',1,make_datetime(2017,1,1)),
minute = datetime_add('minute',1,make_datetime(2017,1,1)),
second = datetime_add('second',1,make_datetime(2017,1,1))

Output

Amount

print  year = datetime_add('year',-5,make_datetime(2017,1,1)),
quarter = datetime_add('quarter',12,make_datetime(2017,1,1)),
month = datetime_add('month',-15,make_datetime(2017,1,1)),
week = datetime_add('week',100,make_datetime(2017,1,1))

Output

74 - datetime_diff()

Calculates the number of the specified periods between two datetime values.

Syntax

datetime_diff(period,datetime1,datetime2)

Parameters

Possible values of period

These values are case insensitive:

• Year
• Quarter
• Month
• Week
• Day
• Hour
• Minute
• Second
• Millisecond
• Microsecond
• Nanosecond

Returns

An integer that represents the amount of periods in the result of subtraction (datetime1 - datetime2).

Example

print
year = datetime_diff('year',datetime(2017-01-01),datetime(2000-12-31)),
quarter = datetime_diff('quarter',datetime(2017-07-01),datetime(2017-03-30)),
month = datetime_diff('month',datetime(2017-01-01),datetime(2015-12-30)),
week = datetime_diff('week',datetime(2017-10-29 00:00),datetime(2017-09-30 23:59)),
day = datetime_diff('day',datetime(2017-10-29 00:00),datetime(2017-09-30 23:59)),
hour = datetime_diff('hour',datetime(2017-10-31 01:00),datetime(2017-10-30 23:59)),
minute = datetime_diff('minute',datetime(2017-10-30 23:05:01),datetime(2017-10-30 23:00:59)),
second = datetime_diff('second',datetime(2017-10-30 23:00:10.100),datetime(2017-10-30 23:00:00.900)),
millisecond = datetime_diff('millisecond',datetime(2017-10-30 23:00:00.200100),datetime(2017-10-30 23:00:00.100900)),
microsecond = datetime_diff('microsecond',datetime(2017-10-30 23:00:00.1009001),datetime(2017-10-30 23:00:00.1008009)),
nanosecond = datetime_diff('nanosecond',datetime(2017-10-30 23:00:00.0000000),datetime(2017-10-30 23:00:00.0000007))

Output

75 - datetime_list_timezones()

Returns a list of supported timezones a time-zone specification.

Syntax

datetime_list_timezones()

Parameters

None, the function doesn’t have any parameters.

Returns

A list of timezones supported by the Internet Assigned Numbers Authority (IANA) Time Zone Database.

Example

print datetime_list_timezones()

Output print datetime_list_timezones()

Related content

• To convert from UTC to local, see datetime_utc_to_local()
• To convert a datetime from local to UTC, see datetime_local_to_utc()
• Timezones
• format_datetime()

76 - datetime_local_to_utc()

Converts local datetime to UTC datetime using a time-zone specification.

Syntax

datetime_local_to_utc(from,timezone)

Parameters

Returns

A UTC datetime that corresponds the local datetime in the specified timezone.

Example

datatable(local_dt: datetime, tz: string)
[ datetime(2020-02-02 20:02:20), 'US/Pacific', 
  datetime(2020-02-02 20:02:20), 'America/Chicago', 
  datetime(2020-02-02 20:02:20), 'Europe/Paris']
| extend utc_dt = datetime_local_to_utc(local_dt, tz)

Output

range Local from datetime(2022-03-27 01:00:00.0000000) to datetime(2022-03-27 04:00:00.0000000) step 1h
| extend UTC=datetime_local_to_utc(Local, 'Europe/Brussels')
| extend BackToLocal=datetime_utc_to_local(UTC, 'Europe/Brussels')
| extend diff=Local-BackToLocal

Related content

• To convert from UTC to local, see datetime_utc_to_local()
• Timezones
• List of supported timezones
• format_datetime()

77 - datetime_part()

Extracts the requested date part as an integer value.

Syntax

datetime_part(part,datetime)

Parameters

Possible values of part

• Year
• Quarter
• Month
• week_of_year
• Day
• DayOfYear
• Hour
• Minute
• Second
• Millisecond
• Microsecond
• Nanosecond

Returns

An integer representing the extracted part.

Example

let dt = datetime(2017-10-30 01:02:03.7654321); 
print 
year = datetime_part("year", dt),
quarter = datetime_part("quarter", dt),
month = datetime_part("month", dt),
weekOfYear = datetime_part("week_of_year", dt),
day = datetime_part("day", dt),
dayOfYear = datetime_part("dayOfYear", dt),
hour = datetime_part("hour", dt),
minute = datetime_part("minute", dt),
second = datetime_part("second", dt),
millisecond = datetime_part("millisecond", dt),
microsecond = datetime_part("microsecond", dt),
nanosecond = datetime_part("nanosecond", dt)

Output

78 - datetime_utc_to_local()

Converts UTC datetime to local datetime using a time-zone specification.

Syntax

datetime_utc_to_local(from,timezone)

Parameters

Returns

A local datetime in the timezone that corresponds the UTC datetime.

Example

print dt=now()
| extend pacific_dt = datetime_utc_to_local(dt, 'US/Pacific'), canberra_dt = datetime_utc_to_local(dt, 'Australia/Canberra')
| extend diff = pacific_dt - canberra_dt

Output

Related content

• To convert a datetime from local to UTC, see datetime_local_to_utc()
• Timezones
• List of supported timezones
• format_datetime()

79 - dayofmonth()

Returns an integer representing the day number of the given datetime.

Syntax

dayofmonth(date)

Parameters

Returns

An integer representing the day number of the given datetime.

Example

dayofmonth(datetime(2015-12-14))

Output

80 - dayofweek()

Returns the number of days since the preceding Sunday, as a timespan.

To convert timespan to int, see Convert timespan to integer.

Syntax

dayofweek(date)

Parameters

Returns

The timespan since midnight at the beginning of the preceding Sunday, rounded down to an integer number of days.

Examples

The following example returns 0, indicating that the specified datetime is a Sunday.

print
Timespan = dayofweek(datetime(1947-11-30 10:00:05))

Output

The following example returns 1, indicating that the specified datetime is a Monday.

print
Timespan = dayofweek(datetime(1970-05-11))

Output

Convert timespan to integer

The following example returns the number of days both as a timespan and as data type int.

let dow=dayofweek(datetime(1970-5-12));
print Timespan = dow, Integer = toint(dow/1d)

Output

Related content

The timespan data type

81 - dayofyear()

Returns the integer number represents the day number of the given year.

Syntax

dayofyear(date)

Parameters

Returns

The day number of the given year.

Example

dayofyear(datetime(2015-12-14))

Output

82 - dcount_hll()

Calculates the distinct count from results generated by hll or hll_merge.

Read about the underlying algorithm (HyperLogLog) and estimation accuracy.

Syntax

dcount_hll(hll)

Parameters

Returns

Returns the distinct count of each value in hll.

Example

The following example shows the distinct count hll merged results.

StormEvents
| summarize hllRes = hll(DamageProperty) by bin(StartTime,10m)
| summarize hllMerged = hll_merge(hllRes)
| project dcount_hll(hllMerged)

Output

Estimation accuracy

83 - degrees()

Converts angle value in radians into value in degrees, using the formula degrees = (180 / PI ) * angle_in_radians.

Syntax

degrees(radians)

Parameters

Returns

The corresponding angle in degrees for an angle specified in radians.

Examples

print degrees0 = degrees(pi()/4), degrees1 = degrees(pi()*1.5), degrees2 = degrees(0)

Output

84 - dynamic_to_json()

Converts a scalar value of type dynamic to a canonical string representation.

Syntax

dynamic_to_json(expr)

Parameters

Returns

Returns a canonical representation of the input as a value of type string, according to the following rules:

• If the input is a scalar value of type other than dynamic, the output is the application of tostring() to that value.

• If the input is an array of values, the output is composed of the characters [, ,, and ] interspersed with the canonical representation described here of each array element.

• If the input is a property bag, the output is composed of the characters {, ,, and } interspersed with the colon (:)-delimited name/value pairs of the properties. The pairs are sorted by the names, and the values are in the canonical representation described here of each array element.

Example

let bag1 = dynamic_to_json(
  dynamic({
    'Y10':dynamic({}),
    'X8': dynamic({
      'c3':1,
      'd8':5,
      'a4':6
    }),
    'D1':114,
    'A1':12,
    'B1':2,
    'C1':3,
    'A14':[15, 13, 18]
}));
let bag2 = dynamic_to_json(
  dynamic({
    'X8': dynamic({
      'a4':6,
      'c3':1,
      'd8':5
    }),
    'A14':[15, 13, 18],
    'C1':3,
    'B1':2,
    'Y10': dynamic({}),
    'A1':12, 'D1':114
  }));
print AreEqual=bag1 == bag2, Result=bag1

Output

85 - endofday()

Returns the end of the day containing the date, shifted by an offset, if provided.

Syntax

endofday(date [, offset])

Parameters

Returns

A datetime representing the end of the day for the given date value, with the offset, if specified.

Example

  range offset from -1 to 1 step 1
 | project dayEnd = endofday(datetime(2017-01-01 10:10:17), offset) 

Output

86 - endofmonth()

Returns the end of the month containing the date, shifted by an offset, if provided.

Syntax

endofmonth(date [, offset])

Parameters

Returns

A datetime representing the end of the month for the given date value, with the offset, if specified.

Example

  range offset from -1 to 1 step 1
 | project monthEnd = endofmonth(datetime(2017-01-01 10:10:17), offset) 

Output

87 - endofweek()

Returns the end of the week containing the date, shifted by an offset, if provided.

Last day of the week is considered to be a Saturday.

Syntax

endofweek(date [, offset])

Parameters

Returns

A datetime representing the end of the week for the given date value, with the offset, if specified.

Example

  range offset from -1 to 1 step 1
 | project weekEnd = endofweek(datetime(2017-01-01 10:10:17), offset)  

Output

88 - endofyear()

Returns the end of the year containing the date, shifted by an offset, if provided.

Syntax

endofyear(date [, offset])

Parameters

Returns

A datetime representing the end of the year for the given date value, with the &offset, if specified.

Example

  range offset from -1 to 1 step 1
 | project yearEnd = endofyear(datetime(2017-01-01 10:10:17), offset) 

Output

89 - erf()

Returns the error function of the input.

Syntax

erf(x)

Parameters

Returns

Error function of x.

Example

range x from -3 to 3 step 1
| extend erf_x = erf(x)

90 - erfc()

Returns the complementary error function of the input.

Syntax

erfc(x)

Parameters

Returns

Complementary error function of x.

Example

range x from -3 to 3 step 1
| extend erf_x = erfc(x)

91 - estimate_data_size()

Returns an estimated data size in bytes of the selected columns of the tabular expression.

Syntax

estimate_data_size(columns)

Parameters

Returns

The estimated data size in bytes of the referenced columns. Estimation is based on data types and actual values. For example, the data size for the string '{"a":"bcd"}' is smaller than the dynamic value dynamic({"a":"bcd"}) because the latter’s internal representation is more complex than that of a string.

Example

The following example calculates the total data size using estimate_data_size().

range x from 1 to 10 step 1                    // x (long) is 8 
| extend Text = '1234567890'                   // Text length is 10  
| summarize Total=sum(estimate_data_size(*))   // (8+10)x10 = 180

Output

Related content

• Estimate table size

92 - exp()

The base-e exponential function of x, which is e raised to the power x: e^x.

Syntax

exp(x)

Parameters

Returns

The exponential value of x.

Related content

• For natural (base-e) logarithms, see log().
• For exponential functions of base-2 and base-10 logarithms, see exp2(), exp10().

93 - exp10()

The base-10 exponential function of x, which is 10 raised to the power x: 10^x.

Syntax

exp10(x)

Parameters

Returns

The exponential value of x.

Related content

• For natural (base-10) logarithms, see log10().
• For exponential functions of base-e and base-2 logarithms, see exp(), exp2().

94 - exp2()

The base-2 exponential function of x, which is 2 raised to the power x: 2^x.

Syntax

exp2(x)

Parameters

Returns

The exponential value of x.

Related content

• For natural (base-2) logarithms, see log2().
• For exponential functions of base-e and base-10 logarithms, see exp(), exp10().

95 - extent_id()

Returns a unique identifier that identifies the data shard (“extent”) that the current record resides in at the time the query was run.

Applying this function to calculated data that isn’t attached to a data shard returns an empty guid (all zeros).

Syntax

extent_id()

Returns

A value of type guid that identifies the current record’s data shard at the time the query was run, or an empty guid (all zeros).

Example

The following example shows how to get a list of all the data shards that currently have records from an hour ago with a specific value for the column ActivityId. It demonstrates that some query operators (here, the where operator, and also extend and project) preserve the information about the data shard hosting the record.

T
| where Timestamp > ago(1h)
| where ActivityId == 'dd0595d4-183e-494e-b88e-54c52fe90e5a'
| extend eid=extent_id()
| summarize by eid

96 - extent_tags()

Returns a dynamic array with the extent tags of the extent that the current record is in.

If you apply this function to calculated data, which isn’t attached to a data shard, returns an empty value.

Syntax

extent_tags()

Returns

A value of type dynamic that is an array holding the current record’s extent tags, or an empty value.

Examples

Some query operators preserve the information about the data shard hosting the record. These operators include where, extend, and project. The following example shows how to get a list the tags of all the data shards that have records from an hour ago, with a specific value for the column ActivityId.

T
| where Timestamp > ago(1h)
| where ActivityId == 'dd0595d4-183e-494e-b88e-54c52fe90e5a'
| extend tags = extent_tags()
| summarize by tostring(tags)

The following example shows how to obtain a count of all records from the last hour, which are stored in extents tagged with the tag MyTag(and potentially other tags), but not tagged with the tag drop-by:MyOtherTag.

T
| where Timestamp > ago(1h)
| extend Tags = extent_tags()
| where Tags has_cs 'MyTag' and Tags !has_cs 'drop-by:MyOtherTag'
| count

97 - extract_all()

Get all matches for a regular expression from a source string. Optionally, retrieve a subset of matching groups.

print extract_all(@"(\d+)", "a set of numbers: 123, 567 and 789") // results with the dynamic array ["123", "567", "789"]

Syntax

extract_all(regex, [captureGroups,] source)

Parameters

Returns

• If regex finds a match in source: Returns dynamic array including all matches against the indicated capture groups captureGroups, or all of capturing groups in the regex.
• If number of captureGroups is 1: The returned array has a single dimension of matched values.
• If number of captureGroups is more than 1: The returned array is a two-dimensional collection of multi-value matches per captureGroups selection, or all capture groups present in the regex if captureGroups is omitted.
• If there’s no match: null.

Examples

Extract a single capture group

The following query returns hex-byte representation (two hex-digits) of the GUID.

print Id="82b8be2d-dfa7-4bd1-8f63-24ad26d31449"
| extend guid_bytes = extract_all(@"([\da-f]{2})", Id) 

Output

Extract several capture groups

The following query uses a regular expression with three capturing groups to split each GUID part into first letter, last letter, and whatever is in the middle.

print Id="82b8be2d-dfa7-4bd1-8f63-24ad26d31449"
| extend guid_bytes = extract_all(@"(\w)(\w+)(\w)", Id)

Output

Extract a subset of capture groups

The following query selects a subset of capturing groups.

The regular expression matches the first letter, last letter, and all the rest.

The captureGroups parameter is used to select only the first and the last parts.

print Id="82b8be2d-dfa7-4bd1-8f63-24ad26d31449"
| extend guid_bytes = extract_all(@"(\w)(\w+)(\w)", dynamic([1,3]), Id) 

Output

Using named capture groups

The captureGroups in the following query uses both capture group indexes and named capture group references to fetch matching values.

print Id="82b8be2d-dfa7-4bd1-8f63-24ad26d31449"
| extend guid_bytes = extract_all(@"(?P<first>\w)(?P<middle>\w+)(?P<last>\w)", dynamic(['first',2,'last']), Id) 

Output

Related content

• extract function
• extract-json function
• parse operator

98 - extract_json()

Get a specified element out of a JSON text using a path expression.

Optionally convert the extracted string to a specific type.

Syntax

extract_json(jsonPath, dataSource, type)

Parameters

Performance tips

• Apply where-clauses before using extract_json().
• Consider using a regular expression match with extract instead. This can run very much faster, and is effective if the JSON is produced from a template.
• Use parse_json() if you need to extract more than one value from the JSON.
• Consider having the JSON parsed at ingestion by declaring the type of the column to be dynamic.

Returns

This function performs a JSONPath query into dataSource, which contains a valid JSON string, optionally converting that value to another type depending on the third argument.

Example

let json = '{"name": "John", "age": 30, "city": "New York"}';
print extract_json("$.name", json, typeof(string));

Output

Related content

• extract function
• extract-all function
• parse operator

99 - extract()

Get a match for a regular expression from a source string.

Optionally, convert the extracted substring to the indicated type.

Syntax

extract(regex, captureGroup, source [, typeLiteral])

Parameters

Returns

If regex finds a match in source: the substring matched against the indicated capture group captureGroup, optionally converted to typeLiteral.

If there’s no match, or the type conversion fails: null.

Examples

Extract month from datetime string

The following query extracts the month from the string Dates and returns a table with the date string and the month.

let Dates = datatable(DateString: string)
[
    "15-12-2024",
    "21-07-2023",
    "10-03-2022"
];
Dates
| extend Month = extract(@"-(\d{2})-", 1, DateString, typeof(int))
| project DateString, Month

Output

Extract username from a string

The following example returns the username from the string. The regular expression ([^,]+) matches the text following “User: " up to the next comma, effectively extracting the username.

let Text = "User: JohnDoe, Email: johndoe@example.com, Age: 29";
print UserName = extract("User: ([^,]+)", 1, Text)

Output

Related content

• extract-all function
• extract-json function
• parse operator
• regular expression

100 - format_bytes()

Formats a number as a string representing data size in bytes.

Syntax

format_bytes(size [, precision [, units]])

Parameters

Returns

A string of size formatted as data size in bytes.

Examples

print 
v1 = format_bytes(564),
v2 = format_bytes(10332, 1),
v3 = format_bytes(20010332),
v4 = format_bytes(20010332, 2),
v5 = format_bytes(20010332, 0, "KB")

Output

Related content

• Estimate table size

101 - format_datetime()

Formats a datetime according to the provided format.

Syntax

format_datetime(date , format)

Parameters

Supported format elements

The format parameter should include one or more of the following elements:

Supported delimiters

The format specifier can include the following delimiters:

Returns

A string with date formatted as specified by format.

Examples

The following three examples return differently formatted datetimes.

let dt = datetime(2017-01-29 09:00:05);
print 
v1=format_datetime(dt,'yy-MM-dd [HH:mm:ss]')

Output

let dt = datetime(2017-01-29 09:00:05);
print 
v2=format_datetime(dt, 'yyyy-M-dd [H:mm:ss]')

Output

let dt = datetime(2017-01-29 09:00:05);
print 
v3=format_datetime(dt, 'yy-MM-dd [hh:mm:ss tt]')

Output

Related content

• To convert from UTC to local, see datetime_utc_to_local()
• To convert a datetime from local to UTC, see datetime_local_to_utc()

102 - format_ipv4_mask()

Parses the input with a netmask and returns a string representing the IPv4 address in CIDR notation.

Syntax

format_ipv4_mask(ip [, prefix])

Parameters

Returns

If conversion is successful, the result will be a string representing IPv4 address as CIDR notation. If conversion isn’t successful, the result will be an empty string.

Examples

datatable(address:string, mask:long)
[
 '192.168.1.1', 24,          
 '192.168.1.1', 32,          
 '192.168.1.1/24', 32,       
 '192.168.1.1/24', long(-1), 
]
| extend result = format_ipv4(address, mask), 
         result_mask = format_ipv4_mask(address, mask)

Output

Related content

• For IPv4 address formatting without CIDR notation, see format_ipv4().
• For a list of functions related to IP addresses, see IPv4 and IPv6 functions.

103 - format_ipv4()

Parses the input with a netmask and returns a string representing the IPv4 address.

Syntax

format_ipv4(ip [, prefix])

Parameters

Returns

If conversion is successful, the result will be a string representing IPv4 address. If conversion isn’t successful, the result will be an empty string.

Examples

datatable(address:string, mask:long)
[
 '192.168.1.1', 24,          
 '192.168.1.1', 32,          
 '192.168.1.1/24', 32,       
 '192.168.1.1/24', long(-1), 
]
| extend result = format_ipv4(address, mask), 
         result_mask = format_ipv4_mask(address, mask)

Output

Related content

• For IPv4 address formatting including CIDR notation, see format_ipv4_mask().
• For a list of functions related to IP addresses, see IPv4 and IPv6 functions.

104 - format_timespan()

Formats a timespan according to the provided format.

Syntax

format_timespan(timespan , format)

Parameters

Supported format elements

Supported delimiters

The format specifier can include following delimiters:

Returns

A string with timespan formatted as specified by format.

Examples

let t = time(29.09:00:05.12345);
print 
v1=format_timespan(t, 'dd.hh:mm:ss:FF'),
v2=format_timespan(t, 'ddd.h:mm:ss [fffffff]')

Output

105 - gamma()

Computes the gamma function for the provided number.

Syntax

gamma(number)

Parameters