I was writing PHP code to generate EMC VMAX performance reports / charts stored in MySQL DB. As we know that Unisphere REST API output performance metrics in epoch (13 digits) format (milliseconds). As an example, to draw a line chart for Frontend Director’s Read Response Time with X axis / Abscissa as epoch time. I’ve used below query to generate the datapoints to draw the chart using pChart
SELECT TIME(FROM_UNIXTIME(timstamp/1000)) as TIME, ROUND(AVG(perfval),2) as wrt FROM vmax_perf
where DAYOFMONTH(FROM_UNIXTIME(timstamp/1000)) = DAYOFMONTH(DATE_SUB(curdate(), INTERVAL 1 DAY))
and objname =’fe’ and perfparam =’ReadResponseTime’
group by HOUR(FROM_UNIXTIME(timstamp/1000))
Let me explain the above statement
TIME(FROM_UNIXTIME(timstamp/1000)) :: Extract Time from the epoch (13 digit) format in millisecond
ROUND(AVG(perfval),2) :: Average of Read Response Time per Hour rounded output to 2 digits after decimal point
DAYOFMONTH(FROM_UNIXTIME(timstamp/1000)) = DAYOFMONTH(DATE_SUB(curdate(), INTERVAL 1 DAY)) :: Extract day (ex. ’12’ from 12th Nov 2016 where current date is 13th Nov 2016) and display the data points
group by HOUR(FROM_UNIXTIME(timstamp/1000)) :: Print the datapoints for every hour
If interested, please reach out to me to get the PHP code.
Stay Tuned for more updates on MySQL queries to generate charts for Daily, Weekly, Monthly and Quarterly performance reports / charts. Thanks for stopping by… Please leave your comments / suggestions.
Thanks for stopping by… Please leave your comments / suggestions.
One thought on “MYSQL PHP Charting epoch Time series data”