The script for automating the collection of the most recent backup file on disk and completion date from the log file is complete. In fact there are 3 scripts, one for each DC (IAD03, IAD05, SJC). Each script is the same aside from the IP address and user/pass that it uses to connect to the central database management server. It connects to the central mysql management server so that all you need to do is enter a client name on the prompt and it will pull all of the client's hostnames and IP addresses so that you can choose a server to audit and will give you the root pass (if applicable) and run commands over ssh to get the pertinent data for the audit.
Note: You can use this from your workstation (windows requires cygwin, mac users all good, linux all good) or from any server on the correct VPN - I would highly advise you to run this script as 0700 privileges since it has a user/pass to the management server's MySQL database (although it is only a read-only account it still has access to passwords for servers so don't leave it sitting around for clients or unauthorized users to see or access).
Here is how it works:
0. on whichever system you are running this on make sure you have perl-DBI and perl-DBD-MySQL installed, also Perl.
1. login to the proper VPN
2. copy the scripts from this email to your preferred directory
2. on your workstation (or server) open a terminal and run the script: ./mysql_backup_audit-IAD03.pl (replace IAD03 with IAD05 or SJC) with the following options if desired.

• --user= username to connect to the server if not using root
• --ssh-key=[dsa,rsa] if you want to distribute your workstation's ssh public key to the server for key-based login in the future. This is not necessary for the audit but I've included it to save you time if you enjoy using ssh keys for login and don't like manually distributing your keys.
• --help will give you the following help information:

-(mreid@mbook)(~/bin)--
--(0)> ./mysql_backup_audit-IAD03.pl --help
#########################################################                                                                                                
Filename: mysql_backup_audit.pl
Author: Matt Reid
Email: mreid@opsource.net
#########################################################
!! Requires perl-DBI and perl-DBD-MySQL modules !!
#########################################################
--help        = this message
--ssh-key     = ssh key type to distribute [dsa,rsa]
--user        = username to connect as if not using root
defaults if variables not specified
ssh-key:     none
user:        root
-(mreid@mbook)(~/bin)--
--(0)>
If all goes as planned it will output something like the following:
--(0)> mysql_backup_audit-SJC.pl --user=mreid --ssh-key=dsa
Please type the first three letters of the client name: ops
Searching for ops
[ID] | Client | Hostname | IP_Address | Cluster_IP
[221] opsource | sjcopsmysqlclustdb01p | 10.128.10.21 |
[222] opsource | sjcopsmysqlclustdb02p | 10.128.10.22 |
Choose ID to connect to: 222
You wanted to connect as --user=mreid however,
Server is not specified as SSO enabled, connecting as root.
Connecting to [222] opsource | sjcopsmysqlclustdb02p | 10.128.10.22
root: <password is here>
Copying SSH key: ~/.ssh/id_dsa.pub to server
Most recent MySQL backup file: -rw-r-r- 1 root root 721964487 Jan 28 03:32 /home/mysql-backups/all-databases-dump.20100128-030201.tar.gz
Most recent entry in backup log for completion date/time: Backup procedure complete at Thu Jan 28 03:32:09 PST 2010
-(mreid@mbook)(~)--