phpoffice/phpspreadsheet is a successor of phpExcel. We recently developed an automated solution to pull all the backup success rate reports from Dell EMC Data Protection Advisor (DPA). The portal which displays the report was good but not enough to use that information further like sharing the reports, pivoting etc..
After trying several methods, found this library to be very useful. There aren’t many examples available on the internet to retrieve data from MySQL and write to XLSX file. Hope this blog might be helpful who would like to use this library for creating XLSX files. Fear not, it doesn’t throw errors while opening this file in MS Office Excel
In continuation to my previous blog on charting epoch time series MySQL data using PHP. I started exploring generating better reports from the DB.
Inspired by the EMC SRM (M&R) storage capacity reports in PDF format, I thought of doing similar kind of reports “in-house” which should be simple yet standard to be shared with customers. I would like to thank Sapan Kumar from EMC who thought me so many things about EMC’s SRM (M&R) custom reporting features.
We have the data in MySQL DB and wanted to use the PDF module in PHP to generate reports. But which module is suitable and easy to use?
After rigorous testing with FPDF and DOMPDF, I decided to use FPDF (not sure why :p) and it produced nice reports indeed!
Please find sample reports screenshot produced below for reference. You could see the capacity reports of VNX and VMAX storage arrays.
If interested, please reach out to me to get the PHP code. Thanks for stopping by… Please leave your comments / suggestions.
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.