REFERENCE

Maintaining column relationships

The relationship explorer is powered by a small JSON file that people maintain by hand: column-relations.json. Schema and module updates leave it alone. To add or update a connection, edit static/data/column-relations.json in the repository and submit a pull request. The site checks the file before it is published.

A mapping

Each connection needs a stable id, a title, a short description, and two endpoints: from and to. Add tags so people can filter the graph, and use guidance to explain when the join is useful. You can also include a kql example for a more specific query. The explorer creates a basic inner join as well, so people can compare your example with a starting point.

Use the optional otherNotes list for related context that people should see beside the connection. Each note has a title, text, sourceLabel, sourceUrl (HTTPS), and query. The explorer shows the source link and lets readers copy the query. Notes are extra context; they don’t change the fields or join generated for the relationship.

{
  "id": "entra-signin-graph-token",
  "title": "Follow a sign-in token into Microsoft Graph activity",
  "description": "Correlate sign-in and Graph API audit events using the token identifier.",
  "from": {
    "table": "EntraIdSignInEvents",
    "column": "UniqueTokenId",
    "timeColumn": "Timestamp"
  },
  "to": {
    "table": "GraphAPIAuditEvents",
    "column": "UniqueTokenIdentifier",
    "timeColumn": "Timestamp"
  },
  "tags": ["Identity", "Token correlation"],
  "guidance": "A token can appear in multiple events; inspect the context on both sides."
}

Add each connection to the top-level relations array, and leave version set to 1. Give every connection a unique ID made from lowercase words separated by hyphens. Table and column names must use the same capitalization as the schema. If you add a timeColumn, the suggested query will use it for the selected look-back window. Leave it out when that table has no suitable time column; the query won’t add a time filter for that side.

Nested JSON paths

For a regular column, set column and leave out path. If the value you want is inside JSON stored in a column, use path for the properties within that value. These examples show the path syntax only; they don’t claim that the fields exist in your tenant:

For example, column: "AdditionalFields" and path: "user.id" will appear as AdditionalFields.user.id. Start the path inside the column; don’t repeat the column name.

The suggested KQL uses parse_json to read JSON, mv-expand for each [] array marker, and string values for the join keys. It skips empty keys. Expanding arrays or matching duplicate keys can return multiple rows. The table schema can’t confirm nested property names, so check them against real events. Add a curated kql example if the join needs extra conditions, consistent casing, tenant filters, or a more specific time range.

Validation and stale fields

The JSON schema can provide suggestions in your editor. Run node scripts/validate_relations.cjs to check the file on your computer. The check catches malformed entries, repeated IDs, invalid paths, and connections that point a field back to itself. If a table or column no longer appears in the current schema, the check gives a warning. The connection stays in the explorer in case it’s still useful for older data.

To share a particular view, copy the page URL after applying filters. It keeps the selected table, tag, field type, search, and connection, so someone else can open the same view.