Splunk Enterprise

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

Sunil2020
Explorer

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.

Labels (2)
Tags (1)
0 Karma
1 Solution

soutamo
SplunkTrust
SplunkTrust

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

View solution in original post

skoelpin
SplunkTrust
SplunkTrust

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
Explorer

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.

0 Karma

soutamo
SplunkTrust
SplunkTrust

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

View solution in original post

Sunil2020
Explorer

Awesome, this worked.

Thanks a ton @soutamo 

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!