Splunk Search

Plotting Date vs Time in Splunk

shikhanshu
Path Finder

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?

Tags (4)
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

shikhanshu
Path Finder

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

shikhanshu
Path Finder

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.

0 Karma

shikhanshu
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...