Having a time range based on a second time field

[ssaenger]

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.

[scelikok]

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.

[ssaenger]

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.000000	2021-01-16 02:59:00	2021-01-16 02:59:00	Crime Patrol
1614576600.000000	2021-03-01 05:30:00	2021-03-01 05:30:00	Crime Patrol

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

[scelikok · Accepted solution]

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

[ssaenger]

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.

[scelikok]

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.

[scelikok]

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... 

[ssaenger]

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.

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

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

Thanks,

[to4kawa]

| 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.