topic Re: Chart displaying UNIX timestamp on x axis. How to change to human readable date? in Splunk Search

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

phoenixdigital — Fri, 11 Feb 2011 13:41:55 GMT

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

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

Ron_Naken — Fri, 11 Feb 2011 14:44:31 GMT

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

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

phoenixdigital — Mon, 14 Feb 2011 12:09:22 GMT

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

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

phoenixdigital — Mon, 14 Feb 2011 12:09:40 GMT

apologies for the formatting seems this wont accept html

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

Ron_Naken — Tue, 15 Feb 2011 08:00:00 GMT

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.

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

Ron_Naken — Tue, 15 Feb 2011 08:03:01 GMT

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