My event has fields like this:
_time = <timestamp>
target_date1 = "1/1/2015"
target_date2 = "2/3/2015"
target_date3 = "4/5/2015"
A subsequent event may have different values for the above "target_date" fields. What I want to chart using Splunk is how the target dates have changed over time. The target dates are nothing but strings in mm/dd/YYYY format
Since "chart" command cannot (understandably) plot strings on the Y-axis, I am converting the fields to epoch time using strptime and that works fine. But to the end user, I want the chart to show date strings again (instead of epoch time).
Can I do this somehow?
Here's a thought: Instead of plotting the dates themselves or their epoch timestamp you plot the delta in days between events.
base search | foreach target_date* [eval target_epoch<<MATCHSEG1>> = strptime('<<FIELD>>', "%m/%d/%Y") / 86400] | delta target_epoch1 as delta1 | delta target_epoch2 as delta2 | delta target_epoch3 as delta3 | timechart avg(delta*) as "Delta Days: Target Date *" | fillnull
delta
can be replaced with streamstats
, which can do wildcards.
... | streamstats current=f window=1 last(target_date*) as last_target_date* | foreach target_date* [eval delta<<MATCHSEG1>> = (strptime('<<FIELD>>', "%m/%d/%Y") - strptime('last_target_date<<MATCHSEG1>>', "%m/%d%Y)) / 86400] | timechart avg(delta*) as ...
You should be able to move the delta (and flip the sign) by reversing the events before applying the delta command.
Looks like "delta" command cannot have wildcards. I am using this search in a dashboard panel which has a multiselect dropdown. Whichever "target_dates" user wants to plot are the ones that should be plotted. I am using the value prefix and value suffix of the multiselect to build a string like "target_date1" "target_date2" etc. and passing this token to "fields" command to limit the fields. And after that I am doing the epoch conversion etc. I need to be able to run delta command on just those fields as well. Doesn't seem like there is a way to do that.
Those numbers seem right to me, in what way are they off?
For example, May 1st to May 15th is a change of 14 days.
Well. The difference is right once i flip the sign. But the rise and fall of the curve is now off by a step so it is a bit misleading.
For instance, if i could plot the actual dates, i would see a bump at the third data point. But if i plot the delta, i see the bump at second data point.
I have decided to stick to epoch for now, at least the curve is consistent with the data.
Thanks a lot for your help! Looks like there is no way to modify the chart data only for visualization while keeping the actual plot days in epoch underneath.
This doesn't look right.
Let's say target_date1 varies as follows with time:
5/1/2015
5/1/2015
5/1/2015
5/15/2015
5/15/2015
5/7/2015
5/7/2015
4/30/2015
The delta gives corresponding values as:
0
0
-14
0
8
0
7
empty
I multiplied by -1 to reverse the sign, but even then the graph is wrong. It is not indicative of the real trend.