Queries

• 1: Aggregation functions
• 1.1: Aggregation Functions
• 1.2: arg_max() (aggregation function)
• 1.3: arg_min() (aggregation function)
• 1.4: avg() (aggregation function)
• 1.5: avgif() (aggregation function)
• 1.6: binary_all_and() (aggregation function)
• 1.7: binary_all_or() (aggregation function)
• 1.8: binary_all_xor() (aggregation function)
• 1.9: buildschema() (aggregation function)
• 1.10: count_distinct() (aggregation function) - (preview)
• 1.11: count_distinctif() (aggregation function) - (preview)
• 1.12: count() (aggregation function)
• 1.13: countif() (aggregation function)
• 1.14: dcount() (aggregation function)
• 1.15: dcountif() (aggregation function)
• 1.16: hll_if() (aggregation function)
• 1.17: hll_merge() (aggregation function)
• 1.18: hll() (aggregation function)
• 1.19: make_bag_if() (aggregation function)
• 1.20: make_bag() (aggregation function)
• 1.21: make_list_if() (aggregation function)
• 1.22: make_list_with_nulls() (aggregation function)
• 1.23: make_list() (aggregation function)
• 1.24: make_set_if() (aggregation function)
• 1.25: make_set() (aggregation function)
• 1.26: max() (aggregation function)
• 1.27: maxif() (aggregation function)
• 1.28: min() (aggregation function)
• 1.29: minif() (aggregation function)
• 1.30: percentile(), percentiles()
• 1.31: percentilew(), percentilesw()
• 1.32: stdev() (aggregation function)
• 1.33: stdevif() (aggregation function)
• 1.34: stdevp() (aggregation function)
• 1.35: sum() (aggregation function)
• 1.36: sumif() (aggregation function)
• 1.37: take_any() (aggregation function)
• 1.38: take_anyif() (aggregation function)
• 1.39: tdigest_merge() (aggregation functions)
• 1.40: tdigest() (aggregation function)
• 1.41: variance() (aggregation function)
• 1.42: varianceif() (aggregation function)
• 1.43: variancep() (aggregation function)
• 2: Best practices for KQL queries
• 2.1: Best practices for Kusto Query Language queries
• 2.2: Named expressions
• 3: Data types
• 3.1: Null values
• 3.2: Scalar data types
• 3.3: The bool data type
• 3.4: The datetime data type
• 3.5: The decimal data type
• 3.6: The dynamic data type
• 3.7: The guid data type
• 3.8: The int data type
• 3.9: The long data type
• 3.10: The real data type
• 3.11: The string data type
• 3.12: The timespan data type
• 4: Entities
• 4.1: Columns
• 4.2: Databases
• 4.3: Entities
• 4.4: Entity names
• 4.5: Entity references
• 4.6: External tables
• 4.7: Fact and dimension tables
• 4.8: Stored functions
• 4.9: Tables
• 4.10: Views
• 5: Functions
• 5.1: bartlett_test_fl()
• 5.2: binomial_test_fl()
• 5.3: comb_fl()
• 5.4: dbscan_dynamic_fl()
• 5.5: dbscan_fl()
• 5.6: detect_anomalous_new_entity_fl()
• 5.7: factorial_fl()
• 5.8: Functions
• 5.9: Functions library
• 5.10: geoip_fl()
• 5.11: get_packages_version_fl()
• 5.12: kmeans_dynamic_fl()
• 5.13: kmeans_fl()
• 5.14: ks_test_fl()
• 5.15: levene_test_fl()
• 5.16: log_reduce_fl()
• 5.17: log_reduce_full_fl()
• 5.18: log_reduce_predict_fl()
• 5.19: log_reduce_predict_full_fl()
• 5.20: log_reduce_train_fl()
• 5.21: mann_whitney_u_test_fl()
• 5.22: normality_test_fl()
• 5.23: pair_probabilities_fl()
• 5.24: pairwise_dist_fl()
• 5.25: percentiles_linear_fl()
• 5.26: perm_fl()
• 5.27: plotly_anomaly_fl()
• 5.28: plotly_gauge_fl()
• 5.29: plotly_scatter3d_fl()
• 5.30: predict_fl()
• 5.31: predict_onnx_fl()
• 5.32: quantize_fl()
• 5.33: series_clean_anomalies_fl()
• 5.34: series_cosine_similarity_fl()
• 5.35: series_dbl_exp_smoothing_fl()
• 5.36: series_dot_product_fl()
• 5.37: series_downsample_fl()
• 5.38: series_exp_smoothing_fl()
• 5.39: series_fbprophet_forecast_fl()
• 5.40: series_fit_lowess_fl()
• 5.41: series_fit_poly_fl()
• 5.42: series_lag_fl()
• 5.43: series_metric_fl()
• 5.44: series_monthly_decompose_anomalies_fl()
• 5.45: series_moving_avg_fl()
• 5.46: series_moving_var_fl()
• 5.47: series_mv_ee_anomalies_fl()
• 5.48: series_mv_if_anomalies_fl()
• 5.49: series_mv_oc_anomalies_fl()
• 5.50: series_rate_fl()
• 5.51: series_rolling_fl()
• 5.52: series_shapes_fl()
• 5.53: series_uv_anomalies_fl()
• 5.54: series_uv_change_points_fl()
• 5.55: time_weighted_avg_fl()
• 5.56: time_weighted_avg2_fl()
• 5.57: time_weighted_val_fl()
• 5.58: time_window_rolling_avg_fl()
• 5.59: two_sample_t_test_fl()
• 5.60: User-defined functions
• 5.61: wilcoxon_test_fl()
• 6: Geospatial
• 6.1: geo_angle()
• 6.2: geo_azimuth()
• 6.3: geo_distance_2points()
• 6.4: geo_distance_point_to_line()
• 6.5: geo_distance_point_to_polygon()
• 6.6: geo_geohash_neighbors()
• 6.7: geo_geohash_to_central_point()
• 6.8: geo_geohash_to_polygon()
• 6.9: geo_h3cell_children()
• 6.10: geo_h3cell_level()
• 6.11: geo_h3cell_neighbors()
• 6.12: geo_h3cell_parent()
• 6.13: geo_h3cell_rings()
• 6.14: geo_h3cell_to_central_point()
• 6.15: geo_h3cell_to_polygon()
• 6.16: geo_intersection_2lines()
• 6.17: geo_intersection_2polygons()
• 6.18: geo_intersection_line_with_polygon()
• 6.19: geo_intersects_2lines()
• 6.20: geo_intersects_2polygons()
• 6.21: geo_intersects_line_with_polygon()
• 6.22: geo_line_buffer()
• 6.23: geo_line_centroid()
• 6.24: geo_line_densify()
• 6.25: geo_line_length()
• 6.26: geo_line_simplify()
• 6.27: geo_line_to_s2cells()
• 6.28: geo_point_buffer()
• 6.29: geo_point_in_circle()
• 6.30: geo_point_in_polygon()
• 6.31: geo_point_to_geohash()
• 6.32: geo_point_to_h3cell()
• 6.33: geo_point_to_s2cell()
• 6.34: geo_polygon_area()
• 6.35: geo_polygon_buffer()
• 6.36: geo_polygon_centroid()
• 6.37: geo_polygon_densify()
• 6.38: geo_polygon_perimeter()
• 6.39: geo_polygon_simplify()
• 6.40: geo_polygon_to_h3cells()
• 6.41: geo_polygon_to_s2cells()
• 6.42: geo_s2cell_neighbors()
• 6.43: geo_s2cell_to_central_point()
• 6.44: geo_s2cell_to_polygon()
• 6.45: geo_simplify_polygons_array()
• 6.46: geo_union_lines_array()
• 6.47: geo_union_polygons_array()
• 6.48: Geospatial data visualizations
• 6.49: Geospatial grid system
• 7: Graph operators
• 7.1: Best practices for Kusto Query Language (KQL) graph semantics
• 7.2: Graph operators
• 7.3: graph-mark-components operator (Preview)
• 7.4: graph-match operator
• 7.5: graph-shortest-paths Operator (Preview)
• 7.6: graph-to-table operator
• 7.7: Kusto Query Language (KQL) graph semantics overview
• 7.8: make-graph operator
• 7.9: Scenarios for using Kusto Query Language (KQL) graph semantics
• 8: Limits and Errors
• 8.1: Query consistency
• 8.2: Query limits
• 8.3: Partial query failures
• 8.3.1: Kusto query result set exceeds internal limit
• 8.3.2: Overflows
• 8.3.3: Runaway queries
• 9: Plugins
• 9.1: Data reshaping plugins
• 9.1.1: bag_unpack plugin
• 9.1.2: narrow plugin
• 9.1.3: pivot plugin
• 9.2: General plugins
• 9.2.1: dcount_intersect plugin
• 9.2.2: infer_storage_schema plugin
• 9.2.3: infer_storage_schema_with_suggestions plugin
• 9.2.4: ipv4_lookup plugin
• 9.2.5: ipv6_lookup plugin
• 9.2.6: preview plugin
• 9.2.7: schema_merge plugin
• 9.3: Language plugins
• 9.3.1: Python plugin
• 9.3.2: Python plugin packages
• 9.3.3: R plugin (Preview)
• 9.4: Machine learning plugins
• 9.4.1: autocluster plugin
• 9.4.2: basket plugin
• 9.4.3: diffpatterns plugin
• 9.4.4: diffpatterns_text plugin
• 9.5: Query connectivity plugins
• 9.5.1: ai_embed_text plugin (Preview)
• 9.5.2: azure_digital_twins_query_request plugin
• 9.5.3: cosmosdb_sql_request plugin
• 9.5.4: http_request plugin
• 9.5.5: http_request_post plugin
• 9.5.6: mysql_request plugin
• 9.5.7: postgresql_request plugin
• 9.5.8: sql_request plugin
• 9.6: User and sequence analytics plugins
• 9.6.1: active_users_count plugin
• 9.6.2: activity_counts_metrics plugin
• 9.6.3: activity_engagement plugin
• 9.6.4: activity_metrics plugin
• 9.6.5: funnel_sequence plugin
• 9.6.6: funnel_sequence_completion plugin
• 9.6.7: new_activity_metrics plugin
• 9.6.8: rolling_percentile plugin
• 9.6.9: rows_near plugin
• 9.6.10: sequence_detect plugin
• 9.6.11: session_count plugin
• 9.6.12: sliding_window_counts plugin
• 9.6.13: User Analytics
• 10: Query statements
• 10.1: Alias statement
• 10.2: Batches
• 10.3: Let statement
• 10.4: Pattern statement
• 10.5: Query parameters declaration statement
• 10.6: Query statements
• 10.7: Restrict statement
• 10.8: Set statement
• 10.9: Tabular expression statements
• 11: Reference
• 11.1: JSONPath syntax
• 11.2: KQL docs navigation guide
• 11.3: Regex syntax
• 11.4: Splunk to Kusto map
• 11.5: SQL to Kusto query translation
• 11.6: Timezone
• 12: Scalar functions
• 12.1: abs()
• 12.2: acos()
• 12.3: ago()
• 12.4: around() function
• 12.5: array_concat()
• 12.6: array_iff()
• 12.7: array_index_of()
• 12.8: array_length()
• 12.9: array_reverse()
• 12.10: array_rotate_left()
• 12.11: array_rotate_right()
• 12.12: array_shift_left()
• 12.13: array_shift_right()
• 12.14: array_slice()
• 12.15: array_sort_asc()
• 12.16: array_sort_desc()
• 12.17: array_split()
• 12.18: array_sum()
• 12.19: asin()
• 12.20: assert()
• 12.21: atan()
• 12.22: atan2()
• 12.23: bag_has_key()
• 12.24: bag_keys()
• 12.25: bag_merge()
• 12.26: bag_pack_columns()
• 12.27: bag_pack()
• 12.28: bag_remove_keys()
• 12.29: bag_set_key()
• 12.30: bag_zip()
• 12.31: base64_decode_toarray()
• 12.32: base64_decode_toguid()
• 12.33: base64_decode_tostring()
• 12.34: base64_encode_fromarray()
• 12.35: base64_encode_fromguid()
• 12.36: base64_encode_tostring()
• 12.37: beta_cdf()
• 12.38: beta_inv()
• 12.39: beta_pdf()
• 12.40: bin_at()
• 12.41: bin_auto()
• 12.42: bin()
• 12.43: binary_and()
• 12.44: binary_not()
• 12.45: binary_or()
• 12.46: binary_shift_left()
• 12.47: binary_shift_right()
• 12.48: binary_xor()
• 12.49: bitset_count_ones()
• 12.50: case()
• 12.51: ceiling()
• 12.52: coalesce()
• 12.53: column_ifexists()
• 12.54: convert_angle()
• 12.55: convert_energy()
• 12.56: convert_force()
• 12.57: convert_length()
• 12.58: convert_mass()
• 12.59: convert_speed()
• 12.60: convert_temperature()
• 12.61: convert_volume()
• 12.62: cos()
• 12.63: cot()
• 12.64: countof()
• 12.65: current_cluster_endpoint()
• 12.66: current_database()
• 12.67: current_principal_details()
• 12.68: current_principal_is_member_of()
• 12.69: current_principal()
• 12.70: cursor_after()
• 12.71: cursor_before_or_at()
• 12.72: cursor_current()
• 12.73: datetime_add()
• 12.74: datetime_diff()
• 12.75: datetime_list_timezones()
• 12.76: datetime_local_to_utc()
• 12.77: datetime_part()
• 12.78: datetime_utc_to_local()
• 12.79: dayofmonth()
• 12.80: dayofweek()
• 12.81: dayofyear()
• 12.82: dcount_hll()
• 12.83: degrees()
• 12.84: dynamic_to_json()
• 12.85: endofday()
• 12.86: endofmonth()
• 12.87: endofweek()
• 12.88: endofyear()
• 12.89: erf()
• 12.90: erfc()
• 12.91: estimate_data_size()
• 12.92: exp()
• 12.93: exp10()
• 12.94: exp2()
• 12.95: extent_id()
• 12.96: extent_tags()
• 12.97: extract_all()
• 12.98: extract_json()
• 12.99: extract()
• 12.100: format_bytes()
• 12.101: format_datetime()
• 12.102: format_ipv4_mask()
• 12.103: format_ipv4()
• 12.104: format_timespan()
• 12.105: gamma()
• 12.106: geo_info_from_ip_address()
• 12.107: gettype()
• 12.108: getyear()
• 12.109: gzip_compress_to_base64_string
• 12.110: gzip_decompress_from_base64_string()
• 12.111: has_any_ipv4_prefix()
• 12.112: has_any_ipv4()
• 12.113: has_ipv4_prefix()
• 12.114: has_ipv4()
• 12.115: hash_combine()
• 12.116: hash_many()
• 12.117: hash_md5()
• 12.118: hash_sha1()
• 12.119: hash_sha256()
• 12.120: hash_xxhash64()
• 12.121: hash()
• 12.122: hll_merge()
• 12.123: hourofday()
• 12.124: iff()
• 12.125: indexof_regex()
• 12.126: indexof()
• 12.127: ingestion_time()
• 12.128: ipv4_compare()
• 12.129: ipv4_is_in_any_range()
• 12.130: ipv4_is_in_range()
• 12.131: ipv4_is_match()
• 12.132: ipv4_is_private()
• 12.133: ipv4_netmask_suffix()
• 12.134: ipv4_range_to_cidr_list()
• 12.135: ipv6_compare()
• 12.136: ipv6_is_in_any_range()
• 12.137: ipv6_is_in_range()
• 12.138: ipv6_is_match()
• 12.139: isascii()
• 12.140: isempty()
• 12.141: isfinite()
• 12.142: isinf()
• 12.143: isnan()
• 12.144: isnotempty()
• 12.145: isnotnull()
• 12.146: isnull()
• 12.147: isutf8()
• 12.148: jaccard_index()
• 12.149: log()
• 12.150: log10()
• 12.151: log2()
• 12.152: loggamma()
• 12.153: make_datetime()
• 12.154: make_timespan()
• 12.155: max_of()
• 12.156: merge_tdigest()
• 12.157: min_of()
• 12.158: monthofyear()
• 12.159: new_guid()
• 12.160: not()
• 12.161: now()
• 12.162: pack_all()
• 12.163: pack_array()
• 12.164: parse_command_line()
• 12.165: parse_csv()
• 12.166: parse_ipv4_mask()
• 12.167: parse_ipv4()
• 12.168: parse_ipv6_mask()
• 12.169: parse_ipv6()
• 12.170: parse_json() function
• 12.171: parse_path()
• 12.172: parse_url()
• 12.173: parse_urlquery()
• 12.174: parse_user_agent()
• 12.175: parse_version()
• 12.176: parse_xml()
• 12.177: percentile_array_tdigest()
• 12.178: percentile_tdigest()
• 12.179: percentrank_tdigest()
• 12.180: pi()
• 12.181: pow()
• 12.182: punycode_domain_from_string
• 12.183: punycode_domain_to_string
• 12.184: punycode_from_string
• 12.185: punycode_to_string
• 12.186: radians()
• 12.187: rand()
• 12.188: range()
• 12.189: rank_tdigest()
• 12.190: regex_quote()
• 12.191: repeat()
• 12.192: replace_regex()
• 12.193: replace_string()
• 12.194: replace_strings()
• 12.195: reverse()
• 12.196: round()
• 12.197: Scalar Functions
• 12.198: set_difference()
• 12.199: set_has_element()
• 12.200: set_intersect()
• 12.201: set_union()
• 12.202: sign()
• 12.203: sin()
• 12.204: split()
• 12.205: sqrt()
• 12.206: startofday()
• 12.207: startofmonth()
• 12.208: startofweek()
• 12.209: startofyear()
• 12.210: strcat_array()
• 12.211: strcat_delim()
• 12.212: strcat()
• 12.213: strcmp()
• 12.214: string_size()
• 12.215: strlen()
• 12.216: strrep()
• 12.217: substring()
• 12.218: tan()
• 12.219: The has_any_index operator
• 12.220: tobool()
• 12.221: todatetime()
• 12.222: todecimal()
• 12.223: toguid()
• 12.224: tohex()
• 12.225: toint()
• 12.226: tolong()
• 12.227: tolower()
• 12.228: toreal()
• 12.229: tostring()
• 12.230: totimespan()
• 12.231: toupper()
• 12.232: translate()
• 12.233: treepath()
• 12.234: trim_end()
• 12.235: trim_start()
• 12.236: trim()
• 12.237: unicode_codepoints_from_string()
• 12.238: unicode_codepoints_to_string()
• 12.239: unixtime_microseconds_todatetime()
• 12.240: unixtime_milliseconds_todatetime()
• 12.241: unixtime_nanoseconds_todatetime()
• 12.242: unixtime_seconds_todatetime()
• 12.243: url_decode()
• 12.244: url_encode_component()
• 12.245: url_encode()
• 12.246: week_of_year()
• 12.247: welch_test()
• 12.248: zip()
• 12.249: zlib_compress_to_base64_string
• 12.250: zlib_decompress_from_base64_string()
• 13: Scalar operators
• 13.1: Bitwise (binary) operators
• 13.2: Datetime / timespan arithmetic
• 13.3: Logical (binary) operators
• 13.4: Numerical operators
• 13.5: Between operators
• 13.5.1: The !between operator
• 13.5.2: The between operator
• 13.6: in operators
• 13.6.1: The case-insensitive !in~ string operator
• 13.6.2: The case-insensitive in~ string operator
• 13.6.3: The case-sensitive !in string operator
• 13.6.4: The case-sensitive in string operator
• 13.7: String operators
• 13.7.1: matches regex operator
• 13.7.2: String operators
• 13.7.3: The case-insensitive !~ (not equals) string operator
• 13.7.4: The case-insensitive !contains string operator
• 13.7.5: The case-insensitive !endswith string operator
• 13.7.6: The case-insensitive !has string operators
• 13.7.7: The case-insensitive !hasprefix string operator
• 13.7.8: The case-insensitive !hassuffix string operator
• 13.7.9: The case-insensitive !in~ string operator
• 13.7.10: The case-insensitive !startswith string operators
• 13.7.11: The case-insensitive =~ (equals) string operator
• 13.7.12: The case-insensitive contains string operator
• 13.7.13: The case-insensitive endswith string operator
• 13.7.14: The case-insensitive has string operator
• 13.7.15: The case-insensitive has_all string operator
• 13.7.16: The case-insensitive has_any string operator
• 13.7.17: The case-insensitive hasprefix string operator
• 13.7.18: The case-insensitive hassuffix string operator
• 13.7.19: The case-insensitive in~ string operator
• 13.7.20: The case-insensitive startswith string operator
• 13.7.21: The case-sensitive != (not equals) string operator
• 13.7.22: The case-sensitive !contains_cs string operator
• 13.7.23: The case-sensitive !endswith_cs string operator
• 13.7.24: The case-sensitive !has_cs string operator
• 13.7.25: The case-sensitive !hasprefix_cs string operator
• 13.7.26: The case-sensitive !hassuffix_cs string operator
• 13.7.27: The case-sensitive !in string operator
• 13.7.28: The case-sensitive !startswith_cs string operator
• 13.7.29: The case-sensitive == (equals) string operator
• 13.7.30: The case-sensitive contains_cs string operator
• 13.7.31: The case-sensitive endswith_cs string operator
• 13.7.32: The case-sensitive has_cs string operator
• 13.7.33: The case-sensitive hasprefix_cs string operator
• 13.7.34: The case-sensitive hassuffix_cs string operator
• 13.7.35: The case-sensitive in string operator
• 13.7.36: The case-sensitive startswith string operator
• 14: Special functions
• 14.1: cluster()
• 14.2: Cross-cluster and cross-database queries
• 14.3: database()
• 14.4: external_table()
• 14.5: materialize()
• 14.6: materialized_view()
• 14.7: Query results cache
• 14.8: stored_query_result()
• 14.9: table()
• 14.10: toscalar()
• 15: Tabular operators
• 15.1: Join operator
• 15.1.1: join flavors
• 15.1.1.1: fullouter join
• 15.1.1.2: inner join
• 15.1.1.3: innerunique join
• 15.1.1.4: leftanti join
• 15.1.1.5: leftouter join
• 15.1.1.6: leftsemi join
• 15.1.1.7: rightanti join
• 15.1.1.8: rightouter join
• 15.1.1.9: rightsemi join
• 15.1.2: Broadcast join
• 15.1.3: Cross-cluster join
• 15.1.4: join operator
• 15.1.5: Joining within time window
• 15.2: Render operator
• 15.2.1: visualizations
• 15.2.1.1: Anomaly chart visualization
• 15.2.1.2: Area chart visualization
• 15.2.1.3: Bar chart visualization
• 15.2.1.4: Card visualization
• 15.2.1.5: Column chart visualization
• 15.2.1.6: Ladder chart visualization
• 15.2.1.7: Line chart visualization
• 15.2.1.8: Pie chart visualization
• 15.2.1.9: Pivot chart visualization
• 15.2.1.10: Plotly visualization
• 15.2.1.11: Scatter chart visualization
• 15.2.1.12: Stacked area chart visualization
• 15.2.1.13: Table visualization
• 15.2.1.14: Time chart visualization
• 15.2.1.15: Time pivot visualization
• 15.2.1.16: Treemap visualization
• 15.2.2: render operator
• 15.3: Summarize operator
• 15.3.1: Kusto partition & compose intermediate aggregation results
• 15.3.2: summarize operator
• 15.4: as operator
• 15.5: consume operator
• 15.6: count operator
• 15.7: datatable operator
• 15.8: distinct operator
• 15.9: evaluate plugin operator
• 15.10: extend operator
• 15.11: externaldata operator
• 15.12: facet operator
• 15.13: find operator
• 15.14: fork operator
• 15.15: getschema operator
• 15.16: invoke operator
• 15.17: lookup operator
• 15.18: mv-apply operator
• 15.19: mv-expand operator
• 15.20: parse operator
• 15.21: parse-kv operator
• 15.22: parse-where operator
• 15.23: partition operator
• 15.24: print operator
• 15.25: Project operator
• 15.26: project-away operator
• 15.27: project-keep operator
• 15.28: project-rename operator
• 15.29: project-reorder operator
• 15.30: Queries
• 15.31: range operator
• 15.32: reduce operator
• 15.33: sample operator
• 15.34: sample-distinct operator
• 15.35: scan operator
• 15.36: search operator
• 15.37: serialize operator
• 15.38: Shuffle query
• 15.39: sort operator
• 15.40: take operator
• 15.41: top operator
• 15.42: top-hitters operator
• 15.43: top-nested operator
• 15.44: union operator
• 15.45: where operator
• 16: Time series analysis
• 16.1: Example use cases
• 16.1.1: Analyze time series data
• 16.1.2: Anomaly diagnosis for root cause analysis
• 16.1.3: Time series anomaly detection & forecasting
• 16.2: make-series operator
• 16.3: series_abs()
• 16.4: series_acos()
• 16.5: series_add()
• 16.6: series_atan()
• 16.7: series_cos()
• 16.8: series_cosine_similarity()
• 16.9: series_decompose_anomalies()
• 16.10: series_decompose_forecast()
• 16.11: series_decompose()
• 16.12: series_divide()
• 16.13: series_dot_product()
• 16.14: series_equals()
• 16.15: series_exp()
• 16.16: series_fft()
• 16.17: series_fill_backward()
• 16.18: series_fill_const()
• 16.19: series_fill_forward()
• 16.20: series_fill_linear()
• 16.21: series_fir()
• 16.22: series_fit_2lines_dynamic()
• 16.23: series_fit_2lines()
• 16.24: series_fit_line_dynamic()
• 16.25: series_fit_line()
• 16.26: series_fit_poly()
• 16.27: series_floor()
• 16.28: series_greater_equals()
• 16.29: series_greater()
• 16.30: series_ifft()
• 16.31: series_iir()
• 16.32: series_less_equals()
• 16.33: series_less()
• 16.34: series_log()
• 16.35: series_magnitude()
• 16.36: series_multiply()
• 16.37: series_not_equals()
• 16.38: series_outliers()
• 16.39: series_pearson_correlation()
• 16.40: series_periods_detect()
• 16.41: series_periods_validate()
• 16.42: series_seasonal()
• 16.43: series_sign()
• 16.44: series_sin()
• 16.45: series_stats_dynamic()
• 16.46: series_stats()
• 16.47: series_subtract()
• 16.48: series_sum()
• 16.49: series_tan()
• 16.50: series_asin()
• 16.51: series_ceiling()
• 16.52: series_pow()
• 17: Window functions
• 17.1: next()
• 17.2: prev()
• 17.3: row_cumsum()
• 17.4: row_number()
• 17.5: row_rank_dense()
• 17.6: row_rank_min()
• 17.7: row_window_session()
• 17.8: Window functions
• 18: Add a comment in KQL
• 19: Debug Kusto Query Language inline Python using Visual Studio Code
• 20: Set timeouts
• 21: Syntax conventions for reference documentation
• 22: T-SQL

