Avamar Failed Backup Query Script–SQL
Avamar is a great product, I love the backup technology, however, it is lacking in a few areas when it comes to data mining/reporting. I’m assuming DPA makes up for this lack of information gathering, but currently I do not have that luxury. I’ve had to come up with a different way to extract the required data from our Avamar environment. I do this using all the resources available from an Avamar perspective.
The issue I have tackled in this blog post is extracting data from SQL Agent Backups that have “Completed with Exceptions” (status_code 30005). In our environment our DBA’s take databases offline constantly, causing our backups to “Complete with Exceptions”. The problem is Avamar does not have a way to report on the client logs…meaning I had no way to see which databases were offline unless I opened each client log (remotely or via the Avamar GUI) and search through it for the word “offline”. Well not anymore, I wrote the following PowerShell script to automate this process. You can download it by clicking here.
1. Download the “grep” utility for Windows and make sure the bin directory is in your PATH. http://gnuwin32.sourceforge.net/packages/grep.htm
2. Make sure you have the Avamar Agent installed on the machine you are running the script from. The avtar.exe is required to gather the log file name to parse. Make sure and put the C:\Program Files\avs\bin\ directory in your PATH.
3. Download and install the PostgreSQL ODBC Driver for Windows. http://www.postgresql.org/ftp/odbc/versions/msi/
4. Download the script and run it from the PowerShell. Make sure you are running PowerShell as a user that has access to all the “client” admin shares, otherwise it will not be able to Get-Content via the UNC path of the LogFile.
Here is a snippet on how the script functions.
This section is where you set all the necessary variables for the script to run.
· $DBHost = This is the Avamar Utility Node
· $DBPort = Default PostgreSQL Port for Avamar
· $DBUser = Default viewuser for Avamar
· $DBPass = Default viewuser password for Avamar
· $DBName = Default Avamar Database
· $avtarUser = Could be MCUser or a new User but must have administrator role
· $avtarPass = The password for the $avtarUser, you could read-host/prompt for this
· $smtpserver = SMTP Server to Relay the HTML Email Results
· $smtpfrom = ‘firstname.lastname@example.org’
· $smtpto = ‘email@example.com’
· $s = This is a <style> tag to format the HTML Email
This section is self-explanatory so I won’t spell it out, I borrowed this piece of code from the vCheck Report from http://www.virtu-al.net/, but there are many ways to format SMTP Send mail functions.
This section is called by the Avamar-BackupLog-Query function to identify the correct log file and parse it for all offline databases. The high level steps are:
1. Set the $grepFilter and $resultsFilter based on variable sent to function.
2. Run the avtar.exe and gather the last log file for that SQL server (in our case the last log file is SQL because that backup job runs after all others)
3. Format the log file string into a usable UNC path
4. Grab the content from the log file based on the $grepFilter
5. Split the results into an array so we can parse out the “Database” section
6. Build the array to return; return the array to Avamar-BackupLog-Query
This section creates the database connection, gathers the servers that had backups that completed with exception, and then calls on the avtar-query function to get the necessary data out of the client log file. I had to modify this post to get the correct DB connection settings (http://stackoverflow.com/questions/1058156/how-do-i-insert-data-into-a-postgres-table-using-powershell-or-vbscript). The high level steps are:
1. Create the DB Connection String
2. Create the DB Connection
3. Set the Connection SQL Query/Command
4. Open the Database Connection and Execute the command
5. For each record, perform the avtar-query and create array to return; return array to execution section/array.
6. Close the DB Connection
This section is where we call the Avamar-BackupLog-Query function and generate the email. The high level steps are:
1. Call the Avamar-BackupLog-Query using the correct Avamar status code for “Completed with Exceptions” which is 30005, and supply it with a filter parameter.
2. Format the results to HTML and email.
Here is a print screen of the output/email I receive. Now I can just send this email to the DBA’s daily and they can verify the backup can be called “successful” even though it “Completed with Exceptions”.
This saves me 20 minutes of work daily depending on how many backups “Complete with Exceptions”, but as you can see this framework can probably be adapted to work with multiple days and other types of backup failures. As the need arises I will add other backup failures to this script. I have a few ideas for easing the access to the client logs.