Group events with xql bin stage

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Announcements

Group events with xql bin stage

L2 Linker

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?

5 REPLIES 5

L4 Transporter

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

 

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

JM

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:

 

micomi_0-1727368567609.png

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:

micomi_1-1727368690643.png

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

Any ideas?

 

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."

 

 

JM

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.

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

JM
  • 282 Views
  • 5 replies
  • 0 Likes
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!