Dashboards & Visualizations

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

masonwhite
Explorer

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

or

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]

masonwhite
Explorer

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...? alt text

0 Karma

FrankVl
Ultra Champion

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.

masonwhite
Explorer

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

0 Karma

masonwhite
Explorer

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.

0 Karma

masonwhite
Explorer

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

0 Karma

FrankVl
Ultra Champion

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

0 Karma

masonwhite
Explorer

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

0 Karma

FrankVl
Ultra Champion

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

0 Karma

masonwhite
Explorer

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

0 Karma

FrankVl
Ultra Champion

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.

0 Karma

FrankVl
Ultra Champion

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 🙂

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