SQL average for period of day

[samgs (Sam)]

Hello,

I have a line graph that shows number of logins today. What I want to do is overlay the average number of logins over the last 30 days. Is that possible? I figure I'll have to use another graph element on canvas and put them on top of each other, but I cant figure out what the SQL would be.

This is my current SQL for today:

SELECT name, data, date FROM index WHERE QUERY('name:visit') AND date > TODAY() ORDER BY date

That give me data from 00:00 to what ever time it is right now. So I only want averages for that period too, say 00:00 - 8am.

Is this possible?

[bhavyarm]

@tims @Catherine_Liu can we please get some help here please?

Thanks,
Bhavya

[tims (Tim Schnell)]

@samgs There's a couple of options to try and get this done. I would suggest maybe trying to do this with the timelion expressions because you can set the time interval dynamically. Here's a tutorial: https://www.elastic.co/blog/timelion-tutorial-from-zero-to-hero

You can also use the math function in the Canvas expression language to get the average if you have a datatable of counts. Here's the documentation for that: https://www.elastic.co/guide/en/kibana/master/canvas-tinymath-functions.html#_mean_8230_args_2

[samgs (Sam)]

Appreciate the answers here, but struggling to get either to work.

qq- should timelion work in canvas? Everytime I try anything other than the standard query

.es(q=*, index=logstash-*)

i get:

[timelion] > Request failed with status code 500

[tims (Tim Schnell)]

Yes, Timelion should work in Canvas. I'm going to open a ticket to unmask the error messages we are getting back as I have also noticed recently that this is a very unhelpful message. If you post your query we can take a look and see if we notice what the error could be.

[edit]
Ticket opened here: https://github.com/elastic/kibana/issues/43583

[Catherine_Liu]

@samgs You might be able to achieve what you want using ES SQL if I've got your problem right.

Here's an example using the kibana flights sample data that displays the number of flights per day between 00:00 and 08:00 for the past 30 days:

filters
| essql 
  query="SELECT Histogram(timestamp, INTERVAL 1 DAY) as date, count(*) as number_of_flights 
    FROM \"kibana_sample_data_flights\" 
    WHERE timestamp < TODAY() 
    AND timestamp >= TODAY() - INTERVAL 30 DAYS 
    AND HOUR_OF_DAY(timestamp) >= 0 
    AND HOUR_OF_DAY(timestamp) <= 8 
    GROUP BY date
    ORDER BY date" 
| table 
| render