Group events with xql bin stage

[micomi]

Hi everyone

I try to count some events per day and used the bin stage to do this. It does work to group the events together but the time is wrong. For example an event at 00:30 will count for the day before (probably because of the timezone). I tried different configurations with the optional parameter timeshift and timezone but I'm not able to get it working.

Does anyone know how set the timeshift and timezone parameter correct so the event are grouped together correctly?

[jmazzeo]

Hi @micomi, thanks for reaching us using the Live Community.

Can you share an example of the XQL query? Please obfuscate any sensitive content.

[micomi]

Hi @jmazzeo 

Sure, this is a simple query to show the incident per day.

config timeframe = 30d
| dataset = incidents 
| sort desc creation_time
| bin creation_time span = 1D
| comp count(creation_time ) as incidents_by_day by creation_time 

With this i get these results:

The issue is that i groups the incidents by 2 o'clock am. But I want to group per day, from midnight to midnight.

The numbers in screenshot aren't correct. The correct numbers should be:

26.09 > 4
25.09 > 4
24.09 > 5

You can check it yourself with this screenshot:

The issue are those incidents which were generated shortly after midnight.

Any ideas?

[jmazzeo]

I have tried you query, and in my case it also showed incorrect numbers.

I have added the timeshift and my timezone and it worked well:

config timeframe = 30d
| dataset = incidents
| sort desc creation_time
| bin creation_time span = 1D timeshift = 1615353499 timezone = "-3:00"
| comp count(creation_time ) as incidents_by_day by creation_time

Take a look at this documentation statement: "The query still runs without defining the epoch time or time zone. If no timeshift = <epoch time> timezone = "<time zone>" is set, the query runs according to last time set in the log."

[micomi]

I don't get it to work. What value should I choose for timeshift?

For the timezone I use a CEST timezone but it still doesn't work. Same behaviour as before.

[jmazzeo]

You need to use the same timeshift value that shows the documentation: 1615353499