Kql

# Query plane — use kusto_query
kusto_query(
    cluster_uri="https://help.kusto.windows.net",
    database="Samples",
    query="StormEvents | summarize count() by EventType | top 5 by count_ desc"
)

# Management plane — use kusto_command
kusto_command(
    cluster_uri="https://help.kusto.windows.net",
    database="Samples",
    command=".show tables"
)

# Schema exploration — use kusto_describe_database or kusto_describe_database_entity
kusto_describe_database(
    cluster_uri="https://help.kusto.windows.net",
    database="Samples"
)

# Sample data — use kusto_sample_entity
kusto_sample_entity(
    cluster_uri="https://help.kusto.windows.net",
    database="Samples",
    entity_name="StormEvents",
    entity_type="table",
    sample_size=5
)

# Graph queries — use kusto_graph_query
kusto_graph_query(
    cluster_uri="https://mycluster.kusto.windows.net",
    database="MyDB",
    graph_name="MyGraph",
    query="| graph-match (node) project labels=labels(node)"
)

# Deeplinks — use kusto_deeplink_from_query
kusto_deeplink_from_query(
    cluster_uri="https://help.kusto.windows.net",
    database="Samples",
    query="StormEvents | count"
)

// ❌ ERROR: "Summarize group key 'Partners' is of a 'dynamic' type"
| summarize count() by Partners

// ✅ FIX
| summarize count() by tostring(Partners)

// ❌ ERROR: "order operator: key can't be of dynamic type"
| order by Area desc

// ✅ FIX
| order by tostring(Area) desc

// ❌ ERROR in join: dynamic join key
| join kind=inner other on $left.Area == $right.Area

// ✅ FIX — cast both sides
| extend Area_str = tostring(Area)
| join kind=inner (other | extend Area_str = tostring(Area)) on Area_str

// ❌ ERROR: "Only equality is allowed in this context"
| join on geo_distance_2points(a.Lat, a.Lon, b.Lat, b.Lon) < 1000

// ✅ WORKAROUND — pre-bucket into spatial cells, then join on cell ID
| extend cell = geo_point_to_s2cell(Lon, Lat, 8)
| join kind=inner (other | extend cell = geo_point_to_s2cell(Lon, Lat, 8)) on cell

// ❌ ERROR: "for each left attribute, right attribute should be selected"
| join kind=inner other on $left.col1

// ✅ FIX — specify both sides explicitly
| join kind=inner other on $left.col1 == $right.col1

// Before joining, check how many rows each side contributes
TableA | summarize dcount(JoinKey)  // → 25,000? Too many for an unconstrained join
TableB | summarize dcount(JoinKey)  // → 195? OK if filtered first

// ❌ ERROR: "extractall(): argument 2 must be a valid regex with [1..16] matching groups"
| extend words = extract_all(@"[a-zA-Z]{3,}", Text)

// ✅ FIX — add parentheses around the pattern
| extend words = extract_all(@"([a-zA-Z]{3,})", Text)

// ❌ ERROR: "Function 'row_cumsum' cannot be invoked. The row set must be serialized."
| summarize Online = sum(Direction) by bin(Timestamp, 5m)
| extend CumulativeOnline = row_cumsum(Online)

// ✅ FIX — add | serialize (or | order by, which implicitly serializes)
| summarize Online = sum(Direction) by bin(Timestamp, 5m)
| order by Timestamp asc
| extend CumulativeOnline = row_cumsum(Online)

Safest ──────────────────────────────────────────────── Most dangerous
| count    | take 10    | where + summarize    | summarize (no filter)    | full scan

// ❌ OUT OF MEMORY — 24M rows, no filter, dcount on every column
Consumption
| summarize dcount(Consumed), count() by Timestamp, HouseholdId, MeterType
| where dcount_Consumed > 1

// ✅ SAFE — filter first, then aggregate
Consumption
| where Timestamp between (datetime(2023-04-15) .. datetime(2023-04-16))
| summarize dcount(Consumed) by HouseholdId, MeterType
| where dcount_Consumed > 1

// ❌ ERROR: "Cannot compare values of types string and string. Try adding explicit casts"
| where geo_point_to_s2cell(Lon, Lat, 16) == other_cell

// ✅ FIX — wrap both sides in tostring()
| where tostring(geo_point_to_s2cell(Lon, Lat, 16)) == tostring(other_cell)

// try it! — cosine similarity on Iris feature vectors
let target = pack_array(5.1, 3.5, 1.4, 0.2);
Iris
| extend Vec = pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth)
| extend sim = series_cosine_similarity(Vec, target)
| top 5 by sim desc

// Distance between two points (meters)
StormEvents | extend dist = geo_distance_2points(BeginLon, BeginLat, EndLon, EndLat)

// Spatial bucketing for joins
StormEvents | extend cell = geo_point_to_s2cell(BeginLon, BeginLat, 8)

// Persistent graph model — query the latest snapshot
graph("Simple")
| graph-match (src)-[e*1..5]->(dst)
  where src.name == "Alice"
  project src.name, dst.name, path_length = array_length(e)

// Transient graph — build inline with make-graph
SimpleGraph_Edges
| make-graph source --> target with SimpleGraph_Nodes on id
| graph-match (src)-[e*1..5]->(dst)
  where src.name == "Alice"
  project src.name, dst.name, path_length = array_length(e)

// Create a time series and detect anomalies
StormEvents
| make-series count() default=0 on StartTime step 1d
| extend anomalies = series_decompose_anomalies(count_)

// ❌ WRONG — bare year is not a valid datetime
| where StartTime > datetime(2007)

// ✅ RIGHT — always use full date format
| where StartTime > datetime(2007-01-01)

// ❌ WRONG — comparing datetime column to integer
| where StartTime == 2007

// ✅ RIGHT — use datetime_part() to extract components
| where datetime_part("year", StartTime) == 2007

// ✅ ALSO RIGHT — use between with datetime range
| where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31T23:59:59))

// This works, but can be harder to read and reuse in complex queries
| summarize count() by startofmonth(StartTime)

// Clearer — extend first, then summarize by the computed column
| extend Month = startofmonth(StartTime)
| summarize count() by Month
| order by Month asc