Lookup Sources

Synopsis

A lookup source is a recipe that generates a lookup table's CSV data on a schedule instead of it being uploaded or entered by hand. The recipe describes where the data comes from — an operator-installed script, a SQL query, or an HTTP endpoint — and the director refreshes the CSV on the recipe's schedule, publishing it into the lookup library under the recipe's name.

Pipelines consume the generated table exactly like a static one, by name:

- lookup:
    lookup_file: tenant_lookup

A recipe is a YAML file. Its lookup name comes from the file name (tenant_lookup.yml produces the tenant_lookup lookup), never from a field inside the file. For the static lookup tables this feature complements — uploaded or hand-entered CSV managed through the web interface — see Library. For how a pipeline reads a lookup table, see the Lookup and Enrich processors.

How It Works

Each director runs its own lookup-source refresh loop, because the lookup library is held per node in memory. The loop re-evaluates every recipe on a fixed cadence (every 15 seconds) and, for each recipe that is due, fetches fresh data and writes the resulting CSV into the lookup library. Ingester workers reload their per-core caches on the next event.

A recipe is due when it is first loaded, when its file changes, or when its schedule fires. A recipe with no schedule refreshes only at startup and whenever its file changes.

The refresh is designed to never leave a working lookup empty:

Last-good retention — if a fetch fails or returns no rows, the previously generated CSV stays in place. The library is overwritten only on a fully successful fetch.
Failure backoff — after a failure, the recipe is retried on a 5-minute backoff (independent of its schedule), so a transient outage recovers without a restart while a persistently broken source does not hammer its backend.
Result guard — a fetch must return a header row plus at least one data row; an empty or header-only result is treated as a failure and the last-good copy is kept.

A recipe whose file name contains example is skipped by the config loader, so a shipped sample recipe stays inert until copied to a real name.

The platform refuses an invalid schedule when a recipe is saved (see Platform API), but a recipe saved before that check can still have one. The director still loads such a recipe, and does not fetch it on a schedule until the schedule is fixed. It still fetches it when the director starts, when the recipe or its secrets change, and to retry a failed fetch. It logs Invalid schedule for lookup source ... when it first sees the recipe, and again every 5 minutes. To fix the recipe, save it with a valid schedule.

Delivering a Recipe

A recipe reaches a director in one of two ways:

Locally — place the file at library/lookup/source/<name>.yml under the node's configuration.
Through the platform — create a lookup with the source format (see Platform API). The recipe ships to directors inside the config bundle as the same library/lookup/source/<name>.yml, delivered only to directors whose objects reference the lookup.

Either way, the file the director loads is identical, and pipelines reference the generated lookup by name.

Common Fields

Every recipe declares its source type and, optionally, a schedule and a timeout.

Field	Required	Default	Description
`type`	Y	-	Source kind: `command`, `sql`, or `http`
`schedule`	N	-	Cron expression for when to refresh: five fields as standard cron, or six or seven fields as Quartz writes them, starting with the second and optionally ending with the year, with Sunday counted as 1. Omit to refresh only at startup and on recipe change
`timeout`	N	`60`	Fetch timeout in seconds

The remaining fields depend on the type, and are covered in the sections below. Cron syntax is the same as elsewhere in the product; see Cron for the expression format, special characters, and time-zone handling.

Command Recipes

A command recipe runs a script already installed on the node and uses its output as the lookup data. This replaces a legacy "generate a CSV with a cron script" workflow: the script's standard output becomes the lookup table.

type: command
schedule: "*/15 * * * *"
timeout: 60
command: generate-lookup.sh
interpreter: sh

Field	Required	Default	Description
`command`	Y	-	Script name to run (not a command line). Resolved against the node's scripts directories
`interpreter`	N	OS default	How to run the script: `sh`, `ps1`, or `exe`. Defaults to `ps1` on Windows and `sh` elsewhere

The script's standard output is used directly when it is CSV. If the script emits JSON, add a JSON response mapping.

Script Resolution and Security

Because a recipe can arrive through shipped configuration, command is a script name, never an arbitrary command line. The name is resolved against two roots, in order:

<package>/scripts — product-shipped scripts, replaced on every install or upgrade.
<user>/scripts — operator-installed scripts, preserved across upgrades.

Put your own scripts in <user>/scripts. A name that resolves outside these roots — an absolute path, or one using .. to traverse upward — is refused, as is a name that matches no file. The script runs directly through its interpreter, never through a shell, so a recipe cannot smuggle in pipes or argument injection.

The interpreter invokes the resolved script as follows:

`interpreter`	Invocation
`sh`	`/bin/sh <script>`
`ps1`	`powershell -NoProfile -NonInteractive -ExecutionPolicy Bypass -File <script>`
`exe`	the script is run directly, with no arguments

SQL Recipes

