Lookup join and visualization on that join

Elastic Stack Kibana
, ,
1

Consider the following, I have populated 2 indices, both have a task_id field and I want to join these 2 indices on task_id to create a dashboard in Kibana.

Firstly, I cannot create a data view out of a lookup join.
I can create an ESQL discover session and add that to the dashboard, now if I apply filters on my dashboard which uses the lookup index’s fields, it does not filter. Found a workaround by using variable control for this.

I want to know if it is possible for me to add this variable control to lens visualizations somehow. Or how can I solve this issue without creating an additional denormalized index. Eventually I want to create visualizations based on the documents that will be created after the join, since there are separate fields in separate indices that I want to use in the visualizations.

In a nutshell how do I create visualizations out of a lookup join.

2

Welcome @jai2 to the Elastic forum!

I just tested to create a couple of indices in a 9.3 installation, one with master data and another for metrics (data below). I could create a dashboard entirely based on ES|QL queries without data views and with a couple of controls that are injecting values to the queries on all panels

Peek 2026-03-16 13-08

Controls use a very simple query as FROM tasks_385464 | STATS BY status for the status drop down.

Sharing the table and heatmap queries here for reference

# Table chart
FROM tasks_385464
| LOOKUP JOIN task_metrics_385464 ON task_id
| KEEP task_id, task_name, owner, status, duration_ms, cpu_pct, memory_mb
| WHERE MV_CONTAINS(?status, status)
  AND MV_CONTAINS(?owner, owner)
| SORT duration_ms DESC


# Heatmap chart
FROM tasks_385464
| LOOKUP JOIN task_metrics_385464 ON task_id
| KEEP task_id, task_name, owner, status, duration_ms, cpu_pct, memory_mb
| WHERE MV_CONTAINS(?status, status)
    AND MV_CONTAINS(?owner, owner)
| STATS avg_duration = AVG(duration_ms) BY task_name, owner

I think it is planned to save ES|QL queries with a name (like a database view concept), so users won't need to repeat the query on every panel and centralize the data gathering definition and chart queries will be focused on the final data aggregation or projection to its specific needs.

Does this help?

Data

Copy this to the Dev Console to create the two sample indices.

# ES|QL Lookup Join Sample Data
# Forum: https://discuss.elastic.co/t/lookup-join-and-visualization-on-that-join/385464
#
# Two indices sharing `task_id` as the join key:
#   - tasks_385464         (main index: task metadata)
#   - task_metrics_385464  (lookup index: task performance data)

# ── 1. Create "tasks_385464" index ──────────────────────────────────────────

PUT tasks_385464
{
  "mappings": {
    "properties": {
      "task_id":    { "type": "keyword" },
      "task_name":  { "type": "keyword" },
      "owner":      { "type": "keyword" },
      "status":     { "type": "keyword" },
      "created_at": { "type": "date" }
    }
  }
}

# ── 2. Ingest documents into "tasks_385464" ──────────────────────────────────

