How do I filter search based on date and hour range?

[Sunil2020]

Hello Splunkers,

I am trying to achieve below in my search.

I have two tables/sources where dates stores are of different format.

Source A has two fields with below format.

startdate -  2020-07-15 09:30:35

enddate -   2020-07-15 11:30:40

Source B

date - 2020-07-15 00:00:00

hour - 10

From Source A I have to extract date and hour separately. For Example, from startdate field , I have to extract date as 2020-07-15 and hour as 09 and from field enddate, date as 2020-07-15 and hour as 12

Using above I am trying to filter events from Source B as below.
...main search

| eval startdate=strptime(startdate, “%Y-%m-%d”), enddate=strptime(enddate,”%Y-%m-%d”), date=strptime (date, “%Y-%m-%d”)

| where (startdate >= date AND starthour >= hour) AND (enddatew<= date AND endhour <= hour)

Above search does not return any events.

Your help is highly appreciated.

[skoelpin]

Assuming you are using a reporting command such as stats and timechart and pass _time after. You can do something as easy as this. You are using the strftime function to explicitly extract out the day and hour value from epoch time then filtering down with where on the day and hour

| eval startdate_time=strptime(startdate,"%Y-%m-%d %H:%M:%S")

| eval enddate_time=strptime(enddate,"%Y-%m-%d %H:%M:%S")

| eval date_time=strptime(date,"%Y-%m-%d %H:%M:%S")

| eval date_hour=strftime(startdate_time, "%H")

| eval date_day=strftime(startdate_time, "%a")

| eval date_hour=strftime(enddate_time, "%H")

| eval date_day=strftime(enddate_time, "%a")

| eval date_day=strftime(date_time, "%a")

| eval date_hour=strftime(date_time, "%H")

| eval alert=if((date_day >= 10 AND date_hour >= 12) AND (date_day <28 date AND date_hour >10),1,0)

| where alert=1

[Sunil2020]

Hello @skoelpin 

I was trying to list the events between date and hour range in Source B where date range and hour range was extracted from Source A.

Idea was to show the relevant events in table.

Thanks for your inputs.

[isoutamo · Accepted solution]

Hi

You could try this:

index=_internal
| head 1
| eval _raw="startdate,enddate,date,hour
2020-07-15 09:30:35,2020-07-15 11:30:40,2020-07-15 00:00:00,10"
| multikv forceheader=1
| rename COMMENT as "Previous generate sample data"
| eval sd = strptime(startdate, "%F %T")
| eval ed = strptime(enddate, "%F %T")
| eval da = strptime(date, "%F %T") 
| eval daho = da + 60*60 * hour
| where (sd <= daho) AND (daho <= ed)

When you are changed between string and epoch you must use the whole format. I am using %F %T which is shortcut for %Y-%m-%d %H:%M:%S. 

Then just add hours to date for comparing the dates.

r. Ismo

[Sunil2020]

Awesome, this worked.

Thanks a ton @isoutamo