Driving KQL Lens from your own code

The task field comes first

One app, one system prompt, one model — and three lanes over the same work object. task is required and decides which sections the reply carries. Send an unrecognised value and the model picks the closest lane and names its choice in lane; it never blends two contracts.

Every lane also fills the shared envelope: lane, title, verdict, headline, scan_risk, time_window, tables, stage_count, checks, coverage_check and summary.

The run input

These are the exact fields app.js submits, taken from its readForm().

A complete body, as the review lane would send it:

{
  "task": "review",
  "query": "let lookback = 30d;\nSigninLogs\n| extend AppName = tostring(parse_json(tostring(Properties)).appDisplayName)\n| where tolower(AppName) contains \"payments\"\n| where ResultType != 0\n| join AppExceptions on $left.UserPrincipalName == $right.user_UPN\n| where TimeGenerated > ago(lookback)\n| summarize Failures = count() by UserPrincipalName, bin(TimeGenerated, 1h)",
  "schema": "",
  "platform": "loganalytics",
  "goal": "failed sign-ins per user per hour for the payments app",
  "context": "Runs for minutes on the workspace and the counts do not match the identity team's.",
  "prescan_facts": {
    "facts": {
      "stage_count": 8,
      "operators": {
        "extend": 1,
        "where": 3,
        "join": 1,
        "summarize": 1
      },
      "tables": [
        "SigninLogs",
        "AppExceptions"
      ],
      "time_window": "where TimeGenerated > ago(lookback) (via let lookback)",
      "window_hours": 720,
      "lets": [
        "lookback"
      ],
      "has_schema": false,
      "prescan_verdict": "rewrite"
    },
    "flags": [
      {
        "id": "late-time-filter",
        "label": "The time filter is not the first thing the query does",
        "severity": "high",
        "stage": 5,
        "evidence": "where TimeGenerated > ago(lookback)"
      },
      {
        "id": "join-no-kind",
        "label": "join with no kind= - the default is innerunique, not inner",
        "severity": "high",
        "stage": 4,
        "evidence": "join AppExceptions on $left.UserPrincipalName == $right.user_UPN"
      }
    ]
  }
}

The nine checks, and why two of them are always unknown without a schema

Each check declares where its evidence must come from. A check whose evidence is the query may fail definitively — if nothing in the pasted pipeline bounds the time range, that is a fail, because a stage that was not pasted cannot reach into a stage that was. A check whose evidence is the schema is forced to unknown when no schema was sent, whatever the model returned. The downgrade is applied in one place, so it cannot drift.

1 A tiny client, and a token

A guest token is enough for /me and /estimate. Running a lane is metered and needs a personal token — sign in through the app, then copy it from the token page. Never hard-code a real token into a file you commit; the samples below use a YOUR_TOKEN placeholder.

curl -s -X POST https://api.skillsafe.ai/v1/app-api/guest \
  -H 'Content-Type: application/json' \
  -d '{"slug": "kql-lens"}'

# The body key is "slug". It is NOT "app_slug" - that returns a validation
# error, with the right route and the wrong payload, which is a slow thing to
# notice. The reply carries {"data": {"token": "aut_...", "guest_id": "..."}}.

import json, urllib.request

API = "https://api.skillsafe.ai/v1/app-api"

def post(path, body, token=None):
    req = urllib.request.Request(API + path, method="POST",
                                 data=json.dumps(body).encode())
    req.add_header("Content-Type", "application/json")
    if token:
        req.add_header("Authorization", "Bearer " + token)
    with urllib.request.urlopen(req) as r:
        return json.load(r)["data"]

token = post("/guest", {"slug": "kql-lens"})["token"]
print(token[:12] + "...")

const API = "https://api.skillsafe.ai/v1/app-api";

async function post(path, body, token) {
  const res = await fetch(API + path, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      ...(token ? { Authorization: `Bearer ${token}` } : {}),
    },
    body: JSON.stringify(body),
  });
  const json = await res.json();
  if (!json.ok) throw new Error(json.error.code + ": " + json.error.message);
  return json.data;
}

const { token } = await post("/guest", { slug: "kql-lens" });