POST tasks_385464/_bulk
{ "index": {} }
{ "task_id": "t001", "task_name": "data-export",    "owner": "alice", "status": "completed", "created_at": "2026-01-01T08:00:00Z" }
{ "index": {} }
{ "task_id": "t002", "task_name": "report-gen",     "owner": "bob",   "status": "failed",    "created_at": "2026-01-01T09:00:00Z" }
{ "index": {} }
{ "task_id": "t003", "task_name": "data-sync",      "owner": "alice", "status": "completed", "created_at": "2026-01-01T10:00:00Z" }
{ "index": {} }
{ "task_id": "t004", "task_name": "cleanup-job",    "owner": "carol", "status": "running",   "created_at": "2026-01-01T11:00:00Z" }
{ "index": {} }
{ "task_id": "t005", "task_name": "index-rebuild",  "owner": "bob",   "status": "completed", "created_at": "2026-01-01T12:00:00Z" }
{ "index": {} }
{ "task_id": "t006", "task_name": "log-rotate",     "owner": "carol", "status": "completed", "created_at": "2026-01-02T08:00:00Z" }
{ "index": {} }
{ "task_id": "t007", "task_name": "backup",         "owner": "alice", "status": "failed",    "created_at": "2026-01-02T09:00:00Z" }
{ "index": {} }
{ "task_id": "t008", "task_name": "data-import",    "owner": "dave",  "status": "completed", "created_at": "2026-01-02T10:00:00Z" }
{ "index": {} }
{ "task_id": "t009", "task_name": "cache-warm",     "owner": "dave",  "status": "completed", "created_at": "2026-01-02T11:00:00Z" }
{ "index": {} }
{ "task_id": "t010", "task_name": "schema-migrate", "owner": "bob",   "status": "failed",    "created_at": "2026-01-02T12:00:00Z" }
{ "index": {} }
{ "task_id": "t011", "task_name": "data-export",    "owner": "carol", "status": "completed", "created_at": "2026-01-03T08:00:00Z" }
{ "index": {} }
{ "task_id": "t012", "task_name": "report-gen",     "owner": "alice", "status": "completed", "created_at": "2026-01-03T09:00:00Z" }
{ "index": {} }
{ "task_id": "t013", "task_name": "data-sync",      "owner": "bob",   "status": "running",   "created_at": "2026-01-03T10:00:00Z" }
{ "index": {} }
{ "task_id": "t014", "task_name": "cleanup-job",    "owner": "dave",  "status": "completed", "created_at": "2026-01-03T11:00:00Z" }
{ "index": {} }
{ "task_id": "t015", "task_name": "index-rebuild",  "owner": "alice", "status": "failed",    "created_at": "2026-01-03T12:00:00Z" }
{ "index": {} }
{ "task_id": "t016", "task_name": "log-rotate",     "owner": "bob",   "status": "completed", "created_at": "2026-01-04T08:00:00Z" }
{ "index": {} }
{ "task_id": "t017", "task_name": "backup",         "owner": "carol", "status": "completed", "created_at": "2026-01-04T09:00:00Z" }
{ "index": {} }
{ "task_id": "t018", "task_name": "data-import",    "owner": "alice", "status": "completed", "created_at": "2026-01-04T10:00:00Z" }
{ "index": {} }
{ "task_id": "t019", "task_name": "cache-warm",     "owner": "bob",   "status": "running",   "created_at": "2026-01-04T11:00:00Z" }
{ "index": {} }
{ "task_id": "t020", "task_name": "schema-migrate", "owner": "carol", "status": "completed", "created_at": "2026-01-04T12:00:00Z" }
{ "index": {} }
{ "task_id": "t021", "task_name": "data-export",    "owner": "dave",  "status": "completed", "created_at": "2026-01-05T08:00:00Z" }
{ "index": {} }
{ "task_id": "t022", "task_name": "report-gen",     "owner": "carol", "status": "failed",    "created_at": "2026-01-05T09:00:00Z" }
{ "index": {} }
{ "task_id": "t023", "task_name": "data-sync",      "owner": "dave",  "status": "completed", "created_at": "2026-01-05T10:00:00Z" }
{ "index": {} }
{ "task_id": "t024", "task_name": "cleanup-job",    "owner": "alice", "status": "completed", "created_at": "2026-01-05T11:00:00Z" }
{ "index": {} }
{ "task_id": "t025", "task_name": "index-rebuild",  "owner": "carol", "status": "running",   "created_at": "2026-01-05T12:00:00Z" }
{ "index": {} }
{ "task_id": "t026", "task_name": "log-rotate",     "owner": "dave",  "status": "completed", "created_at": "2026-01-06T08:00:00Z" }
{ "index": {} }
{ "task_id": "t027", "task_name": "backup",         "owner": "bob",   "status": "completed", "created_at": "2026-01-06T09:00:00Z" }
{ "index": {} }
{ "task_id": "t028", "task_name": "data-import",    "owner": "carol", "status": "failed",    "created_at": "2026-01-06T10:00:00Z" }
{ "index": {} }
{ "task_id": "t029", "task_name": "cache-warm",     "owner": "alice", "status": "completed", "created_at": "2026-01-06T11:00:00Z" }
{ "index": {} }
{ "task_id": "t030", "task_name": "schema-migrate", "owner": "dave",  "status": "completed", "created_at": "2026-01-06T12:00:00Z" }
{ "index": {} }
{ "task_id": "t031", "task_name": "data-export",    "owner": "bob",   "status": "completed", "created_at": "2026-01-07T08:00:00Z" }
{ "index": {} }
{ "task_id": "t032", "task_name": "report-gen",     "owner": "dave",  "status": "completed", "created_at": "2026-01-07T09:00:00Z" }
{ "index": {} }
{ "task_id": "t033", "task_name": "data-sync",      "owner": "carol", "status": "failed",    "created_at": "2026-01-07T10:00:00Z" }
{ "index": {} }
{ "task_id": "t034", "task_name": "cleanup-job",    "owner": "bob",   "status": "completed", "created_at": "2026-01-07T11:00:00Z" }
{ "index": {} }
{ "task_id": "t035", "task_name": "index-rebuild",  "owner": "dave",  "status": "completed", "created_at": "2026-01-07T12:00:00Z" }
{ "index": {} }
{ "task_id": "t036", "task_name": "log-rotate",     "owner": "alice", "status": "running",   "created_at": "2026-01-08T08:00:00Z" }
{ "index": {} }
{ "task_id": "t037", "task_name": "backup",         "owner": "dave",  "status": "completed", "created_at": "2026-01-08T09:00:00Z" }
{ "index": {} }
{ "task_id": "t038", "task_name": "data-import",    "owner": "bob",   "status": "completed", "created_at": "2026-01-08T10:00:00Z" }
{ "index": {} }
{ "task_id": "t039", "task_name": "cache-warm",     "owner": "carol", "status": "completed", "created_at": "2026-01-08T11:00:00Z" }
{ "index": {} }
{ "task_id": "t040", "task_name": "schema-migrate", "owner": "alice", "status": "failed",    "created_at": "2026-01-08T12:00:00Z" }
{ "index": {} }
{ "task_id": "t041", "task_name": "data-export",    "owner": "carol", "status": "completed", "created_at": "2026-01-09T08:00:00Z" }
{ "index": {} }
{ "task_id": "t042", "task_name": "report-gen",     "owner": "alice", "status": "running",   "created_at": "2026-01-09T09:00:00Z" }
{ "index": {} }
{ "task_id": "t043", "task_name": "data-sync",      "owner": "bob",   "status": "completed", "created_at": "2026-01-09T10:00:00Z" }
{ "index": {} }
{ "task_id": "t044", "task_name": "cleanup-job",    "owner": "dave",  "status": "completed", "created_at": "2026-01-09T11:00:00Z" }
{ "index": {} }
{ "task_id": "t045", "task_name": "index-rebuild",  "owner": "carol", "status": "completed", "created_at": "2026-01-09T12:00:00Z" }
{ "index": {} }
{ "task_id": "t046", "task_name": "log-rotate",     "owner": "bob",   "status": "failed",    "created_at": "2026-01-10T08:00:00Z" }
{ "index": {} }
{ "task_id": "t047", "task_name": "backup",         "owner": "alice", "status": "completed", "created_at": "2026-01-10T09:00:00Z" }
{ "index": {} }
{ "task_id": "t048", "task_name": "data-import",    "owner": "carol", "status": "completed", "created_at": "2026-01-10T10:00:00Z" }
{ "index": {} }
{ "task_id": "t049", "task_name": "cache-warm",     "owner": "dave",  "status": "running",   "created_at": "2026-01-10T11:00:00Z" }
{ "index": {} }
{ "task_id": "t050", "task_name": "schema-migrate", "owner": "bob",   "status": "completed", "created_at": "2026-01-10T12:00:00Z" }

