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

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

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

L1 Bithead

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

1 accepted solution

Accepted Solutions

L2 Linker

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

View solution in original post

4 REPLIES 4

L2 Linker

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

 

 

 

 

L1 Bithead

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

L2 Linker

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

Thanks Lance, This query solves my problem.


Regards,

Vinay

  • 1 accepted solution
  • 1533 Views
  • 4 replies
  • 0 Likes
  • 25 Subscriptions
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!