1 - Aggregation functions

1.1 - Aggregation Functions

An aggregation function performs a calculation on a set of values, and returns a single value. These functions are used in conjunction with the summarize operator. This article lists all available aggregation functions grouped by type. For scalar functions, see Scalar function types.

Binary functions

Dynamic functions

Row selector functions

Statistical functions

1.2 - arg_max() (aggregation function)

Finds a row in the table that maximizes the specified expression. It returns all columns of the input table or specified columns.

Syntax

arg_max (ExprToMaximize, * | ExprToReturn [, …])

Parameters

Returns

Returns a row in the table that maximizes the specified expression ExprToMaximize, and the values of columns specified in ExprToReturn.

Examples

Find maximum latitude

The following example finds the maximum latitude of a storm event in each state.

StormEvents 
| summarize arg_max(BeginLat, BeginLocation) by State

Output

The results table displays only the first 10 rows.

Find last state fatal event

The following example finds the last time an event with a direct death happened in each state, showing all the columns.

The query first filters the events to include only those events where there was at least one direct death. Then the query returns the entire row with the most recent StartTime.

StormEvents
| where DeathsDirect > 0
| summarize arg_max(StartTime, *) by State

Output

The results table displays only the first 10 rows and first three columns.

Handle nulls

The following example demonstrates null handling.

