topic Force Splunk to Read Date as a Value but Display as Human Readable in Dashboards & Visualizations

Force Splunk to Read Date as a Value but Display as Human Readable

masonwhite — Thu, 21 Jun 2018 17:14:27 GMT

I am working on creating an other than normal bubble chart. -Basically i am plotting 2 string values/categories (y-axis, and color), 1 numeric value ( bubble size), all over time(x-axis). I know this is traditionally NOT what bubble charts are for but this is what the requirement is. Currently i am able to "trick" the chart to plot the data correctly by converting the timestamp into Epoch time and then using epoch as a value number. This plots the data correctly but now i want to be able to view the x axis as the dates not epoch millions values.

I see 2 ways to solve this, create a post-stats command but pre-table command that convert the timestamp into somewhat readable format but still forces Splunk to read it as a numeric value

Honestly more preferred do some kind of custom html that would allow me to change the values of the axis that are displayed, and keep the plotting values "behind the curtain"
-my y axis i had to change to string value IDs and would like those displayed as the string value as well as they are currently being displayed as numbers..

index=hidden sourcetype=hidden 
| eval z_axis=if("a special calculation that generates a value") 
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(_time) as date
| stats sum(z_axis) as "Score" by "Value", date, ID 
| table "Value", date, ID, "Score"![alt text][1]

Re: Force Splunk to Read Date as a Value but Display as Human Readable

FrankVl — Thu, 21 Jun 2018 17:19:56 GMT

Presenting something in a readable format, while keeping the numeric value under the hood sounds like a job for the fieldformat command, which does exactly that.
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Fieldformat

 index=hidden sourcetype=hidden 
 | eval z_axis=if("a special calculation that generates a value") 
 | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(_time) as date
 | fieldformat date = strftime(date,"%Y-%m-%d %H:%M:%S")
 | stats sum(z_axis) as "Score" by "Value", date, ID 
 | table "Value", date, ID, "Score"

PS: why the convert and mktime? The _time field already is an epoch value normally (but presented in the GUI as a readable string). So I think the following should be the same:

 index=hidden sourcetype=hidden 
 | eval z_axis=if("a special calculation that generates a value") 
 | eval date = _time
 | fieldformat date = strftime(date,"%Y-%m-%d %H:%M:%S")
 | stats sum(z_axis) as "Score" by "Value", date, ID 
 | table "Value", date, ID, "Score"

Of course in either case, adjust the time format string in the fieldformat command to your preference.

Re: Force Splunk to Read Date as a Value but Display as Human Readable

masonwhite — Thu, 21 Jun 2018 17:25:42 GMT

fieldformat may work... any idea how to fieldformat an epoch number into a date? im drawing a blank on my end.

Re: Force Splunk to Read Date as a Value but Display as Human Readable

FrankVl — Thu, 21 Jun 2018 17:30:05 GMT

See my updated answer, just adjust the time format string to whatever you want. If you just want the date printed, then use "%Y-%m-%d" for instance 🙂

Re: Force Splunk to Read Date as a Value but Display as Human Readable

masonwhite — Thu, 21 Jun 2018 17:37:31 GMT

For some reason when using _time or the query with the fieldformat the bubble chart doesnt understand the values. i included a screenshot of what the chart looks like after doing the fieldformat. its almost as if i need to edit the values after the chart is rendered...?

Re: Force Splunk to Read Date as a Value but Display as Human Readable

masonwhite — Thu, 21 Jun 2018 17:40:16 GMT

if you are able to open the first jpg "bubble" this is the data plotted correctly and then view the second jpg "bubble2" this is what happens when any manipulation to the values is done

Re: Force Splunk to Read Date as a Value but Display as Human Readable

FrankVl — Thu, 21 Jun 2018 17:42:53 GMT

What if you move the fieldformat a bit further down (all the way to the bottom perhaps, but at least past the stats command)?

Re: Force Splunk to Read Date as a Value but Display as Human Readable

masonwhite — Thu, 21 Jun 2018 17:43:09 GMT

Also yes your are correct the convert mktime was a redundant command and the chart does still plot correctly with the eval command. the issue i was having was i was trying to use _time in my stats command, which did not work.

Re: Force Splunk to Read Date as a Value but Display as Human Readable

masonwhite — Thu, 21 Jun 2018 17:46:34 GMT

tried that, moved alll the way to the last line, it still renders the chart with the values post field format

Re: Force Splunk to Read Date as a Value but Display as Human Readable

FrankVl — Thu, 21 Jun 2018 17:49:09 GMT

You could try printing the date as 20180621, such that is a number and properly sorts. Not as pretty, but may do the job?

Re: Force Splunk to Read Date as a Value but Display as Human Readable

masonwhite — Thu, 21 Jun 2018 17:53:04 GMT

ahh good idea, same concept im doing now. if all else fails that is much better than 1,534,566,000... haha

Re: Force Splunk to Read Date as a Value but Display as Human Readable

FrankVl — Thu, 21 Jun 2018 18:03:14 GMT

I got an example working locally. This format works, but the x-axis is continuous, so you get a bunch of whitespace between 20180531 and 2010601...
And it still prints thousands separators.