CANVAS SQL: condition with timestamp

Elastic Stack Elasticsearch
1

Hello,

I am creating a canvas where I would like to show some anomalies in my cluster, and I wanna create something that show me if a new user in my AD has been created between midnight and 7am for example.

I have this SQL which allow me to see the number of users created, but I don't know how to add the timestamp condition:

SELECT COUNT(*) as user_added 
FROM "winlogbeat-*" 
WHERE "event.code" = 4720

Could you please help me ?
Thanks :slight_smile:

2

Over all time? Or only over the last X days?

There might be several ways to do this, but sounds like you need an "hour of day" kind of filter. Seems like this would be best handled by an upcoming feature in 7.11 called "Runtime fields" which will give you the capability to define arbitrary new fields that are not in the mappings.

runtimefield_editor_1
runtimefield_editor_1928×704 2.15 MB

The above example creates "day of week", but certainly hour of day would just be as easy.

But, it might be about a month until 7.11 is released. In the meantime, you could investigate using a Transform with a scripted metric aggregation (to create an "hour of day" field) and have the transform put this in a new index - one that you query from Canvas.

3

Thanks for your answer @richcollier, I will try that with transform as you suggested.