datatable(Fruit: string, Color: string, Version: int) [
    "Apple", "Red", 1,
    "Apple", "Green", int(null),
    "Banana", "Yellow", int(null),
    "Banana", "Green", int(null),
    "Pear", "Brown", 1,
    "Pear", "Green", 2,
]
| summarize arg_max(Version, *) by Fruit

Output

Comparison to max()

The arg_max() function differs from the max() function. The arg_max() function allows you to return other columns along with the maximum value, and max() only returns the maximum value itself.

Examples

arg_max()

Find the last time an event with a direct death happened, showing all the columns in the table.

The query first filters the events to only include events where there was at least one direct death. Then the query returns the entire row with the most recent (maximum) StartTime.

StormEvents
| where DeathsDirect > 0
| summarize arg_max(StartTime, *)

The results table returns all the columns for the row containing the highest value in the expression specified.

| StartTime | EndTime | EpisodeId | EventId | State | EventType | … | |–|–|–|–| | 2007-12-31T15:00:00Z | 2007-12-31T15:00:00 | 12688 | 69700 | UTAH | Avalanche | … |

max()

Find the last time an event with a direct death happened.

The query filters events to only include events where there is at least one direct death, and then returns the maximum value for StartTime.

StormEvents
| where DeathsDirect > 0
| summarize max(StartTime)

The results table returns the maximum value of StartTime, without returning other columns for this record.

Related content

• Aggregation function types at a glance
• arg_min function
• max function
• avg function
• percentile function

1.3 - arg_min() (aggregation function)

Finds a row in the table that minimizes the specified expression. It returns all columns of the input table or specified columns.

Syntax

arg_min (ExprToMinimize, * | ExprToReturn [, …])

Parameters

Null handling

When ExprToMinimize is null for all rows in a table, one row in the table is picked. Otherwise, rows where ExprToMinimize is null are ignored.

Returns

Returns a row in the table that minimizes ExprToMinimize, and the values of columns specified in ExprToReturn. Use or * to return the entire row.

Examples

Find the minimum latitude of a storm event in each state.

StormEvents 
| summarize arg_min(BeginLat, BeginLocation) by State

Output

The results table shown includes only the first 10 rows.

Find the first time an event with a direct death happened in each state, showing all of the columns.

The query first filters the events to only include those where there was at least one direct death. Then the query returns the entire row with the lowest value for StartTime.

StormEvents
| where DeathsDirect > 0
| summarize arg_min(StartTime, *) by State

Output

The results table shown includes only the first 10 rows and first 3 columns.

The following example demonstrates null handling.

datatable(Fruit: string, Color: string, Version: int) [
    "Apple", "Red", 1,
    "Apple", "Green", int(null),
    "Banana", "Yellow", int(null),
    "Banana", "Green", int(null),
    "Pear", "Brown", 1,
    "Pear", "Green", 2,
]
| summarize arg_min(Version, *) by Fruit

Output

Comparison to min()

The arg_min() function differs from the min() function. The arg_min() function allows you to return additional columns along with the minimum value, and min() only returns the minimum value itself.

Examples

arg_min()

Find the first time an event with a direct death happened, showing all the columns in the table.

The query first filters the events to only include those where there was at least one direct death. Then the query returns the entire row with the lowest value for StartTime.

StormEvents
| where DeathsDirect > 0
| summarize arg_min(StartTime, *)

The results table returns all the columns for the row containing the lowest value in the expression specified.

| StartTime | EndTime | EpisodeId | EventId | State | EventType | … | |–|–|–|–| | 2007-01-01T00:00:00Z | 2007-01-22T18:49:00Z | 2408 | 11929 | INDIANA | Flood | … |

min()

Find the first time an event with a direct death happened.

The query filters events to only include those where there is at least one direct death, and then returns the minimum value for StartTime.

StormEvents
| where DeathsDirect > 0
| summarize min(StartTime)

The results table returns the lowest value in the specific column only.

Related content

• min function
• max function
• avg function
• percentile function
• min-of function

1.4 - avg() (aggregation function)

Calculates the average (arithmetic mean) of expr across the group.

Syntax

avg(expr)

Parameters

Returns

Returns the average value of expr across the group.

Example

The following example returns the average number of damaged crops per state.

StormEvents
| summarize AvgDamageToCrops = avg(DamageCrops) by State

The results table shown includes only the first 10 rows.

Related content

• Aggregation function types at a glance
• min() (aggregation function)
• max() (aggregation function)
• percentile(), percentiles() (aggregation function)

1.5 - avgif() (aggregation function)

Calculates the average of expr in records for which predicate evaluates to true.

Syntax

avgif (expr, predicate)

Parameters

Returns

Returns the average value of expr in records where predicate evaluates to true.

Example

The following example calculates the average damage by state in cases where there was any damage.

StormEvents
| summarize Averagedamage=tolong(avg( DamageCrops)),AverageWhenDamage=tolong(avgif(DamageCrops,DamageCrops >0)) by State

Output

The results table shown includes only the first 10 rows.

Related content

• Aggregation function types at a glance
• avg() (aggregation function)
• minif() (aggregation function)
• maxif() (aggregation function)

1.6 - binary_all_and() (aggregation function)

Accumulates values using the binary AND operation for each summarization group, or in total if a group isn’t specified.

Syntax

binary_all_and (expr)

Parameters

Returns

Returns an aggregated value using the binary AND operation over records for each summarization group, or in total if a group isn’t specified.

Example

The following example produces CAFEF00D using binary AND operations:

datatable(num:long)
[
  0xFFFFFFFF, 
  0xFFFFF00F,
  0xCFFFFFFD,
  0xFAFEFFFF,
]
| summarize result = toupper(tohex(binary_all_and(num)))

Output

Related content

• Aggregation function types at a glance
• binary_all_or() (aggregation function)
• binary_all_xor() (aggregation function)
• binary_and()

1.7 - binary_all_or() (aggregation function)

Accumulates values using the binary OR operation for each summarization group, or in total if a group isn’t specified.

Syntax

binary_all_or (expr)

Parameters

Returns

Returns an aggregated value using the binary OR operation over records for each summarization group, or in total if a group isn’t specified.

Example

The following example produces CAFEF00D using binary OR operations:

datatable(num:long)
[
  0x88888008,
  0x42000000,
  0x00767000,
  0x00000005, 
]
| summarize result = toupper(tohex(binary_all_or(num)))

Output

Related content

• Aggregation function types at a glance
• binary_all_or() (aggregation function)
• binary_all_xor() (aggregation function)
• binary_and()
• binary_or()

1.8 - binary_all_xor() (aggregation function)

Accumulates values using the binary XOR operation for each summarization group, or in total if a group is not specified.

Syntax

binary_all_xor (expr)

Parameters

Returns

Returns a value that is aggregated using the binary XOR operation over records for each summarization group, or in total if a group isn’t specified.

Example

The following example produces CAFEF00D using binary XOR operations:

datatable(num:long)
[
  0x44404440,
  0x1E1E1E1E,
  0x90ABBA09,
  0x000B105A,
]
| summarize result = toupper(tohex(binary_all_xor(num)))

Output

Related content

• Aggregation function types at a glance
• binary_all_or() (aggregation function)
• binary_all_and() (aggregation function)
• binary_xor()

1.9 - buildschema() (aggregation function)

Builds the minimal schema that admits all values of DynamicExpr.

Syntax

buildschema (DynamicExpr)

Parameters

Returns

Returns the minimal schema that admits all values of DynamicExpr.

Example

The following example builds a schema based on:

• {"x":1, "y":3.5}
• {"x":"somevalue", "z":[1, 2, 3]}
• {"y":{"w":"zzz"}, "t":["aa", "bb"], "z":["foo"]}

datatable(value: dynamic) [
    dynamic({"x":1, "y":3.5}),
    dynamic({"x":"somevalue", "z":[1, 2, 3]}),
    dynamic({"y":{"w":"zzz"}, "t":["aa", "bb"], "z":["foo"]})
]
| summarize buildschema(value)

Results

Schema breakdown

In the resulting schema:

• The root object is a container with four properties named x, y, z, and t.
• Property x is either type long or type string.
• Property y is either type double or another container with a property w of type string.
• Property z is an array, indicated by the indexer keyword, where each item can be either type long or type string.
• Property t is an array, indicated by the indexer keyword, where each item is a string.
• Every property is implicitly optional, and any array might be empty.

Related content

• Best practices for schema management
• getschema operator
• infer_storage_schema plugin

1.10 - count_distinct() (aggregation function) - (preview)

Counts unique values specified by the scalar expression per summary group, or the total number of unique values if the summary group is omitted.

If you only need an estimation of unique values count, we recommend using the less resource-consuming dcount aggregation function.

To count only records for which a predicate returns true, use the count_distinctif aggregation function.

Syntax

count_distinct (expr)

Parameters

Returns

Long integer value indicating the number of unique values of expr per summary group.

Example

The following example shows how many types of storm events happened in each state.

Function performance can be degraded when operating on multiple data sources from different clusters.

StormEvents
| summarize UniqueEvents=count_distinct(EventType) by State
| top 5 by UniqueEvents

Output

Related content

• Aggregation function types at a glance
• count_distinctif() (aggregation function)
• count() (aggregation function)
• countof()
• countif() (aggregation function)

1.11 - count_distinctif() (aggregation function) - (preview)

Conditionally counts unique values specified by the scalar expression per summary group, or the total number of unique values if the summary group is omitted. Only records for which predicate evaluates to true are counted.

If you only need an estimation of unique values count, we recommend using the less resource-consuming dcountif aggregation function.

Syntax

count_distinctif (expr, predicate)

Parameters

Returns

Integer value indicating the number of unique values of expr per summary group, for all records for which the predicate evaluates to true.

Example

The following example shows how many types of death-causing storm events happened in each state. Only storm events with a nonzero count of deaths are counted.

StormEvents
| summarize UniqueFatalEvents=count_distinctif(EventType,(DeathsDirect + DeathsIndirect)>0) by State
| where UniqueFatalEvents > 0
| top 5 by UniqueFatalEvents

Output

Related content

• Aggregation function types at a glance
• count_distinct() (aggregation function)
• countif() (aggregation function)
• dcountif() (aggregation function)

1.12 - count() (aggregation function)

Counts the number of records per summarization group, or total if summarization is done without grouping.

To only count records for which a predicate returns true, use countif().

Syntax

count()

Returns

Returns a count of the records per summarization group, or in total if summarization is done without grouping.

Example

The following example returns a count of events in states:

StormEvents
| summarize Count=count() by State

Output

Related content

• Aggregation function types at a glance
• countof()
• countif() (aggregation function)
• count_distinct() (aggregation function)
• bin_at()

1.13 - countif() (aggregation function)

Counts the rows in which predicate evaluates to true.

Syntax

countif (predicate)

Parameters

Returns

Returns a count of rows in which predicate evaluates to true.

Examples

Count storms by state

This example shows the number of storms with damage to crops by state.

StormEvents
| summarize TotalCount=count(),TotalWithDamage=countif(DamageCrops >0) by State

