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:

SELECT title, uuid FROM mem.graphs;
mysql> SELECT title, uuid FROM mem.graphs;
+--------------------------------------------+--------------------------------------+
| title                                      | uuid                                 |
+--------------------------------------------+--------------------------------------+
| agent_lua_mem_usage.name                   | 545e6c5e-ccab-457e-89ca-cc6e5eeb1e1d |
| agent_reporting_delay.name                 | f924cb42-fed5-11df-923c-a6466b4620ce |
| avg_row_accesses.name                      | f289dae0-82be-11df-9df0-f1c3fca44363 |
| binlog_cache.name                          | f84b270e-7a21-11df-9df0-f30c5eb77a3c |
.....

This should make it easier than copying the variables in the links as described above, but I wanted to show you how to get the information and explain all of the variables in the graph URL links. (Thanks Mark!)

In the files.txt file, we also want a name for the graph (which will also be used for the PNG image file name), the graph value, the servers value and the server or server group values from the above URLs, as well as the name of the server group or individual server. You should separate the values with a delimiter of three tildes "~~~".

So, for the two example graphs above, your files.txt file should contain the following values - Graph Name~~~graph value~~~server information~~~server or group name: (please note that the graph values that I have here may not be the same values that you would have for the same graph)

Agent Reporting Delay~~~f924cb42-fed5-11df-923c-a6466b4620ce~~~servers_group=0~~~All Servers
Disk IO Usage~~~6d9c8ac0-7a3b-11df-9df0-f30c5eb77a3c~~~servers_group=0&servers_server=111~~~iMac Tony

The first line above will produce an "Agent Report Delay" graph for "All Servers". The second line will produce a "Disk IO Usage" graph for only the server named "iMac-Tony".

Now that we have our files.txt file in place (it should be placed in the same folder as the Perl script - or you may modify the Perl script for a different file location), we will use this Perl script to download our graphs as PNG image files. In case you want to place this script in a cron job to run every X number of minutes, we will include a variable to allow you to select the previous number of minutes to include in your graph. For example, in the Perl script, if you set the value of the variable $time_interval to 60 (minutes) and run the job at 30 minutes past the hour, the script will retrieve a graph for the past 60 minutes from the time of script execution.

For this example, we will name the Perl script "get_graphs.pl". There will be some variables in the script that you will have to change once, to match your system's information. The variables that you need to change are highlighted in blue text in the script:


#!/usr/bin/perl

use WWW::Mechanize;
use Date::Calc qw(Add_Delta_DHMS);

# file name for input - this contains the Graph Name and Graph URL
$filename = "files.txt";

# time interval must be in minutes
$time_interval = '60';

# the width of your graph
$dims_width = "800";
# the height of your graph
$dims_height = "300";

# IP and port number of your MEM server
$server = "192.168.1.2:18080";

# get the current time using the display_time_now subroutine
$unixtimenow = &display_time_now();
($time_toDate, $time_toTime) = split(" ",$unixtimenow);

# get the past time using the display_time_past subroutine
$unixtimepast = &display_time_past();
($time_fromDate, $time_fromTime) = split(" ",$unixtimepast);

# fool the web server into thinking we are a person
my $mech = WWW::Mechanize->new();
# look like a real person
$mech->agent('User-Agent=Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1.5) Gecko/20091102 Firefox/3.5.5');
# we need cookies
$mech->cookie_jar(HTTP::Cookies->new);

# Login to the MySQL Enterprise Monitor
$mech->get('http://192.168.1.2:18080/Auth.action');
$mech->success or die "login GET fail";

# you will need to substitute your user name and password for MEM here
my $user = 'tonydarnell';
my $pass = 'tonyd999';

# find a fill out the login form
my $login = $mech->form_name("DoAuth");
$login->value('username' => $user);
$login->value('password' => $pass);
$mech->submit();
$mech->success or die "login POST fail";

open(line, "$filename") || die (print "\nERROR - could not open file: $filename\n");
while (<line>)