package main

import (
	"bytes"
	"encoding/json"
	"fmt"
	"net/http"
)

const API = "https://api.skillsafe.ai/v1/app-api"

func post(path string, body any, token string) (map[string]any, error) {
	b, _ := json.Marshal(body)
	req, _ := http.NewRequest("POST", API+path, bytes.NewReader(b))
	req.Header.Set("Content-Type", "application/json")
	if token != "" {
		req.Header.Set("Authorization", "Bearer "+token)
	}
	res, err := http.DefaultClient.Do(req)
	if err != nil {
		return nil, err
	}
	defer res.Body.Close()
	var out struct {
		OK    bool           `json:"ok"`
		Data  map[string]any `json:"data"`
		Error map[string]any `json:"error"`
	}
	json.NewDecoder(res.Body).Decode(&out)
	if !out.OK {
		return nil, fmt.Errorf("%v", out.Error)
	}
	return out.Data, nil
}

func main() {
	d, _ := post("/guest", map[string]string{"slug": "kql-lens"}, "")
	fmt.Println(d["token"])
}

import java.net.URI;
import java.net.http.*;

var http = HttpClient.newHttpClient();
var API = "https://api.skillsafe.ai/v1/app-api";

HttpResponse<String> post(String path, String body, String token) throws Exception {
    var b = HttpRequest.newBuilder(URI.create(API + path))
        .header("Content-Type", "application/json")
        .POST(HttpRequest.BodyPublishers.ofString(body));
    if (token != null) b.header("Authorization", "Bearer " + token);
    return http.send(b.build(), HttpResponse.BodyHandlers.ofString());
}

var res = post("/guest", "{\"slug\":\"kql-lens\"}", null);
System.out.println(res.body());

require "json"
require "net/http"

API = URI("https://api.skillsafe.ai/v1/app-api")

def post(path, body, token = nil)
  uri = URI(API.to_s + path)
  req = Net::HTTP::Post.new(uri, "Content-Type" => "application/json")
  req["Authorization"] = "Bearer #{token}" if token
  req.body = body.to_json
  res = Net::HTTP.start(uri.hostname, uri.port, use_ssl: true) { |h| h.request(req) }
  json = JSON.parse(res.body)
  raise json["error"].to_s unless json["ok"]
  json["data"]
end

token = post("/guest", { slug: "kql-lens" })["token"]

<?php
const API = "https://api.skillsafe.ai/v1/app-api";

function post_json(string $path, array $body, ?string $token = null): array {
    $headers = ["Content-Type: application/json"];
    if ($token) $headers[] = "Authorization: Bearer " . $token;
    $ch = curl_init(API . $path);
    curl_setopt_array($ch, [
        CURLOPT_POST => true,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_HTTPHEADER => $headers,
        CURLOPT_POSTFIELDS => json_encode($body),
    ]);
    $json = json_decode(curl_exec($ch), true);
    curl_close($ch);
    if (empty($json["ok"])) throw new RuntimeException(json_encode($json["error"]));
    return $json["data"];
}

$token = post_json("/guest", ["slug" => "kql-lens"])["token"];

using System.Net.Http.Json;
using System.Text.Json;

var http = new HttpClient { BaseAddress = new Uri("https://api.skillsafe.ai/v1/app-api/") };

async Task<JsonElement> PostAsync(string path, object body, string? token = null) {
    var req = new HttpRequestMessage(HttpMethod.Post, path) { Content = JsonContent.Create(body) };
    if (token is not null) req.Headers.Add("Authorization", "Bearer " + token);
    var res = await http.SendAsync(req);
    var doc = JsonDocument.Parse(await res.Content.ReadAsStringAsync());
    if (!doc.RootElement.GetProperty("ok").GetBoolean())
        throw new Exception(doc.RootElement.GetProperty("error").ToString());
    return doc.RootElement.GetProperty("data");
}

var data = await PostAsync("guest", new { slug = "kql-lens" });
var token = data.GetProperty("token").GetString();

2 Who am I, and what is my balance

subject_type is user or guest; credits is the balance in credits (10,000 credits = $1).