The results table shown includes only the first 10 rows.

Count based on string length

This example shows the number of names with more than four letters.

let T = datatable(name:string, day_of_birth:long)
[
   "John", 9,
   "Paul", 18,
   "George", 25,
   "Ringo", 7
];
T
| summarize countif(strlen(name) > 4)

Output

Related content

• Aggregation function types at a glance
• count_distinctif() (aggregation function) - (preview)
• dcountif() (aggregation function)
• count()

1.14 - dcount() (aggregation function)

Calculates an estimate of the number of distinct values that are taken by a scalar expression in the summary group.

Syntax

dcount (expr[, accuracy])

Parameters

Returns

Returns an estimate of the number of distinct values of expr in the group.

Example

This example shows how many types of storm events happened in each state.

StormEvents
| summarize DifferentEvents=dcount(EventType) by State
| order by DifferentEvents

The results table shown includes only the first 10 rows.

Estimation accuracy

Related content

• Aggregation function types at a glance
• dcountif() (aggregation function)
• count()
• count_distinct() (aggregation function)

1.15 - dcountif() (aggregation function)

Estimates the number of distinct values of expr for rows in which predicate evaluates to true.

Syntax

dcountif (expr, predicate, [, accuracy])

Parameters

Returns

Returns an estimate of the number of distinct values of expr for rows in which predicate evaluates to true.

Example

This example shows how many types of fatal storm events happened in each state.

StormEvents
| summarize DifferentFatalEvents=dcountif(EventType,(DeathsDirect + DeathsIndirect)>0) by State
| where DifferentFatalEvents > 0
| order by DifferentFatalEvents 

The results table shown includes only the first 10 rows.

Estimation accuracy

Related content

• Aggregation function types at a glance
• dcount() (aggregation function)
• countif() (aggregation function)
• count_distinctif() (aggregation function)

1.16 - hll_if() (aggregation function)

Calculates the intermediate results of dcount in records for which the predicate evaluates to true.

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

Syntax

hll_if (expr, predicate [, accuracy])

Parameters

Returns

Returns the intermediate results of distinct count of Expr for which Predicate evaluates to true.

Examples

The following query results in the number of unique flood event sources in Iowa and Kansas. It uses the hll_if() function to show only flood events.

StormEvents
| where State in ("IOWA", "KANSAS")
| summarize hll_flood = hll_if(Source, EventType == "Flood") by State
| project State, SourcesOfFloodEvents = dcount_hll(hll_flood)

Output

Estimation accuracy

Related content

• Aggregation function types at a glance
• Using hll() and tdigest()
• hll() (aggregation function)
• hll_merge() (aggregation function)

1.17 - hll_merge() (aggregation function)

Merges HLL results across the group into a single HLL value.

For more information, see the underlying algorithm (HyperLogLog) and estimation accuracy.

Syntax

hll_merge (hll)

Parameters

Returns

The function returns the merged HLL values of hll across the group.

Example

The following example shows HLL results across a group merged into a single HLL value.

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

Output

The results show only the first five results in the array.

Estimation accuracy

Related content

• Aggregation function types at a glance
• Using hll() and tdigest()
• hll() (aggregation function)
• hll_if() (aggregation function)

1.18 - hll() (aggregation function)

The hll() function is a way to estimate the number of unique values in a set of values. It does so by calculating intermediate results for aggregation within the summarize operator for a group of data using the dcount function.

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

Syntax

hll (expr [, accuracy])

Parameters

Returns

Returns the intermediate results of distinct count of expr across the group.

Example

In the following example, the hll() function is used to estimate the number of unique values of the DamageProperty column within each 10-minute time bin of the StartTime column.

StormEvents
| summarize hll(DamageProperty) by bin(StartTime,10m)

Output

The results table shown includes only the first 10 rows.

Estimation accuracy

Related content

• Aggregation function types at a glance
• Using hll() and tdigest()
• hll_if() (aggregation function)
• hll_merge() (aggregation function)

1.19 - make_bag_if() (aggregation function)

Creates a dynamic JSON property bag (dictionary) of expr values in records for which predicate evaluates to true.

Syntax

make_bag_if(expr, predicate [, maxSize])

Parameters

Returns

Returns a dynamic JSON property bag (dictionary) of expr values in records for which predicate evaluates to true. Nondictionary values are skipped. If a key appears in more than one row, an arbitrary value, out of the possible values for this key, are selected.

Example

The following example shows a packed JSON property bag.

let T = datatable(prop:string, value:string, predicate:bool)
[
    "prop01", "val_a", true,
    "prop02", "val_b", false,
    "prop03", "val_c", true
];
T
| extend p = bag_pack(prop, value)
| summarize dict=make_bag_if(p, predicate)

Output

Use bag_unpack() plugin for transforming the bag keys in the make_bag_if() output into columns.

let T = datatable(prop:string, value:string, predicate:bool)
[
    "prop01", "val_a", true,
    "prop02", "val_b", false,
    "prop03", "val_c", true
];
T
| extend p = bag_pack(prop, value)
| summarize bag=make_bag_if(p, predicate)
| evaluate bag_unpack(bag)

Output

Related content

• Aggregation function types at a glance
• make_bag() (aggregation function)
• bag_unpack()
• bag_pack()
• make_list_if() (aggregation function)
• parse_json()

1.20 - make_bag() (aggregation function)

Creates a dynamic JSON property bag (dictionary) of all the values of expr in the group.

Syntax

make_bag (expr [, maxSize])

Parameters

Returns

Returns a dynamic JSON property bag (dictionary) of all the values of Expr in the group, which are property bags. Nondictionary values are skipped. If a key appears in more than one row, an arbitrary value, out of the possible values for this key, is selected.

Example

The following example shows a packed JSON property bag.

let T = datatable(prop:string, value:string)
[
    "prop01", "val_a",
    "prop02", "val_b",
    "prop03", "val_c",
];
T
| extend p = bag_pack(prop, value)
| summarize dict=make_bag(p)

Output

Use the bag_unpack() plugin for transforming the bag keys in the make_bag() output into columns.

let T = datatable(prop:string, value:string)
[
    "prop01", "val_a",
    "prop02", "val_b",
    "prop03", "val_c",
];
T
| extend p = bag_pack(prop, value)
| summarize bag=make_bag(p)
| evaluate bag_unpack(bag)

Output

Related content

• Aggregation function types at a glance
• make_bag_if() (aggregation function)
• bag_unpack()
• bag_pack()
• make_list() (aggregation function)
• parse_json()

1.21 - make_list_if() (aggregation function)

Creates a dynamic array of expr values in the group for which predicate evaluates to true.

Syntax

make_list_if(expr, predicate [, maxSize])

Parameters

Returns

Returns a dynamic array of expr values in the group for which predicate evaluates to true. If the input to the summarize operator isn’t sorted, the order of elements in the resulting array is undefined. If the input to the summarize operator is sorted, the order of elements in the resulting array tracks that of the input.

Example

The following example shows a list of names with more than 4 letters.

let T = datatable(name:string, day_of_birth:long)
[
   "John", 9,
   "Paul", 18,
   "George", 25,
   "Ringo", 7
];
T
| summarize make_list_if(name, strlen(name) > 4)

Output

Related content

• Aggregation function types at a glance
• make_list
• make_bag_if() (aggregation function)

1.22 - make_list_with_nulls() (aggregation function)

Creates a dynamic array of all the values of expr in the group, including null values.

Syntax

make_list_with_nulls(expr)

Parameters

Returns

Returns a dynamic JSON object (array) of all the values of expr in the group, including null values. If the input to the summarize operator isn’t sorted, the order of elements in the resulting array is undefined. If the input to the summarize operator is sorted, the order of elements in the resulting array tracks that of the input.

Example

The following example shows null values in the results.

let shapes = datatable (name:string , sideCount: int)
[
    "triangle", int(null),
    "square", 4,
    "rectangle", 4,
    "pentagon", 5,
    "hexagon", 6,
    "heptagon", 7,
    "octagon", 8,
    "nonagon", 9,
    "decagon", 10
];
shapes
| summarize mylist = make_list_with_nulls(sideCount)

Output

1.23 - make_list() (aggregation function)

Creates a dynamic array of all the values of expr in the group.

Syntax

make_list(expr [, maxSize])

Parameters

Returns

Returns a dynamic array of all the values of expr in the group. If the input to the summarize operator isn’t sorted, the order of elements in the resulting array is undefined. If the input to the summarize operator is sorted, the order of elements in the resulting array tracks that of the input.

Examples

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

One column

The following example uses the datatable, shapes, to return a list of shapes in a single column.

let shapes = datatable (name: string, sideCount: int)
[
    "triangle", 3,
    "square", 4,
    "rectangle", 4,
    "pentagon", 5,
    "hexagon", 6,
    "heptagon", 7,
    "octagon", 8,
    "nonagon", 9,
    "decagon", 10
];
shapes
| summarize mylist = make_list(name)

Output

Using the ‘by’ clause

The following example uses the make_list function and the by clause to create two lists of objects grouped by whether they have an even or odd number of sides.

let shapes = datatable (name: string, sideCount: int)
[
    "triangle", 3,
    "square", 4,
    "rectangle", 4,
    "pentagon", 5,
    "hexagon", 6,
    "heptagon", 7,
    "octagon", 8,
    "nonagon", 9,
    "decagon", 10
];
shapes
| summarize mylist = make_list(name) by isEvenSideCount = sideCount % 2 == 0

Output

Packing a dynamic object

The following examples show how to pack a dynamic object in a column before making it a list. It returns a column with a boolean table isEvenSideCount indicating whether the side count is even or odd and a mylist column that contains lists of packed bags int each category.

let shapes = datatable (name: string, sideCount: int)
[
    "triangle", 3,
    "square", 4,
    "rectangle", 4,
    "pentagon", 5,
    "hexagon", 6,
    "heptagon", 7,
    "octagon", 8,
    "nonagon", 9,
    "decagon", 10
];
shapes
| extend d = bag_pack("name", name, "sideCount", sideCount)
| summarize mylist = make_list(d) by isEvenSideCount = sideCount % 2 == 0

Output

Related content

• Aggregation function types at a glance
• make_list_if
• make_bag() (aggregation function)

1.24 - make_set_if() (aggregation function)

Creates a dynamic array of the set of distinct values that expr takes in records for which predicate evaluates to true.

Syntax

make_set_if(expr, predicate [, maxSize])

Parameters

Returns

Returns a dynamic array of the set of distinct values that expr takes in records for which predicate evaluates to true. The array’s sort order is undefined.

Example

The following example shows a list of names with more than four letters.

let T = datatable(name:string, day_of_birth:long)
[
   "John", 9,
   "Paul", 18,
   "George", 25,
   "Ringo", 7
];
T
| summarize make_set_if(name, strlen(name) > 4)

Output

Related content

• Aggregation function types at a glance
• make_set() (aggregation function)
• make_list_if() (aggregation function)
• make_bag_if() (aggregation function)
• mv-expand

1.25 - make_set() (aggregation function)

Creates a dynamic array of the set of distinct values that expr takes in the group.

Syntax

make_set(expr [, maxSize])

Parameters

Returns

Returns a dynamic array of the set of distinct values that expr takes in the group. The array’s sort order is undefined.

Example

Set from a scalar column

The following example shows the set of states grouped with the same amount of crop damage.

StormEvents 
| summarize states=make_set(State) by DamageCrops

The results table shown includes only the first 10 rows.

Set from array column

The following example shows the set of elements in an array.

datatable (Val: int, Arr1: dynamic)
[
    1, dynamic(['A1', 'A2', 'A3']), 
    5, dynamic(['A2', 'C1']),
    7, dynamic(['C2', 'A3']),
    5, dynamic(['C2', 'A1'])
] 
| summarize Val_set=make_set(Val), Arr1_set=make_set(Arr1)

Related content

• Aggregation function types at a glance
• make_set_if
• make_list
• make_bag() (aggregation function)
• mv-expand

1.26 - max() (aggregation function)

Finds the maximum value of the expression in the table.

Syntax

max(expr)

Parameters

Returns

Returns the value in the table that maximizes the specified expression.

Example

The following example returns the last record in a table by querying the maximum value for StartTime.

StormEvents
| summarize LatestEvent=max(StartTime)

Output

Related content

• Aggregation function types at a glance
• arg_max
• min function
• avg function
• percentile function

1.27 - maxif() (aggregation function)

Calculates the maximum value of expr in records for which predicate evaluates to true.

See also - max() function, which returns the maximum value across the group without predicate expression.

Syntax

maxif(expr,predicate)

Parameters

Returns

Returns the maximum value of expr in records for which predicate evaluates to true.

Example

This example shows the maximum damage for events with no casualties.

StormEvents
| extend Damage=DamageCrops + DamageProperty, Deaths=DeathsDirect + DeathsIndirect
| summarize MaxDamageNoCasualties=maxif(Damage, Deaths == 0) by State

Output

The results table shown includes only the first 10 rows.

Related content

• Aggregation function types at a glance
• minif() (aggregation function)
• max_of()
• arg_max() (aggregation function)

1.28 - min() (aggregation function)

Finds the minimum value of the expression in the table.

Syntax

min (expr)

Parameters

