Subtract variables in Kibana canvas with essql

[blodstrupmoen]

Hi, Im having a real hard time subtracting one query from another.

I cant seem to find any good examples on how to use var_set and var properly.

How can I subtract the value (epochtime) of variable "start" from variable "end"

filters
| var_set name="start" value={
    essql query="
      SELECT created
      FROM \"my-index-*\"
      WHERE id='1367'
      AND status='started'"
  }
| var_set name="end" value={
    essql query="
      SELECT updated
      FROM \"my-index-*\"
      WHERE id='1367'
      AND status='done'"
  }

[wylie (Wylie Conlon)]

I can help you with the mental model of doing this. SQL always return tabular results, but it seems like you can guarantee that each table has exactly one cell. This helps a lot because you can insert a staticColumn into the output table, and then run a math function like updated - created assuming these are numeric.

So roughly, you'd do something like this (I haven't tested the code I'm sending you):

filters
| var_set name="start" ...
| essql query="..."
| staticColumn name="start" value={var "start"}
| math "updated - start"

There is also a second way of doing this, where you can use string manipulation to generate SQL. Since the first approach is simpler I would recommend it.
I think we'd appreciate help documenting this use case, since we don't have good docs for it today.

[wylie (Wylie Conlon)]

That's definitely a harder problem, and what I would recommend is changing the way you ingest data if you really need to calculate the duration between two events. There is a feature called transforms which should let you do exactly the query you are asking for using painless scripting, and then it will get saved to a new index.

Another option that you could do is register a Kibana plugin that builds an expression function for you to use.

I do think that this is a weak point of our existing functions: we could expand the table manipulation logic available in Canvas. Basically something like a standard library for tables.