MySQL PHP Generating reports in PDF format

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.

2016-11-30-11_07_12-mremoteng-confcons-xml2016-11-30-11_03_57-mremoteng-confcons-xml

If interested, please reach out to me to get the PHP code. Thanks for stopping by… Please leave your comments / suggestions.

Advertisements

MYSQL PHP Charting epoch Time series data

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

2016-11-13-14_27_22-10-76-6-62-remote-desktop-connection

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.

EMC VMAX Storage Automated Performance Report

This blog is being written as a companion to my previous blog on Automated EMC VMAX Capacity Reporting

platform-vmax

In recent times, we’re asked to develop scripts to capture performance metrics from EMC VMAX storage. There is a ‘symstat’ command with many attributes to capture performance metrics information from the array. But this command was not fulfilling all our requirements. While exploring various options and consultation with EMC support / community we decided to try Unisphere / RESTAPI.

So far I was using Perl as THE LANGUAGE to talk to my storage arrays. But I was forced to switch over to Python which works best with REST API / JSON. Additionally, there are lots of code out there on RESTAPI written in Python. So it is easy to ‘get inspired’ by those codes and write customized code for our requirements. So this would make me yet another ‘Pythonistas’ 🙂

This is my first ever Python (version 2.7 on GNU/Debian Linux) script to capture EMC VMAX Performance Metrics retrieved from Unisphere for VMAX (version 8.2) via RESTAPI. I’ve referred this Python script to develop custom script to suit our requirements. Many thanks to Matt Cowger (mcowger) for sharing the script in Github.

There are plenty of metrics that can be captured using this script but I’ve written a simple code for demo purpose to print few metrics in CSV format which can be either imbibed by excel for further reporting / charting or injected to MySQL DB to do many stuffs…

Here is the sample + cropped output for reference. In the below table timestamp (column B) is in epoch format which is converted to MYSQL datetime format via INSERT query

2016-11-04-22_25_58-book1-excel

 

P.S: I’ve changed VMAX serial number for various factors🙂

If interested, please reach out to me to get these Python scripts.

Image Courtesy: https://www.emc.com

References: https://github.com/mcowger/randompython/blob/master/symmREST.py

Thanks for stopping by… Please leave your comments / suggestions.

EMC VMAX Storage Automated Capacity Report

EMC VMAX3

Storage capacity reporting has become a tedious task these days unless we don’t have tools in place. These tools are not as cheap we can think of. There are additional layers added to it to hype and increase its value in market. But if customer ask a storage admin to run the report manually then its a nightmare for the admins.

We always want to go by the easy way which means GUI / EMC Unisphere but for reporting this doesn’t helps to customize and fulfill the customer requirements . EMC SYMCLI / Solutions Enabler can be used effectively here to address this issue. We can use SYMCLI to automate these manual reports. By means of automation, we can ensure quality, timely and error-free reports are generated which can be scheduled via CRON or Tasks Scheduler to send it directly to the stakeholders. SYMCLI built-in supports XML formatted output of the commands which would become easier to parse the information using XML supported programming language.

I’ve developed PERL scripts to generate Storage Pool capacity and Disk capacity report for multiple VMAX storage arrays which are managed from the SYMCLI / Solutions Enabler server. Following are the benefits of using this script.
1. Generates Pool capacity report for all storage arrays which has columns – SYM ID,MODEL,TOTAL USABLE POOL CAPACITY TB,TOTAL USED POOL CAPACITY TB,TOTAL FREE POOL CAPACITY TB, TOTAL POOL UTILIZATION %, TOTAL POOL SUBSCRIBED %
2. Generates Disk capacity report for all storage arrays which has columns – SYM ID,MODEL,TOTAL EFD,TOTAL FC_SAS,TOTAL SATA_NLSAS,FORMATTED EFD CAPACITY GB,FORMATTED FC_SAS CAPACITY GB,FORMATTED SATA_NLSAS CAPACITY GB, TOTAL FORMATTED CAPACITY GB, UNFORMATTED EFD CAPACITY GB,UNFORMATTED FC_SAS CAPACITY GB,UNFORMATTED SATA_NLSAS CAPACITY GB,TOTAL UNCONFIGURED CAPACITY GB,TOTAL UNFORMATTED CAPACITY GB

Output generated from the scripts will be in the form of CSV format. This is information can be plugged into desired format and generate reports with Pivots, charts etc…

Please find below  sample output for reference.

Pool Capacity Report:

2016-02-17 21_21_57-Book1 - Excel

Disk Capacity Report:

2016-02-17 21_22_41-Book1 - Excel

P.S: I’ve removed VMAX serial number for various factors 🙂

If interested, please reach out to me to get these PERL scripts.

Image Courtesy: http://www.storagereview.com and http://www.emc.com

Symmigrate – symcli command to migrate devices

