Relative and Exact Time

[genesiusj]

Hello,
I am using these two commands at the end of my search, and it works.

| timewrap d
| where _time >= relative_time(now(), "-1h@h-10m") AND _time <= relative_time(now(), "-1h@h+10m")

What I am looking to do (this is pseudo-code) | where _time >= 6:00 am AND _time <=9:59 pm

I want to set a time chart for the same time range every day. Hence, the | timewrap d.

Thanks, stay safe and healthy, and God bless,
Genesius

[genesiusj]

@richgalloway
A colleague found the solution.
Adding a latest field-value to the end of the |AND command AND date_hour>=6 AND date_hour<=22 latest=+1d@d(see complete code below). Note: We also added some code to clean up the names of the series.

index IN (oit_catalina,dol_solarisevents)
    AND source=/opt/as7/domains/domain1/dolapps1/logs/access
    AND sourcetype=access_combined
    AND host=nas3*
    AND method IN (GET,POST)
    AND date_hour>=6 AND date_hour<=22 latest=+1d@d
| rex mode=sed field=host "s/.sa.state.nj.us//g" 
| eval certsFiled=case(file="confirm_new.jsp","1") 
| timechart count span=1min
| timewrap d series=short
| where _time >= relative_time(now(), "@d+6h+55min") AND _time <= relative_time(now(), "@d+22h")
| eval colname0 = strftime(relative_time(now(), "@d"),"%D-%a")
| eval colname1 = strftime(relative_time(now(), "-d@d"), "%D-%a")
| eval colname2 = strftime(relative_time(now(), "-2d@d"), "%D-%a")
| eval colname3 = strftime(relative_time(now(), "-3d@d"),"%D-%a")
| eval colname4 = strftime(relative_time(now(), "-4d@d"), "%D-%a")
| eval colname5 = strftime(relative_time(now(), "-5d@d"), "%D-%a")
| eval colname6 = strftime(relative_time(now(), "-6d@d"), "%D-%a")
| eval {colname0} = s0
| eval {colname1} = s1
| eval {colname2} = s2
| eval {colname3} = s3
| eval {colname4} = s4
| eval {colname5} = s5
| eval {colname6} = s6
| fields - s* col* 

Thanks for all your help.
Stay safe and healthy, you and yours.
God bless,
Genesius

[richgalloway · Accepted solution]

Try this. The "@d+6h" construct means "6 hours after 0:00 today" or 6am. Similarly, "@d+22h" mean 22 hours after 0:00 or 10pm.

| where _time >= relative_time(now(), "@d+6h") AND _time < relative_time(now(), "@d+22h")

[genesiusj]

@richgalloway
I'm not taking away the kudos; however, there is one issue.

Your solution works for previous days; but not for the current day. I just ran the search (3:55 pm). For each of the 7 days, it is graphing 6 am to 3:55 pm. The last 6 hours of the previous 6 days is not graphed.

This is one of the modifications I made
*| where (_time >= relative_time(now(), "-1d@d+6h") AND _time <= relative_time(now(), "-1d@d+22h")) OR (_time >= relative_time(now(), "@d+6h") AND _time <= relative_time(now(), "@d+22h"))
*
but the displayed "dead space (10 pm to 6 am).

Thanks, stay safe and healthy, and God bless,
Genesius

[genesiusj]

@richgalloway
When I use
| where (_time >= relative_time(now(), "-1d@d+6h") AND _time <= relative_time(now(), "-1d@d+22h"))
I now lose all of today's and the previous 6 days time from 6 am to the time the search is run.
Thanks, stay safe and healthy, and God bless,
Genesius

[richgalloway]

The relative_time example I gave you will only work for the current day. For previous days, we'll have to replace now() with something else.

Here's another idea. If you have the 'date_hour' field in your events you could use | where (date_hour >= 6 AND date_hour <= 21).

[genesiusj]

@richgalloway
Please let me know if the pic is not viewable.

Thanks, stay safe and healthy, and God bless,
Genesius

[richgalloway]

The gap in the first graph is expected since events during that time have been filtered out.
The two searches with missing results is normal since the date_time field used in the final where statement is not available after timechart.
The last search filters out all events except those between 0655 and 2200 today.

[genesiusj]

@richgalloway
But with the last example, I'm still not getting the results from now to 2200 for the other 6 days. Is it possible to get that missing data? Maybe you've provided me an answer earlier in the post and I missed it. 🙂
Thanks, stay safe and healthy, and God bless,
Genesius

[richgalloway]

The text in the graphs are too small to read so I can't see what intervals are being reported.
I think we may have covered this already. The last line of the last search tells Splunk to show only those events that occurred between 0655 and 2200 today.
The first search appears to be the most correct. Is the time window set for 7 days?

[genesiusj]

The text in the graphs are too small to read so I can't see what intervals are being reported. The last line of the last search tells Splunk to show only those events that occurred between 0655 and 2200 today.

It doesn't. The results are only from 0655 to ~1230 when the search was run. All results from 1230 to 2200 are not displayed.

Thanks, stay safe and healthy, and God bless,
Genesius

[richgalloway]

If it's only 1230 now, then there should be no events between 1230 and 2200 today.

[genesiusj]

@richgalloway
But why can't I get the 1230 to 2200 for the other 6 days?
Thanks, stay safe and healthy, and God bless,
Genesius

[richgalloway]

I don't understand why that's happening.

[genesiusj]

@richgalloway
At least we are on the same page now. Thanks for your help. Your solution is very helpful because the tech team can compare the current timeframe from today with the previous 6 days for that timeframe.
I'll keep plugging away.
Thanks, stay safe and healthy, and God bless,
Genesius

[genesiusj]

@richgalloway
Perfect. Simple answer. Couldn't see it.
Thanks, stay safe and healthy, and God bless,
Genesius