topic Re: Group events with xql bin stage in Cortex XDR Discussions

Group events with xql bin stage

micomi — Thu, 26 Sep 2024 12:37:01 GMT

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?

Re: Group events with xql bin stage

jmazzeo — Thu, 26 Sep 2024 15:38:46 GMT

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

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

Re: Group events with xql bin stage

micomi — Thu, 26 Sep 2024 16:38:42 GMT

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?

Re: Group events with xql bin stage

jmazzeo — Thu, 26 Sep 2024 17:58:55 GMT

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

Re: Group events with xql bin stage

micomi — Fri, 27 Sep 2024 11:28:15 GMT

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.

Re: Group events with xql bin stage

jmazzeo — Fri, 27 Sep 2024 15:50:24 GMT

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