What is better way to filter creation time field for specific time range

[Vinay-AS]

Hello Everyone,

We have "creation time" field in the incidents dataset. I wanted to query the "incidents" dataset for the specific date ranges of creation time like 1d, 7d etc. Have tried multiple ways, but couldn't succeed. Please share details if some has it.

Cortex XSIAM

[LancePettay]

Hello, Vinay!

This is possible, but since the regular query time range is based off the "_time" field rather than "created_time", you will need to start by choosing a time range that goes back at least far as you need to include all relevant incidents. For example "7 days"

This is going to return more incidents than you want, since older incidents which have been updated recently will still show up in the incidents dataset. To address this, we can filter out any incidents whose creation_time is older than 7 days like so:

dataset = incidents
| filter timestamp_diff(current_time(), creation_time , "DAY") < 7

This filter will calculate how many days since the creation time for each incident, and only keep those that were created < 7 days ago. Of course you can change that 7 to whatever number of days you want, just be sure to also increase the query time range in the top-right of the query builder.

I hope this helps!

Take care,

Lance

[Vinay-AS]

Thanks Lance for your prompt response. I have tried your suggestion, its working. But i cannot use this to get a specific time. For example Jan 1st 00:00 to Jan 15th 23:59. Do you have any suggestion for this.

Can we Bin functionality of XSIAM to achieve my results.  Please let me know your input.

• Time Span

  bin <field> span = <time> [timeshift = <epoch time> [timezone = "<time zone>"]]

[LancePettay · Accepted solution]

Hi Vinay!

"bin" will not solve your problem there, as that is meant to group events into buckets containing several events for specific groupings of time. I understand that you want to return all incidents within a certain time frame.

For that, you can modify this query to your needs:

dataset = incidents
| alter from_time = parse_timestamp("%Y-%m-%d %H:%M:%S ", "2024-01-13 00:00:00")
| alter to_time = parse_timestamp("%Y-%m-%d %H:%M:%S ", "2024-01-20 00:00:00")
| filter creation_time > from_time and creation_time < to_time

//Optional version with timezone (e.g. "+08:00" or https://en.wikipedia.org/wiki/List_of_tz_database_time_zones)
// | alter from_time = parse_timestamp("%Y-%m-%d %H:%M:%S ", "2024-01-13 00:00:00", "+08:00")
// | alter to_time = parse_timestamp("%Y-%m-%d %H:%M:%S ", "2024-01-20 00:00:00", "America/Los_Angeles")
// | filter creation_time > from_time and creation_time < to_time

 I hope this helps, and let me know if there are any other questions.

- Lance

[Vinay-AS]

Thanks Lance, This query solves my problem.

Regards,

Vinay