Returns

Returns the minimum value of expr across the table.

Example

This example returns the first record in a table.

StormEvents
| summarize FirstEvent=min(StartTime)

Output

Related content

• arg_min function
• max function
• avg function
• percentile function
• min-of function

1.29 - minif() (aggregation function)

Returns the minimum of Expr in records for which Predicate evaluates to true.

• Can be used only in context of aggregation inside summarize

See also - min() function, which returns the minimum value across the group without predicate expression.

Syntax

minif (Expr,Predicate)

Parameters

Returns

The minimum value of Expr in records for which Predicate evaluates to true.

Example

This example shows the minimum damage for events with casualties (Except 0)

StormEvents
| extend Damage=DamageCrops+DamageProperty, Deaths=DeathsDirect+DeathsIndirect
| summarize MinDamageWithCasualties=minif(Damage,(Deaths >0) and (Damage >0)) by State 
| where MinDamageWithCasualties >0 and isnotnull(MinDamageWithCasualties)

Output

The results table shown includes only the first 10 rows.

Related content

• Aggregation function types at a glance
• maxif() (aggregation function)
• min_of()
• arg_max() (aggregation function)

1.30 - percentile(), percentiles()

The percentile() function calculates an estimate for the specified nearest-rank percentile of the population defined by expr. The accuracy depends on the density of population in the region of the percentile.

percentiles() works similarly to percentile(). However, percentiles() can calculate multiple percentile values at once, which is more efficient than calculating each percentile value separately.

To calculate weighted percentiles, see percentilesw().

Syntax

percentile(expr, percentile)

percentiles(expr, percentiles)

Parameters

Returns

Returns a table with the estimates for expr of the specified percentiles in the group, each in a separate column.

Examples

Calculate single percentile

The following example shows the value of DamageProperty being larger than 95% of the sample set and smaller than 5% of the sample set.

StormEvents | summarize percentile(DamageProperty, 95) by State

Output

The results table shown includes only the first 10 rows.

Calculate multiple percentiles

The following example shows the value of DamageProperty simultaneously calculated using 5, 50 (median) and 95.

StormEvents | summarize percentiles(DamageProperty, 5, 50, 95) by State

Output

The results table shown includes only the first 10 rows.

Return percentiles as an array

Instead of returning the values in individual columns, use the percentiles_array() function to return the percentiles in a single column of dynamic array type.

Syntax

percentiles_array(expr, percentiles)

Parameters

Returns

Returns an estimate for expr of the specified percentiles in the group as a single column of dynamic array type.

Examples

Comma-separated percentiles

Multiple percentiles can be obtained as an array in a single dynamic column, instead of in multiple columns as with percentiles().

TransformedSensorsData
| summarize percentiles_array(Value, 5, 25, 50, 75, 95), avg(Value) by SensorName

Output

The results table displays only the first 10 rows.

Dynamic array of percentiles

Percentiles for percentiles_array can be specified in a dynamic array of integer or floating-point numbers. The array must be constant but doesn’t have to be literal.

TransformedSensorsData
| summarize percentiles_array(Value, dynamic([5, 25, 50, 75, 95])), avg(Value) by SensorName

Output

The results table displays only the first 10 rows.

Nearest-rank percentile

P-th percentile (0 < P <= 100) of a list of ordered values, sorted in ascending order, is the smallest value in the list. The P percent of the data is less or equal to P-th percentile value (from Wikipedia article on percentiles).

Define 0-th percentiles to be the smallest member of the population.

Estimation error in percentiles

The percentiles aggregate provides an approximate value using T-Digest.

Related content

• Aggregation function types at a glance
• percentilew(), percentilesw() (aggregation function)
• avg function

1.31 - percentilew(), percentilesw()

The percentilew() function calculates a weighted estimate for the specified nearest-rank percentile of the population defined by expr. percentilesw() works similarly to percentilew(). However, percentilesw() can calculate multiple weighted percentile values at once, which is more efficient than calculating each weighted percentile value separately.

Weighted percentiles calculate percentiles in a dataset by giving each value in the input dataset a weight. In this method, each value is considered to be repeated a number of times equal to its weight, which is then used to calculate the percentile. By giving more importance to certain values, weighted percentiles provide a way to calculate percentiles in a “weighted” manner.

To calculate unweighted percentiles, see percentiles().

Syntax

percentilew(expr, weightExpr, percentile)

percentilesw(expr, weightExpr, percentiles)

Parameters

Returns

Returns a table with the estimates for expr of the specified percentiles in the group, each in a separate column.

Examples

Calculate weighted percentiles

Assume you repetitively measure the time (Duration) it takes an action to complete. Instead of recording every value of the measurement, you record each value of Duration, rounded to 100 msec, and how many times the rounded value appeared (BucketSize).

Use summarize percentilesw(Duration, BucketSize, ...) to calculate the given percentiles in a “weighted” way. Treat each value of Duration as if it was repeated BucketSize times in the input, without actually needing to materialize those records.

The following example shows weighted percentiles. Using the following set of latency values in milliseconds: { 1, 1, 2, 2, 2, 5, 7, 7, 12, 12, 15, 15, 15, 18, 21, 22, 26, 35 }.

To reduce bandwidth and storage, do pre-aggregation to the following buckets: { 10, 20, 30, 40, 50, 100 }. Count the number of events in each bucket to produce the following table:

let latencyTable = datatable (ReqCount:long, LatencyBucket:long) 
[ 
    8, 10, 
    6, 20, 
    3, 30, 
    1, 40 
];
latencyTable

The table displays:

• Eight events in the 10-ms bucket (corresponding to subset { 1, 1, 2, 2, 2, 5, 7, 7 })
• Six events in the 20-ms bucket (corresponding to subset { 12, 12, 15, 15, 15, 18 })
• Three events in the 30-ms bucket (corresponding to subset { 21, 22, 26 })
• One event in the 40-ms bucket (corresponding to subset { 35 })

At this point, the original data is no longer available. Only the number of events in each bucket. To compute percentiles from this data, use the percentilesw() function. For the 50, 75, and 99.9 percentiles, use the following query:

let latencyTable = datatable (ReqCount:long, LatencyBucket:long) 
[ 
    8, 10, 
    6, 20, 
    3, 30, 
    1, 40 
];
latencyTable
| summarize percentilesw(LatencyBucket, ReqCount, 50, 75, 99.9)

Output

Return percentiles as an array

Instead of returning the values in individual columns, use the percentilesw_array() function to return the percentiles in a single column of dynamic array type.

Syntax

percentilesw_array(expr, weightExpr, percentiles)

Parameters

Returns

Returns an estimate for expr of the specified percentiles in the group as a single column of dynamic array type.

Examples

Comma-separated percentiles

let latencyTable = datatable (ReqCount:long, LatencyBucket:long) 
[ 
    8, 10, 
    6, 20, 
    3, 30, 
    1, 40 
];
latencyTable
| summarize percentilesw_array(LatencyBucket, ReqCount, 50, 75, 99.9)

Output

Dynamic array of percentiles

let latencyTable = datatable (ReqCount:long, LatencyBucket:long) 
[ 
    8, 10, 
    6, 20, 
    3, 30, 
    1, 40 
];
latencyTable
| summarize percentilesw_array(LatencyBucket, ReqCount, dynamic([50, 75, 99.9]))

Output

Related content

• Aggregation function types at a glance
• percentile(), percentiles() (aggregation function)
• avg function

1.32 - stdev() (aggregation function)

Calculates the standard deviation of expr across the group, using Bessel’s correction for a small dataset that is considered a sample.

For a large dataset that is representative of the population, use stdevp() (aggregation function).

Formula

This function uses the following formula.

Syntax

stdev(expr)

Parameters

Returns

Returns the standard deviation value of expr across the group.

Example

The following example shows the standard deviation for the group.

range x from 1 to 5 step 1
| summarize make_list(x), stdev(x)

Output

1.33 - stdevif() (aggregation function)

Calculates the standard deviation of expr in records for which predicate evaluates to true.

Syntax

stdevif(expr,predicate)

Parameters

Returns

Returns the standard deviation value of expr in records for which predicate evaluates to true.

Example

The following example shows the standard deviation in a range of 1 to 100.

range x from 1 to 100 step 1
| summarize stdevif(x, x % 2 == 0)

Output

1.34 - stdevp() (aggregation function)

Calculates the standard deviation of expr across the group, considering the group as a population for a large dataset that is representative of the population.

For a small dataset that is a sample, use stdev() (aggregation function).

Formula

This function uses the following formula.

Syntax

stdevp(expr)

Parameters

Returns

Returns the standard deviation value of expr across the group.

Example

range x from 1 to 5 step 1
| summarize make_list(x), stdevp(x)

Output

1.35 - sum() (aggregation function)

Calculates the sum of expr across the group.

Syntax

sum(expr)

Parameters

Returns

Returns the sum value of expr across the group.

Example

This example returns the total value of crop and property damages by state, and sorted in descending value.

StormEvents 
| summarize EventCount=count(), TotalDamages = sum(DamageCrops+DamageProperty) by State 
| sort by TotalDamages

Output

The results table shown includes only the first 10 rows.

| State | Eventcount | TotalDamages | | —- | — | | CALIFORNIA | 898 | 2801954600 | | GEORGIA | 1983 | 1190448750 | | MISSOURI | 2016 | 1096887450 | | OKLAHOMA | 1716 | 916557300 | | MISSISSIPPI | 1218 | 802890160 | | KANSAS | 3166 | 738830000 | | TEXAS | 4701 | 572086700 | | OHIO | 1233 | 417989500 | | FLORIDA | 1042 | 379455260 | | NORTH DAKOTA | 905 | 342460100 | | … | … | … |

1.36 - sumif() (aggregation function)

Calculates the sum of expr in records for which predicate evaluates to true.

You can also use the sum() function, which sums rows without predicate expression.

Syntax

sumif(expr,predicate)

Parameters

Returns

Returns the sum of expr for which predicate evaluates to true.

Example showing the sum of damages based on no casualty count

This example shows the sum total damage for storms without casualties.

StormEvents
| summarize DamageNoCasualties=sumif((DamageCrops+DamageProperty),(DeathsDirect+DeathsIndirect)==0) by State

Output

The results table shown includes only the first 10 rows.

Example showing the sum of birth dates

This example shows the sum of the birth dates for all names that have more than 4 letters.

let T = datatable(name:string, day_of_birth:long)
[
   "John", 9,
   "Paul", 18,
   "George", 25,
   "Ringo", 7
];
T
| summarize sumif(day_of_birth, strlen(name) > 4)

Output

1.37 - take_any() (aggregation function)

Arbitrarily chooses one record for each group in a summarize operator, and returns the value of one or more expressions over each such record.

Syntax

take_any(expr_1 [, expr_2 …])

take_any(*)

Parameters

Returns

The take_any aggregation function returns the values of the expressions calculated for each of the records selected Indeterministically from each group of the summarize operator.

If the * argument is provided, the function behaves as if the expressions are all columns of the input to the summarize operator barring the group-by columns, if any.

Remarks

This function is useful when you want to get a sample value of one or more columns per value of the compound group key.

When the function is provided with a single column reference, it will attempt to return a non-null/non-empty value, if such value is present.

As a result of the indeterministic nature of this function, using this function multiple times in a single application of the summarize operator isn’t equivalent to using this function a single time with multiple expressions. The former may have each application select a different record, while the latter guarantees that all values are calculated over a single record (per distinct group).

Examples

Show indeterministic State:

StormEvents
| summarize take_any(State)

Output

Show all the details for a random record:

StormEvents
| project StartTime, EpisodeId, State, EventType
| summarize take_any(*)

Output

Show all the details of a random record for each State starting with ‘A’:

StormEvents
| where State startswith "A"
| project StartTime, EpisodeId, State, EventType
| summarize take_any(*) by State

Output

Related content

• take_anyif function
• arg_max function
• arg_min function

1.38 - take_anyif() (aggregation function)

Arbitrarily selects one record for each group in a summarize operator in records for which the predicate is ’true’. The function returns the value of an expression over each such record.

This function is useful when you want to get a sample value of one column per value of the compound group key, subject to some predicate that is true. If such a value is present, the function attempts to return a non-null/non-empty value.

Syntax

take_anyif( expr, predicate )

Parameters

Returns

The take_anyif aggregation function returns the value of the expression calculated for each of the records randomly selected from each group of the summarize operator. Only records for which predicate returns ’true’ may be selected. If the predicate doesn’t return ’true’, a null value is produced.

Examples

Pick a random EventType from Storm events, where event description has a key phrase.

StormEvents
| summarize take_anyif(EventType, EventNarrative has 'strong wind')

Output

Related content

• take_any function
• arg_max function
• arg_min function

1.39 - tdigest_merge() (aggregation functions)

Merges tdigest results across the group.

For more information about the underlying algorithm (T-Digest) and the estimated error, see estimation error in percentiles.

Syntax

tdigest_merge(expr)

Parameters

Returns

Returns the merged tdigest values of expr across the group.

Example

StormEvents
| summarize PreAggDamageProperty=tdigest(DamageProperty) by State
| summarize tdigest_merge(PreAggDamageProperty)

Output

1.40 - tdigest() (aggregation function)

