Axiom
Overview

Sample queries

For an introduction to APL and to the structure of an APL query, see Introduction to APL.

Summarize data#

summarize produces a table that aggregates the content of the dataset. Use the aggregation functions with the summarize operator to produce different fields.

The following query counts events by time bins.

['sample-http-logs']
| summarize count() by bin_auto(_time)

Run in Playground

The example below summarizes the top 10 GitHub push events by maximum push ID.

['github-push-event']
| summarize max_if = maxif(push_id, true) by size
| top 10 by max_if desc

Run in Playground

The example below summarizes the distinct city count by server datacenter.

['sample-http-logs']
| summarize cities = dcount(['geo.city']) by server_datacenter

Run in Playground

Tabular operators#

where#

where filters the content of the dataset that meets a condition when executed.

The following query filters the data by method and content_type:

['sample-http-logs']
| where method == "GET" and content_type == "application/octet-stream"
| project method , content_type

Run in Playground

count#

count returns the number of events from the input dataset.

['sample-http-logs']
| count

Run in Playground

project#

project selects a subset of fields.

['sample-http-logs']
| project content_type, ['geo.country'], method, resp_body_size_bytes, resp_header_size_bytes

Run in Playground

take#

take returns up to the specified number of rows.

['sample-http-logs']
| take 100

Run in Playground

limit#

The limit operator is an alias to the take operator.

['sample-http-logs']
| limit 10

Run in Playground

Scalar functions#

parse_json#

parse_json extracts the JSON elements from an array.

['sample-http-logs']
| project parsed_json = parse_json( "config_jsonified_metrics")

Run in Playground

replace_string#

replace_string replaces all string matches with another string.

['sample-http-logs']
| extend replaced_string = replace_string( "creator", "method", "machala" )
| project replaced_string

Run in Playground

split#

split splits a given string according to a given delimiter and returns a string array.

['sample-http-logs']
| project split_str = split("method_content_metrics", "_")
| take 20

Run in Playground

strcat_delim#

strcat_delim concatenates a string array into a string with a given delimiter.

['sample-http-logs']
| project strcat = strcat_delim(":", ['geo.city'], resp_body_size_bytes)

Run in Playground

indexof#

indexof reports the zero-based index of the first occurrence of a specified string within the input string.

['sample-http-logs']
| extend based_index =  indexof( ['geo.country'], content_type, 45, 60, resp_body_size_bytes ), specified_time = bin(resp_header_size_bytes, 30)

Run in Playground

Regex examples#

Remove leading characters

['sample-http-logs']
| project remove_cutset = trim_start_regex("[^a-zA-Z]", content_type )

Run in Playground

Find logs from a city

['sample-http-logs']
| where tostring(geo.city) matches regex "^Camaquã$"

Run in Playground

Identify logs from a user agent

['sample-http-logs']
| where tostring(user_agent) matches regex "Mozilla/5.0"

Run in Playground

Find logs with response body size in a certain range

['sample-http-logs']
| where toint(resp_body_size_bytes) >= 4000 and toint(resp_body_size_bytes) <= 5000

Run in Playground

Find logs with user agents containing Windows NT

['sample-http-logs']
| where tostring(user_agent) matches regex @"Windows NT [\d\.]+"

Run in Playground

Find logs with specific response header size

['sample-http-logs']
| where toint(resp_header_size_bytes) == 31

Run in Playground

Find logs with specific request duration

['sample-http-logs']
| where toreal(req_duration_ms) < 1

Run in Playground

Find logs where TLS is enabled and method is POST

['sample-http-logs']
| where tostring(is_tls) == "true" and tostring(method) == "POST"

Run in Playground

Array functions#

array_concat#

array_concat concatenates a number of dynamic arrays to a single array.

['sample-http-logs']
| extend concatenate = array_concat( dynamic([5,4,3,87,45,2,3,45]))
| project concatenate

Run in Playground

array_sum#

array_sum calculates the sum of elements in a dynamic array.

['sample-http-logs']
| extend summary_array=dynamic([1,2,3,4])
| project summary_array=array_sum(summary_array)

Run in Playground

Conversion functions#

todatetime#

todatetime converts input to datetime scalar.

['sample-http-logs']
| extend dated_time = todatetime("2026-08-16")

Run in Playground

dynamic_to_json#

dynamic_to_json converts a scalar value of type dynamic to a canonical string representation.

['sample-http-logs']
| extend dynamic_string = dynamic_to_json(dynamic([10,20,30,40 ]))

Run in Playground

Scalar operators#

APL supports a wide range of scalar operators:

contains#

The query below uses the contains operator to find the strings that contain the string -bot and [bot]:

['github-issue-comment-event']
| extend bot = actor contains "-bot" or actor contains "[bot]"
| where bot == true
| summarize count() by bin_auto(_time), actor
| take 20

Run in Playground

['sample-http-logs']
| extend user_status = status contains "200" , agent_flow = user_agent contains "(Windows NT 6.4; AppleWebKit/537.36 Chrome/41.0.2225.0 Safari/537.36"
| where user_status == true
| summarize count() by bin_auto(_time), status
| take 15

Run in Playground

Hash functions#

  • hash_md5 returns an MD5 hash value for the input value.
  • hash_sha256 returns a sha256 hash value for the input value.
  • hash_sha1 returns a sha1 hash value for the input value.
['sample-http-logs']
| extend sha_256 = hash_md5( "resp_header_size_bytes" ), sha_1 = hash_sha1( content_type), md5 = hash_md5( method), sha512 = hash_sha512( "resp_header_size_bytes" )
| project sha_256, sha_1, md5, sha512

Run in Playground

