Splunk Search

Chart displaying UNIX timestamp on x axis. How to change to human readable date?

phoenixdigital
Builder

Hi All,

I'll start with the data we are dealing with. It deals with predictions of a price into the future. We receive data every five minutes with a prediction of a future price.

First set of data
DateTimeOfPrediction, PredictionTime, Price, regionID
2011/02/11 15:05:00, 2011/02/11 15:10:00, 30, VIC
2011/02/11 15:05:00, 2011/02/11 15:15:00, 33, VIC
2011/02/11 15:05:00, 2011/02/11 15:20:00, 32, VIC
2011/02/11 15:05:00, 2011/02/11 15:25:00, 34, VIC
2011/02/11 15:05:00, 2011/02/11 15:30:00, 36, VIC
2011/02/11 15:05:00, 2011/02/11 15:35:00, 33, VIC

Then the next set of data arrives 5 minutes later
DateTimeOfPrediction, PredictionTime, Price, regionID
2011/02/11 15:10:00, 2011/02/11 15:10:00, 30, VIC
2011/02/11 15:10:00, 2011/02/11 15:15:00, 37, VIC
2011/02/11 15:10:00, 2011/02/11 15:20:00, 33, VIC
2011/02/11 15:10:00, 2011/02/11 15:25:00, 34, VIC
2011/02/11 15:10:00, 2011/02/11 15:30:00, 32, VIC

As you can see we end up getting multiple copies of PredictionTime for each time the prediction is made. Now I want to chart the most recent prediction for each time. Meaning x-axis being the PredictionTime and y-axis being the Price.

So we started with this which works and gets the most recent versions of each PredictionTime
sourcetype="p5_reports" | dedup PredictionTime, regionID

Next we tried to set the _time to the PredictionTime as by default splunk made _time = DateTimeOfPrediction
sourcetype="p5_reports" | dedup PredictionTime, regionID | eval _time=strptime(PredictionTime,"%Y/%m/%d %H:%M:%S")

This fails everytime and gives the error
[EventsViewer module] Cannot access search data; job 1297402597.221 is a zombie and is no longer with us

So we played around some more and can get charting however the x axis labels are only displaying unix timestamps not a human readable date.
semi working version
sourcetype="p5_reports" | dedup PredictionTime , regionID| eval IDT=strptime(PredictionTime,"%Y/%m/%d %H:%M:%S") | chart max(Price) by IDT span=5m, regionID

Anyone got an idea how to resolve this and get the x-axis to display a human readable date?

Thanks

Tags (3)
1 Solution

Ron_Naken
Splunk Employee
Splunk Employee

This seems like it would display readable dates along the x-axis:

sourcetype="p5_reports" | dedup PredictionTime regionID | chart max(Price) over PredictionTime by regionID

View solution in original post

Ron_Naken
Splunk Employee
Splunk Employee

This seems like it would display readable dates along the x-axis:

sourcetype="p5_reports" | dedup PredictionTime regionID | chart max(Price) over PredictionTime by regionID

Ron_Naken
Splunk Employee
Splunk Employee

It should work if you convert IDT to EPOCH (strptime), chart by IDT as EPOCH, then eval IDT back to a time/date format after the chart command (strftime).

0 Karma

Ron_Naken
Splunk Employee
Splunk Employee

You need to use strptime() to convert a string into EPOCH -- it looks like you used strftime() -- | eval IDT=strptime(PredictionTime, "%Y/%m/%d %H:%M:%S"). Make sure you get the time format string correct.

0 Karma

phoenixdigital
Builder

apologies for the formatting seems this wont accept html

0 Karma

phoenixdigital
Builder

Thanks heaps that worked alot better than my attempts.

Still a small problem which is probably resolved easily but not with my tests.

As you can see from this screenshot the chart doesn't really show the hours and minutes as it is trying to render the whole date and time.http://i56.tinypic.com/k142td.gif

I tried this but got no results unfortunately

sourcetype="p5_reports" | dedup PredictionTime, RegionID | eval IDT=strftime(PredictionTime, "%H:%M") | chart max(RRP) over IDT by RegionID

This has no results unfortunately

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...