Plotting Date vs Time in Splunk

[shikhanshu]

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?

[martin_mueller]

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

[martin_mueller]

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

[martin_mueller]

You should be able to move the delta (and flip the sign) by reversing the events before applying the delta command.

[shikhanshu]

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.

[martin_mueller]

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.

[shikhanshu]

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.

[shikhanshu]

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.