Calculates the intermediate results of percentiles() across the group.

For more information, see the underlying algorithm (T-Digest) and the estimated error.

Syntax

tdigest(expr [, weight])

Parameters

Returns

The Intermediate results of weighted percentiles of *expr* across the group.

Examples

Results per state

This example shows the results of the tdigest percentiles sorted by state.

StormEvents
| summarize tdigest(DamageProperty) by State

The results table shown includes only the first 10 rows.

Convert pre-existing centroids

The following example shows how one can convert pre-existing T-Digest centroids for long-term storage. The V column holds the value of each centroid, and the W column is its weight (relative count). The tdigest() aggregate function is then applied to convert the data in table DT into the internal representation, and percentile_tdigest() is used to demonstrate how ot find the 50-tile value.

let DT=datatable(V:real, W:long) [
    1.0, 1,
    2.0, 2
];
DT
| summarize TD=tdigest(V, W)
| project P50=percentile_tdigest(TD, 50)

1.41 - variance() (aggregation function)

Calculates the variance of expr across the group, considering the group as a sample.

The following formula is used:

Syntax

variance(expr)

Parameters

Returns

Returns the variance value of expr across the group.

Example

range x from 1 to 5 step 1
| summarize make_list(x), variance(x) 

Output

1.42 - varianceif() (aggregation function)

Calculates the variance of expr in records for which predicate evaluates to true.

Syntax

varianceif(expr, predicate)

Parameters

Returns

Returns the variance value of expr in records for which predicate evaluates to true.

Example

range x from 1 to 100 step 1
| summarize varianceif(x, x%2 == 0)

Output

1.43 - variancep() (aggregation function)

Calculates the variance of expr across the group, considering the group as a population.

The following formula is used:

Syntax

variancep(expr)

Parameters

Returns

Returns the variance value of expr across the group.

Example

range x from 1 to 5 step 1
| summarize make_list(x), variancep(x) 

Output

2 - Best practices for KQL queries

2.1 - Best practices for Kusto Query Language queries

Here are several best practices to follow to make your query run faster.

In short

Reduce the amount of data being processed

A query’s performance depends directly on the amount of data it needs to process. The less data is processed, the quicker the query (and the fewer resources it consumes). Therefore, the most important best-practice is to structure the query in such a way that reduces the amount of data being processed.

In order of importance:

• Only reference tables whose data is needed by the query. For example, when using the union operator with wildcard table references, it’s better from a performance point-of-view to only reference a handful of tables, instead of using a wildcard (*) to reference all tables and then filter data out using a predicate on the source table name.

• Take advantage of a table’s data scope if the query is relevant only for a specific scope. The table() function provides an efficient way to eliminate data by scoping it according to the caching policy (the DataScope parameter).

• Apply the where query operator immediately following table references.

• When using the where query operator, the order in which you place the predicates, whether you use a single where operator, or multiple consecutive where operators, can have a significant effect on the query performance.

• Apply predicates that act upon datetime table columns first. Kusto includes an efficient index on such columns, often completely eliminating whole data shards without needing to access those shards.

• Then apply predicates that act upon string and dynamic columns, especially such predicates that apply at the term-level. Order the predicates by the selectivity. For example, searching for a user ID when there are millions of users is highly selective and usually involves a term search, for which the index is very efficient.

• Then apply predicates that are selective and are based on numeric columns.

• Last, for queries that scan a table column’s data (for example, for predicates such as contains "@!@!", that have no terms and don’t benefit from indexing), order the predicates such that the ones that scan columns with less data are first. Doing so reduces the need to decompress and scan large columns.

Avoid using redundant qualified references

Reference entities such as tables and materialized views by name.

For example, the table T can be referenced as simply T (the unqualified name), or by using a database qualifier (for example, database("DB").T when the table is in a database called DB), or by using a fully qualified name (for example, cluster("<serviceURL>").database("DB").T).

For example, the table T can be referenced as simply T (the unqualified name), or by using a database qualifier (for example, database("DB").T when the table is in a database called DB), or by using a fully qualified name (for example, cluster("X.Y.kusto.windows.net").database("DB").T).

It’s a best practice to avoid using name qualifications when they’re redundant, for the following reasons:

1. Unqualified names are easier to identify (for a human reader) as belonging to the database-in-scope.

2. Referencing database-in-scope entities is always at least as fast, and in some cases much faster, then entities that belong to other databases. This is especially true when those databases are in a different cluster. This is especially true when those databases are in a different Eventhouse. Avoiding qualified names helps the reader to do the right thing.

2.2 - Named expressions

This article discusses how to optimize repeat use of named expressions in a query.

In Kusto Query Language, you can bind names to complex expressions in several different ways:

• In a let statement
• In the as operator
• In the formal parameters list of user-defined functions

When you reference these named expressions in a query, the following steps occur:

1. The calculation within the named expression is evaluated. This calculation produces either a scalar or tabular value.
2. The named expression is replaced with the calculated value.

If the same bound name is used multiple times, then the underlying calculation will be repeated multiple times. When is this a concern?

• When the calculations consume many resources and are used many times.
• When the calculation is non-deterministic, but the query assumes all invocations to return the same value.

Mitigation

To mitigate these concerns, you can materialize the calculation results in memory during the query. Depending on the way the named calculation is defined, you’ll use different materialization strategies:

Tabular functions

Use the following strategies for tabular functions:

• let statements and function parameters: Use the materialize() function.
• as operator: Set the hint.materialized hint value to true.

For example, the following query uses the non-deterministic tabular sample operator:

Behavior without using the materialize function

range x from 1 to 100 step 1
| sample 1
| as T
| union T

Output

Behavior using the materialize function

range x from 1 to 100 step 1
| sample 1
| as hint.materialized=true T
| union T

Output

Scalar functions

Non-deterministic scalar functions can be forced to calculate exactly once by using toscalar().

For example, the following query uses the non-deterministic function, rand():

let x = () {rand(1000)};
let y = () {toscalar(rand(1000))};
print x, x, y, y

Output

Related content

• Let statement
• as operator
• toscalar()

3 - Data types

3.1 - Null values

All scalar data types in Kusto have a special value that represents a missing value. This value is called the null value, or null.

Null literals

The null value of a scalar type T is represented in the query language by the null literal T(null).

The following query returns a single row full of null values:

print bool(null), datetime(null), dynamic(null), guid(null), int(null), long(null), real(null), double(null), timespan(null)

Predicates on null values

The scalar function isnull() can be used to determine if a scalar value is the null value. The corresponding function isnotnull() can be used to determine if a scalar value isn’t the null value.

Equality and inequality of null values

• Equality (==): Applying the equality operator to two null values yields bool(null). Applying the equality operator to a null value and a non-null value yields bool(false).
• Inequality (!=): Applying the inequality operator to two null values yields bool(null). Applying the inequality operator to a null value and a non-null value yields bool(true).

For example:

datatable(val:int)[5, int(null)]
| extend IsBiggerThan3 = val > 3
| extend IsBiggerThan3OrNull = val > 3 or isnull(val)
| extend IsEqualToNull = val == int(null)
| extend IsNotEqualToNull = val != int(null)

Output

Null values and aggregation functions

When applying the following operators to entities that include null values, the null values are ignored and don’t factor into the calculation:

• dcount()
• dcountif()
• make_bag()
• make_bag_if()
• make_list()
• make_list_if()
• make_set()
• make_set_if()
• stdev()
• stdevif()
• sum()
• sumif()
• variance()
• varianceif()

Null values and the where operator

The where operator use Boolean expressions to determine if to emit each input record to the output. This operator treats null values as if they’re bool(false). Records for which the predicate returns the null value are dropped and don’t appear in the output.

For example:

datatable(ival:int, sval:string)[5, "a", int(null), "b"]
| where ival != 5

Output

Null values and binary operators

Binary operators are scalar operators that accept two scalar values and produce a third value. For example, greater-than (>) and Boolean AND (&&) are binary operators.

For all binary operators, except as noted in Exceptions to this rule, the rule is as follows:

If one or both of the values input to the binary operator are null values, then the output of the binary operator is also the null value. In other words, the null value is “sticky”.

Exceptions to this rule

• For the equality (==) and inequality (!=) operators, if one of the values is null and the other value isn’t null, then the result is either bool(false) or bool(true), respectively.
• For the logical AND (&&) operator, if one of the values is bool(false), the result is also bool(false).
• For the logical OR (||) operator, if one of the values is bool(true), the result is also bool(true).

For example:

datatable(val:int)[5, int(null)]
| extend Add = val + 10
| extend Multiply = val * 10

Output

Null values and the logical NOT (!) operator

The logical NOT operator not() yields the value bool(null) if the argument is the null value.

Null values and the in operator

• The in operator behaves like a logical OR of equality comparisons.
• The !in operator behaves like a logical AND of inequality comparisons.

Null values and data ingestion

For most data types, a missing value in the data source produces a null value in the corresponding table cell. However, columns of type string and CSV (or CSV-like) data formats are an exception to this rule, and a missing value produces an empty string.

For example:

.create table T(a:string, b:int)

.ingest inline into table T
[,]
[ , ]
[a,1]

T
| project a, b, isnull_a=isnull(a), isempty_a=isempty(a), stlen_a=strlen(a), isnull_b=isnull(b)

Output

3.2 - Scalar data types

Every data value, like the value of an expression or a function parameter, has a data type which is either a scalar data type or a user-defined record. A scalar data type is one of the built-in predefined types in Supported data types. A user-defined record is an ordered sequence of name and scalar-data-type pairs, like the data type of a row in a table.

As in most languages, the data type determines what calculations and manipulations can be run against a value. For example, if you have a value that is of type string, you won’t be able to perform arithmetic calculations against it.

Supported data types

In Kusto Query Language, most of the data types follow standard conventions and have names you’ve probably seen before. The following table shows the full list:

While most of the data types are standard, you might be less familiar with types like dynamic or timespan, and guid.

• Dynamic has a structure similar to JSON, but with one key difference: It can store Kusto Query Language-specific data types that traditional JSON can’t, such as a nested dynamic value, or timespan.

• Timespan is a data type that refers to a measure of time such as hours, days, or seconds. Don’t confuse timespan with datetime, which evaluates to an actual date and time, not a measure of time. The following table shows a list of timespan suffixes.

• GUID is a datatype representing a 128-bit, globally unique identifier, which follows the standard format of [8]-[4]-[4]-[4]-[12], where each [number] represents the number of characters and each character can range from 0-9 or a-f.

Null values

All nonstring data types can be null. When a value is null, it indicates an absence or mismatch of data. For example, if you try to input the string abc into an integer column, it results in the null value. To check if an expression is null, use the isnull() function.

For more information, see Null values.

3.3 - The bool data type

The bool data type can be: true (1), false (0), or null.

bool literals

To specify a bool literal, use one of the following syntax options:

Boolean operators

The bool data type supports all of the logical operators: equality (==), inequality (!=), logical-and (and), and logical-or (or).

Related content

• tobool()

3.4 - The datetime data type

The datetime data type represents an instant in time, typically expressed as a date and time of day. Values range from 00:00:00 (midnight), January 1, 0001 Anno Domini (Common Era) through 11:59:59 P.M., December 31, 9999 A.D. (C.E.) in the Gregorian calendar.

Time values are measured in 100-nanosecond units called ticks, and a particular date is the number of ticks since 12:00 midnight, January 1, 0001 A.D. (C.E.) in the GregorianCalendar calendar (excluding ticks that would be added by leap seconds). For example, a ticks value of 31241376000000000 represents the date, Friday, January 01, 0100 12:00:00 midnight. This is sometimes called “a moment in linear time”.

datetime literals

To specify a datetime literal, use one of the following syntax options:

The now() and ago() special functions

Kusto provides two special functions, now() and ago(), to allow queries to reference the time at which the query starts execution.

Supported formats

There are several formats for datetime that are supported as datetime() literals and the todatetime() function.

ISO 8601

RFC 822

RFC 850

Sortable

Related content

• todatetime()
• ago()
• between

3.5 - The decimal data type

The decimal data type represents a 128-bit wide, decimal number.

decimal literals

To specify a decimal literal, use one of the following syntax options:

|Syntax|Description|Example| |–|–| |decimal(number)|A decimal number represented by one or more digits, followed by a decimal point, and then one or more digits.|decimal(1.0)| |decimal(numbereexponent)|A decimal number represented by scientific notation.|decimal(1e5) is equivalent to 100,000| |decimal(null)|Represents the null value.||

Related content

• todecimal()
• toreal()

3.6 - The dynamic data type

The dynamic scalar data type can be any of the following values:

• An array of dynamic values, holding zero or more values with zero-based indexing.
• A property bag that maps unique string values to dynamic values. The property bag has zero or more such mappings (called “slots”), indexed by the unique string values. The slots are unordered.
• A value of any of the primitive scalar data types: bool, datetime, guid, int, long, real, string, and timespan.
• Null. For more information, see Null values.

Dynamic literals

To specify a dynamic literal, use one of the following syntax options:

Dynamic object accessors

To subscript a dictionary, use either the dot notation (dict.key) or the brackets notation (dict["key"]). When the subscript is a string constant, both options are equivalent.