curl -s https://api.skillsafe.ai/v1/app-api/me \
  -H 'Authorization: Bearer YOUR_TOKEN' \
  -H 'Content-Type: application/json'
# -> {"subject_type": "user", "credits": 128400, ...}

# post() and the token come from step 1
data = post("/me", None, token)
print(json.dumps(data, indent=2))
# -> {"subject_type": "user", "credits": 128400, ...}

// post() and the token come from step 1
const data = await post("/me", null, token);
console.log(data);
# -> {"subject_type": "user", "credits": 128400, ...}

// post() and the token come from step 1
d, err := post("/me", map[string]any{ /* None */ }, token)
if err != nil {
	panic(err)
}
fmt.Printf("%+v\n", d)
# -> {"subject_type": "user", "credits": 128400, ...}

// post() and the token come from step 1
var res = post("/me", """
None
""", token);
System.out.println(res.body());
# -> {"subject_type": "user", "credits": 128400, ...}

# post() and the token come from step 1
data = post("/me", nil
None
JSON
, token)
pp data
# -> {"subject_type": "user", "credits": 128400, ...}

<?php
// post_json() and $token come from step 1
$data = post_json("/me", json_decode(<<<'JSON'
None
JSON, true), $token);
print_r($data);
# -> {"subject_type": "user", "credits": 128400, ...}

// PostAsync() and the token come from step 1
var data = await PostAsync("me",
    JsonSerializer.Deserialize<object>("""
None
"""), token);
Console.WriteLine(data);
# -> {"subject_type": "user", "credits": 128400, ...}

3 Price the lane — free, and it charges nothing

/estimate runs no job and costs nothing. hold_credits is the reservation, not the price: you are charged charged_credits at settlement, usually far less. The hold differs per lane, so estimate the lane you are about to run — never show one lane's price for another's run. The reply also proves the model binding: model reads gpt-5.6-terra, model_alias reads gpt-terra.

curl -s -X POST https://api.skillsafe.ai/v1/app-api/estimate \
  -H 'Authorization: Bearer YOUR_TOKEN' \
  -H 'Content-Type: application/json' \
  -d '{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}'
# -> {"model": "gpt-5.6-terra", "model_alias": "gpt-terra",
#     "markup_bps": 1000, "hold_credits": ..., "min_credits": ...}

# post() and the token come from step 1
data = post("/estimate", {'task': 'review', 'query': 'SigninLogs | where TimeGenerated > ago(1d) | count', 'platform': 'loganalytics'}, token)
print(json.dumps(data, indent=2))
# -> {"model": "gpt-5.6-terra", "model_alias": "gpt-terra",
#     "markup_bps": 1000, "hold_credits": ..., "min_credits": ...}