Rounding functions#

  • floor() calculates the largest integer less than, or equal to, the specified numeric expression.
  • ceiling() calculates the smallest integer greater than, or equal to, the specified numeric expression.
  • bin() rounds values down to an integer multiple of a given bin size.
['sample-http-logs']
| extend largest_integer_less = floor( resp_header_size_bytes ), smallest_integer_greater = ceiling( req_duration_ms ), integer_multiple = bin( resp_body_size_bytes, 5 )
| project largest_integer_less, smallest_integer_greater, integer_multiple

Run in Playground

Truncate decimals using round function

['sample-http-logs']
| project rounded_value = round(req_duration_ms, 2)

Run in Playground

Truncate decimals using floor function

['sample-http-logs']
| project floor_value = floor(resp_body_size_bytes), ceiling_value = ceiling(req_duration_ms)

Run in Playground

Other examples#

List all unique groups

['sample-http-logs']
| distinct ['id'], is_tls

Run in Playground

Count of all events per service

['sample-http-logs']
| summarize Count = count() by server_datacenter
| order by Count desc

Run in Playground

Change the time clause

['github-issues-event']
| where _time == ago(1m)
| summarize count(), sum(['milestone.number']) by _time=bin(_time, 1m)

Run in Playground

HTTP 5xx responses for the last 7 days, one bar per day

['sample-http-logs']
| where _time > ago(7d)
| where req_duration_ms >= 5 and req_duration_ms < 6
| summarize count(), histogram(resp_header_size_bytes, 20) by bin(_time, 1d)
| order by _time desc

Run in Playground

Implement a remapper on remote address logs

['sample-http-logs']
| extend RemappedStatus = case(req_duration_ms >= 0.57, "new data", resp_body_size_bytes >= 1000, "size bytes", resp_header_size_bytes == 40, "header values", "doesntmatch")

Run in Playground

Advanced aggregations

['sample-http-logs']
| extend prospect = ['geo.city'] contains "Okayama" or uri contains "/api/v1/messages/back"
| extend possibility = server_datacenter contains "GRU" or status contains "301"
| summarize count(), topk( user_agent, 6 ) by bin(_time, 10d), ['geo.country']
| take 4

Run in Playground

Search map fields

['otel-demo-traces']
| where isnotnull( ['attributes.custom'])
| extend extra = tostring(['attributes.custom'])
| search extra:"0PUK6V6EV0"
| project _time, trace_id, name, ['attributes.custom']

Run in Playground

Configure processing rules

['sample-http-logs']
| where _sysTime > ago(1d)
| summarize count() by method

Run in Playground

Return different values based on the evaluation of a condition

['sample-http-logs']
| extend MemoryUsageStatus = iff(req_duration_ms > 10000, "Highest", "Normal")

Run in Playground

Working with different operators

['hn']
| extend superman = text contains "superman" or title contains "superman"
| extend batman = text contains "batman" or title contains "batman"
| extend hero = case(
    superman and batman, "both",
    superman, "superman   ", // spaces change the color
    batman, "batman       ",
    "none")
| where (superman or batman) and not (batman and superman)
| summarize count(), topk(type, 3) by bin(_time, 30d), hero
| take 10

Run in Playground

['sample-http-logs']
| summarize flow = dcount( content_type) by ['geo.country']
| take 50

Run in Playground

Get the JSON into a property bag using parse-json

example
| where isnotnull(log)
| extend parsed_log = parse_json(log)
| project service, parsed_log.level, parsed_log.message

Get average response using project-keep

['sample-http-logs']
| where ['geo.country']  == "United States" or ['id'] == 'b2b1f597-0385-4fed-a911-140facb757ef'
| extend systematic_view = ceiling( resp_header_size_bytes )
| extend resp_avg = cos( resp_body_size_bytes )
| project-away systematic_view
| project-keep resp_avg
| take 5

Run in Playground

Combine multiple percentiles into a single chart

['sample-http-logs']
| summarize percentiles_array(req_duration_ms, 50, 75, 90) by bin_auto(_time)

Run in Playground

Combine mathematical functions

['sample-http-logs']
| extend tangent = tan( req_duration_ms ), cosine = cos( resp_header_size_bytes ), absolute_input = abs( req_duration_ms ), sine = sin( resp_header_size_bytes ), power_factor = pow( req_duration_ms, 4)
| extend angle_pi = degrees( resp_body_size_bytes ), pie = pi()
| project tangent, cosine, absolute_input, angle_pi, pie, sine, power_factor

Run in Playground

['github-issues-event']
| where actor !endswith "[bot]"
| where repo startswith "kubernetes/"
| where action == "opened"
| summarize count() by bin_auto(_time)

Run in Playground

Change global configuration attributes

['sample-http-logs']
| extend status = coalesce(status, "info")

Run in Playground

Set defualt value on event field

['sample-http-logs']
| project status = case(
    isnotnull(status) and status != "", content_type, // use the contenttype if it’s not null and not an empty string
    "info" // default value
  )

Run in Playground

Extract nested payment amount from custom attributes map field

['otel-demo-traces']
| extend amount = ['attributes.custom']['app.payment.amount']
| where isnotnull( amount)
| project _time, trace_id, name, amount, ['attributes.custom']

Run in Playground

Filtering GitHub issues by label identifier

['github-issues-event']
| extend data = tostring(labels)
| where labels contains "d73a4a"

Run in Playground

Aggregate trace counts by HTTP method attribute in custom map

['otel-demo-traces']
| extend httpFlavor = tostring(['attributes.custom'])
| summarize Count=count() by ['attributes.http.method']

Run in Playground

Updated

Was this page helpful?