A sql recipe runs a query and turns the result set into the lookup table. The query's result column names become the CSV header row.

type: sql
schedule: "0 * * * *"
driver: postgres
dsn: "$secret{store=vault-prod,ref=lookup/pg_dsn}"
query: 'SELECT virtualmetric_tenantid AS "VirtualMetric_TenantID", tenantid FROM tenants'

Field	Required	Default	Description
`driver`	Y	-	Database driver: `postgres`, `mysql`, `sqlserver`, `clickhouse`, or `oracle`. See Supported Drivers for the others
`query`	Y	-	`SELECT` statement; its result columns become the CSV columns
`dsn`	Y*	-	Connection string. Accepts plain text, `${ENV}` references, or `$secret{...}` vault tokens
`credential`	Y*	-	A credential reference used as the full DSN, as an alternative to `dsn`

* Provide either dsn or credential. credential is valid only on sql recipes; setting it on any other type is rejected.

Supported Drivers

These are the values driver accepts. They are case-sensitive.

Database	`driver`	Notes
PostgreSQL	`postgres`	Offered in the console
MySQL	`mysql`	Offered in the console
Microsoft SQL Server	`sqlserver`	Offered in the console
ClickHouse	`clickhouse`	Offered in the console
Oracle	`oracle`	Offered in the console
PostgreSQL	`pgx`, `pgx/v5`	Also supported. The console does not offer them
Microsoft SQL Server	`mssql`	Also supported. The console does not offer it; use `sqlserver` instead

The platform refuses any other driver when the recipe is saved, with LOOKUP_INVALID_RECIPE and a message that lists the supported drivers (see Error Codes). This catches a typo such as postgress at once, instead of letting every fetch fail on the director and leaving the lookup table empty.

The dsn format depends on the driver: each takes its own connection string, such as postgres://user@host/db for PostgreSQL or sqlserver://user@host?database=db for SQL Server.

HTTP Recipes

An http recipe calls an endpoint and uses the response as the lookup data. A CSV response is used directly; a JSON response is mapped with a JSON response mapping.

type: http
schedule: "0 */6 * * *"
url: "https://internal.example/api/tenants"
method: GET
headers:
  Authorization: "$secret{store=vault-prod,ref=lookup/api_token}"
records_path: "data.items"
columns:
  - { path: "id", name: "VirtualMetric_TenantID" }
  - { path: "tenant.name", name: "tenantid" }

Field	Required	Default	Description
`url`	Y	-	Endpoint to request
`method`	N	`GET`	HTTP method
`headers`	N	-	Request headers. Each value accepts plain text, `${ENV}`, or `$secret{...}` tokens

A response status in the 200–299 range is required; anything else is a failure and the last-good copy is kept. The request is sent with no body. HTTP authentication is expressed through headers (there is no credential field for HTTP).

JSON Response Mapping

An http response or a command script's output that is JSON must be mapped to columns. A payload whose first non-whitespace character is { or [ is treated as JSON; anything else is parsed as CSV. A JSON payload with no columns mapping is an error.

Field	Required	Default	Description
`records_path`	N	root	Dot path to the array of record objects. Omit to use the payload root
`columns`	Y*	-	Ordered list of `{ path, name }` mappings

* columns is required to map a JSON payload. Each entry needs both path (a dot path within a record) and name (the output CSV header). Paths address nested object fields only — array indexing is not supported; records_path selects the array.

The header row is the name values in order. For each record, a missing field yields an empty cell rather than an error. Scalars render predictably: booleans as true/false, integers without a trailing .0, and null as an empty cell.

Credential Resolution

Secret-bearing fields — the SQL dsn (or credential) and every HTTP header value — are resolved on the director exactly as device and target credentials are. Each accepts:

Plain text — used as-is.
${ENV} references — replaced with the environment variable's value; an unset variable is an error.
$secret{...} tokens — resolved through the vault. Both the $secret{store=<name>,ref=<value>} and $secret{id=<numeric>} forms are supported.

See Vault for the token forms and credential stores. Command recipes have no secret-bearing fields.

Limits and Behavior

Payload cap — a single fetch (command output, SQL result, or HTTP body) is capped at 10 MB, matching the lookup library's own storage limit. A larger payload is rejected and the last-good copy is kept.
Last-good retention — any failure at parse, credential resolution, script resolution, or fetch leaves the previously generated CSV in place.
Result guard — a successful fetch must contain a header and at least one data row.

Platform API

Recipes are managed through the lookup library API, which serves both static CSV lookups and source recipes. The format field selects between them.

Method	Path	Purpose
`POST`	`/api/library/lookup`	Create a lookup (JSON body)
`PUT`	`/api/library/lookup/{id}`	Update a lookup (JSON body)
`POST`	`/api/library/lookup/upload`	Create a lookup from an uploaded file (multipart)
`PUT`	`/api/library/lookup/{id}/upload`	Update a lookup from an uploaded file (multipart)