// post() and the token come from step 1
const data = await post("/estimate", {"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}, token);
console.log(data);
# -> {"model": "gpt-5.6-terra", "model_alias": "gpt-terra",
#     "markup_bps": 1000, "hold_credits": ..., "min_credits": ...}

// post() and the token come from step 1
d, err := post("/estimate", map[string]any{ /* {"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"} */ }, token)
if err != nil {
	panic(err)
}
fmt.Printf("%+v\n", d)
# -> {"model": "gpt-5.6-terra", "model_alias": "gpt-terra",
#     "markup_bps": 1000, "hold_credits": ..., "min_credits": ...}

// post() and the token come from step 1
var res = post("/estimate", """
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
""", token);
System.out.println(res.body());
# -> {"model": "gpt-5.6-terra", "model_alias": "gpt-terra",
#     "markup_bps": 1000, "hold_credits": ..., "min_credits": ...}

# post() and the token come from step 1
data = post("/estimate", JSON.parse(<<~JSON)
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
JSON
, token)
pp data
# -> {"model": "gpt-5.6-terra", "model_alias": "gpt-terra",
#     "markup_bps": 1000, "hold_credits": ..., "min_credits": ...}

<?php
// post_json() and $token come from step 1
$data = post_json("/estimate", json_decode(<<<'JSON'
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
JSON, true), $token);
print_r($data);
# -> {"model": "gpt-5.6-terra", "model_alias": "gpt-terra",
#     "markup_bps": 1000, "hold_credits": ..., "min_credits": ...}

// PostAsync() and the token come from step 1
var data = await PostAsync("estimate",
    JsonSerializer.Deserialize<object>("""
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
"""), token);
Console.WriteLine(data);
# -> {"model": "gpt-5.6-terra", "model_alias": "gpt-terra",
#     "markup_bps": 1000, "hold_credits": ..., "min_credits": ...}

4 Run it, and poll

Send an Idempotency-Key on every run. The app derives it from (lane, input, attempt) — two lanes over the same query are two distinct runs and must not collide on one key, and a retry that reuses the key cannot double-bill. /run returns a job_id; poll /jobs/{id} until status is succeeded or failed. The model's text is at data.output.output.

curl -s -X POST https://api.skillsafe.ai/v1/app-api/run \
  -H 'Authorization: Bearer YOUR_TOKEN' \
  -H 'Content-Type: application/json' \
  -d '{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}'
# Add:  -H 'Idempotency-Key: kql-lens:review:<hash>:a1'
# -> {"job_id": "job_..."}   then GET /jobs/job_...

# post() and the token come from step 1
data = post("/run", {'task': 'review', 'query': 'SigninLogs | where TimeGenerated > ago(1d) | count', 'platform': 'loganalytics'}, token)
print(json.dumps(data, indent=2))
# Add:  -H 'Idempotency-Key: kql-lens:review:<hash>:a1'
# -> {"job_id": "job_..."}   then GET /jobs/job_...

// post() and the token come from step 1
const data = await post("/run", {"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}, token);
console.log(data);
# Add:  -H 'Idempotency-Key: kql-lens:review:<hash>:a1'
# -> {"job_id": "job_..."}   then GET /jobs/job_...

// post() and the token come from step 1
d, err := post("/run", map[string]any{ /* {"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"} */ }, token)
if err != nil {
	panic(err)
}
fmt.Printf("%+v\n", d)
# Add:  -H 'Idempotency-Key: kql-lens:review:<hash>:a1'
# -> {"job_id": "job_..."}   then GET /jobs/job_...

// post() and the token come from step 1
var res = post("/run", """
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
""", token);
System.out.println(res.body());
# Add:  -H 'Idempotency-Key: kql-lens:review:<hash>:a1'
# -> {"job_id": "job_..."}   then GET /jobs/job_...

# post() and the token come from step 1
data = post("/run", JSON.parse(<<~JSON)
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
JSON
, token)
pp data
# Add:  -H 'Idempotency-Key: kql-lens:review:<hash>:a1'
# -> {"job_id": "job_..."}   then GET /jobs/job_...

<?php
// post_json() and $token come from step 1
$data = post_json("/run", json_decode(<<<'JSON'
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
JSON, true), $token);
print_r($data);
# Add:  -H 'Idempotency-Key: kql-lens:review:<hash>:a1'
# -> {"job_id": "job_..."}   then GET /jobs/job_...

// PostAsync() and the token come from step 1
var data = await PostAsync("run",
    JsonSerializer.Deserialize<object>("""
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
"""), token);
Console.WriteLine(data);
# Add:  -H 'Idempotency-Key: kql-lens:review:<hash>:a1'
# -> {"job_id": "job_..."}   then GET /jobs/job_...

5 Or stream it

/run-stream is the same call over SSE. Events are job, delta and done; concatenate the delta payloads to rebuild the reply. The app advances its progress card on the envelope's own section keys arriving in that stream.

curl -s -X POST https://api.skillsafe.ai/v1/app-api/run-stream \
  -H 'Authorization: Bearer YOUR_TOKEN' \
  -H 'Content-Type: application/json' \
  -d '{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}'
# Content-Type: text/event-stream
# data: {"type":"delta","text":"..."}

# post() and the token come from step 1
data = post("/run-stream", {'task': 'review', 'query': 'SigninLogs | where TimeGenerated > ago(1d) | count', 'platform': 'loganalytics'}, token)
print(json.dumps(data, indent=2))
# Content-Type: text/event-stream
# data: {"type":"delta","text":"..."}

// post() and the token come from step 1
const data = await post("/run-stream", {"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}, token);
console.log(data);
# Content-Type: text/event-stream
# data: {"type":"delta","text":"..."}

// post() and the token come from step 1
d, err := post("/run-stream", map[string]any{ /* {"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"} */ }, token)
if err != nil {
	panic(err)
}
fmt.Printf("%+v\n", d)
# Content-Type: text/event-stream
# data: {"type":"delta","text":"..."}

// post() and the token come from step 1
var res = post("/run-stream", """
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
""", token);
System.out.println(res.body());
# Content-Type: text/event-stream
# data: {"type":"delta","text":"..."}

# post() and the token come from step 1
data = post("/run-stream", JSON.parse(<<~JSON)
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
JSON
, token)
pp data
# Content-Type: text/event-stream
# data: {"type":"delta","text":"..."}

<?php
// post_json() and $token come from step 1
$data = post_json("/run-stream", json_decode(<<<'JSON'
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
JSON, true), $token);
print_r($data);
# Content-Type: text/event-stream
# data: {"type":"delta","text":"..."}

// PostAsync() and the token come from step 1
var data = await PostAsync("run-stream",
    JsonSerializer.Deserialize<object>("""
{"task": "review", "query": "SigninLogs | where TimeGenerated > ago(1d) | count", "platform": "loganalytics"}
"""), token);
Console.WriteLine(data);
# Content-Type: text/event-stream
# data: {"type":"delta","text":"..."}

What comes back, per lane

One envelope, three fillings. A worked example of the substantive part of each:

review

{
  "lane": "review",
  "verdict": "rewrite",
  "headline": "The 30-day bound lands after the join and the join has no `kind=`, so this reads both tables over full retention and then counts `innerunique` fan-out - which is why its failure counts disagree with the identity team's.",
  "scan_risk": "wide",
  "time_window": "where TimeGenerated > ago(lookback) (via let lookback)",
  "tables": [
    "SigninLogs",
    "AppExceptions"
  ],
  "stage_count": 9,
  "checks": [
    {
      "id": "time-window",
      "status": "fail",
      "evidence": "| where TimeGenerated > ago(lookback) - stage 6, after two `extend`s and after the join.",
      "requirement": "`| where TimeGenerated > ago(lookback)` directly under `SigninLogs`, and a matching filter inside the join's right side."
    },
    {
      "id": "filter-order",
      "status": "fail",
      "evidence": "| extend AppName = tostring(parse_json(tostring(Properties)).appDisplayName) runs on the unreduced table; the only time bound runs after the join.",
      "requirement": "Time filter first, then the term filters, then the `extend`s that parse JSON."
    },
    "\u2026 7 more"
  ],
  "findings": [
    {
      "id": "KQ-001",
      "severity": "high",
      "stage": 0,
      "title": "Nothing bounds either table until after the join",
      "evidence": "SigninLogs",
      "why": "The only time bound is `| where TimeGenerated > ago(lookback)` at stage 6. Everything above it runs against full retention: the JSON parse on every row, both string filters, and both sides of the join's shuffle. Being late also makes it narrow. If `AppExceptions` carries its own `TimeGenerated`, the join renames the right-hand copy, so stage 6 filters the sign-in side only and the exceptions side stays unbounded no matter how long the query runs.",
      "fix": "Move the identical filter to sit directly under `SigninLogs`, and delete it from stage 6. The right side gets its own copy inside the join's parentheses - see KQ-002.",
      "fixed_fragment": "SigninLogs\n| where TimeGenerated > ago(lookback)"
    },
    {
      "id": "KQ-002",
      "severity": "high",
      "stage": 5,
      "title": "The join runs `innerunique`, so `Failures` is not a count of failed sign-ins",
      "evidence": "| join AppExceptions on $left.UserPrincipalName == $right.user_UPN",
      "why": "With no `kind=` the flavour is `innerunique`, which de-duplicates the LEFT side on the join key before joining. One arbitrary `SigninLogs` row survives per `UserPrincipalName`, and that single row is then paired with every matching `AppExceptions` row. So `Failures = count()` counts exception rows, not sign-in failures; every user collapses into the one hour bin belonging to whichever row survived; and `Countries = dcount(Country)` can only ever return 1, because only one left row is left to count countries from. This is the mismatch with the identity team.",
      "fix": "Say the flavour out loud, and make it a membership test rather than a pairing - \"is this user also throwing exceptions\" is a semi-join. `kind=leftsemi` keeps each left row exactly once and returns left columns only, so `count()` goes back to counting failed sign-ins. Bound and narrow the right side inside the parentheses. The numbers will change; the new ones are the ones the header comment asks for.",
      "fixed_fragment": "| join kind=leftsemi (\n    AppExceptions\n    | where TimeGenerated > ago(lookback)\n    | distinct user_UPN\n) on $left.UserPrincipalName == $right.user_UPN"
    },
    "\u2026 4 more"
  ],
  "coverage_check": [
    {
      "flag_id": "late-time-filter",
      "status": "confirmed",
      "note": "KQ-001. Agreed, and worse than \"not first\": stage 6 sits after the join, so it cannot bound the `AppExceptions` side at all."
    },
    {
      "flag_id": "dynamic-before-filter",
      "status": "confirmed",
      "note": "KQ-003. Two costs on one stage - it runs before any row reduction, and `parse_json(tostring(...))` re-parses a value that is already dynamic."
    },
    "\u2026 5 more"
  ],
  "quick_wins": [
    "Move `| where TimeGenerated > ago(lookback)` up to sit directly under `SigninLogs`, and delete it from where it is now.",
    "Write `kind=leftsemi` on the join. Today it is `innerunique`, which is why the counts do not match.",
    "\u2026 3 more"
  ],
  "summary": "This reads `SigninLogs` and `AppExceptions` over full retention: the only time bound is at stage 6, after the join, and it cannot reach the right-hand table. The join has no `kind=`, so it runs `innerunique`, which de-duplicates `SigninLogs` to one row per user before pairing - `Failures` is therefore a count of exception rows rather than of failed sign-ins, and `dcount(Country)` is always 1, which is exactly the disagreement with the identity team. Move the time filter under the table, make the join `kind=leftsemi` over a time-bounded `distinct user_UPN`, and project to the four columns the summarize reads before re-running."
}

optimize

{
  "lane": "optimize",
  "verdict": "rewrite",
  "headline": "The join carries no kind=, so it runs as innerunique and de-duplicates SigninLogs on UserPrincipalName before anything is counted - that, not the late time filter, is why Failures disagrees with the identity team.",
  "rewrite": "// On-call: which users are failing sign-in against the payments app,\n// and are those users also throwing exceptions in the app itself?\nlet lookback = 30d;\nSigninLogs\n| where TimeGenerated > ago(lookback)\n| where Properties has \"payments\"\n| where ResultType != 0\n| extend AppName = tostring(parse_json(tostring(Properties)).appDisplayName)\n| where AppName has \"payments\"\n| extend Country = tostring(LocationDetails.countryOrRegion)\n| project TimeGenerated, UserPrincipalName, AppName, Country\n| join kind=leftsemi (\n    AppExceptions\n    | where TimeGenerated > ago(lookback)\n    | distinct user_UPN\n  ) on $left.UserPrincipalName == $right.user_UPN\n| summarize Failures = count(), Countries = dcount(Country)\n    by UserPrincipalName, AppName, bin(TimeGenerated, 1h)\n| top 200 by Failures desc",
  "rewrite_notes": [
    {
      "change": "`| where TimeGenerated > ago(lookback)` is now the first stage after `SigninLogs`, moved up from stage 6 where it sat behind two `extend` stages and the join.",
      "buys": "The 30d bound now decides what is read instead of what survives. Every later stage - the JSON parse, the join, the aggregation - is multiplied by a row set that has already been cut to the window."
    },
    {
      "change": "Added `| where Properties has \"payments\"` on the raw column, before `parse_json` runs.",
      "buys": "`has` is a term predicate on a base column, so the term index does the first cut and `parse_json` is only paid for on rows that could match. This is a superset filter - 'payments' anywhere in `Properties` passes it - which is why the exact predicate is re-applied after the parse. It matches whole terms, so an app named 'PaymentsPortal' with no separator would be dropped here where the original kept it; if that is possible, use `contains \"payments\"` in this stage instead."
    },
    "\u2026 7 more"
  ],
  "coverage_check": [
    {
      "flag_id": "late-time-filter",
      "status": "confirmed",
      "note": "Agreed and it is the first thing the rewrite fixes: the filter is now the stage directly after `SigninLogs`, and the right side of the join gets the same bound, which the original never had."
    },
    {
      "flag_id": "dynamic-before-filter",
      "status": "confirmed",
      "note": "Agreed. `parse_json(tostring(Properties))` ran on every row of the unbounded read. In the rewrite it runs after the time bound, the `Properties has \"payments\"` term filter and `ResultType != 0`; the `LocationDetails` extend moved behind all filters too."
    },
    "\u2026 5 more"
  ],
  "summary": "This query reads `SigninLogs` with nothing bounding it, parses JSON on every row, then applies its 30d filter six stages in - after the join. That explains the wait. It does not explain the wrong numbers: the join names no `kind=`, so it runs as `innerunique`, de-duplicating the left side on `UserPrincipalName` before `count()` ever sees it, which makes `Failures` a count of exception rows per user rather than failed sign-ins. The rewrite moves the window and the term filters to the front, prunes to the four columns the aggregation reads, and states `kind=leftsemi` so the exception side decides who is included without touching the count - same output columns, same hourly grain, numbers that now match their names."
}

explain

{
  "lane": "explain",
  "verdict": "explained",
  "headline": "One row is one user, one app name and one hour, and its Failures number counts joined sign-in/exception pairs rather than sign-in failures; the whole result is every such user-app-hour bucket in the last 30d, biggest first, with no cap on how many rows come back.",
  "stages": [
    {
      "n": 0,
      "operator": "source",
      "purpose": "Names the table the pipeline reads: every sign-in record in the workspace.",
      "reads": "SigninLogs, all columns",
      "note": "The two comment lines and let lookback = 30d; above this are not pipeline stages. The let only binds a value, and that value is not used until stage 6, so nothing here restricts what is read."
    },
    {
      "n": 1,
      "operator": "extend",
      "purpose": "Lifts the application's display name out of the sign-in property bag into a column called AppName, so the next stages can filter and group on it.",
      "reads": "Properties; produces AppName",
      "note": "This runs on every row of an as-yet unbounded read, so the property bag is parsed for rows the query is about to throw away. tostring(Properties) then parse_json(...) also round-trips the value through a string; if Properties is already dynamic, Properties.appDisplayName reaches the same field without that detour."
    },
    "\u2026 7 more"
  ],
  "assumptions": [
    "No schema was pasted, so every column type is taken on faith from Log Analytics convention: SigninLogs carries TimeGenerated, UserPrincipalName, ResultType, Properties and LocationDetails, and AppExceptions carries user_UPN.",
    "Properties is assumed to be the sign-in property bag that holds appDisplayName, and LocationDetails the bag that holds countryOrRegion.",
    "\u2026 6 more"
  ],
  "open_questions": [
    "Is Failures meant to be the number of failed sign-ins, the number of exceptions, or the number of users? As written it is none of those - see stage 5 and stage 7.",
    "Should the join be kind=inner, so every failed sign-in is kept and paired with every matching exception, or kind=innerunique kept deliberately for a one-row-per-user view?",
    "\u2026 7 more"
  ],
  "summary": "This returns one row per user, per app display name, per hour for the last 30d, covering users who both failed a sign-in against an app whose name contains payments and appear in AppExceptions. The number in Failures is not the failure count: the join at stage 5 states no kind=, so the default innerunique keeps one arbitrary sign-in row per user before counting, and count() then counts surviving sign-in/exception pairs - which is why the figures disagree with the identity team's. Two further things a reader should know before touching it: the time bound is the sixth stage and never reaches the AppExceptions side, and the final sort has no bound, so the number of rows returned is whatever the client truncates at."
}

Error codes