In the examples below dict and arr are columns of dynamic type:

Accessing a sub-object of a dynamic value yields another dynamic value, even if the sub-object has a different underlying type. Use the gettype function to discover the actual underlying type of the value, and any of the cast function listed below to cast it to the actual type.

Casting dynamic objects

After subscripting a dynamic object, you must cast the value to a simple type.

Cast functions are:

• tolong()
• todouble()
• todatetime()
• totimespan()
• tostring()
• toguid()
• parse_json()

Building dynamic objects

Several functions enable you to create new dynamic objects:

• bag_pack() creates a property bag from name/value pairs.
• pack_array() creates an array from list of values (can be list of columns, for each row it will create an array from the specified columns).
• range() creates an array with an arithmetic series of numbers.
• zip() pairs “parallel” values from two arrays into a single array.
• repeat() creates an array with a repeated value.

Additionally, there are several aggregate functions which create dynamic arrays to hold aggregated values:

• buildschema() returns the aggregate schema of multiple dynamic values.
• make_bag() returns a property bag of dynamic values within the group.
• make_bag_if() returns a property bag of dynamic values within the group (with a predicate).
• make_list() returns an array holding all values, in sequence.
• make_list_if() returns an array holding all values, in sequence (with a predicate).
• make_list_with_nulls() returns an array holding all values, in sequence, including null values.
• make_set() returns an array holding all unique values.
• make_set_if() returns an array holding all unique values (with a predicate).

Operators and functions over dynamic types

For a complete list of scalar dynamic/array functions, see dynamic/array functions.

Indexing for dynamic data

Every field is indexed during data ingestion. The scope of the index is a single data shard.

To index dynamic columns, the ingestion process enumerates all “atomic” elements within the dynamic value (property names, values, array elements) and forwards them to the index builder. Otherwise, dynamic fields have the same inverted term index as string fields.

Examples

Dynamic property bag

The following query creates a dynamic property bag.

print o=dynamic({"a":123, "b":"hello", "c":[1,2,3], "d":{}})
| extend a=o.a, b=o.b, c=o.c, d=o.d

For convenience, dynamic literals that appear in the query text itself may also include other Kusto literals with types: datetime, timespan, real, long, guid, bool, and dynamic. This extension over JSON isn’t available when parsing strings (such as when using the parse_json function or when ingesting data), but it enables you to do the following:

print d=dynamic({"a": datetime(1970-05-11)})

To parse a string value that follows the JSON encoding rules into a dynamic value, use the parse_json function. For example:

• parse_json('[43, 21, 65]') - an array of numbers
• parse_json('{"name":"Alan", "age":21, "address":{"street":432,"postcode":"JLK32P"}}') - a dictionary
• parse_json('21') - a single value of dynamic type containing a number
• parse_json('"21"') - a single value of dynamic type containing a string
• parse_json('{"a":123, "b":"hello", "c":[1,2,3], "d":{}}') - gives the same value as o in the example above.

Ingest data into dynamic columns

The following example shows how you can define a table that holds a dynamic column (as well as a datetime column) and then ingest single record into it. It also demonstrates how you can encode JSON strings in CSV files.

// dynamic is just like any other type:
.create table Logs (Timestamp:datetime, Trace:dynamic)

// Everything between the "[" and "]" is parsed as a CSV line would be:
// 1. Since the JSON string includes double-quotes and commas (two characters
//    that have a special meaning in CSV), we must CSV-quote the entire second field.
// 2. CSV-quoting means adding double-quotes (") at the immediate beginning and end
//    of the field (no spaces allowed before the first double-quote or after the second
//    double-quote!)
// 3. CSV-quoting also means doubling-up every instance of a double-quotes within
//    the contents.

.ingest inline into table Logs
  [2015-01-01,"{""EventType"":""Demo"", ""EventValue"":""Double-quote love!""}"]

Output

Related content

• For an example on how to query using dynamic objects and object accessors, see Map values from one set to another.

3.7 - The guid data type

The guid data type represents a 128-bit globally unique value.

guid literals

To specify a guid literal, use one of the following syntax options:

Related content

• toguid()

3.8 - The int data type

The int data type represents a signed, 32-bit wide, integer.

int literals

To specify an int literal, use one of the following syntax options:

|Syntax|Description|Example| |–|–| |int(number)|A positive integer.|int(2)| |int(-number)|A negative integer.|int(-2)| |int(null)|Represents the null value.||

Related content

• toint()

3.9 - The long data type

The long data type represents a signed, 64-bit wide, integer.

By default, integers and integers represented with hexadecimal syntax are of type long.

long literals

To specify a long literal, use one of the following syntax options:

|Syntax|Description|Example| |–|–| |number|An integer. You don’t need to wrap the integer with long() because integers are by default of type long.|12| |0xhex|An integer represented with hexadecimal syntax.|0xf is equivalent to 15| |long(-number)|A negative integer.|long(-1)| |long(null)|Represents the null value.||

Related content

• tolong()
• To convert the long type into a hex string, see tohex() function.

3.10 - The real data type

The real data type represents a 64-bit wide, double-precision, floating-point number.

By default, decimal numbers and numbers represented with scientific notation are of type real.

real literals

To specify a real literal, use one of the following syntax options:

Related content

• toreal()

3.11 - The string data type

The string data type represents a sequence of zero or more Unicode characters.

For information on string query operators, see String operators.

string literals

A string literal is a string enclosed in quotes. You can use double quotes or single quotes to encode string literals in query text. With double quotes, you must escape nested double quote characters with a backslash (\). With single quotes, you must escape nested single quote characters, and you don’t need to escape double quotes.

Use the backslash character to escape the enclosing quote characters, tab characters (\t), newline characters (\n), and the backslash itself (\\).

Verbatim string literals

Verbatim string literals are string literals prepended with the @ character, which serves as a verbatim identifier. In this form, the backslash character (\) stands for itself and isn’t an escape character. In verbatim string literals, double quotes are escaped with double quotes and single quotes are escaped with single quotes.

For an example, see Verbatim string.

Multi-line string literals