Please find the steps to perform Virtual LUN Migration using symmigrate command. Output is self explanatory and verbose in nature 🙂

In order to migrate a group of thin devices contained in the VP_ProdApp1 storage group, currently bound to a RAID 6 14+2 on SATA thin pool, to a RAID 1 on FC thin pool, the following steps would be performed:

1. Validate the migration:
symmigrate -sid 1234 -sg SG-group1 -tgt_pool -pool ST_R662 -name migration validate

2. Establish the migration:
symmigrate -sid 1234 -sg SG-group1 -tgt_pool -pool ST_R662 -name migration establish

3. Monitor the migration progress:
symmigrate -sid 1234 -name migration query

4. Verify the migration has completed:
symmigrate -name migration verify -migrated

5. Terminate the migration:
symmigrate -sid 1234 -name migration terminate

C:\Users\>symmigrate -sid 1234 -sg SG-group1 -tgt_pool -pool ST_R662 -name migration validate

Execute ‘Validate’ operation execution for
the device group ‘SG-group1’ (y/[n]) ? y

‘Validate’ operation execution is in progress for
the device group ‘SG-group1’. Please wait…

Validate Migration……………………………………….Started.
Validate Migration……………………………………….Done.

‘Validate’ operation successfully executed for
the device group ‘SG-group1’.

C:\Users\>symmigrate -sid 1234 -sg SG-group1 -tgt_pool -pool ST_R662 -name migration establish

Execute ‘Establish’ operation execution for
the device group ‘SG-group1’ (y/[n]) ? y

‘Establish’ operation execution is in progress for
the device group ‘SG-group1’. Please wait…

Establish Migration………………………………………Started.
Establish Migration………………………………………Done.

‘Establish’ operation successfully executed for
the device group ‘SG-group1’.

C:\Users\>symmigrate -sid 1234 -name migration query

Symmetrix ID: 000198701234

Invalid    Status    Done Flags
Src  Tgt    Tracks  SRC => TGT   (%)   T   Session Name
—- —- ——– ———— —- —– —————-
04DD  N/A 62915280 SyncInProg      0   V   migration
04ED  N/A 62915280 SyncInProg      0   V   migration
04FD  N/A 62915280 SyncInProg      0   V   migration
050D  N/A 62915280 SyncInProg      0   V   migration
0680  N/A 31457640 SyncInProg      0   V   migration

Total     ——–
Tracks   283118760
MB(s)    17694922
Done(%)         0

Flags:
(T)ype: V = The session is a Virtual Provisioning migration session.
C = The session is a configured migration session.
U = The session is an unconfigured migration session.
P = The session is a Virtual Provisioning pool migration session.

C:\Users\vbabuume>symmigrate -sid 1234 -name migration query

Symmetrix ID: 000198701234

Invalid    Status    Done Flags
Src  Tgt    Tracks  SRC => TGT   (%)   T   Session Name
—- —- ——– ———— —- —– —————-
04DD  N/A        0 Migrated      100   V   migration
04ED  N/A        0 Migrated      100   V   migration
04FD  N/A        0 Migrated      100   V   migration
050D  N/A        0 Migrated      100   V   migration
0680  N/A        0 Migrated      100   V   migration

Total     ——–
Tracks          0
MB(s)         0.0
Done(%)       100

Flags:
(T)ype: V = The session is a Virtual Provisioning migration session.
C = The session is a configured migration session.
U = The session is an unconfigured migration session.
P = The session is a Virtual Provisioning pool migration session.

C:\Users\>

C:\Users\>symmigrate -sid 1234 -name migration verify -migrated

All session(s) with name ‘migration’ are in ‘Migrated’ state.

C:\Users\>symmigrate -sid 1234 -name migration terminate

Execute ‘Terminate’ operation execution for
the session named ‘migration’ (y/[n]) ? y

‘Terminate’ operation execution is in progress for
the session named ‘migration’. Please wait…

Terminate Migration………………………………………Started.
Terminate Migration………………………………………Done.

‘Terminate’ operation successfully executed for
the session named ‘migration’.

VMAX Storage Group Report

Here is the the simplest way to generate a simple VMAX Storage Group’s allocated capacity report.

Run the below command

symsg -sid 1234 list -v > sglist.txt

Above command show each SG’s information in details along with Devices, type and capacity. We’ve redirected the output to a text file called ‘sglist.txt’. To produce a simple report using the above output; we need to use the below grep command.

grep ‘Name:\|2-way\|TDEV’ sglist.txt > out.txt

Above command greps lines having name of storage group, devices with 2-way mirror config and thin devices which are redirected to another text file called ‘out.txt’. We can see the devices with Status, capacity etc… which is sufficient to produce / calculate allocated storage capacity.

How to set/limit IOPS on VMAX per storage groups

Here is a good article on how to limit IOPS per storage groups on VMAX

https://community.emc.com/message/789943