# ── 3. Create "task_metrics_385464" index ────────────────────────────────────

PUT task_metrics_385464
{
  "settings": {
    "index.mode": "lookup"
  },
  "mappings": {
    "properties": {
      "task_id":     { "type": "keyword" },
      "duration_ms": { "type": "long" },
      "cpu_pct":     { "type": "float" },
      "memory_mb":   { "type": "integer" }
    }
  }
}

# ── 4. Ingest documents into "task_metrics_385464" ───────────────────────────

POST task_metrics_385464/_bulk
{ "index": {} }
{ "task_id": "t001", "duration_ms":  1200, "cpu_pct": 45.2, "memory_mb":  512 }
{ "index": {} }
{ "task_id": "t002", "duration_ms":  3400, "cpu_pct": 88.7, "memory_mb": 1024 }
{ "index": {} }
{ "task_id": "t003", "duration_ms":   900, "cpu_pct": 30.1, "memory_mb":  256 }
{ "index": {} }
{ "task_id": "t004", "duration_ms":  5100, "cpu_pct": 72.4, "memory_mb":  768 }
{ "index": {} }
{ "task_id": "t005", "duration_ms":  2200, "cpu_pct": 55.0, "memory_mb":  640 }
{ "index": {} }
{ "task_id": "t006", "duration_ms":   780, "cpu_pct": 22.3, "memory_mb":  128 }
{ "index": {} }
{ "task_id": "t007", "duration_ms":  6700, "cpu_pct": 91.5, "memory_mb": 2048 }
{ "index": {} }
{ "task_id": "t008", "duration_ms":  1500, "cpu_pct": 48.0, "memory_mb":  384 }
{ "index": {} }
{ "task_id": "t009", "duration_ms":   430, "cpu_pct": 15.6, "memory_mb":  192 }
{ "index": {} }
{ "task_id": "t010", "duration_ms":  8200, "cpu_pct": 95.2, "memory_mb": 1536 }
{ "index": {} }
{ "task_id": "t011", "duration_ms":  1100, "cpu_pct": 41.8, "memory_mb":  448 }
{ "index": {} }
{ "task_id": "t012", "duration_ms":  2900, "cpu_pct": 67.3, "memory_mb":  896 }
{ "index": {} }
{ "task_id": "t013", "duration_ms":  4400, "cpu_pct": 79.1, "memory_mb":  832 }
{ "index": {} }
{ "task_id": "t014", "duration_ms":   650, "cpu_pct": 19.4, "memory_mb":  160 }
{ "index": {} }
{ "task_id": "t015", "duration_ms":  7300, "cpu_pct": 93.8, "memory_mb": 1792 }
{ "index": {} }
{ "task_id": "t016", "duration_ms":   560, "cpu_pct": 18.2, "memory_mb":  112 }
{ "index": {} }
{ "task_id": "t017", "duration_ms":  1800, "cpu_pct": 52.6, "memory_mb":  576 }
{ "index": {} }
{ "task_id": "t018", "duration_ms":  1350, "cpu_pct": 44.7, "memory_mb":  320 }
{ "index": {} }
{ "task_id": "t019", "duration_ms":  3700, "cpu_pct": 75.9, "memory_mb":  704 }
{ "index": {} }
{ "task_id": "t020", "duration_ms":  9100, "cpu_pct": 97.4, "memory_mb": 2048 }
{ "index": {} }
{ "task_id": "t021", "duration_ms":  1050, "cpu_pct": 38.5, "memory_mb":  416 }
{ "index": {} }
{ "task_id": "t022", "duration_ms":  5800, "cpu_pct": 84.2, "memory_mb": 1280 }
{ "index": {} }
{ "task_id": "t023", "duration_ms":   820, "cpu_pct": 26.7, "memory_mb":  224 }
{ "index": {} }
{ "task_id": "t024", "duration_ms":   990, "cpu_pct": 35.1, "memory_mb":  288 }
{ "index": {} }
{ "task_id": "t025", "duration_ms":  4600, "cpu_pct": 80.3, "memory_mb":  960 }
{ "index": {} }
{ "task_id": "t026", "duration_ms":   670, "cpu_pct": 21.0, "memory_mb":  144 }
{ "index": {} }
{ "task_id": "t027", "duration_ms":  2100, "cpu_pct": 57.8, "memory_mb":  608 }
{ "index": {} }
{ "task_id": "t028", "duration_ms":  6200, "cpu_pct": 89.6, "memory_mb": 1664 }
{ "index": {} }
{ "task_id": "t029", "duration_ms":   510, "cpu_pct": 14.3, "memory_mb":  176 }
{ "index": {} }
{ "task_id": "t030", "duration_ms":  3300, "cpu_pct": 70.5, "memory_mb":  736 }
{ "index": {} }
{ "task_id": "t031", "duration_ms":  1650, "cpu_pct": 49.9, "memory_mb":  496 }
{ "index": {} }
{ "task_id": "t032", "duration_ms":  2750, "cpu_pct": 63.4, "memory_mb":  672 }
{ "index": {} }
{ "task_id": "t033", "duration_ms":  7800, "cpu_pct": 94.1, "memory_mb": 1920 }
{ "index": {} }
{ "task_id": "t034", "duration_ms":   740, "cpu_pct": 23.8, "memory_mb":  208 }
{ "index": {} }
{ "task_id": "t035", "duration_ms":  3100, "cpu_pct": 68.7, "memory_mb":  800 }
{ "index": {} }
{ "task_id": "t036", "duration_ms":  4800, "cpu_pct": 81.5, "memory_mb":  992 }
{ "index": {} }
{ "task_id": "t037", "duration_ms":  1900, "cpu_pct": 54.2, "memory_mb":  560 }
{ "index": {} }
{ "task_id": "t038", "duration_ms":   860, "cpu_pct": 28.9, "memory_mb":  240 }
{ "index": {} }
{ "task_id": "t039", "duration_ms":  2400, "cpu_pct": 60.0, "memory_mb":  624 }
{ "index": {} }
{ "task_id": "t040", "duration_ms":  9500, "cpu_pct": 98.3, "memory_mb": 2048 }
{ "index": {} }
{ "task_id": "t041", "duration_ms":  1400, "cpu_pct": 46.6, "memory_mb":  464 }
{ "index": {} }
{ "task_id": "t042", "duration_ms":  3600, "cpu_pct": 74.0, "memory_mb":  720 }
{ "index": {} }
{ "task_id": "t043", "duration_ms":   480, "cpu_pct": 13.7, "memory_mb":  152 }
{ "index": {} }
{ "task_id": "t044", "duration_ms":  2600, "cpu_pct": 62.1, "memory_mb":  656 }
{ "index": {} }
{ "task_id": "t045", "duration_ms":  1700, "cpu_pct": 51.3, "memory_mb":  528 }
{ "index": {} }
{ "task_id": "t046", "duration_ms":  5500, "cpu_pct": 83.0, "memory_mb": 1152 }
{ "index": {} }
{ "task_id": "t047", "duration_ms":   920, "cpu_pct": 31.4, "memory_mb":  272 }
{ "index": {} }
{ "task_id": "t048", "duration_ms":  2050, "cpu_pct": 56.7, "memory_mb":  592 }
{ "index": {} }
{ "task_id": "t049", "duration_ms":  4200, "cpu_pct": 78.2, "memory_mb":  848 }
{ "index": {} }
{ "task_id": "t050", "duration_ms":  3900, "cpu_pct": 76.5, "memory_mb":  784 }
{ "index": {} }
{ "task_id": "t051", "duration_ms":  1250, "cpu_pct": 43.1, "memory_mb":  432 }
{ "index": {} }
{ "task_id": "t052", "duration_ms":  6900, "cpu_pct": 92.7, "memory_mb": 1856 }
{ "index": {} }
{ "task_id": "t053", "duration_ms":   590, "cpu_pct": 17.5, "memory_mb":  120 }
{ "index": {} }
{ "task_id": "t054", "duration_ms":  2300, "cpu_pct": 59.2, "memory_mb":  616 }
{ "index": {} }
{ "task_id": "t055", "duration_ms":  4900, "cpu_pct": 82.4, "memory_mb": 1024 }
{ "index": {} }
{ "task_id": "t056", "duration_ms":   700, "cpu_pct": 20.6, "memory_mb":  136 }
{ "index": {} }
{ "task_id": "t057", "duration_ms":  1950, "cpu_pct": 53.4, "memory_mb":  544 }
{ "index": {} }
{ "task_id": "t058", "duration_ms":  3200, "cpu_pct": 69.8, "memory_mb":  752 }
{ "index": {} }
{ "task_id": "t059", "duration_ms":  8700, "cpu_pct": 96.1, "memory_mb": 2048 }
{ "index": {} }
{ "task_id": "t060", "duration_ms":   460, "cpu_pct": 12.9, "memory_mb":  168 }
{ "index": {} }
{ "task_id": "t061", "duration_ms":  2700, "cpu_pct": 64.5, "memory_mb":  688 }
{ "index": {} }
{ "task_id": "t062", "duration_ms":  1600, "cpu_pct": 50.8, "memory_mb":  512 }
{ "index": {} }
{ "task_id": "t063", "duration_ms":  5300, "cpu_pct": 83.9, "memory_mb": 1088 }
{ "index": {} }
{ "task_id": "t064", "duration_ms":   810, "cpu_pct": 25.3, "memory_mb":  216 }
{ "index": {} }
{ "task_id": "t065", "duration_ms":  3500, "cpu_pct": 73.2, "memory_mb":  712 }
{ "index": {} }
{ "task_id": "t066", "duration_ms":  4700, "cpu_pct": 80.9, "memory_mb":  976 }
{ "index": {} }
{ "task_id": "t067", "duration_ms":  1750, "cpu_pct": 52.0, "memory_mb":  568 }
{ "index": {} }
{ "task_id": "t068", "duration_ms":   630, "cpu_pct": 18.8, "memory_mb":  104 }
{ "index": {} }
{ "task_id": "t069", "duration_ms":  2500, "cpu_pct": 61.3, "memory_mb":  648 }
{ "index": {} }
{ "task_id": "t070", "duration_ms":  7100, "cpu_pct": 93.2, "memory_mb": 1984 }
{ "index": {} }
{ "task_id": "t071", "duration_ms":  1300, "cpu_pct": 44.0, "memory_mb":  448 }
{ "index": {} }
{ "task_id": "t072", "duration_ms":  3800, "cpu_pct": 75.4, "memory_mb":  760 }
{ "index": {} }
{ "task_id": "t073", "duration_ms":   550, "cpu_pct": 16.1, "memory_mb":  112 }
{ "index": {} }
{ "task_id": "t074", "duration_ms":  2850, "cpu_pct": 65.7, "memory_mb":  696 }
{ "index": {} }
{ "task_id": "t075", "duration_ms":  5700, "cpu_pct": 86.3, "memory_mb": 1216 }
{ "index": {} }
{ "task_id": "t076", "duration_ms":   890, "cpu_pct": 29.5, "memory_mb":  248 }
{ "index": {} }
{ "task_id": "t077", "duration_ms":  2150, "cpu_pct": 58.1, "memory_mb":  600 }
{ "index": {} }
{ "task_id": "t078", "duration_ms":  4300, "cpu_pct": 79.6, "memory_mb":  864 }
{ "index": {} }
{ "task_id": "t079", "duration_ms":  1450, "cpu_pct": 47.3, "memory_mb":  480 }
{ "index": {} }
{ "task_id": "t080", "duration_ms":  6500, "cpu_pct": 90.4, "memory_mb": 1728 }
{ "index": {} }
{ "task_id": "t081", "duration_ms":   720, "cpu_pct": 22.9, "memory_mb":  200 }
{ "index": {} }
{ "task_id": "t082", "duration_ms":  3050, "cpu_pct": 67.9, "memory_mb":  744 }
{ "index": {} }
{ "task_id": "t083", "duration_ms":  1850, "cpu_pct": 53.7, "memory_mb":  552 }
{ "index": {} }
{ "task_id": "t084", "duration_ms":  5400, "cpu_pct": 84.8, "memory_mb": 1120 }
{ "index": {} }
{ "task_id": "t085", "duration_ms":   940, "cpu_pct": 32.6, "memory_mb":  264 }
{ "index": {} }
{ "task_id": "t086", "duration_ms":  2450, "cpu_pct": 60.7, "memory_mb":  632 }
{ "index": {} }
{ "task_id": "t087", "duration_ms":  7600, "cpu_pct": 94.5, "memory_mb": 1984 }
{ "index": {} }
{ "task_id": "t088", "duration_ms":   490, "cpu_pct": 14.9, "memory_mb":  184 }
{ "index": {} }
{ "task_id": "t089", "duration_ms":  3450, "cpu_pct": 72.8, "memory_mb":  728 }
{ "index": {} }
{ "task_id": "t090", "duration_ms":  1550, "cpu_pct": 49.2, "memory_mb":  504 }
{ "index": {} }
{ "task_id": "t091", "duration_ms":  6100, "cpu_pct": 88.9, "memory_mb": 1600 }
{ "index": {} }
{ "task_id": "t092", "duration_ms":   760, "cpu_pct": 24.6, "memory_mb":  232 }
{ "index": {} }
{ "task_id": "t093", "duration_ms":  2950, "cpu_pct": 66.4, "memory_mb":  712 }
{ "index": {} }
{ "task_id": "t094", "duration_ms":  4500, "cpu_pct": 80.1, "memory_mb":  944 }
{ "index": {} }
{ "task_id": "t095", "duration_ms":  1150, "cpu_pct": 40.3, "memory_mb":  408 }
{ "index": {} }
{ "task_id": "t096", "duration_ms":  8400, "cpu_pct": 96.7, "memory_mb": 2048 }
{ "index": {} }
{ "task_id": "t097", "duration_ms":   610, "cpu_pct": 19.0, "memory_mb":  128 }
{ "index": {} }
{ "task_id": "t098", "duration_ms":  2800, "cpu_pct": 64.9, "memory_mb":  680 }
{ "index": {} }
{ "task_id": "t099", "duration_ms":  3750, "cpu_pct": 75.0, "memory_mb":  744 }
{ "index": {} }
{ "task_id": "t100", "duration_ms":  1000, "cpu_pct": 36.0, "memory_mb":  360 }
{ "index": {} }
{ "task_id": "t101", "duration_ms":  5200, "cpu_pct": 83.5, "memory_mb": 1056 }
{ "index": {} }
{ "task_id": "t102", "duration_ms":   830, "cpu_pct": 27.2, "memory_mb":  216 }
{ "index": {} }
{ "task_id": "t103", "duration_ms":  2650, "cpu_pct": 63.8, "memory_mb":  664 }
{ "index": {} }
{ "task_id": "t104", "duration_ms":  4100, "cpu_pct": 77.7, "memory_mb":  816 }
{ "index": {} }
{ "task_id": "t105", "duration_ms":  1500, "cpu_pct": 48.5, "memory_mb":  488 }
{ "index": {} }
{ "task_id": "t106", "duration_ms":  7400, "cpu_pct": 93.5, "memory_mb": 1952 }
{ "index": {} }
{ "task_id": "t107", "duration_ms":   570, "cpu_pct": 17.0, "memory_mb":  116 }
{ "index": {} }
{ "task_id": "t108", "duration_ms":  3150, "cpu_pct": 69.3, "memory_mb":  728 }
{ "index": {} }
{ "task_id": "t109", "duration_ms":  1800, "cpu_pct": 52.9, "memory_mb":  560 }
{ "index": {} }
{ "task_id": "t110", "duration_ms":  6300, "cpu_pct": 89.2, "memory_mb": 1632 }
{ "index": {} }
{ "task_id": "t111", "duration_ms":   870, "cpu_pct": 29.8, "memory_mb":  240 }
{ "index": {} }
{ "task_id": "t112", "duration_ms":  2550, "cpu_pct": 62.7, "memory_mb":  648 }
{ "index": {} }
{ "task_id": "t113", "duration_ms":  4650, "cpu_pct": 81.1, "memory_mb":  968 }
{ "index": {} }
{ "task_id": "t114", "duration_ms":  1250, "cpu_pct": 43.4, "memory_mb":  440 }
{ "index": {} }
{ "task_id": "t115", "duration_ms":  9000, "cpu_pct": 97.8, "memory_mb": 2048 }
{ "index": {} }
{ "task_id": "t116", "duration_ms":   680, "cpu_pct": 21.5, "memory_mb":  144 }
{ "index": {} }
{ "task_id": "t117", "duration_ms":  2900, "cpu_pct": 66.0, "memory_mb":  704 }
{ "index": {} }
{ "task_id": "t118", "duration_ms":  3850, "cpu_pct": 75.7, "memory_mb":  752 }
{ "index": {} }
{ "task_id": "t119", "duration_ms":  1100, "cpu_pct": 39.6, "memory_mb":  392 }
{ "index": {} }
{ "task_id": "t120", "duration_ms":  5600, "cpu_pct": 85.4, "memory_mb": 1184 }
{ "index": {} }
{ "task_id": "t121", "duration_ms":   750, "cpu_pct": 24.1, "memory_mb":  208 }
{ "index": {} }
{ "task_id": "t122", "duration_ms":  2200, "cpu_pct": 58.9, "memory_mb":  584 }
{ "index": {} }
{ "task_id": "t123", "duration_ms":  4400, "cpu_pct": 79.3, "memory_mb":  880 }
{ "index": {} }
{ "task_id": "t124", "duration_ms":  1600, "cpu_pct": 50.2, "memory_mb":  512 }
{ "index": {} }
{ "task_id": "t125", "duration_ms":  7900, "cpu_pct": 94.8, "memory_mb": 2016 }
{ "index": {} }
{ "task_id": "t126", "duration_ms":   520, "cpu_pct": 15.3, "memory_mb":  104 }
{ "index": {} }
{ "task_id": "t127", "duration_ms":  3250, "cpu_pct": 70.9, "memory_mb":  728 }
{ "index": {} }
{ "task_id": "t128", "duration_ms":  1950, "cpu_pct": 54.5, "memory_mb":  568 }
{ "index": {} }
{ "task_id": "t129", "duration_ms":  6400, "cpu_pct": 90.0, "memory_mb": 1696 }
{ "index": {} }
{ "task_id": "t130", "duration_ms":   790, "cpu_pct": 26.0, "memory_mb":  224 }
{ "index": {} }
{ "task_id": "t131", "duration_ms":  2350, "cpu_pct": 60.4, "memory_mb":  616 }
{ "index": {} }
{ "task_id": "t132", "duration_ms":  4750, "cpu_pct": 81.7, "memory_mb":  984 }
{ "index": {} }
{ "task_id": "t133", "duration_ms":  1350, "cpu_pct": 45.8, "memory_mb":  456 }
{ "index": {} }
{ "task_id": "t134", "duration_ms":  8600, "cpu_pct": 96.3, "memory_mb": 2048 }
{ "index": {} }
{ "task_id": "t135", "duration_ms":   640, "cpu_pct": 19.7, "memory_mb":  128 }
{ "index": {} }
{ "task_id": "t136", "duration_ms":  3000, "cpu_pct": 67.6, "memory_mb":  720 }
{ "index": {} }
{ "task_id": "t137", "duration_ms":  1700, "cpu_pct": 51.7, "memory_mb":  536 }
{ "index": {} }
{ "task_id": "t138", "duration_ms":  5000, "cpu_pct": 82.9, "memory_mb": 1040 }
{ "index": {} }
{ "task_id": "t139", "duration_ms":   960, "cpu_pct": 33.1, "memory_mb":  272 }
{ "index": {} }
{ "task_id": "t140", "duration_ms":  2700, "cpu_pct": 64.2, "memory_mb":  672 }
{ "index": {} }
{ "task_id": "t141", "duration_ms":  7200, "cpu_pct": 93.3, "memory_mb": 1968 }
{ "index": {} }
{ "task_id": "t142", "duration_ms":   500, "cpu_pct": 14.6, "memory_mb":  176 }
{ "index": {} }
{ "task_id": "t143", "duration_ms":  3550, "cpu_pct": 73.5, "memory_mb":  736 }
{ "index": {} }
{ "task_id": "t144", "duration_ms":  1500, "cpu_pct": 48.8, "memory_mb":  496 }
{ "index": {} }
{ "task_id": "t145", "duration_ms":  6200, "cpu_pct": 89.4, "memory_mb": 1616 }
{ "index": {} }
{ "task_id": "t146", "duration_ms":   770, "cpu_pct": 25.0, "memory_mb":  216 }
{ "index": {} }
{ "task_id": "t147", "duration_ms":  2450, "cpu_pct": 61.6, "memory_mb":  640 }
{ "index": {} }
{ "task_id": "t148", "duration_ms":  4350, "cpu_pct": 79.8, "memory_mb":  872 }
{ "index": {} }
{ "task_id": "t149", "duration_ms":  1200, "cpu_pct": 42.1, "memory_mb":  424 }
{ "index": {} }
{ "task_id": "t150", "duration_ms":  8900, "cpu_pct": 97.1, "memory_mb": 2048 }
{ "index": {} }
{ "task_id": "t151", "duration_ms":   660, "cpu_pct": 20.2, "memory_mb":  136 }
{ "index": {} }
{ "task_id": "t152", "duration_ms":  2950, "cpu_pct": 66.8, "memory_mb":  712 }
{ "index": {} }
{ "task_id": "t153", "duration_ms":  3900, "cpu_pct": 76.8, "memory_mb":  768 }
{ "index": {} }
{ "task_id": "t154", "duration_ms":  1050, "cpu_pct": 37.4, "memory_mb":  376 }
{ "index": {} }
{ "task_id": "t155", "duration_ms":  5800, "cpu_pct": 86.7, "memory_mb": 1232 }
{ "index": {} }
{ "task_id": "t156", "duration_ms":   840, "cpu_pct": 28.1, "memory_mb":  232 }
{ "index": {} }
{ "task_id": "t157", "duration_ms":  2100, "cpu_pct": 57.4, "memory_mb":  592 }
{ "index": {} }
{ "task_id": "t158", "duration_ms":  4600, "cpu_pct": 80.6, "memory_mb":  960 }
{ "index": {} }
{ "task_id": "t159", "duration_ms":  1400, "cpu_pct": 46.9, "memory_mb":  472 }
{ "index": {} }
{ "task_id": "t160", "duration_ms":  7000, "cpu_pct": 92.0, "memory_mb": 1840 }
{ "index": {} }
{ "task_id": "t161", "duration_ms":   580, "cpu_pct": 17.8, "memory_mb":  120 }
{ "index": {} }
{ "task_id": "t162", "duration_ms":  3100, "cpu_pct": 68.5, "memory_mb":  728 }
{ "index": {} }
{ "task_id": "t163", "duration_ms":  1850, "cpu_pct": 53.0, "memory_mb":  544 }
{ "index": {} }
{ "task_id": "t164", "duration_ms":  5300, "cpu_pct": 84.3, "memory_mb": 1104 }
{ "index": {} }
{ "task_id": "t165", "duration_ms":   950, "cpu_pct": 32.9, "memory_mb":  264 }
{ "index": {} }
{ "task_id": "t166", "duration_ms":  2600, "cpu_pct": 63.2, "memory_mb":  656 }
{ "index": {} }
{ "task_id": "t167", "duration_ms":  7700, "cpu_pct": 94.6, "memory_mb": 1984 }
{ "index": {} }
{ "task_id": "t168", "duration_ms":   470, "cpu_pct": 13.4, "memory_mb":  168 }
{ "index": {} }
{ "task_id": "t169", "duration_ms":  3400, "cpu_pct": 72.1, "memory_mb":  720 }
{ "index": {} }
{ "task_id": "t170", "duration_ms":  1600, "cpu_pct": 50.5, "memory_mb":  512 }
{ "index": {} }
{ "task_id": "t171", "duration_ms":  6000, "cpu_pct": 88.5, "memory_mb": 1568 }
{ "index": {} }
{ "task_id": "t172", "duration_ms":   780, "cpu_pct": 25.5, "memory_mb":  216 }
{ "index": {} }
{ "task_id": "t173", "duration_ms":  2800, "cpu_pct": 65.3, "memory_mb":  696 }
{ "index": {} }
{ "task_id": "t174", "duration_ms":  4500, "cpu_pct": 80.2, "memory_mb":  928 }
{ "index": {} }
{ "task_id": "t175", "duration_ms":  1150, "cpu_pct": 40.7, "memory_mb":  408 }
{ "index": {} }
{ "task_id": "t176", "duration_ms":  8300, "cpu_pct": 96.5, "memory_mb": 2048 }
{ "index": {} }
{ "task_id": "t177", "duration_ms":   620, "cpu_pct": 19.3, "memory_mb":  128 }
{ "index": {} }
{ "task_id": "t178", "duration_ms":  2750, "cpu_pct": 64.6, "memory_mb":  680 }
{ "index": {} }
{ "task_id": "t179", "duration_ms":  3800, "cpu_pct": 75.3, "memory_mb":  752 }
{ "index": {} }
{ "task_id": "t180", "duration_ms":  1050, "cpu_pct": 37.8, "memory_mb":  368 }
{ "index": {} }
{ "task_id": "t181", "duration_ms":  5100, "cpu_pct": 83.2, "memory_mb": 1072 }
{ "index": {} }
{ "task_id": "t182", "duration_ms":   850, "cpu_pct": 28.5, "memory_mb":  232 }
{ "index": {} }
{ "task_id": "t183", "duration_ms":  2550, "cpu_pct": 62.4, "memory_mb":  648 }
{ "index": {} }
{ "task_id": "t184", "duration_ms":  4200, "cpu_pct": 78.5, "memory_mb":  832 }
{ "index": {} }
{ "task_id": "t185", "duration_ms":  1450, "cpu_pct": 47.6, "memory_mb":  480 }
{ "index": {} }
{ "task_id": "t186", "duration_ms":  7500, "cpu_pct": 93.9, "memory_mb": 1968 }
{ "index": {} }
{ "task_id": "t187", "duration_ms":   530, "cpu_pct": 15.8, "memory_mb":  112 }
{ "index": {} }
{ "task_id": "t188", "duration_ms":  3200, "cpu_pct": 70.2, "memory_mb":  736 }
{ "index": {} }
{ "task_id": "t189", "duration_ms":  1900, "cpu_pct": 54.8, "memory_mb":  560 }
{ "index": {} }
{ "task_id": "t190", "duration_ms":  6400, "cpu_pct": 90.2, "memory_mb": 1712 }
{ "index": {} }
{ "task_id": "t191", "duration_ms":   800, "cpu_pct": 26.4, "memory_mb":  224 }
{ "index": {} }
{ "task_id": "t192", "duration_ms":  2400, "cpu_pct": 61.0, "memory_mb":  624 }
{ "index": {} }
{ "task_id": "t193", "duration_ms":  4800, "cpu_pct": 82.0, "memory_mb":  992 }
{ "index": {} }
{ "task_id": "t194", "duration_ms":  1300, "cpu_pct": 44.3, "memory_mb":  448 }
{ "index": {} }
{ "task_id": "t195", "duration_ms":  9200, "cpu_pct": 98.0, "memory_mb": 2048 }
{ "index": {} }
{ "task_id": "t196", "duration_ms":   690, "cpu_pct": 21.8, "memory_mb":  144 }
{ "index": {} }
{ "task_id": "t197", "duration_ms":  2900, "cpu_pct": 66.2, "memory_mb":  704 }
{ "index": {} }
{ "task_id": "t198", "duration_ms":  3700, "cpu_pct": 74.7, "memory_mb":  744 }
{ "index": {} }
{ "task_id": "t199", "duration_ms":  1100, "cpu_pct": 39.9, "memory_mb":  400 }
{ "index": {} }
{ "task_id": "t200", "duration_ms":  5500, "cpu_pct": 85.0, "memory_mb": 1168 }