Indicate a multi-line string literals by a “triple-backtick chord” (```) at the beginning and end of the literal.

For an example, see Multi-line string literal.

Concatenation of separated string literals

In a Kusto query, when two or more adjacent string literals have no separation between them, they’re automatically combined to form a new string literal. Similarly, if the string literals are separated only by whitespace or comments, they’re also combined to form a new string literal.

For an example, see Concatenated string literals.

Obfuscated string literals

Queries are stored for telemetry and analysis. To safeguard sensitive information like passwords and secrets, you can mark a string as an obfuscated string literal. These marked strings are logged in obfuscated form replaced with asterisks (*) in the query text.

An obfuscated string literal is created by prepending an h or an H character in front of a standard or verbatim string literal.

For an example, see Obfuscated string literal.

Examples

String literal with quotes

The following example demonstrates how to use quotes within string literals encompassed by single quotes and double quotes. For more information, see String literals.

print
    s1 = 'string with "double quotes"',
    s2 = "string with 'single quotes'"

Output

String literal with backslash escaping

The following example creates a regular expression pattern using backslashes to escape special characters. For more information, see String literals.

print pattern = '\\n.*(>|\'|=|\")[a-zA-Z0-9/+]{86}=='

Output

String literal with Unicode

The following example shows that a backslash is needed to include a Unicode character in a string literal.

print space = "Hello\u00A0World"

Output

Verbatim string literal

The following example creates a path in which the backslashes are part of the path instead of escape characters. To do this, the string @ sign is prepended to the string, creating a verbatim string literal.

print myPath = @'C:\Folder\filename.txt'

Output

Multi-line string literal

The following example shows the syntax for a multi-line string literal, which uses newlines and tabs to style a code block. For more information, see Multi-line string literals.

print program = ```
  public class Program {
    public static void Main() {
      System.Console.WriteLine("Hello!");
    }
  }```

Output

Concatenated string literals

The following expressions all yield a string of length 13. For more information, see Concatenation of separated string literals.

print 
    none = strlen("Hello"', '@"world!"),
    whitespace = strlen("Hello" ', ' @"world!"),
    whitespaceAndComment = strlen("Hello" 
        // Comment
        ', '@"world!"
    );

Output

Obfuscated string literal

In the following query output, the h string is visible in your results. However, in tracing or telemetry, the h string is stored in an obfuscated form and substituted with asterisks in the log. For more information, see Obfuscated string literals.

print blob="https://contoso.blob.core.windows.net/container/blob.txt?"
    h'sv=2012-02-12&se=2013-04-13T0...'

Output

Related content

• String operators

3.12 - The timespan data type

The timespan data type represents a time interval.

timespan literals

To specify a timespan literal, use one of the following syntax options:

timespan operators

Two values of type timespan may be added, subtracted, and divided. The last operation returns a value of type real representing the fractional number of times one value can fit the other.

Examples

The following example calculates how many seconds are in a day in several ways:

print
    result1 = 1d / 1s,
    result2 = time(1d) / time(1s),
    result3 = 24 * 60 * time(00:01:00) / time(1s)

This example converts the number of seconds in a day (represented by an integer value) to a timespan unit:

print 
    seconds = 86400
| extend t = seconds * 1s

Related content

• totimespan function
• make-timespan function

4 - Entities

4.1 - Columns

Columns are named entities that have a scalar data type. Columns are referenced in the query relative to the tabular data stream that is in context of the specific operator referencing them.Every table in Kusto, and every tabular data stream, is a rectangular grid of columns and rows. The columns of a table or a tabular data stream are ordered, so a column also has a specific position in the table’s collection of columns.

Reference columns in queries

In queries, columns are generally referenced by name only. They can only appear in expressions, and the query operator under which the expression appears determines the table or tabular data stream. The column’s name doesn’t need to be scoped further.

For example, in the following query we have an unnamed tabular data stream that is defined through the datatable operator and has a single column, c. The tabular data stream is filtered by a predicate on the value of that column, and produces a new unnamed tabular data stream with the same columns but fewer rows. The as operator then names the tabular data stream, and its value is returned as the results of the query. Notice how column c is referenced by name without referencing its container:

datatable (c:int) [int(-1), 0, 1, 2, 3]
| where c*c >= 2
| as Result

Related content

• Manage columns.

4.2 - Databases

Databases are named entities that hold tables and stored functions. Kusto follows a relation model of storing the data where the upper-level entity is a database.

A single cluster can host several databases, in which each database hosts its own collection of tables, stored functions, and external tables. Each database has its own set of permissions that follow the Role Based Access Control (RBAC) model.

A single Eventhouse can host several databases, in which each database hosts its own collection of tables, stored functions, and external tables. Each database has its own set of permissions that follow the Role Based Access Control (RBAC) model.

A database hosts its own collection of tables, stored functions, and external tables. Each database has its own set of permissions that follow the Role Based Access Control (RBAC) model.

4.3 - Entities

Kusto queries execute in the context of a Kusto database. Data in the database is arranged in tables, which the query may reference, and within the table it is organized as a rectangular grid of columns and rows. Additionally, queries may reference stored functions in the database, which are query fragments made available for reuse.

• Clusters are entities that hold databases. Clusters have no name, but they can be referenced by using the cluster() special function with the cluster’s URI. For example, cluster("https://help.kusto.windows.net") is a reference to a cluster that holds the Samples database.

• Databases are named entities that hold tables and stored functions. All Kusto queries run in the context of some database, and the entities of that database may be referenced by the query with no qualifications. Additionally, other databases may be referenced using the database() special function. For example, cluster("https://help.kusto.windows.net").database("Samples") is a universal reference to a specific database.

• Tables are named entities that hold data. A table has an ordered set of columns, and zero or more rows of data, each row holding one data value for each of the columns of the table. Tables may be referenced by name only if they are in the database in context of the query, or by qualifying them with a database reference otherwise. For example, cluster("https://help.kusto.windows.net").database("Samples").StormEvents is a universal reference to a particular table in the Samples database. Tables may also be referenced by using the table() special function.

• Columns are named entities that have a scalar data type. Columns are referenced in the query relative to the tabular data stream that is in context of the specific operator referencing them.

• Stored functions are named entities that allow reuse of Kusto queries or query parts.

• Views are virtual tables based on functions (stored or defined in an ad-hoc fashion).

• External tables are entities that reference data stored outside Kusto database. External tables are used for exporting data from Kusto to external storage as well as for querying external data without ingesting it into Kusto.

Related content

• Entity names.

4.4 - Entity names

Kusto entities are referenced in a query by name. Entities that can be referenced by their name include databases, tables, columns, and stored functions, but not clusters. The name you assign an entity is called an identifier. In addition to entities, you can also assign an identifier to query parameters, or variables set through a let statement.

Kusto entities are referenced in a query by name. Entities that can be referenced by their name include databases, tables, columns, and stored functions. The name you assign an entity is called an identifier. In addition to entities, you can also assign an identifier to query parameters, or variables set through a let statement.

An entity’s name is unique to the entity type in the context of its container. For example, two tables in the same database can’t have the same name, but a database and a table can have the same name because they’re different entity types. Similarly, a table and a stored function may have the same name.

Pretty names

In addition to the entity’s name, some entities may have a pretty name. Similar to the use of entity names, pretty names can be used to reference an entity in queries. But unlike entity names, pretty names aren’t necessarily unique in the context of their container. When a container has multiple entities with the same pretty name, the pretty name can’t be used to reference the entity.

Pretty names allow middle-tier applications to map automatically created entity names (such as UUIDs) to names that are human-readable for display and referencing purposes.

For an example on how to assign a pretty name, see .alter database prettyname command.

Identifier naming rules

An identifier is the name you assign to entities, query parameters, or variable set through a let statement. Valid identifiers must follow these rules:

• Identifiers are case-sensitive. Database names are case-insensitive, and therefore an exception to this rule.
• Identifiers must be between 1 and 1024 characters long.
• Identifiers may contain letters, digits, and underscores (_).
• Identifiers may contain certain special characters: spaces, dots (.), and dashes (-). For information on how to reference identifiers with special characters, see Reference identifiers in queries.

Avoid naming identifiers as language keywords or literals

In KQL, there are keywords and literals that have similar naming rules as identifiers. You can have identifiers with the same name as keywords or literals. However, we recommend that you avoid doing so as referencing them in queries requires special quoting.

To avoid using an identifier that might also be a language keyword or literal, such as where, summarize, and 1day, you can choose your entity name according to the following conventions, which aren’t applicable to language keywords:

• Use a name that starts with a capital letter (A to Z).

• Use a name that starts or ends with a single underscore (_).

  [!NOTE] KQL reserves all identifiers that start or end with a sequence of two underscore characters (__); users can’t define such names for their own use.

For information on how to reference these identifiers, see Reference identifiers in queries.

Reference identifiers in queries

The following table provides an explanation on how to reference identifiers in queries.

Related content

• Entity types.
• Syntax conventions for reference documentation.

4.5 - Entity references

Kusto entities are referenced in a query by name. Entities that can be referenced by their name include databases, tables, columns, and stored functions, but not clusters.

Kusto entities are referenced in a query by name. Entities that can be referenced by their name include databases, tables, columns, and stored functions.

If the entity’s container is unambiguous in the current context, use the entity name without additional qualifications. For example, when running a query against a database called DB, you may reference a table called T in that database by its name, T.

If the entity’s container isn’t available from the context, or you want to reference an entity from a container different than the container in context, use the entity’s qualified name. The name is the concatenation of the entity name to the container’s, and potentially its container’s, and so on. In this way, a query running against database DB may refer to a table T1 in a different database DB1, by using database("DB1").T1.

If the query wants to reference a table from another cluster it can do so, for example, by using cluster("https://C2.kusto.windows.net/").database("DB2").T2.

Entity references can also use the entity pretty name, as long as it’s unique in the context of the entity’s container. For more information, see entity pretty names.

Wildcard matching for entity names

In some contexts, you may use a wildcard (*) to match all or part of an entity name. For example, the following query references all tables in the current database, and all tables in database DB whose name starts with a T:

union *, database("DB1").T*

Such names are system-reserved.

Related content

• Entity types.
• Entity names.

4.6 - External tables

An external table is a schema entity that references data stored external to a Kusto database.

Similar to tables, an external table has a well-defined schema (an ordered list of column name and data type pairs). Unlike tables where data is ingested into your cluster, external tables operate on data stored and managed outside your cluster.

Supported external data stores are:

• Files stored in Azure Blob Storage or in Azure Data Lake. Most commonly the data is stored in some standard format such as CSV, JSON, Parquet, AVRO, etc. For the list of supported formats, refer to supported formats.
• SQL table (SQL Server, MySql, PostgreSql, and Cosmos DB).

See the following ways of creating external tables:

• Create or alter Azure Blob Storage/ADLS external tables
• Create or alter delta external tables
• Create and alter SQL external tables
• Create external table using Azure Data Explorer web UI Wizard

An external table can be referenced by its name using the external_table() function.

Use the following commands to manage external tables:

• .drop external table
• .show external tables
• .show external table schema

For more information about how to query external tables, and ingested and uningested data, see Query data in Azure Data Lake using Azure Data Explorer.

To accelerate queries over external delta tables, see Query acceleration policy.

4.7 - Fact and dimension tables

When designing the schema for a database, think of tables as broadly belonging to one of two categories.

• Fact tables
• Dimension tables

Fact tables

Fact tables are tables whose records are immutable “facts”, such as service logs and measurement information. Records are progressively appended into the table in a streaming fashion or in large chunks. The records stay there until they’re removed because of cost or because they’ve lost their value. Records are otherwise never updated.

Entity data is sometimes held in fact tables, where the entity data changes slowly. For example, data about some physical entity, such as a piece of office equipment that infrequently changes location. Since data in Kusto is immutable, the common practice is to have each table hold two columns:

• An identity (string) column that identifies the entity
• A last-modified (datetime) timestamp column

Only the last record for each entity identity is then retrieved.

Dimension tables

Dimension tables:

• Hold reference data, such as lookup tables from an entity identifier to its properties
• Hold snapshot-like data in tables whose entire contents change in a single transaction

Dimension tables aren’t regularly ingested with new data. Instead, the entire data content is updated at once, using operations such as .set-or-replace, .move extents, or .rename tables.

Sometimes, dimension tables might be derived from fact tables. This process can be done via a materialized view on the fact table, with a query on the table that takes the last record for each entity.

Differentiate fact and dimension tables

There are processes in Kusto that differentiate between fact tables and dimension tables. One of them is continuous export.

These mechanisms are guaranteed to process data in fact tables precisely once. They rely on the database cursor mechanism.

For example, every execution of a continuous export job, exports all records that were ingested since the last update of the database cursor. Continuous export jobs must differentiate between fact tables and dimension tables. Fact tables only process newly ingested data, and dimension tables are used as lookups. As such, the entire table must be taken into account.

There’s no way to “mark” a table as being a “fact table” or a “dimension table”. The way data is ingested into the table, and how the table is used, is what identifies its type.

The way data is ingested into the table, and how the table is used, is what identifies its type.

4.8 - Stored functions

Functions are reusable queries or query parts. Functions can be stored as database entities, similar to tables, called stored functions. Alternatively, functions can be created in an ad-hoc fashion with a let statement, called query-defined functions. For more information, see user-defined functions.

To create and manage stored functions, see the Stored functions management overview.

For more information on working with functions in Log Analytics, see Functions in Azure Monitor log queries.

4.9 - Tables

Tables are named entities that hold data. A table has an ordered set of columns, and zero or more rows of data. Each row holds one data value for each of the columns of the table. The order of rows in the table is unknown, and doesn’t in general affect queries, except for some tabular operators (such as the top operator) that are inherently undetermined. For information on how to create and manage tables, see managing tables.

Tables occupy the same namespace as stored functions. If a stored function and a table both have the same name, the stored function will be chosen.

References tables in queries

The simplest way to reference a table is by using its name. This reference can be done for all tables that are in the database in context. For example, the following query counts the records of the current database’s StormEvents table:

StormEvents
| count

An equivalent way to write the query above is by escaping the table name:

["StormEvents"]
| count

Tables may also be referenced by explicitly noting the database they are in. Then you can author queries that combine data from multiple databases. For example, the following query will work with any database in context, as long as the caller has access to the target database:

cluster("https://help.kusto.windows.net").database("Samples").StormEvents
| count

It’s also possible to reference a table by using the table() special function, as long as the argument to that function evaluates to a constant. For example:

let counter=(TableName:string) { table(TableName) | count };
counter("StormEvents")

Related content

• Estimate table size

4.10 - Views

A view is a virtual table based on the result-set of a Kusto Query Language (KQL) query.

Like real tables, views organize data with rows and columns, and participate in tasks that involve wildcard table name resolution, such as union * and search * scenarios. However, unlike real tables, views don’t maintain dedicated data storage. Rather, they dynamically represent the result of a query.

How to define a view

Views are defined through user-defined functions, which come in two forms: query-defined functions and stored functions. To qualify as a view, a function must accept no arguments and yield a tabular expression as its output.

To define a query-defined function as a view, specify the view keyword before the function definition. For an example, see Query-defined view.

To define a stored function as a view, set the view property to true when you create the function. For an example, see Stored view. For more information, see the .create function command.

Examples

Query-defined view

The following query defines two functions: T_view and T_notview. The query results demonstrate that only T_view is resolved by the wildcard reference in the union operation.

let T_view = view () { print x=1 };
let T_notview = () { print x=2 };
union T*

Stored view

The following query defines a stored view. This view behaves like any other stored function, yet can partake in wildcard scenarios.

.create function 
    with (view=true, docstring='Simple demo view', folder='Demo')  
    MyView() { StormEvents | take 100 }

Related content

• User-defined functions
• union operator
• search operator

5 - Functions

5.1 - bartlett_test_fl()

The bartlett_test_fl() function is a user-defined tabular function that performs the Bartlett Test.

Syntax

T | invoke bartlett_test_fl()(data1, data2, test_statistic,p_value)

Parameters

Function definition

You can define the function by either embedding its code as a query-defined function, or creating it as a stored function in your database, as follows:

Query-defined

Define the function using the following let statement. No permissions are required.

let bartlett_test_fl = (tbl:(*), data1:string, data2:string, test_statistic:string, p_value:string)
{
    let kwargs = bag_pack('data1', data1, 'data2', data2, 'test_statistic', test_statistic, 'p_value', p_value);
    let code = ```if 1:
        from scipy import stats
        data1 = kargs["data1"]
        data2 = kargs["data2"]
        test_statistic = kargs["test_statistic"]
        p_value = kargs["p_value"]
        def func(row):
            statistics = stats.bartlett(row[data1], row[data2])
            return statistics[0], statistics[1]
        result = df
        result[[test_statistic, p_value]]  = df.apply(func, axis=1, result_type = "expand")
    ```;
    tbl
    | evaluate python(typeof(*), code, kwargs)
};
// Write your query to use the function here.

Stored

Define the stored function once using the following .create function. Database User permissions are required.

.create-or-alter function with (folder = "Packages\\Stats", docstring = "Bartlett Test")
bartlett_test_fl(tbl:(*), data1:string, data2:string, test_statistic:string, p_value:string)
{
    let kwargs = bag_pack('data1', data1, 'data2', data2, 'test_statistic', test_statistic, 'p_value', p_value);
    let code = ```if 1:
        from scipy import stats
        data1 = kargs["data1"]
        data2 = kargs["data2"]
        test_statistic = kargs["test_statistic"]
        p_value = kargs["p_value"]
        def func(row):
            statistics = stats.bartlett(row[data1], row[data2])
            return statistics[0], statistics[1]
        result = df
        result[[test_statistic, p_value]]  = df.apply(func, axis=1, result_type = "expand")
    ```;
    tbl
    | evaluate python(typeof(*), code, kwargs)
}

Example

The following example uses the invoke operator to run the function.

Query-defined

To use a query-defined function, invoke it after the embedded function definition.

let bartlett_test_fl = (tbl:(*), data1:string, data2:string, test_statistic:string, p_value:string)
{
    let kwargs = bag_pack('data1', data1, 'data2', data2, 'test_statistic', test_statistic, 'p_value', p_value);
    let code = ```if 1:
        from scipy import stats
        data1 = kargs["data1"]
        data2 = kargs["data2"]
        test_statistic = kargs["test_statistic"]
        p_value = kargs["p_value"]
        def func(row):
            statistics = stats.bartlett(row[data1], row[data2])
            return statistics[0], statistics[1]
        result = df
        result[[test_statistic, p_value]]  = df.apply(func, axis=1, result_type = "expand")
    ```;
    tbl
    | evaluate python(typeof(*), code, kwargs)
};
// Example query that uses the function
datatable(id:string, sample1:dynamic, sample2:dynamic) [
'Test #1', dynamic([23.64, 20.57, 20.42]), dynamic([27.1, 22.12, 33.56]),
'Test #2', dynamic([20.85, 21.89, 23.41]), dynamic([35.09, 30.02, 26.52]),
'Test #3', dynamic([20.13, 20.5, 21.7, 22.02]), dynamic([32.2, 32.79, 33.9, 34.22])
]
| extend test_stat= 0.0, p_val = 0.0
| invoke bartlett_test_fl('sample1', 'sample2', 'test_stat', 'p_val')

Stored

datatable(id:string, sample1:dynamic, sample2:dynamic) [
'Test #1', dynamic([23.64, 20.57, 20.42]), dynamic([27.1, 22.12, 33.56]),
'Test #2', dynamic([20.85, 21.89, 23.41]), dynamic([35.09, 30.02, 26.52]),
'Test #3', dynamic([20.13, 20.5, 21.7, 22.02]), dynamic([32.2, 32.79, 33.9, 34.22])
]
| extend test_stat= 0.0, p_val = 0.0
| invoke bartlett_test_fl('sample1', 'sample2', 'test_stat', 'p_val')

Output

| Test #3 | [20.13, 20.5, 21.7, 22.02] | [32.2, 32.79, 33.9, 34.22] | 0.0026985713829234454 | 0.958570306268548 |

5.2 - binomial_test_fl()

The function binomial_test_fl() is a UDF (user-defined function) that performs the binomial test.

Syntax

T | invoke binomial_test_fl(successes, trials [,success_prob [, alt_hypotheis ]])