{

chomp $_;

print "\n$_\n";

($imagefilename, $graph_to_get, $servers_to_get, $servers_name) = split(/~~~/);

if (length($imagefilename) > 2)

{

$time_toDate_for_filename = $time_toDate;
$time_toDate_for_filename =~ s/\-/_/g;

$time_toTime_for_filename = $time_toTime;
$time_toTime_for_filename =~ s/\:/_/g;

$servers_name =~ s/ /_/g;

$imagefilename =~ s/ /_/g;
$imagefilename = $servers_name . "_" . $imagefilename . "_" . $time_toDate_for_filename . "_" . $time_toTime_for_filename . ".png";

# you will need to change your settings here to match your URL for your graphs
$graph = "http://". $server . "/Graph.action?dims_height=" . $dims_height . "&dims_width=" . $dims_width . "&graph=" . $graph_to_get . "&locale=en_US&noDefaults=false&" . $servers_to_get . "&style=NORMAL&time_fromDate=" . $time_fromDate . "&time_fromTime=" . $time_fromTime . "&time_toDate=" . $time_toDate . "&time_toTime=" . $time_toTime . "&time_type=FROMTO&tzName=America%2FNew_York";

print "\n$graph\n";

#exit;

# Get the PNG image file from the URL
$mech->get($graph);
$mech->save_content($imagefilename);

}

}

exit;

close($filename);

# ------------------------------------------------
# sub-routines

sub display_time_now {
my ($sec,$min,$hour,$mday,$mon,$year,undef,undef,undef) = localtime time();
$year += 1900;
$mon += 1;
return "$year-".sprintf("%02d-%02d %02d:%02d",$mon,$mday,$hour,$min);
}

sub display_time_past {
my ($sec,$min,$hour,$mday,$mon,$year,undef,undef,undef) = localtime time() - ($time_interval*60);
$year += 1900;
$mon += 1;
return "$year-".sprintf("%02d-%02d %02d:%02d",$mon,$mday,$hour,$min);
}

# ------------------------------------------------

When we executed the script, two files were created and downloaded - All_Servers_Agent_Reporting_Delay_2011_11_16_16_17.png and iMac_Tony_Disk_IO_Usage_2011_11_16_16_17.png:


All_Servers_Agent_Reporting_Delay_2011_11_16_16_17.png


iMac_Tony_Disk_IO_Usage_2011_11_16_16_17.png

You could also create a similar script to download the information as a .csv file, but the syntax is very different (maybe I will do that in a future post). But for now, I have a possible solution for the client - and I hope that he likes it.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world's most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Using MySQL, Perl and jQuery to Auto-Populate a Form Field on a Web Page

If you have ever built a form on a web page, you might have used a drop-down menu to display the choices available for a particular field. With a drop-down menu, you restrict the choices a user may select so that the user doesn’t enter invalid data (among other reasons). If a user misspells an entry, then a subsequent search for that value would not produce a found result.

A friend of mine who runs an online forum sent me an email about a problem he was having. He was trying to modify an existing registration web page using jQuery to auto-populate the state names, and then pass the state abbreviation back to his MySQL database. Believe it or not, he was actually having problems with people knowing their own state abbreviation. He had searched and found an example of what he wanted to do, but he couldn’t get it to work. So, I took the example that he found and figured out what he was doing wrong.

I had first suggested that he just hard-code the list of states and their abbreviations in the HTML code. But, he said that if he figured out how to use jQuery with his state abbreviation problem, he would also use it on other parts of his web page where he had dynamic data. With drop-down menus, it is fairly easy to create a list of static data to use in the HTML form. But, in his case, what happens if the data is dynamic, or if there are too many items to list in a drop-down menu? If you have ever had to include your country in a form (and you live in the US), it is always a pain to have to scroll down to the bottom of the long list to find “United States”.

An easy way to create a dynamic list (or even a long static list) is to use jQuery to access your data from a MySQL database, and to build an auto-populated field in your form. When the user starts typing their entry, jQuery will search the MySQL database for a list of “like” matches, and then return a list of all of the matching results for that field.

For this example, we will be using jQuery, Perl and a MySQL database to auto-populate a “state” field in a form. Once a user has typed three characters in the state field box, a list of matching states will appear in a drop-down menu. It will also return the state abbreviation and state id (code), which can then be saved in the MySQL database after the form has been submitted. In this picture from our example, the user has typed “new”, and returned all states that match that string:

First, let’s start by creating the MySQL database that we will use. Here is the SQL code to create the database, which is named “states”. The table will contain the state name, the state abbreviation, and an ID number.