# ── 5. Example ES|QL LOOKUP JOIN query ───────────────────────────────────────

FROM tasks_385464
| LOOKUP JOIN task_metrics_385464 ON task_id
| KEEP task_id, task_name, owner, status, duration_ms, cpu_pct, memory_mb
| SORT duration_ms DESC

# ── 6. Cleanup (run when done) ───────────────────────────────────────────────

DELETE tasks_385464
DELETE task_metrics_385464
3

If I want to apply filters for the fields from the task_metrics_385464, it does not filter based on that, also can you help me identify where can I put these esql queries in the lens visualizations, or any documentation for the same.

4

I could create a control that filters the metrics index, see details below.

My issue is that I can't reuse that control in other panels for some reason. I'll tag my colleagues to see if I hit a know bug and if there are other options available, sorry about that.

The query I updated in the Counts first widget is

FROM tasks_385464
| LOOKUP JOIN task_metrics_385464 ON task_id
| KEEP task_id, task_name, owner, status, duration_ms, cpu_pct, memory_mb
| WHERE MV_CONTAINS(?status, status)
    AND MV_CONTAINS(?owner, owner)
    AND cpu_pct >= ?cpu_pct
| STATS counts=COUNT(*)

With a new control ?cpu_pct using fixed values (no range slider control yet on ES|QL)

image
image1336×611 54.4 KB

But then when I apply the same AND clause to the table panel it fails

image
image1988×919 120 KB

Is this the issue you find @jai2 or anything else?

5

Created issue to track this, feel free to chime in if you have additional feedback to the issue

6

Yups.

Also consider this, if I want to use one ESQL control inside a definition of another ESQL control, can I do it? I tried doing it, it does not throw any errors, it just doesn’t allow me to run(refresh) the query, and hence I am not able to create the control, is it a bug or that’s how it is meant to be?