Timelion in canvas throws error [mapColumn] > [getCell] > Column not found

[AClerk]

Hi,
Trying to create timelion in canvas workpad.
Getting an error

ESQL code:

timelion 
  query=".es(index=my-index-*,
				timefield='timestamp',
   				metric=cardinality:field1.keyword,
				q='field2.keyword:xxx AND field3.keyword:yyy AND NOT field4.keyword:NULL')
				.label('my-label')" timezone="Australia/Sydney" from="now-24h" interval="auto"
| mapColumn "timestamp_epoch"
  fn={getCell "timestamp_epoch" | rounddate "YYYY-MM"}
| pointseries x="@timestamp" y="mean(value)"
| plot defaultStyle={seriesStyle lines=3} 
  font={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="left" color="#FFFFFF" weight="normal" underline=false italic=false} palette={palette "#D3FB71" "#131A00" gradient=true}
| render

What is wrong? Why can't canvas find this field?
Tried other fields as well, still getting the error.
Cheers!

[markov00 (Marco Vettorello)]

hi, the timestamp_epoch is not part of your requested table,
you can visualize the returning table from the timelion query replacing your expression with:

timelion 
  query=".es(index=my-index-*,
				timefield='timestamp',
   				metric=cardinality:field1.keyword,
				q='field2.keyword:xxx AND field3.keyword:yyy AND NOT field4.keyword:NULL')
				.label('my-label')" timezone="Australia/Sydney" from="now-24h" interval="auto"

| table
| render

and you will find the columns returned by your timelion query

[AClerk]

@markov00
Thanks for the tip.
I see only 3 columns are returned.
Is there a way I can use other/more fields from my-index?
Also, where is @timestamp coming from?
It is not in the doc. Is it an internal field that is mapped to the internal field 'Time'?

Last, but not least, in the table created, the @timestamp shows the correct time by timezone. Howcome it changes to UTC when using plot? Or maybe this is the root cause of the bug?! i.e the bug is in the plot function and not rendering the correct time.

Cheers!

[markov00 (Marco Vettorello)]

If I'm not wrong, querying your data with timelion returns only series with timestamp and value (and an eventual label). the @timestamp column name is just a mapping name for your timefield . If you have more than one timelion series it probably returns more columns but I haven't tried.
If you need to return more aggregated columns and data it's better to move to ESQL that gives you much more flexibility then timelion.

The issue with the UTC dates is on the plot rendering function not on the data provided to that renderer.

[AClerk]

@markov00
Thanks for the info. It is very useful.
How can I imitate a timelion/time series with ESQL?
Any example or link will be great.
Cheers!