> ## Documentation Index
> Fetch the complete documentation index at: https://docs.clarion.cantina.xyz/llms.txt
> Use this file to discover all available pages before exploring further.

# Log queries (KQL)

> Query your request logs, GitHub audit log, Okta System Log, AWS CloudTrail and S3 logs with KQL, and name anything the Brain knows right inside a query.

> [!NOTE]
>
> Clarion stores the logs your monitors send and lets you query them with KQL, the Kusto Query Language used by Microsoft Sentinel and Azure Data Explorer. You write queries on the **Detect → Logs** page. Agents run the same language through the **Query Request Logs** tool, and detectors use it to describe what they look for.

## Your first query

A query starts with a table and pipes it through operators with `|`. Each operator takes the rows from the one before it.

```kusto theme={null}
requests
| where time_dt > ago(7d)
| where http_response.code >= 400
| summarize failed = count(), endpoints = dcount(endpoint) by src_endpoint.ip
| top 20 by failed
```

This reads the last 7 days of request logs, keeps the failed requests, counts them per client IP along with how many endpoints each IP hit, and returns the 20 noisiest IPs. The time range is part of the query: see [Time range](#time-range).

> [!TIP]
>
> Not sure how to write it? Click **Write with AI**, describe what to find, and Clarion writes the query for you to review and run.

## Time range

A query sets its own time range with a `where` on `time_dt` right after its table:

```kusto theme={null}
okta
| where time_dt > ago(7d)
```

| Write | Reads |
| - | - |
| `where time_dt > ago(1h)` | The last hour. `ago()` takes any time span, such as `90m` or `14d`. |
| `where time_dt between (datetime(2026-11-17) .. datetime(2026-11-18T12:00:00Z))` | Exactly that range. Dates without a time zone are UTC. |
| `where time_dt > datetime(2026-11-17)` | From that moment until now. |
| `where time_dt < datetime(2026-11-17)` | The 24 hours before that moment. |
| No `time_dt` condition | The last 24 hours. |

The range picker next to **Run** is a shortcut for the same thing: picking **Last 7 days** writes `| where time_dt > ago(7d)` after the table, and **Custom range** writes a `between`. If you write a different time condition yourself, the picker shows **Set in query**.

A condition sets the range only when it comes before any other operator, joined to the rest with `and`. Anywhere else, such as after a `summarize` or inside an `or`, it filters rows but doesn't widen what is read, so the table is read over the last 24 hours.

When a query reads several tables, each has its own range. A `where time_dt` right after a `union` applies to every table it stacks. In a `join`, each side sets its own, so you can match the last hour of Okta failures against a week of requests:

```kusto theme={null}
okta
| where time_dt > ago(1h) and status_id == 2
| summarize failures = count() by src_endpoint.ip
| join kind=inner (
    requests
    | where time_dt > ago(7d)
    | summarize requests = count() by src_endpoint.ip
  ) on src_endpoint.ip
```

> [!NOTE]
>
> Detectors are the exception. A detector runs on every window its schedule sets, so its query can't set a time range of its own, and saving one that does is refused.

## Tables

| Table | What it holds |
| - | - |
| `requests` | HTTP request logs from your log drains (Vercel, Cloudflare and others), one row per request. |
| `github_audit` | The GitHub organization audit log, one row per event. |
| `okta` | The Okta System Log, one row per event. |
| `cloudtrail` | AWS CloudTrail events, one row per event. |
| `s3_logs` | JSON log records read from S3 buckets, one row per record, mapped onto the shared columns. |
| `github_actor_repo_baseline` | What is normal: each GitHub actor's repositories over the 30 days before your time range starts. See [Baselines](#baselines). |
| `cloudtrail_principal_action_baseline` | What is normal: each AWS principal's write calls over the 30 days before your time range starts. See [Baselines](#baselines). |

The **Fields** panel in the console lists every column of every table, with its type and a description. Start typing in the editor and it suggests tables, columns, operators and functions.

### Shared columns

Columns follow [OCSF](https://schema.ocsf.io) attribute names, so one name means the same thing in every table. A dotted name such as `src_endpoint.ip` is a single column: write it as is, with no quotes or brackets.

| Column | Type | Meaning |
| - | - | - |
| `time_dt` | datetime | When the event happened. |
| `metadata.uid` | string | The source's id for the event. |
| `metadata.event_code` | string | What happened, in the source's words, such as `git.clone`, `user.session.start` or `ConsoleLogin`. |
| `event_category` | string | The family the event belongs to, such as `repo`, `user` or `Management`. |
| `status_id` | int | `1` Success, `2` Failure, `99` Other, `0` Unknown. |
| `actor.user.name` | string | Who acted: a GitHub login, an Okta login, an AWS principal ARN. |
| `actor.user.uid` | string | The source's id for the actor. |
| `actor.user.display_name` | string | The actor's display name. |
| `actor.user.type` | string | What kind of principal acted, such as `IAMUser` or `AssumedRole`. |
| `src_endpoint.ip` | string | The IP address the event came from. |
| `src_endpoint.location.country` | string | Two-letter country code of that IP, such as `DE`. |
| `src_endpoint.autonomous_system.number` | string | Autonomous system number of that IP, such as `13335`. |
| `http_request.user_agent` | string | The User-Agent. |

Not every table has every shared column; the **Fields** panel shows which. A field an event doesn't carry reads as empty text, so test for it with `isnotempty()` rather than `isnotnull()`.

### Table-specific columns

> [accordion] requests
>
> | Column | Meaning |
> | - | - |
> | `http_request.url.hostname`, `http_request.url.path` | Host and path the request was sent to. The path has no query string. |
> | `http_request.http_method` | The HTTP method. |
> | `route` | The route that served it, such as `/[slug]/issues/[id]`. |
> | `endpoint` | Method plus route for a successful request, method plus raw path for a failed one. |
> | `http_response.code` | The status code. |
> | `http_request.length`, `http_response.length` | Bytes in and out. |
> | `http_request.referrer` | The Referer header, without its query string. |
> | `http_request.http_headers` | Logged request headers, as a map by lower-case name. Read one with `tostring(http_request.http_headers["x-forwarded-for"])`. Credential headers are never stored. |
> | `payload_classes` | Attack payloads found in the path or query: `sqli`, `xss`, `traversal`, `ssti`, `nosqli`, `cmdi`, comma-separated. |
> | `is_browser`, `is_static_asset` | Whether the client is a browser or app, and whether this was a static file. |
> | `ja4` | The JA4 TLS fingerprint. |
> | `action_id`, `firewall_action`, `firewall_rule.uid` | `2` when the provider's firewall denied the request, its own verdict, and the rule that matched. |
> | `bot_score`, `waf_attack_score` | Cloudflare's scores, from 1 (bot or attack) to 99 (human or clean). |
> | `project_name`, `project_id`, `dst_endpoint.location.region` | The provider's project and the edge location that served it. |

> [accordion] github\_audit
>
> | Column | Meaning |
> | - | - |
> | `repo` | Repository as `owner/name`; empty for organization-level actions. |
> | `org`, `team`, `user` | The organization, the team the action names, and the user it was done to. |
> | `permission`, `visibility`, `is_public_repo` | The permission set, and the repository's visibility. |
> | `operation_type` | `create`, `modify`, `remove`, `access` or `transfer`. |
> | `is_bot` | The actor is a bot or app. |
> | `transport_protocol` | `http` or `ssh` for git events. |
> | `programmatic_access_type`, `token_id`, `hashed_token` | How a token acted, and which token. `hashed_token` matches one token across events. |
> | `external_identity` | The actor's SAML or SCIM identity. |
> | `workflow_id` | The Actions workflow a `workflows.*` action names. |

> [accordion] okta
>
> | Column | Meaning |
> | - | - |
> | `outcome`, `outcome_reason` | `SUCCESS`, `FAILURE`, `DENY` and so on, and why, such as `INVALID_CREDENTIALS`. |
> | `display_message`, `severity` | Okta's one-line summary and severity. |
> | `target`, `target_type`, `targets` | The first thing acted on, its type, and every target, comma-separated. |
> | `credential_type`, `auth_provider`, `factor` | How the actor authenticated, such as `PASSWORD` or `OKTA_VERIFY_PUSH`. |
> | `is_proxy`, `threat_suspected` | An anonymizing proxy was used, or Okta ThreatInsight flagged the request. |
> | `risk`, `behaviors` | Okta's risk and behavior assessments, as text. |
> | `country_name`, `city`, `isp`, `as_org` | Where the client IP is and who owns it. |
> | `device` | Okta's device class, such as `Computer`. |
> | `session_id`, `transaction_id` | Okta's session and transaction ids. |

> [accordion] cloudtrail
>
> | Column | Meaning |
> | - | - |
> | `event_source` | The service called, such as `iam.amazonaws.com`. |
> | `is_read_only` | The call only read data. |
> | `error_code`, `error_message` | AWS's error, such as `AccessDenied`; empty on success. |
> | `role_arn` | For an assumed-role session, the role it belongs to. |
> | `access_key_id`, `is_mfa` | The access key used, and whether the session used MFA. |
> | `account_id`, `actor_account_id`, `region` | The account the event happened in, the caller's account, and the region. They differ for a cross-account call. |
> | `request_parameters` | The request parameters as JSON text, to search with `has` or `contains`. |

> [accordion] s3\_logs
>
> | Column | Meaning |
> | - | - |
> | `monitor_id` | The S3 logs monitor that read the record. |
> | `object_key` | The object it came from, as `bucket/key`. |
> | `raw` | The whole record as JSON text, to search fields the mapping leaves out. |

## Operators

| Operator | What it does | Example |
| - | - | - |
| `where` | Keeps the rows a condition holds for. | `where status_id == 2` |
| `extend` | Adds computed columns. To build on a computed column, use it in a later `extend`. | `extend action = strcat(event_source, ":", metadata.event_code)` |
| `project` | Keeps, renames or computes the columns you list, in that order. | `project time_dt, who = actor.user.name, src_endpoint.ip` |
| `summarize ... by` | Groups rows and aggregates each group. Leave out `by` for one row over everything. | `summarize events = count() by actor.user.name` |
| `sort by` / `order by` | Orders rows, descending unless you write `asc`. | `sort by time_dt asc` |
| `top N by` | The first N rows by a column, descending unless you write `asc`. | `top 10 by events` |
| `take N` / `limit N` | Any N rows. | `take 50` |
| `count` | The number of rows, as a single `Count` column. | `count` |
| `union` | Stacks the rows of several tables. See [Combining logs](#combining-logs). | `union okta, cloudtrail` |
| `join` | Matches rows of two tables on columns. See [Combining logs](#combining-logs). | `join kind=inner (okta) on src_endpoint.ip` |

### Comparing values

| Operator | Matches |
| - | - |
| `==`, `!=`, `<`, `<=`, `>`, `>=` | Equal, not equal, and ordering. |
| `=~`, `!~` | Equal or not equal, ignoring case. |
| `in (...)`, `!in (...)` | One of a list. `in~` and `!in~` ignore case. |
| `between (a .. b)`, `!between (a .. b)` | Inside a range, bounds included. |
| `has`, `!has`, `has_cs` | A whole term: `has "admin"` matches `role: admin` but not `administrator`. `has_cs` is case-sensitive. |
| `has_any (...)` | Any of several whole terms. |
| `contains`, `!contains`, `contains_cs`, `!contains_cs` | A substring anywhere. |
| `startswith`, `!startswith`, `startswith_cs` | A prefix. |
| `endswith`, `!endswith`, `endswith_cs` | A suffix. |
| `matches regex` | A regular expression, in [RE2 syntax](https://github.com/google/re2/wiki/Syntax). |
| `and`, `or`, `not(...)` | Combine conditions. |

Text comparisons ignore case unless the operator ends in `_cs`, except `==`, `!=` and `in`, which are exact.

### Literals

| Kind | Examples |
| - | - |
| Text | `"git.clone"`, `'ConsoleLogin'`, `@"C:\path"` (verbatim, no escapes) |
| Numbers | `400`, `0.5` |
| Booleans | `true`, `false` |
| Time spans | `30s`, `15m`, `1h`, `7d` |
| Dates | `datetime(2026-11-20)`, `datetime(2026-11-20T08:00:00Z)` |

### Functions

| Kind | Functions |
| - | - |
| Aggregates, inside `summarize` | `count()`, `countif()`, `dcount()`, `dcountif()`, `sum()`, `sumif()`, `avg()`, `avgif()`, `min()`, `max()`, `minif()`, `maxif()`, `take_any()`, `make_set()`, `make_set_if()`, `percentile()` |
| Text | `tolower()`, `toupper()`, `strlen()`, `strcat()`, `tostring()` |
| Numbers | `toint()`, `tolong()`, `todouble()`, `toreal()`, `bin()` |
| Conditions | `iff()` (or `iif()`), `coalesce()`, `isempty()`, `isnotempty()`, `isnull()`, `isnotnull()`, `not()` |
| Time | `now()`, `ago()`, `bin()` |

`bin(time_dt, 1h)` rounds a time down to the hour, which is how you chart activity over time with `summarize ... by bin(time_dt, 1h)`. `make_set()` keeps at most 100 distinct values. Dividing two whole numbers gives a whole number, so wrap one in `todouble()` to get a ratio.

## Combining logs

### union

`union` stacks rows from several tables, lining columns up by name. Because the shared columns have the same name everywhere, one query can read all your sources at once. A table without a column reads it as empty. `withsource=<name>` adds a column naming each row's table.

```kusto theme={null}
union withsource=source_table okta, cloudtrail, github_audit
| where isnotempty(src_endpoint.ip)
| summarize sources = dcount(source_table), events = count() by src_endpoint.ip
| where sources >= 2
| top 20 by events
```

This lists the IP addresses that show up in more than one of your logs.

### join

`join` matches rows of the query so far (the left side) with rows of another query (the right side). Join on columns with the same name with `on name`, or on differently named ones with `on $left.a == $right.b`. List several keys separated by commas.

```kusto theme={null}
okta
| where outcome == "FAILURE"
| summarize failures = count() by src_endpoint.ip
| join kind=inner (requests | summarize requests = count() by src_endpoint.ip) on src_endpoint.ip
```

| Kind | Returns |
| - | - |
| `innerunique` (the default) | Matching rows, keeping only one left row per key. |
| `inner` | Every matching pair of rows. |
| `leftouter`, `rightouter`, `fullouter` | Matching rows, plus the unmatched rows of the left side, the right side, or both, with the other side's columns empty. |
| `leftsemi`, `rightsemi` | The rows of one side that have a match, with that side's columns only. |
| `leftanti` (or `anti`), `rightanti` | The rows of one side that have no match. |

> [!TIP]
>
> The default `innerunique` keeps one left row per key, as in Sentinel. If you expect several left rows to match, write `kind=inner`.

When both sides have a column of the same name, the right side's copy gets a number appended. Because that new name isn't a known column, quote it with brackets when it has a dot: `project ['src_endpoint.ip1']`.

## Baselines

A baseline table holds what is normal for each actor, computed every day from the 30 whole days before your time range starts. Because the window being searched is never part of its own baseline, joining to one finds behavior that is new.

| Table | One row per | Columns |
| - | - | - |
| `github_actor_repo_baseline` | GitHub actor and repository. Bots are left out. | `actor.user.name`, `repo`, `active_days`, `events`, `first_seen`, `last_seen` |
| `cloudtrail_principal_action_baseline` | AWS principal and write call. Read-only calls, AWS services and failed sign-ins are left out. | `principal`, `action`, `active_days`, `events`, `first_seen`, `last_seen` |

`active_days` is how many days in the lookback had any activity for that pair; `1` means a one-off. In CloudTrail, `principal` is the role ARN for an assumed-role session, so every session of a role counts as one principal, and `action` is `event_source:metadata.event_code`, such as `iam.amazonaws.com:CreateAccessKey`.

Find GitHub users touching a repository they haven't worked in for the past month:

```kusto theme={null}
github_audit
| where metadata.event_code startswith "git." and not(is_bot)
| summarize events = count() by actor.user.name, repo
| join kind=leftouter (github_actor_repo_baseline) on actor.user.name, repo
| where isnull(active_days)
| top 50 by events
```

Find AWS principals making write calls they have never made before:

```kusto theme={null}
cloudtrail
| where not(is_read_only)
| extend principal = iff(isnotempty(role_arn), role_arn, actor.user.name), action = strcat(event_source, ":", metadata.event_code)
| join kind=leftanti (cloudtrail_principal_action_baseline) on principal, action
| summarize new_calls = make_set(action, 20), calls = count() by principal
| top 20 by calls
```

In the console you can also query a baseline on its own:

```kusto theme={null}
cloudtrail_principal_action_baseline
| where principal endswith "role/deploy"
| sort by events
```

> [!NOTE]
>
> A detector can read a baseline only as the right side of a join that keeps the log's rows: `leftouter`, `inner`, `innerunique`, `leftsemi` or `leftanti`. Starting a query from a baseline works in the console and for agents.

## Brain references

You can name anything the [Brain](https://docs.clarion.cantina.xyz/learn/agents/brain.md) has discovered directly in a query, and Clarion fills in the values it holds. Instead of copying a laptop's IP addresses into a query, write `alice_laptop.ip`:

```kusto theme={null}
requests
| where src_endpoint.ip in (alice_laptop.ip)
| summarize requests = count() by http_request.url.hostname
```

A reference is an entity followed by a dot and one of its attributes. When the query runs, Clarion reads the values from the Brain and shows, next to the results, which entity each reference read and when it was last seen. Open the **Brain** tab in the console to browse entities and copy the exact reference for each one.

### Naming an entity

| Form | Example | Names |
| - | - | - |
| Handle | `alice_laptop.ip` | The entity whose name is "Alice Laptop", written in lower case with every run of other characters turned into `_`. "Alice's MacBook Pro" is `alice_s_macbook_pro`. |
| Kind and exact name | `device("Alice's MacBook Pro").ip`, `repository("payments-api").url` | The entity of that kind with exactly that name, ignoring case. Use it when a handle matches several entities. |
| A person | `user("alice@acme.com").email_addr` | A person, by email, across every account the Brain has linked to them. |
| A filtered set | `identity(labels == "admin:super_admin").email_addr` | Every entity of a kind that matches the filter. |

The kinds are `compute`, `datastore`, `network`, `service`, `repository`, `dns`, `monitor`, `scope`, `kubeCluster`, `identity`, `device`, `mcpServer`, `package`, `vulnerability`, `secret`, `accessControl`, `compliance`, `ai`, `technology`, `pipelineDefinition` and `other`.

A filter compares an attribute with text using `==`, `=~`, `contains` or `startswith`, and joins comparisons with `and`. An entity matches when any of its values for the attribute matches:

```kusto theme={null}
device(owner.email_addr == "alice@acme.com" and type contains "mac").ip
```

A column always wins over an entity of the same name. If an entity's handle clashes with a column, name it with its kind instead.

### Attributes

| Attribute | Holds |
| - | - |
| `name` | Its name. For a person, their names across accounts. |
| `ip` | The IP addresses the Brain holds for it. |
| `hostname` | Its hostname, as its EDR or MDM agent reports it. |
| `domain` | The DNS names that point at it. |
| `url` | Its URL. |
| `email_addr` | An identity's or person's email addresses, aliases included. |
| `owner.email_addr` | The email addresses of the people who own it. |
| `uid`, `uid_alt` | Its id at its provider, such as an ARN, and its other ids. |
| `type` | Its type at its provider, such as `ec2:instance`. |
| `provider` | The integrations that discovered it. |
| `account.uid`, `org.uid`, `project_uid` | The cloud account, organization and projects it belongs to. |
| `region` | Its region, zone or environment. |
| `labels` | Its tags, as `key:value`. |
| `service.name` | The service it is part of. |
| `repo_url` | The repository that manages it, as `host/owner/name`. |
| `version` | Its version. |
| `related_vulnerabilities` | An advisory's other ids, such as the CVE behind a GHSA. |

If you name an attribute an entity has no value for, the error lists the attributes it does hold.

### Rules

* **One value, or a list.** Compared with `==` or `!=`, a reference must hold exactly one value. A reference that holds several, like a laptop with two IP addresses or every super admin's email, goes inside `in (...)`, `!in (...)` or `has_any (...)`. The error tells you which to use.
* **Up to 100 values** per reference.
* **Recent and discovered only.** A query can name assets an integration discovered and has seen in the last 30 days. Assets inferred from log contents can't be named, because anyone who can write to your logs could choose their names.
* **Exactly one match.** If a handle matches no entity, or several, the query doesn't run, and the error lists the candidates to choose from.
* **Your workspace only.** References read only your workspace's Brain, and never change it.

## Examples

### Web traffic

Attack payloads in requests that weren't blocked:

```kusto theme={null}
requests
| where payload_classes has_any ("sqli", "traversal", "cmdi") and action_id != 2
| project time_dt, src_endpoint.ip, http_request.http_method, http_request.url.path, payload_classes, http_response.code
| sort by time_dt
```

Scripts hammering the login page:

```kusto theme={null}
requests
| where http_request.url.path startswith "/login" and not(is_browser)
| summarize attempts = count(), agents = make_set(http_request.user_agent, 5) by src_endpoint.ip
| where attempts > 50
| top 20 by attempts
```

Error rate per hour:

```kusto theme={null}
requests
| summarize requests = count(), errors = countif(http_response.code >= 500) by bin(time_dt, 1h)
| extend error_rate = todouble(errors) / requests
| sort by time_dt asc
```

Clients forwarded through a proxy:

```kusto theme={null}
requests
| extend forwarded_for = tostring(http_request.http_headers["x-forwarded-for"])
| where isnotempty(forwarded_for)
| summarize requests = count() by forwarded_for, src_endpoint.ip
| top 20 by requests
```

Slowest-to-download routes, by the 95th percentile of response size:

```kusto theme={null}
requests
| where isnotnull(http_response.length)
| summarize p95_bytes = percentile(http_response.length, 95), requests = count() by route
| top 10 by p95_bytes
```

### GitHub

Who cloned the most repositories:

```kusto theme={null}
github_audit
| where metadata.event_code == "git.clone"
| summarize repos = dcount(repo) by actor.user.name
| top 10 by repos
```

Destructive and access-changing actions:

```kusto theme={null}
github_audit
| where metadata.event_code in ("repo.destroy", "repo.access", "org.remove_member", "protected_branch.destroy", "org.update_member")
| project time_dt, actor.user.name, metadata.event_code, repo, user, permission
| sort by time_dt
```

One token used from many IP addresses:

```kusto theme={null}
github_audit
| where isnotempty(hashed_token)
| summarize ips = dcount(src_endpoint.ip), actions = make_set(metadata.event_code, 10) by hashed_token, actor.user.name
| where ips > 3
```

### Okta

Password spraying: one IP failing sign-ins for many users:

```kusto theme={null}
okta
| where metadata.event_code == "user.session.start" and status_id == 2
| summarize failures = count(), users = dcount(actor.user.name) by src_endpoint.ip
| where users >= 5
| top 20 by failures
```

MFA fatigue: bursts of push notifications to one user:

```kusto theme={null}
okta
| where metadata.event_code == "system.push.send_factor_verify_push"
| summarize pushes = count() by actor.user.name, window = bin(time_dt, 10m)
| where pushes >= 5
```

Sign-ins through anonymizing proxies, or flagged by ThreatInsight:

```kusto theme={null}
okta
| where is_proxy or threat_suspected
| summarize events = count(), event_types = make_set(metadata.event_code, 10) by actor.user.name, src_endpoint.ip, country_name
```

Privilege grants:

```kusto theme={null}
okta
| where metadata.event_code startswith "user.account.privilege" or metadata.event_code == "group.user_membership.add"
| project time_dt, actor.user.name, metadata.event_code, target, display_message
| sort by time_dt
```

### AWS CloudTrail

Principals hitting access denied:

```kusto theme={null}
cloudtrail
| where error_code in ("AccessDenied", "UnauthorizedOperation")
| summarize denied = count(), calls = make_set(metadata.event_code, 20) by actor.user.name
| top 10 by denied
```

Console sign-ins without MFA:

```kusto theme={null}
cloudtrail
| where metadata.event_code == "ConsoleLogin" and status_id == 1 and not(is_mfa)
| project time_dt, actor.user.name, src_endpoint.ip, http_request.user_agent, account_id
```

Tampering with CloudTrail itself:

```kusto theme={null}
cloudtrail
| where metadata.event_code in ("StopLogging", "DeleteTrail", "UpdateTrail", "PutEventSelectors")
| project time_dt, actor.user.name, metadata.event_code, account_id, region, request_parameters
```

Calls from another AWS account:

```kusto theme={null}
cloudtrail
| where isnotempty(actor_account_id) and actor_account_id != account_id
| summarize calls = count(), actions = make_set(metadata.event_code, 20) by actor_account_id, account_id
```

### S3 logs

Search fields your mapping leaves out through `raw`:

```kusto theme={null}
s3_logs
| where raw has "denied"
| summarize records = count() by object_key
| top 20 by records
```

### Across sources

IPs that failed Okta sign-ins and also sent requests to your apps:

```kusto theme={null}
okta
| where status_id == 2
| summarize okta_failures = count() by src_endpoint.ip
| join kind=inner (requests | summarize requests = count(), paths = dcount(http_request.url.path) by src_endpoint.ip) on src_endpoint.ip
| top 20 by okta_failures
```

GitHub activity from IPs that never signed in to Okta:

```kusto theme={null}
github_audit
| where isnotempty(src_endpoint.ip) and not(is_bot)
| summarize events = count(), actors = make_set(actor.user.name, 10) by src_endpoint.ip
| join kind=leftanti (okta | where metadata.event_code == "user.session.start" | summarize by src_endpoint.ip) on src_endpoint.ip
| top 20 by events
```

### With the Brain

Everything one person's laptops did on your apps:

```kusto theme={null}
requests
| where src_endpoint.ip in (device(owner.email_addr == "alice@acme.com").ip)
| summarize requests = count(), errors = countif(http_response.code >= 400) by http_request.url.hostname
```

Super admins signing in from outside the country:

```kusto theme={null}
okta
| where metadata.event_code == "user.session.start"
| where actor.user.name in (identity(labels == "admin:super_admin").email_addr)
| where src_endpoint.location.country != "US"
| project time_dt, actor.user.name, src_endpoint.ip, country_name, city
```

AWS calls made from your production instances:

```kusto theme={null}
cloudtrail
| where src_endpoint.ip in (compute(labels == "env:prod").ip)
| summarize calls = count(), actions = make_set(metadata.event_code, 20) by actor.user.name
```

One device, named exactly:

```kusto theme={null}
requests
| where src_endpoint.ip in (device("Alice's MacBook Pro").ip)
| project time_dt, http_request.http_method, http_request.url.path, http_response.code
| sort by time_dt
```

A person's activity across GitHub and Okta, when the Brain has linked both accounts to them:

```kusto theme={null}
union withsource=source_table github_audit, okta
| where actor.user.name in (user("alice@acme.com").name) or actor.user.name in (user("alice@acme.com").email_addr)
| summarize events = count(), actions = make_set(metadata.event_code, 20) by source_table, src_endpoint.ip
```

## Limits

| Limit | Value |
| - | - |
| Rows returned | 1,000. A result cut short says so; add `summarize`, `top` or a tighter `where`. |
| Query length | 4,000 characters. |
| Values in `make_set()` | 100. |
| Values per Brain reference | 100. |

### Not supported

Clarion compiles KQL to its own log store, and refuses anything it doesn't support with an error pointing at the exact spot:

* `let` statements, and more than one statement per query. Write one query that starts with a table.
* Operators other than those listed in [Operators](#operators), such as `parse`, `mv-expand`, `make-series` or `render`.
* `summarize ... by bin=`, and `nulls first` / `nulls last` in sorts.
* `union` kinds other than the default `outer`.
* `has` and `has_any` with anything other than quoted text.
* Looking up a map by position: `http_request.http_headers["user-agent"]` works, `http_request.http_headers[0]` doesn't.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.