Automatically Download MySQL Enterprise Monitor Graphs as PNG Files Using Perl

I was giving a presentation of the MySQL’s Enterprise Monitor* application to a client recently. I was demonstrating the “graphs” section of MEM, where you can monitor MySQL sessions, connections, replication latency and more with 60+ graphs. Usually, you view the graphs from within the MEM Enterprise Dashboard (via a web browser). But the client asked if there was a way to automatically download graphs. I wasn’t sure why he wanted to download the graphs (I didn’t ask), but I knew it wasn’t possible by using MEM alone. However, in the past I have written Perl scripts to automatically download files from web sites, so I thought I would see if it was possible with MEM.

 
*The MySQL Enterprise Monitor (MEM) continuously monitors your MySQL servers and alerts you to potential problems before they impact your system. Its like having a “Virtual DBA Assistant” at your side to recommend best practices to eliminate security vulnerabilities, improve replication, optimize performance and more. As a result, the productivity of your developers, DBAs and System Administrators is improved significantly. (from: http://www.mysql.com/products/enterprise/monitor.html)
 
 

Of course, you have to install MEM and at least one agent. Let’s assume that you have already accomplished this task, and that MEM is running properly. Open MEM in your browser, login, click on the graphs tab, and then you will see a list of all of the available graphs.

For this example, we are going to automatically download the Agent Reporting Delay and the Disk IO Usage graphs. We will download the first graph for all of the servers in a particular group, and the second graph for an individual server. First, click on a server group in your server list on the left side of MEM.

Next, we will need to change the Time Range settings to “From/To”, so that we can enter the a timeline for the graph in our script. Don’t worry about the time settings that are in MEM, as we will change these settings later, but we need them so that they will be included in the URL that we will use (more on this later). After you have changed the Time Range settings, click on the “Filter” button.

Next, click on the plus sign for the graph that you want to use so that MEM will draw the graph. For this example, we will click on the “Agent Reporting Delay” graph:

You will notice two icons to the right of the graph name. The first icon (on the left) allows you click on the icon to download the graph as a .csv file. The second icon (on the right) allows you to click on the icon and download the graph as a PNG image file.

We need some information from the actual link that is used when you click on the PNG icon. So, we will need to right-click on the icon to get the URL link location information for the Agent Reporting Delay graph:

The URL for this graph is then copied to your clipboard. This is the URL location (which is for all servers in the group that I selected):

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=f924cb42-fed5-11df-923c-a6466b4620ce&locale=en_US&noDefaults=false&servers_group=0&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=11%3A24&time_toDate=2011-11-16&time_toTime=11%3A54&time_type=FROMTO&tzName=America%2FNew_York

As you can see in the URL above, there are several variable values that we will include in our script to produce our graphs (in blue text above). In this example, we will only be working with the following variables:
- dims_height
- dims_width
- time_fromDate
- time_fromTime
- time_toDate
- time_toTime
- graph name/ID (which is a UUID and is constant)
- servers_group and servers_server
(the servers_server variable and value are not shown in the above example, but will be in the next example below)

We will be using a text file named files.txt to store some of the graph variable values that will be used by the script. Now that you know how to copy the URL for a graph, you will need to extract the value for the graph variable and the value for the servers variable and place the values into your files.txt file. The graph value for the above URL (shown again below) is in blue text, and the value for the server variable is in red text: (notice that all values are separated on the left by an equal sign “=” and on the right by an ampersand “&”)

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=f924cb42-fed5-11df-923c-a6466b4620ce&locale=en_US&noDefaults=false&servers_group=0&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=11%3A24&time_toDate=2011-11-16&time_toTime=11%3A54&time_type=FROMTO&tzName=America%2FNew_York

In the above example, we had selected a group of servers in our server list (on the left side of MEM), and therefore the URL will not have a value for the individual server (variable named servers_server). The graph that we will extract will be for this group of servers (in this case servers_group has a value of zero, which is still a value). This is what we had chosen under our Servers list:

Now, we want to select an individual server. In this case, we will click on “iMac-Tony”:

Now that we have chosen an individual server, in the URL for that graph, you will have a value for the variable named “servers_server”, as well as a value for servers_group – and you will need both values together. So, if you want a graph for an individual server, you will need to click on that individual server in your servers list, reselect the “Time Range” value of “From/To”, click “Filter”, and re-copy the PNG graph URL. Once we have copied the URL for this graph for an individual server, you will see a different value for the graph variable (in red) and a value for servers_group and servers_server (in blue) like this:

http://192.168.1.2:18080/Graph.action?dims_height=300&dims_width=800&graph=6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c&locale=en_US&noDefaults=false&servers_group=0&servers_server=111&style=NORMAL&time_fromDate=2011-11-16&time_fromTime=15%3A27&time_toDate=2011-11-16&time_toTime=15%3A57&time_type=FROMTO&tzName=America%2FNew_York

We will use the above URL information for our second graph – the Disk IO Usage graph. You will need to copy all of the graph and server values for the graphs that you want to download. For the above URL, we will grab these values, to be placed in our files.txt file:
graph = 6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c
server group and server name = servers_group=0&servers_server=111

This is a tedious process at first, but you should only have to do this once for each graph. Mark Leith from the MySQL software development team in the UK gave me some great information for finding out the graph names along with the graph UUID value (be sure to also check out Mark’s blog). Mark stated “The uuid per graph does not change over time/versions – it’s how we maintain the constant between them in fact.” The graph name and graph value (UUID) is stored in the MEM Service Manager repository database, which contains all of the statistical information for MEM. To access this database, simply login to your MEM Service Manager MySQL repository database with the following syntax:

mysql -uroot -p -P13306 -h127.0.0.1

During the installation of MEM, if you decided to use your current MySQL database to store the repository information, you will need to just login to that database. Once you have logged into the repository database, you can get a list of the graph names and graph UUID’s with this command: