I'm wondering if there isn't some way to use custom relative times in Splunk. I suspect not, but I thought I'd ask.
Here's the scenario. My company uses a time delineator called a 'period' that is 4 weeks long. This does not align on month boundaries and even better it doesn't start on specific times every year. People have been asking me if there's some way they can run a search that is effectively
<search stuff> earliest=-2P@P latest=@P
Where P is this custom 'period' thing. The pseudo search above would show events for the previous 2 periods.
I haven't seen anything in either Answers or the docs indicating that this is doable using the convention above. That is, defining something other than 'w', 'd', 'mon' etc for relative time.
Given that that's not possible, I'm assuming the next approach would be to setup a lookup table that I populate every year that showed start and stop dates for each period and come up with some fancy SPL to read that table and do some date calculations to determine the periods. I haven't looked into that last bit yet, but it's floating around in my mind now :-).
@mfrost8, based on the updated details, please find below a run anywhere example which will generate Custom Time Period of 4 week (28 days) using gentimes command as per the further details provided by you.
1) Following is the query that generates the Earliest and Latest Time for specific period:
| gentimes start=1/1 increment=28d | fields starttime endtime | eval P1_String=strftime(starttime,"%Y/%m/%d")." - ".strftime(endtime,"%Y/%m/%d"),P1_Epoch=starttime."-".endtime | table P1_String, P1_Epoch
I have purposely not added
end=12/31 as a parameter to gentimes command so that it generates time period only based on the current time.
2) The default time for Custom Time Dropdown is set to current period using dummy search with post-processing to set to latest (most current) period. Null results for first query and this post-process search needs to be handled (I have purposely not looked into it).
<eval> tags have been used to split Epoch Earliest and Latest time to be passed on to Dashboard Searches based on delimeter hyphen
-and String Earliest and Latest time have been split to be displayed on Panel Title to display time range.
Following is the Simple XML code for the run any where dashboard.
<form> <label>Custom Time Period 4 weeks</label> <search base="baseCustomPeriodSearch"> <query>| tail 1 </query> <done> <set token="tokLatestPeriod">$result.P1_Epoch$</set> </done> </search> <fieldset submitButton="false"> <input type="dropdown" token="tokTimePeriod" searchWhenChanged="true"> <label>Select Time Period</label> <fieldForLabel>P1_String</fieldForLabel> <fieldForValue>P1_Epoch</fieldForValue> <default>$tokLatestPeriod$</default> <search id="baseCustomPeriodSearch"> <query>| gentimes start=1/1 increment=28d | fields starttime endtime | eval P1_String=strftime(starttime,"%Y/%m/%d")." - ".strftime(endtime,"%Y/%m/%d"),P1_Epoch=starttime."-".endtime | table P1_String, P1_Epoch</query> <earliest>-1s@s</earliest> <latest>now</latest> </search> <change> <eval token="tokTimeEarliestEpoch">mvindex(split($value$,"-"),0)</eval> <eval token="tokTimeLatestEpoch">mvindex(split($value$,"-"),1)</eval> <eval token="tokTimeEarliestString">mvindex(split($label$," - "),0)</eval> <eval token="tokTimeLatestString">mvindex(split($label$," - "),1)</eval> </change> </input> </fieldset> <row> <panel> <title>Error Timechart between $tokTimeEarliestString$ - $tokTimeLatestString$</title> <chart> <search> <query>index=_internal sourcetype=splunkd log_level!=INFO | timechart count by component limit=5 useother=f</query> <earliest>$tokTimeEarliestEpoch$</earliest> <latest>$tokTimeLatestEpoch$</latest> <sampleRatio>1</sampleRatio> </search> <option name="charting.chart">area</option> <option name="charting.drilldown">none</option> <option name="refresh.display">progressbar</option> </chart> </panel> </row> </form>
Wow. That's pretty cool. Yet another command I've never heard of (gentimes).
What I was hoping for was to be able to do reporting per period. The partly hypothetical example rattling around in my head would be too average the total daily number of user per period. So like, maybe I want to show the first 2 periods of the year, or explicitly periods 3, 4 and 5. And ideally have them labelled with the period name.
While the application group hasn't explicitly asked for this, they've hinted at it. Mostly what these guys do is generate a daily PDF that they send off to their leadership showing their application usage. Currently they're doing it monthly and weekly.
Thanks again, for all of your efforts on this. Greatly appreciated.
@mfrost8, If you need to customize the start date and end date for a period or multiple periods, your option would be to pass
increment parameters to
gentimes as per your need. For two period it will will be
2*28=56d. I would say your specific customization should still be possible around the run anywhere example provided above. Hope this helps!
If you can get time ranges as suggested. For applying custom periods, you can use dropdown. You can use static periods as per the example below or define periods in a lookup file.
<form> <label>Span P=4 weeks</label> <fieldset submitButton="false"> <input type="time" token="tokTime" searchWhenChanged="true"> <label>Select Time</label> <default> <earliest>-30d@d</earliest> <latest>now</latest> </default> </input> <input type="dropdown" token="tokPeriod" searchWhenChanged="true"> <label>Select Period</label> <choice value="4w">1P (4 Week)</choice> <choice value="8w">2P (8 Week)</choice> <choice value="12w">3P (12 Week)</choice> <choice value="16w">4P (16 Week)</choice> </input> </fieldset> <row> <panel> <title>Errors By Component Split by Selected period</title> <chart> <search> <query>index=_internal sourcetype=splunkd log_level!="ERROR" component!="Metrics" AND component!="Periodic*" AND component!="Search*" | timechart span=$tokPeriod$ count by component limit=5 useother=f</query> <earliest>$tokTime.earliest$</earliest> <latest>$tokTime.latest$</latest> <sampleRatio>1</sampleRatio> </search> <option name="charting.axisTitleX.text">Time</option> <option name="charting.chart">bar</option> <option name="charting.chart.showDataLabels">minmax</option> <option name="charting.chart.stackMode">stacked</option> <option name="charting.drilldown">none</option> <option name="refresh.display">progressbar</option> </chart> </panel> </row> </form>
I probably needed to be a bit more explicit in my question. I'd go back and modify it, but I can't seem to figure out how to do that.
Here's an example of some periods this year
P2 = 1/29 - 2/25
P3 = 2/26 - 3/25
P4 = 3/26 - 4/22
P5 = 4/23 - 5/1
and ultimately I'm interesting in reporting on these periods. It would be awesome if I could just use time modifiers ( "earliest=-2P@P", say), but ultimately it would be nice to be able to do a "BY" on an transforming command.
I don't think I follow you on the lookup method. Could you give me an example?
In thinking about this last week, it occurred to me that I could take an approach of counting forward from the start of the year (yeah, periods do at least start on 1/1), and then identify 4 week increments, but that would mean I'd have to pull back the entire year only to filter that down again to just what I wanted. Given that range, that could be expensive.
Thanks very much!
@mfrost8 , I have added another answer based on the details provided. I dont think you need Custom Span selection, rather you need custom Time Selection. Please try out the following new answer and confirm!
If you can calculate period boundaries in an eval expression you could create an eval-based macro that computes "start of current period", "two periods ago", etc. with the period number passed as an argument. Then use that macro to calculate
latest= in your searches.
If you can't calculate period boundaries, e.g. because they're defined arbitrarily, you will need to load that arbitrary list into a lookup and calculate the appropriate period boundaries in subsearches that output
I put a little more detail in my comment above to niketnilay.
Sometimes I wish SPL were a bit more like a scripting language in that I could do some eval calculations (i.e. like setting variables) before I ran the search.
While the dates do seem arbitrary computing them from Jan 1 isn't really that hard as they are definitely every 28 days. And based on that p3_start would be 3*28. Not sure if I can say earliest=@y+28d and latest=@y+55d.
I have a rough idea of what you're suggesting, but I'm still not sure I can imagine the SPL for that. Could you provide something kind of pseudo-code-ish?
"The first period of the year starts on January first" is the key bit of information. Based on that you can calculate all you need.
I only have my phone with me this week so all I can offer for now is a rough draft of pseudocode 🙂
The key is to find the start of the current period, based on the day number in the current year - %j in strftime. Once you do
strftime(time(), "%j") % 28 you know how many days into the past you need to go to get the first day in the current period... Possibly off by one, depending on whether %j starts at 1 or 0. Once you have that you can add or subtract x*28d to get any period.
That makes a lot of sense.
I'm still sort of stuck with the same problem though, aren't I? Since with the exception of a few commands, I have to start with a search. In this case a pretty broad search, like maybe @y and then run another search in the pipeline to limit it only to the calculated time range.
You could also teach your company to not try to re-invent the calendar. Julius Ceasar and Pope Gregory XIII have done quite well doing so, maybe you could use that?
Sorry, couldn't resist that urge, I have a customer that also tried to reinvent the wheel, eh, calendar, and it's really a PITA. 😉
Thanks, niketnilay. That would change the pull-down search ranges, but it wouldn't help me report on them. That is, "show me the average X from P2".
On the one hand, I suppose one could construct something that starts on 1/1 and then rolls forward 4 weeks * the period #, where P2 = 4*2. In that case you'd have to pull back a lot of events and then filter them out.
What I think would be the nicest of the pain-in-the-butt mechanisms would be if I could create a lookup table that defined a manual time range for each period for that year. Yeah, I'd have to do it manually once per year. Unfortunately, that would mean a lookup table value that is actual SPL ("earliest=X latest=Y") that I'd need to have executed and if Splunk allows that I haven't seen it.
I actually need that lookup as SPL capability for something else I'm working on.