#---------------------------------------------------------------# # Variable Section # #---------------------------------------------------------------# $DBHost = '10.10.10.10' $DBPort = '5555' $DBUser = 'viewuser' $DBPass = 'viewuser1' $DBName = 'mcdb' $avtarUser = 'avamar-user' #Could be MCUser or a new User but must have administrator role $avtarPass = 'avamar-user-password' #You could prompt for this $smtpserver = 'smtpserver.domain.com' $smtpfrom = 'fromaddress@domain.com' $smtpto = 'toaddress@domain.com' $s = "" #---------------------------------------------------------------# # Send-SMTPmail Function (thanks http://www.virtu-al.net/ # #---------------------------------------------------------------# function Send-SMTPmail($to, $from, $subject, $smtpserver, $body) { $mailer = new-object Net.Mail.SMTPclient($smtpserver) $msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body) $msg.IsBodyHTML = $true $mailer.send($msg) } #---------------------------------------------------------------# # avtar-query Function # #---------------------------------------------------------------# function avtar-query {param ([string]$server,[string]$domain,[string]$recorded_date,[string]$filter) switch ($filter){ "offline"{$grepFilter = "offline";$resultsFilter = "Database"} } $logString = avtar.exe --nostdout --hfsaddr=10.10.10.10 --id="$avtarUser" --ap="$avtarPass" --path="$domain/$server" --showlog |grep "logfile=" $b = $logString.indexof("logfile=") + 9 $logFile = $logString.SubString($b) $logFile = $logFile.Replace(":","$") $logFile = $logFile.Replace("#0","") $logFile = $logFile.Replace('"',"") $logFile = "\\" + $server + "\" + $logFile $results = Get-Content -Path "$logFile" |grep "$grepFilter" $results = $results -split ("$resultsFilter") $logEntries = @() foreach ($result in $results){ if ($result -like "*$grepFilter*"){ $logEntries += "$server$result$recorded_date" } } return $logEntries } #---------------------------------------------------------------# # Avamar-BackupLog-Query Function # #---------------------------------------------------------------# function Avamar-BackupLog-Query {param([string]$statuscode,[string]$logfilter) $DBConnectionString = "Driver={PostgreSQL UNICODE};Server=$DBHost;Port=$DBPort;Database=$DBName;Uid=$DBUser;Pwd=$DBPass;" $DBConn = New-Object System.Data.Odbc.OdbcConnection $DBConn.ConnectionString = $DBConnectionString $DBCmd = $DBConn.CreateCommand() $DBCmd.CommandText = "select client_name,domain,recorded_date from v_activities where status_code = " + $statuscode + " and recorded_date_time > current_timestamp - interval '24 hours';" $DBCmd.Connection.Open() $rdr = $DBCmd.ExecuteReader() $logResults = @() While ($rdr.Read()){ $return = avtar-query -server $rdr.GetString(0) -domain $rdr.GetString(1) -recorded_date $rdr.GetString(2) -filter $logfilter $logResults += $return $logResults = $logResults | Sort-Object } return $logResults $DBCmd.Connection.Close() } #---------------------------------------------------------------# # Execute Query Section # #---------------------------------------------------------------# $offlineExceptions = @() $offlineExceptions = Avamar-BackupLog-Query -statuscode '30005' -logfilter 'offline' $offlineExceptions = "$s

SQL Backups that Completed with Exceptions/Databases Offline" + $offlineExceptions + "
Server NameLog DataRecorded Date
" Send-SMTPmail -to $smtpto -from $smtpfrom -subject 'Avamar SQL Backup Exceptions (Offline)' -smtpserver $smtpserver -body $offlineExceptions