azure-kusto-graph

# Kusto Graph Semantics Build transient and persistent graphs from tabular data using KQL graph operators. This skill translates natural language into the edges-first graph construction pattern and graph query operators. ## Activation Triggers Use this skill when the user: - Wants to build a graph from tabular data (`make-graph`) - Asks to find patterns, paths, or relationships in data - Mentions `graph-match`, `graph-shortest-paths`, `graph-to-table`, `graph-mark-components` - Wants to create a persistent graph model or snapshot - Says "build a graph", "find the shortest path", "find connected components", "show relationships" - Asks about transient vs persistent graphs **Not a natural-language-to-KQL converter.** The input should generally be a working KQL query whose results the user wants converted to a graph, plus a natural-language description of the desired graph structure. Basic NL source requests are supported only when they map directly to a known table with obvious columns. For general NL-to-KQL conversion, use a dedicated query-generation skill (available separately). **Complementary skills:** - `azure-kusto-irql` -- composable security query primitives that produce the tabular inputs for graphs - `azure-kusto-irql-graph` -- IRQL's `Lift_To_Graph` JSON mapping system for richly-typed, icon-decorated graphs in Kusto Explorer ## The Edges-First Approach The fundamental pattern for building graphs in Kusto: ``` 1. Define your EDGES -> src --> dest, with relationship type/properties 2. Define your NODE LOOKUPS -> display names, types, properties for each node ID 3. Union edge types -> if you have multiple relationship types 4. Union node lookups -> if you have multiple node types 5. Call make-graph -> edges | make-graph Source --> Target with nodes on nodeId ``` This is how to think in `make-graph`. Edges are the relationships you care about. Nodes are lookup tables that give those IDs a face -- display names, types, properties. ## Graph Operators Reference ### `make-graph` -- Build a graph from tables ```kql Edges | make-graph SourceId --> TargetId with Nodes on NodeId ``` - `Edges`: tabular source where each row is an edge - `SourceId --> TargetId`: columns containing source and target node IDs - `with Nodes on NodeId`: optional node property table joined by ID - Supports multiple node tables: `with Nodes1 on Id1, Nodes2 on Id2` - Nodes appearing in edges but missing from the node table get empty properties ### `graph-match` -- Find patterns ```kql G | graph-match (a)-[e]->(b) where <constraints> project <output> ``` Pattern notation: | Element | Named | Anonymous | |---|---|---| | Node | `(n)` | `()` | | Edge left->right | `-[e]->` | `-->` | | Edge right->left | `<-[e]-` | `<--` | | Any direction | `-[e]-` | `--` | | Variable length | `-[e*1..5]->` | `-[*1..5]->` | Multi-hop patterns: `(a)-[e1]->(b)-[e2]->(c)` Star patterns: `(a)--(center)--(b), (c)--(center)--(d)` Cycles control: `cycles = all | none | unique_edges` (default: `unique_edges`) ### `graph-shortest-paths` -- Find shortest paths ```kql G | graph-shortest-paths (start)-[e*1..20]->(end) where start.name == "Alice" and end.name == "Server01" project Path = e, Length = array_length(e) ``` - Requires at least one variable-length edge - `output = any` (default, one path per pair) or `output = all` (all equal-length shortest paths) - Variable-length edge properties returned as dynamic arrays ### `graph-to-table` -- Export graph to tables ```kql G | graph-to-table nodes // export nodes G | graph-to-table edges // export edges G | graph-to-table nodes as N, edges as E // export both G | graph-to-table nodes with_node_id=Id // include node hash ID G | graph-to-table edges with_source_id=Src with_target_id=Tgt // include edge endpoint IDs ``` ### `graph-mark-components` -- Find connected components ```kql G | graph-mark-components with_component_id=ComponentId | graph-to-table nodes | summarize Members = make_list(name) by ComponentId ``` Assigns a `ComponentId` to each node. Nodes in the same connected component share the same ID. ### `graph()` function -- Query persistent graphs ```kql graph("MyGraphModel") // latest snapshot graph("MyGraphModel", "Snapshot_2025_01") // specific snapshot graph("MyGraphModel", true) // transient from model definition ``` ## Transient Graphs Created dynamically during query execution. No setup required. Ideal for ad-hoc analysis, exploration, and prototyping. ### Template: Basic two-entity graph ```kql // 1. Define edges let edges = <SourceTable> | summarize <aggregations> by SourceCol, TargetCol; // 2. Define node lookups let source_nodes = edges | distinct SourceCol | project nodeId = SourceCol, label = SourceCol, nodeType = "<SourceType>"; let target_nodes = edges | distinct TargetCol | project nodeId = TargetCol, label = TargetCol, nodeType = "<TargetType>"; let all_nodes = union source_nodes, target_nodes; // 3. Build and query the graph edges | make-graph SourceCol --> TargetCol with all_nodes on nodeId | graph-match (s)-[e]->(t) where <constraints> project Source = s.label, Target = t.label, <edge properties> ``` ### Template: Multi-relationship graph ```kql // Multiple edge types -> union them with a common schema let auth_edges = AuthEvents | project Source = username, Target = hostname, edgeType = "authenticates", ts = timestamp; let net_edges = NetworkEvents | project Source = src_ip, Target = url, edgeType = "connects", ts = timestamp; let all_edges = union auth_edges, net_edges; // Node lookups from all sources let user_nodes = Employees | project nodeId = username, label = name, nodeType = "User"; let host_nodes = AuthEvents | distinct hostname | project nodeId = hostname, label = hostname, nodeType = "Host"; let all_nodes = union user_nodes, host_nodes; all_edges | make-graph Source --> Target with all_nodes on nodeId ``` ## Persistent Graphs For large-scale, reusable graphs. Stored in database metadata. Support snapshots for historical comparison. > **Safety:** Creating or altering graph models and snapshots modifies the database. Always show the exact command and confirm with the user before executing `.create-or-alter graph_model` or `.make graph_snapshot`. ### Step 1: Create a graph model ```kql .create-or-alter graph_model SecurityGraph { "Schema": { "Nodes": { "User": {"name": "string", "role": "string"}, "Host": {"hostname": "string"}, "IP": {"ip": "string"} }, "Edges": { "AuthenticatesTo": {"timestamp": "datetime", "result": "string"}, "ConnectsFrom": {"timestamp": "datetime"} } }, "Definition": { "Steps": [ { "Kind": "AddNodes", "Query": "Employees | project name, role", "NodeIdColumn": "name", "Labels": ["User"] }, { "Kind": "AddNodes", "Query": "AuthenticationEvents | distinct hostname | project hostname", "NodeIdColumn": "hostname", "Labels": ["Host"] }, { "Kind": "AddEdges", "Query": "AuthenticationEvents | project username, hostname, timestamp, result", "SourceColumn": "username", "TargetColumn": "hostname", "Labels": ["AuthenticatesTo"] } ] } } ``` ### Step 2: Create a snapshot ```kql .make graph_snapshot SecurityGraph Snapshot_2025_07 ``` ### Step 3: Query the snapshot ```kql graph("SecurityGraph") | graph-match (user)-[auth]->(host) where user.role == "Admin" and auth.result == "Failed Login" project User = user.name, Host = host.hostname, Time = auth.timestamp ``` ### Management commands > **Safety:** All control commands below modify or delete database objects. Never execute `.drop`, `.create-or-alter graph_model`, or `.make graph_snapshot` automatically. Always show the exact command, cluster, database, and affected object, then require explicit user confirmation before execution. ```kql .show graph_models // list all models .show graph_model SecurityGraph // show model details .show graph_snapshots SecurityGraph // list snapshots .drop graph_snapshot SecurityGraph Snapshot_2025_07 // delete a snapshot (CONFIRM FIRST) .drop graph_model SecurityGraph // delete model and all snapshots (CONFIRM FIRST) ``` ## Transient vs Persistent: When to Use Which | Factor | Transient (`make-graph`) | Persistent (`graph()`) | |---|---|---| | Setup | None -- inline in query | Create model + snapshot | | Lifetime | Query execution only | Stored in database metadata | | Data freshness | Always current | Snapshot at creation time | | Scale | Limited by query memory | Enterprise-scale | | Reuse | Rebuilt every query | Shared across users/queries | | Best for | Ad-hoc hunts, prototyping | Production workflows, dashboards | ## Security & Threat Hunting Examples ### Authentication graph: who logged into what from where ```kql let auth_edges = AuthenticationEvents | summarize logins = count(), fails = countif(result == "Failed Login") by src_ip, username, hostname; let ip_nodes = auth_edges | distinct src_ip | project nodeId = src_ip, label = src_ip, nodeType = "IP"; let user_nodes = auth_edges | distinct username | project nodeId = username, label = username, nodeType = "User"; let host_nodes = auth_edges | distinct hostname | project nodeId = hostname, label = hostname, nodeType = "Host"; let all_nodes = union ip_nodes, user_nodes, host_nodes; // IP -> User edges let ip_user = auth_edges | project Source = src_ip, Target = username, logins, fails; // User -> Host edges let user_host = auth_edges | project Source = username, Target = hostname, logins, fails; union ip_user, user_host | make-graph Source --> Target with all_nodes on nodeId | graph-match (ip)-[e1]->(user)-[e2]->(host) where e2.fails > 20 project IP = ip.label, User = user.label, Host = host.label, Failures = e2.fails | order by Failures desc ``` ### Lateral movement detection: users sharing compromised hosts ```kql // Pattern: (user1)-[auth1]->(host)<-[auth2]-(user2) // Two users both failing on the same host = possible credential spray let edges = AuthenticationEvents | summarize fails = countif(result == "Failed Login"), logins = count() by username, hostname; let nodes = union (edges | distinct username | project nodeId = username, nodeType = "User"), (edges | distinct hostname | project nodeId = hostname, nodeType = "Host"); edges | make-graph username --> hostname with nodes on nodeId | graph-match (u1)-[e1]->(h)<-[e2]-(u2) where u1.nodeId != u2.nodeId and e1.fails > 10 and e2.fails > 10 project User1 = u1.nodeId, User2 = u2.nodeId, SharedHost = h.nodeId, User1Fails = e1.fails, User2Fails = e2.fails | distinct User1, SharedHost, User2, User1Fails, User2Fails | order by User1Fails + User2Fails desc ``` ### Shortest attack path ```kql let edges = SecurityEvents | project Source = source_entity, Target = target_entity, action, timestamp; let nodes = union (edges | distinct Source | project nodeId = Source), (edges | distinct Target | project nodeId = Target); edges | make-graph Source --> Target with nodes on nodeId | graph-shortest-paths (start)-[e*1..10]->(end) where start.nodeId == "ExternalIP_1.2.3.4" and end.nodeId == "DatabaseServer" project PathLength = array_length(e), Actions = e.action, Hops = e.Target ``` ### Connected components: find isolated clusters ```kql let edges = NetworkFlows | project Source = src_ip, Target = dst_ip; let nodes = union (edges | distinct Source | project nodeId = Source), (edges | distinct Target | project nodeId = Target); edges | make-graph Source --> Target with nodes on nodeId | graph-mark-components with_component_id = ComponentId | graph-to-table nodes | summarize Members = make_list(nodeId), Size = count() by ComponentId | order by Size desc ``` ### Visualize in Kusto Explorer End a query at `make-graph` (without piping to `graph-match`) to trigger Kusto Explorer's interactive graph visualization window: ```kql edges | make-graph Source --> Target with all_nodes on nodeId // <- stop here. Kusto Explorer renders the graph visually. ``` To flatten back to a table for dashboards or export, pipe through `graph-match | project` or `graph-to-table`. ## Using with IRQL When working with security data, consider using IRQL selectors (`Get_*`) from the `azure-kusto-irql` skill as the data source. IRQL gives you a unified schema without memorizing raw table names or column mappings. For rich visualization with icons and node folding, the `azure-kusto-irql-graph` skill's `Lift_To_Graph` is the faster path. | Approach | Best For | |---|---| | Raw `make-graph` (this skill) | Full control, persistent models, shortest paths, connected components, custom schemas |