Splunk Search

Having a time range based on a second time field

ssaenger
Path Finder

Hi,

 

i have extracted data from a database into a summary index which is updated every hour.

The database has information that is in the past and the future.

DESCR="TV HD",  START_Time="2021-01-10 09:00:00", NAME="Crime Patrol"

DESCR="TV HD", START_Time="2021-01-11 10:00:00", NAME="Fire Patrol"

DESCR="TV HD", START_Time="2021-01-12 09:00:00", NAME="Ambulance Patrol"

DESCR="TV HD", START_Time="2021-01-13 09:00:00", NAME="Crime Patrol"

DESCR="TV HD", START_Time="2021-01-14 09:00:00", NAME="Fire Patrol"

DESCR="TV HD", START_Time="2021-01-15 09:00:00", NAME="Ambulance Patrol"

DESCR="TV HD", START_Time="2021-01-16 09:00:00", NAME="Crime Patrol"

I would like to extract data for the last two days based on START_time.

eg todays date is 2021-01-15

returned data

DESCR="TV HD", START_Time="2021-01-14 09:00:00", NAME="Fire Patrol"

DESCR="TV HD", START_Time="2021-01-15 09:00:00", NAME="Ambulance Patrol"

I have tried to use relative time/strptime but i am unable to get the time frame correct.

My problem is that most solutions require my to eval START_Time to _time using strftime.

However as my data is in a summary index the above data has multiple time entries in front of it and to get the latest time i use earliest=-60m@m latest=@m. This causes me issues when modifying _time in a search.

I have tried to use this solution as a guide.

https://www.splunk.com/en_us/blog/tips-and-tricks/get-time-on-your-side-how-to-sort-by-more-than-one...

The time picker is ignored as i am using earliest/latest.

i only get details for the last hour, if i change it to earliest=-120m@m latest=@m i get a double line. 

2021-01-15 14:25:13.206 DESCR="TV HD", START_Time="2021-01-14 09:00:00", NAME="Fire Patrol"

2021-01-15 13:25:13.206  DESCR="TV HD", START_Time="2021-01-14 09:00:00", NAME="Fire Patrol"

Does any one have any ideas?

Thanks.

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

scelikok
Champion

Hi @ssaenger,

We can also filter the result till today;

index=summary sourcetype=foo source=bar  earliest=-2d@d
| sort - _time
| dedup DESCR START_Time Name
| eval sTime=strptime(START_Time,"%Y-%m-%d %H:%M:%S")
| where sTime>=relative_time(now(), "-2d@d") AND sTime<=relative_time(now(), "@d") 
| sort -sTime
| table START_Time NAME
If this reply helps you an upvote is appreciated.

View solution in original post

scelikok
Champion

Hi @ssaenger,

My solution should filter the events based on START_Time, but your search is not the same as mine. Results are wrong because relative_time comparison should be -2d@d not +2d@d. Please try below;

index=summary sourcetype=foo source=bar  earliest=-2d@d
| sort - _time
| dedup DESCR START_Time Name
| eval sTime=strptime(START_Time,"%Y-%m-%d %H:%M:%S")
| where sTime>=relative_time(now(), "-2d@d")
| sort -sTime
| table START_Time NAME

 

If this reply helps you an upvote is appreciated.

If this reply helps you an upvote is appreciated.
0 Karma

ssaenger
Path Finder

Hi scelikok,

Sorry i should have posted that i tried the -2d@d but did not post the result. 

It just changes the start time but the end time still goes to the end of the time in the database.

sTime siTime start_time name

1610765940.0000002021-01-16 02:59:002021-01-16 02:59:00Crime Patrol
1614576600.0000002021-03-01 05:30:002021-03-01 05:30:00Crime Patrol

 

The above results is run on 18/01//2021.

Tags (3)
0 Karma

scelikok
Champion

Hi @ssaenger,

We can also filter the result till today;

index=summary sourcetype=foo source=bar  earliest=-2d@d
| sort - _time
| dedup DESCR START_Time Name
| eval sTime=strptime(START_Time,"%Y-%m-%d %H:%M:%S")
| where sTime>=relative_time(now(), "-2d@d") AND sTime<=relative_time(now(), "@d") 
| sort -sTime
| table START_Time NAME
If this reply helps you an upvote is appreciated.

View solution in original post

ssaenger
Path Finder

Hi scelikok,

Yes that worked. can you explain how the addition of the AND sTime<=relative_time(now(), "@d")  works please, what does "@D" mean?

 

thanks.

0 Karma

scelikok
Champion

Hi @ssaenger,

Please try below search, you can use a time range that is suitable for your data, dedup command will filter identical events if any.

| dedup _raw 
| eval sTime=strptime(START_Time,"%Y-%m-%d %H:%M:%S") 
| where sTime>=relative_time(now(), "-2d@d")
| fields -sTime

 

If this reply helps you an upvote is appreciated.

If this reply helps you an upvote is appreciated.
0 Karma

scelikok
Champion

Hi @ssaenger,

Great! 

I added second condition to filter the events that START_Time is later than today. "@d" means start of the day. It results "today 00:00:00".

You can find more detail below;

https://docs.splunk.com/Documentation/Splunk/8.1.1/SearchReference/SearchTimeModifiers#Specify_a_sna... 

If this reply helps you an upvote is appreciated.
Tags (1)
0 Karma

ssaenger
Path Finder

HI scelikok,

thanks for your answer, however the range is giving me problems. It seems that your solution on provides the start time of the search and returns all results from that time, rather than within the time range.

index=summary sourcetype=foo source=bar  earliest=-60m@m latest=@m
| eval sTime=strptime(START_TIME,"%Y-%m-%d %H:%M:%S")
| sort -sTime
| addinfo
| where sTime>=relative_time(now(), "+2d@d")
| eval sTime=strftime(info_min_time,"%Y-%m-%d %H:%M:%S")
| fields sTime START_TME NAME
| table sTime START_TIME NAME

This is the results.

 

   
sTimeSTART_TimeNAME
2021-01-16 20:05:002021-01-18 05:30:00Fire Patrol
2021-01-16 20:05:002021-03-01 04:30:00Crime Patrol

 

how would i get only the results of START_TIME with a 2 day window?

Thanks,

 

Tags (3)
0 Karma

to4kawa
SplunkTrust
SplunkTrust
| makeresults
| eval _raw="sTime	START_Time	NAME
2021-01-16 20:05:00	2021-01-18 05:30:00	Fire Patrol
2021-01-16 20:05:00	2021-03-01 04:30:00	Crime Patrol"
| multikv forceheader=1
| table sTime	START_Time	NAME
| eval earliest=strptime(START_Time,"%F %T")
| eval latest=relative_time(earliest,"+2d")
| fields earliest, latest
| format

Pass the time in a subsearch like this.

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!