CREATE TABLE `states` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`state` varchar(50) NOT NULL,
`abbrev` char(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Next, you will want to import values into the database. You could do this by importing a .csv file containing this information into MySQL, but to make it quicker for this example, here is the SQL:

insert into states (state, abbrev) values ('Alabama', 'AL');
insert into states (state, abbrev) values ('Alaska', 'AK');
insert into states (state, abbrev) values ('Arizona', 'AZ');
insert into states (state, abbrev) values ('Arkansas', 'AR');
insert into states (state, abbrev) values ('California', 'CA');
insert into states (state, abbrev) values ('Colorado', 'CO');
insert into states (state, abbrev) values ('Connecticut', 'CT');
insert into states (state, abbrev) values ('Delaware', 'DE');
insert into states (state, abbrev) values ('District of Columbia', 'DC');
insert into states (state, abbrev) values ('Florida', 'FL');
insert into states (state, abbrev) values ('Georgia', 'GA');
insert into states (state, abbrev) values ('Hawaii', 'HI');
insert into states (state, abbrev) values ('Idaho', 'ID');
insert into states (state, abbrev) values ('Illinois', 'IL');
insert into states (state, abbrev) values ('Indiana', 'IN');
insert into states (state, abbrev) values ('Iowa', 'IA');
insert into states (state, abbrev) values ('Kansas', 'KS');
insert into states (state, abbrev) values ('Kentucky', 'KY');
insert into states (state, abbrev) values ('Louisiana', 'LA');
insert into states (state, abbrev) values ('Maine', 'ME');
insert into states (state, abbrev) values ('Maryland', 'MD');
insert into states (state, abbrev) values ('Massachusetts', 'MA');
insert into states (state, abbrev) values ('Michigan', 'MI');
insert into states (state, abbrev) values ('Minnesota', 'MN');
insert into states (state, abbrev) values ('Mississippi', 'MS');
insert into states (state, abbrev) values ('Missouri', 'MO');
insert into states (state, abbrev) values ('Montana', 'MT');
insert into states (state, abbrev) values ('Nebraska', 'NE');
insert into states (state, abbrev) values ('Nevada', 'NV');
insert into states (state, abbrev) values ('New Hampshire', 'NH');
insert into states (state, abbrev) values ('New Jersey', 'NJ');
insert into states (state, abbrev) values ('New Mexico', 'NM');
insert into states (state, abbrev) values ('New York', 'NY');
insert into states (state, abbrev) values ('North Carolina', 'NC');
insert into states (state, abbrev) values ('North Dakota', 'ND');
insert into states (state, abbrev) values ('Ohio', 'OH');
insert into states (state, abbrev) values ('Oklahoma', 'OK');
insert into states (state, abbrev) values ('Oregon', 'OR');
insert into states (state, abbrev) values ('Pennsylvania', 'PA');
insert into states (state, abbrev) values ('Rhode Island', 'RI');
insert into states (state, abbrev) values ('South Carolina', 'SC');
insert into states (state, abbrev) values ('South Dakota', 'SD');
insert into states (state, abbrev) values ('Tennessee', 'TN');
insert into states (state, abbrev) values ('Texas', 'TX');
insert into states (state, abbrev) values ('Utah', 'UT');
insert into states (state, abbrev) values ('Vermont', 'VT');
insert into states (state, abbrev) values ('Virginia', 'VA');
insert into states (state, abbrev) values ('Washington', 'WA');
insert into states (state, abbrev) values ('West Virginia', 'WV');
insert into states (state, abbrev) values ('Wisconsin', 'WI');
insert into states (state, abbrev) values ('Wyoming', 'WY');
insert into states (state, abbrev) values ('American Samoa');
insert into states (state, abbrev) values ('Guam', 'GU');
insert into states (state, abbrev) values ('Northern Mariana Islands', 'MP');
insert into states (state, abbrev) values ('Puerto Rico', 'PR');

Now that we have the database, we will need to create the Perl script. Here is the Perl script, and you will need to change the database variables to match your system:

#!/usr/bin/perl -w
use CGI;
use DBI;
use DBD::mysql;
use JSON;

# print the http header specifically for json
print "Content-type: application/json; charset=iso-8859-1\n\n";

# your database variables
my $database = "scripting_mysql";
my $host = "192.168.1.2";
my $port = "3306";
my $tablename = "states";
my $user = "root";
my $pass = "root_password";
my $cgi = CGI->new();
my $term = $cgi->param('term');

# mysql connection information
$dsn = "dbi:mysql:$database:$host:$port";

# open the database connection
$connect = DBI->connect($dsn, $user, $pass) || die print "Can't connect - error...";

# prepare the query
$query = $connect->prepare(qq{select id, state AS value, abbrev FROM states where state like ?;});

# execute the query
$query->execute('%'.$term.'%');

# obtain the results
while ( my $row = $query->fetchrow_hashref ){
push @query_output, $row;
}

# close the database connection
$connect->disconnect();

# print the json output to be returned to the HTML page
print JSON::to_json(\@query_output);

For this example, the Perl script should be named states.pl. When executed from the jQuery script in the web page, it will perform a search on the database where the search criteria is in a “like” statement, with a percent sign on both sides of the search term, like this:

# execute the query
$query->execute('%'.$term.'%');

You might want to change this query if you have a long data set where entering a few letters would bring up a result that isn’t what the user would probably want. Here is an example of the same form where we are asking the user to enter their state name, and they enter “min” for Minnesota, but it also brings up Wyoming:

If you want to only search for the beginning of the word, remove the first percent sign, like this:

# execute the query
$query->execute($term.'%');

This would remove Wyoming from being populated as a choice:

Lastly, here is a simple web page that uses jQuery. We are using the jQuery hosted by Google, and we are using one of their css templates. You may delete their css template and use your own. Also, you will need to change the path of your Perl script on this line:

source: "http://192.168.1.2/cgi-bin/mysql/jquery/state.pl"

And here is the HTML:

<html>

<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jQueryui/1.8.16/themes/base/jQuery-ui.css">

<!-- source = http://code.google.com/apis/libraries/devguide.html#jQuery -->
<script src="https://ajax.googleapis.com/ajax/libs/jQuery/1.6.4/jQuery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jQueryui/1.8.16/jQuery-ui.min.js"></script>

<!-- Here is the jQuery code -->
<script>
   $(function(get_states) {
    $('#abbrev').val("");
    $("#state").autocomplete({
    source: "http://192.168.1.2/cgi-bin/mysql/jQuery/state.pl",
    minLength: 3,
    select: function(event, ui) {
     $('#state_id').val(ui.item.id);
     $('#abbrev').val(ui.item.abbrev);
    }
    });
   });
</script>

<!-- The HTML is simplistic for the example: -->
<form method="post">
<table>
<tr><td><p class="ui-widget" ><label for="state"/>State: </label></td>
<td><input type="text" id="state" name="state" size=30/> </td>
<td><input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/></td></tr>
</table>
<input type="hidden" id="id" name="id" />
<p><input type="submit" name="submit" value="Submit" /></p>
</form>

<!-- This section will populate the form field with a list of matching states -->
<script>
  $("#auto_populate_field").submit(function(get_states){
   $("#submitted").html("State: " + $("#state").val() + "<br />State Abbreviation: " + $("#abbrev").val() + "<br />State ID: " + $("#state_id").val());
   return false;
  });
</script>

In this example, once the user starts typing past three characters, a drop-down menu will appear with choices that match the letters being typed. You can decrease/increase the total number of characters that the user will need to type before it queries the database. This is done via this line in the jQuery code:

minLength: 3,

Every time that the person types a character past the initial three characters, the jQuery script will query the MySQL database. This needs to be taken into consideration if you will have a lot of traffic on the pages where you are using jQuery.

The Perl script returns three values in json output – the id, state and abbreviation fields. The values of these fields are place in the text boxes by the autocomplete jQuery function.

In this example, the abbreviation for the state field is returned and placed in the “abbrev” field in the form. The value for the state id is also returned, but since the id field is a hidden field in the form, you won’t see the output/value – but it will be stored in the form.

If you want to see the value of the state id, simply change your web form to look like this:

<form method="post">
<table>
<tr><td><p class="ui-widget" style="textalign:right;"><label for="state"/>State: </label></td>
<td><input type="text" id="state" name="state" size=30/> </td>
<td><input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/></td>
<td><input type="text" size=4 id="state_id" name="state_id" /></td>
</tr>
</table>
<p><input type="submit" name="submit" value="Submit" /></p>
</form>

You can now see the third field which will contain the state id:

When you enter “min” and then click on Minnesota, you will see the state ID (24) in the third field:

You could also use this to auto-populate city and state fields after someone enters their zip code. Or you could even use this to auto-populate the shipping costs for an online order (I might tackle this one in a future post). Hopefully you will be able to copy and paste this example and have a working jQuery form web page example in just a few minutes.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Follow

Get every new post delivered to your Inbox.

Join 37 other followers