The JSON body (LookupRequest) and the multipart form share these fields:

Field	Required	Default	Description
`name`	Y	-	Lookup name (the key pipelines reference)
`description`	N	-	Optional description
`storageType`	Y	-	`memory` or `disk`
`format`	N	`csv`	`csv` for static data, or `source` for a recipe. Omitting it on update keeps the stored format
`text`	Y	-	The CSV data (`csv`) or the recipe YAML (`source`). JSON body only

File uploads carry format as a form field and the content as file. The uploaded file's extension must match the format: .csv for csv, and .yml or .yaml for source.

When format is source, the recipe is validated at save time with the same parser the director runs, so a recipe that saves is a recipe that loads. A malformed recipe is rejected with the parse detail. The platform also refuses a sql recipe whose driver is not one of the supported drivers, and a schedule that is not a valid cron expression. It checks the schedule with the director's own cron parser:

`schedule`	Result when the recipe is saved
Left out, or empty	Saved. The director fetches the lookup when it starts and when the recipe changes
Five fields, such as `/15 * * *`	Saved
Six or seven fields, in Quartz form	Saved
Not a cron expression the parser accepts, such as `nightly`, `/15 `, or `61 * * *`	Refused
Seven fields whose year is in the past	Refused, because the schedule would never fire again

These checks apply whenever a recipe is created or updated: in the console, through the API, in a Bicep template, or with PowerShell. A recipe saved before them keeps running unchanged, but its next save is checked, so a recipe with an invalid schedule cannot be updated until the schedule is fixed.

Error Codes

Status	Code	Cause
400	`INVALID_REQUEST`	Malformed request
400	`LOOKUP_INVALID_RECIPE`	A `source` recipe failed validation (parse detail included), including an unsupported SQL `driver` or an invalid `schedule`; also an unsupported `format` value
400	`LOOKUP_INVALID_FILE_TYPE`	Uploaded file extension does not match the format
409	`NAME_ALREADY_USE`	A lookup with that name, or the same name once spaces are removed, already exists
413	`LOOKUP_FILE_TOO_LARGE`	Content exceeds 10 MB
422	`LOOKUP_STORAGE_LIMIT_EXCEEDED`	Tenant lookup storage (100 MB) would be exceeded

Examples

Command Source Producing CSV

Refresh a lookup every 15 minutes from an operator-installed script...	`type: command schedule: "/15 * * *" command: generate-lookup.sh`
where the script (in `<user>/scripts`) prints CSV to stdout...	`VirtualMetric_TenantID,tenantid a1b2c3,acme d4e5f6,globex`

SQL Source with a Vault DSN

Rebuild the table hourly from a Postgres query, resolving the DSN from the vault...

type: sql
schedule: "0 * * * *"
driver: postgres
dsn: "$secret{store=vault-prod,ref=lookup/pg_dsn}"
query: 'SELECT virtualmetric_tenantid AS "VirtualMetric_TenantID", tenantid FROM tenants'

the query's column names become the CSV header...

VirtualMetric_TenantID,tenantid
a1b2c3,acme
d4e5f6,globex

HTTP Source Mapping JSON

Pull tenants from an internal API every six hours, mapping JSON records to columns...

type: http
schedule: "0 */6 * * *"
url: "https://internal.example/api/tenants"
headers:
  Authorization: "$secret{store=vault-prod,ref=lookup/api_token}"
records_path: "data.items"
columns:
  - { path: "id", name: "VirtualMetric_TenantID" }
  - { path: "tenant.name", name: "tenantid" }

given a response body like this...

{
  "data": {
    "items": [
      { "id": "a1b2c3", "tenant": { "name": "acme" } },
      { "id": "d4e5f6", "tenant": { "name": "globex" } }
    ]
  }
}

Consuming the Generated Lookup

A pipeline references the generated lookup by name, exactly like a static one...

- lookup:
    lookup_file: tenant_lookup
    match_mode: exact
    match_type: first
    lookup_fields:
      - event_field: VirtualMetric_TenantID
        lookup_field: VirtualMetric_TenantID
    output_fields:
      - lookup_field: tenantid
        event_field: tenant.name

the enrichment stays current as the source refreshes on its schedule...

{
  "VirtualMetric_TenantID": "a1b2c3",
  "tenant": { "name": "acme" }
}

Synopsis​

How It Works​

Delivering a Recipe​

Common Fields​

Command Recipes​

Script Resolution and Security​

SQL Recipes​

Supported Drivers​

HTTP Recipes​

JSON Response Mapping​

Credential Resolution​

Limits and Behavior​

Platform API​

Error Codes​

Examples​

Command Source Producing CSV​

SQL Source with a Vault DSN​

HTTP Source Mapping JSON​

Consuming the Generated Lookup​