Horizontal Line on Visualization Based on Max of Query Result

[LisaJ (Lisa)]

I am looking for some way chart a horizontal line at some value determined by a query. I have a time series line chart showing dB loss values across the time series. There is another index that stores baseline dB loss values for the same locations. I want to draw a horizontal line at the baseline value for the location -- so someone can quickly see if the current scans are pretty close to "as good as it's ever going to get" or if the current scans show a potential problem.

I've tried both Vega and TimeLion (we're using ES7, so MathLion doesn't appear to be an option). In TimeLion, I can use .value(10) to get a horizontal line at 10, so that seemed like a good direction. I just need to populate .value() based on a search instead of a static value. But my attempts haven't yielded anything.

.es(index= db_traces-*,
    timefield='@timestamp',
    q='locationname:SampleLocation1',
    metric='max:KeyEvents.Summary.loss')
  .label('Total Loss')
  .title('Loss History')
  .yaxis(1,position=left)
  .lines(width=2).color(purple)
  .fit(none)
,
.value(.es(index= db_baselines-*,
    q='locationname:SampleLocation1',
    metric='max:KeyEvents.Summary.loss'))
  .lines(width=3)
  .color(maroon)
  .label('Baseline Loss')

[LeeDr (Lee Drengenberg)]

Hi Lisa,

I'm not sure what version of Kibana you're on. The most recent release is 8.3.2. On that version, in Lens, you can add layers of data from different indices. Looks like this feature was available at least as far back as 7.17, maybe further.
There's an option for Reference lines, but to use data from another index I think you would need to use the Visualization option.

I don't really have good data in different indices to demo this but here's an example where there are two layers that both come from the same index;

[LisaJ (Lisa)]

Thank you for the information -- we're running 7.7.0, but we wanted to upgrade to the latest 7.x iteration in the mid-term ... so I'll try this out in my lab and see if "wait until we upgrade" is a viable answer (or, more realistically, expedite this upgrade because we want our graphs!).

[LisaJ (Lisa)]

I was finally able to figure out a set of data contortions that allowed me to draw a horizontal line over the extent of the graph. It's a bit convoluted, but I figured I'd post the approach here in case anyone else with older iterations of ES gets hung up trying to draw a horizontal line.

My data elements look like this -- for both scans and baselines:

            { "@timestamp": "2022-05-26T15:10:14.395Z", "loss": 13.558},

I added transforms to my scan data to ensure each record has a max datetime and then used a lookup from the baseline data to retrieve the value from my scan table. Since the lookup method needs to find an exact match, I appended a static value to all of the records too. Pi ... because mmm, pie!

        "transform":[
                {"type": "formula", "expr": "datetime(datum['@timestamp'])", "as": "transformedtimestamp"}
               , {"type": "joinaggregate", "fields": ["@timestamp"], "ops": ["max"], "as": ["maxtime"]}
               , {"type": "formula", "as": "pi", "expr": "PI"}
    ] 

Now my scan data includes a few extra elements:

Then I used a lookup from the baseline data -- fortunately this seems to work fine even though there are multiple matching records.

{"type": "lookup", "from": "scandata", "key": "pi", "fields": ["pi"], "values": ["maxtime"], "as": ["maxtime"]}

Since my baseline data contains the max date for the x-axis:

I can use a 'rule' mark to draw the line at loss value from 0 to max time value:

 {
            "type": "rule",
            "from": {"data": "baseline"},
            "encode": {
              "enter": {
                "stroke": {"value": "#652c90"},
                "x": {"scale": "x", "value": 0},
                "y": {"scale": "y",      "type": "quantitative","field": "loss"},
                "x2": {"scale": "x","field": "maxtime", "type": "temporal"},
                "strokeWidth": {"value": 4},
                "opacity": {"value": 0.3}
              }
            }
          }