Here it is: Powershell script to add VEB-EMails

Backup agent for Microsoft Windows servers and workstations (formerly Veeam Endpoint Backup FREE)

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby graben » Thu Oct 29, 2015 6:35 pm

Is there anyway to modify these powershell scripts to run in Maxfocus? I basically wish the scripts to function the same as the email but just pass or fail a check so that we could be alerted if the backups were not running or failing.
If anyone has any experience with Maxfocus checks and could explain how to make these scripts work for a check it would be really appreciated.
Thank you for your help.
graben
Novice
 
Posts: 4
Liked: 2 times
Joined: Thu Oct 29, 2015 6:30 pm

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby graben » Wed Nov 04, 2015 5:03 pm 1 person likes this post

I was able to make my own check based on your great examples. Thank you! It can be found here http://fixitscripts.com/problems/veeam- ... -log-check
graben
Novice
 
Posts: 4
Liked: 2 times
Joined: Thu Oct 29, 2015 6:30 pm

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby abelliniSIBA » Fri Dec 04, 2015 4:46 pm 2 people like this post

Hi guys,

i wrote a small Powershell script (EndpointSummary.ps1) that collect Endpoint backup results from Veeam Database, adding those informations on the body of an email,
you can run it directly on Veeam Server (or where it is located the DB server)
This script will add all informations (available from DB or maybe as i can :D) dinamically adding new pc's joined to your VeeamB&R repository of the last 24 h.
I scheduled that script to run each morning to have a full vision on my endpoint backups.
just copy and paste this one in an empty *.ps1 file
than Keep these 3 suggestions otherwise your script will not run:

1. Open Powershell and run : PS C:\> set-executionpolicy unrestricted;
2. WARNING You need to run Invoke-SqlCmd and you need to install these components:
2a. Install SQL Server 2008 R2 Management Objects using Web PI (http://www.microsoft.com/web/downloads/platform.aspx);
2b. Install 'Windows PowerShell Extensions for SQL Server' from the Microsoft® SQL Server® 2008 R2 Feature Pack page (http://www.microsoft.com/download/en/de ... PowerShell)
3. To schedule this script you need to personalize some attribbute (SMTPServer, From, To.. ):

PS C:\>.\EndpointSummary.ps1

Code: Select all
# +----------------------------------------------------------------------------+
# |NAME: EndpointSummary.ps1                                                    |
# |AUTHOR: Alessandro                                                                |
# |DATE: 04/12/2015                                                                   |
# |VERSION: 1.0                                                                           |
# |                                                                                              |
# |KEYWORDS:  Veeam, notification, backup, SQLDB, Endpoint      |
# |                                                                                              |
# |COMMENTS: mail  reading informations on veeam db for VEB      |
# |                                                                                               |
# |PARAMETER :                                                                           |
# |    smtpserver use this to change the default smtp server            |
# |    from: you can specify a dif sender email address                   |
# |    to: same like from                                                               |
# +----------------------------------------------------------------------------+
# +----------------------------------------------------------------------------+
# | Maintenance History                                                                |
# | -------------------                                                                       |
# | Name                   Date       Version     Description                     |
# | ---------------------------------------------------------------------------+
# | Alessandro Bellini 04/12/2015 1.0  Endpoint version Notification|
# +----------------------------------------------------------------------------+
Param (
[string]$SourceServer = "localhost (or instancename)",
[string]$SmtpServer = "yoursmtpserver",
[string]$From = "who sends",
[string]$To = "who receives",
[string]$Outputfile = "VeamEndpointBackupReport.html",
    [string]$subject = $subjectFormat
)
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Invoke-Sqlcmd -ServerInstance $SourceServer -Database "VeeamBackup" -Query "Select [job_name] as [Job Name],[creation_time] as [Started],[end_time] as [Ended],[progress] as [Progress] From [VeeamBackup].[dbo].[ReportSessionView] where [job_type] = '4000' and creation_time >= DATEADD(day, -1, GETDATE()) Order by [creation_time] desc" | Select-Object -Property * -ExcludeProperty ItemArray, Table, HasErrors, RowState, RowError | ConvertTo-Html | Out-File $Outputfile
$body = (Get-Content $Outputfile | Out-String)
$subjectFormat = ("Veeam Endpoint Backup Summary Report " + (Get-Date -Format d))
Function SendEmailStatus($From, $To, $Subject, $SmtpServer, $BodyAsHtml, $Body)
{
$SmtpMessage = New-Object System.Net.Mail.MailMessage $From, $To, $Subject, $Body
$SmtpMessage.IsBodyHTML = $BodyAsHtml
$SmtpClient = New-Object System.Net.Mail.SmtpClient $SmtpServer
$SmtpClient.Send($SmtpMessage)
$SmtpMessage.Dispose()
}
SendEmailStatus -From $From -To $To -Subject $subjectFormat -SmtpServer $SmtpServer -BodyAsHtml $true -Body $Body


Hope it could helps you

Probably i need the make it beautiful with HTML format but for the moment it works :wink:
Cheers from Paris
Alessandro Bellini
abelliniSIBA
Novice
 
Posts: 9
Liked: 5 times
Joined: Wed Nov 19, 2014 2:01 pm
Full Name: Alessandro Bellini

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby abelliniSIBA » Tue Dec 08, 2015 5:20 pm

Hi guys,

i wrote another script changing the point of view of execution.
Instade read DB informations (not all informations are listed), I decide to run a script on the Veeam Server to contact eventlog for each machine, read informations and add it on a html report (+mail notification).
I scheduled that to run each morning.
This script will add all informations reading pc's listed on a txt file (for example computers.txt) that contains:
pcLaptop1
pcLaptopN
pcDesktop1
pcDesktopN

I scheduled that script to run each morning as scheduled task, to have a full vision on my endpoint backups.
just copy and paste this one in an empty *.ps1 file
than Keep these 3 suggestions otherwise your script will not run:

1. Open Powershell in Veeam server and run: PS C:\> set-executionpolicy unrestricted;
2. WARNING You need at least Powershel v4.0
3. To schedule this script you need to personalize some attribbute (SMTPServer, From, To.. ):
4: Verify to have rights on the endpoint machine to list eventlog at least;
5. Run this script adding some parameters:
example
PS C:\> .\MailSummaryEndpoint.ps1 -computers .\computers.txt -Outpufilehtml .\VeeamEndpointReportSummary.html -EmailLog

-computers .\computers.txt (file with listed pc to be contacted)
-Outpufilehtml .\VeeamEndpointReportSummary.html (if you want report html)
-EmailLog (for email notification)

Code: Select all
# +----------------------------------------------------------------------------+
# |NAME: MailEndpointSummary.ps1                                              |
# |AUTHOR: Alessandro Bellini                                                      |
# |DATE: 08/12/2015                                                                   |
# |VERSION: 1.0                                                                           |
# |                                                                                               |
# |KEYWORDS:  Veeam, notification, backup, Eventlog, Endpoint   |
# |                                                                                               |
# |COMMENTS: mail the event success\not from windows Event log  |
# |                                                                                               |
# |PARAMETER :                                                                           |
# |    smtpserver use this to change the default smtp server             |
# |    from: you can specify a dif sender email address                    |
# |    to: same like from                                                                |
# | emailLog:If you whan to send the repport by a email; you need |
# |                smtpserver, from and to varibale                               |
# |    outpufile: if you prefere have file for the repport                   |
# |                                                                                               |
# |NEED : none                                                                             |
# +----------------------------------------------------------------------------+
# +----------------------------------------------------------------------------+
# | Maintenance History                                                                |
# | -------------------                                                                      |
# | Name                   Date       Version     Description                     |
# | ---------------------------------------------------------------------------+
# | Alessandro Bellini 08/12/2015 1.0  Endpoint version Notification|
# +----------------------------------------------------------------------------+

#paramter of the script fonction
Param (
$computers,
[string]$SmtpServer = "smtprelay",
[string]$From = "SenderMail",
[string]$To = "DestinationMailAddress",
[switch]$EmailLog,
[string]$Outpufilehtml
)
#start
Begin
{
$script:CurrentErrorActionPreference = $ErrorActionPreference
$script:Output = @()
$script:ProcessedServers = @()
$ErrorActionPreference = "SilentlyContinue"
$script:Subject = @()
$script:TimeGeneratedJob = @()
$script:MessageJob = @()
$script:Status = 1
$script:ErrorMessage = 0
$script:WarningMessage = 0
$script:EnabledMessage = 0
$script:NbSrvMessage = 0
# table style for the Email
$Table = @{ Name = "Server Name"; expression = { $_.servername } }, @{ Name = "Job start"; expression = { $_.TimeGenerated } }, @{ Name = "Job finish"; expression = { $_.TimeGeneratedJob } }, @{ Name = "EntryType"; expression = { $_.EntryType } }, @{ Name = "Source"; expression = { $_.Source } }, @{ Name = "InstanceID"; expression = { $_.InstanceID } }, @{ Name = "Message"; expression = { $_.Message } }

if ($computers)
{
if (Test-Path $computers[0]) { $ServerList = Get-Content -Path $computers }
else { $ServerList = $computers }
}

########## START FUNCTION SECTION ###############
#fonction from website to Set the color line in table
Function AlternatingRows
{
[CmdletBinding()]
Param (
[Parameter(Mandatory,
ValueFromPipeline)]
[string]$Line
)
Begin
{
$ClassName = 1
}
Process
{
If ($Line.Contains("<tr>"))
{
If ($ClassName)
{
$ClassName = 0
$ClassColor = "background-color:#3d7e00;"
}
Else
{
$ClassName = 1
$ClassColor = "background-color:#afff61;"
}
If ($Line.Contains("Warning"))
{
$ClassColor = "background-color:#FFD700;"
}
If ($Line.Contains("Failed"))
{
$ClassColor = "background-color:#fc0200;"
}
$Line = $Line.Replace("<tr>", "<tr style=""$ClassColor"">")
}
Return $Line
}
}

If ($EmailLog)
{

Function SendEmailStatus($From, $To, $Subject, $SmtpServer, $BodyAsHtml, $Body)
{
$SmtpMessage = New-Object System.Net.Mail.MailMessage $From, $To, $Subject, $Body
$SmtpMessage.IsBodyHTML = $BodyAsHtml
$SmtpClient = New-Object System.Net.Mail.SmtpClient $SmtpServer
$SmtpClient.Send($SmtpMessage)
If ($? -eq $False) { Write-Warning "$($Error[0].Exception.Message) | $($Error[0].Exception.GetBaseException().Message)" }
$SmtpMessage.Dispose()
rv SmtpClient
rv SmtpMessage
}
}


function GetEventlog
{
Param ($SServer
)
# Put most info into the body of the email:
$TimeGeneratedJob = (get-eventlog -ComputerName $SServer -LogName "Veeam Endpoint Backup" -InstanceId 190 -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property TimeGenerated | out-string).substring(20)
$MessageJob = (get-eventlog -ComputerName $SServer -LogName "Veeam Endpoint Backup" -InstanceId 190 -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property Message | out-string).substring(14)
$TimeGenerated = (get-eventlog -ComputerName $SServer -LogName "Veeam Endpoint Backup" -InstanceId 150, 110 -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Where { $_.message -match $SServer } | Format-List -property TimeGenerated | out-string).substring(20)
$Source = (get-eventlog -ComputerName $SServer -LogName "Veeam Endpoint Backup" -InstanceId 150, 110 -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Where { $_.message -match $SServer } | Format-List -property Source | out-string).substring(13)
$EntryType = (get-eventlog -ComputerName $SServer -LogName "Veeam Endpoint Backup" -InstanceId 150, 110 -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Where { $_.message -match $SServer } | Format-List -property EntryType | out-string).substring(16)
$Message = (get-eventlog -ComputerName $SServer -LogName "Veeam Endpoint Backup" -InstanceId 150, 110 -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Where { $_.message -match $SServer } | Format-List -property Message | out-string).substring(14)
$InstanceID = (get-eventlog -ComputerName $SServer -LogName "Veeam Endpoint Backup" -InstanceId 150, 110 -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Where { $_.message -match $SServer } | Format-List -property InstanceID | out-string).substring(17)
#check if the value is empty frome the Evente log
if (!$TimeGenerated)
{ $TimeGenerated = "no info" }
if (!$Source)
{ $Source = "no info" }
if (!$EntryType)
{ $EntryType = "no info" }
if (!$Message)
{ $Message = "no info" }
if (!$InstanceID)
{ $InstanceID = "no info" }

#Created the PS object for store date in table. and # result screen     
$Object = New-Object psobject
$Object | Add-Member NoteProperty Servername $SServer -PassThru | Add-Member NoteProperty TimeGenerated $TimeGenerated -PassThru | Add-Member NoteProperty TimeGeneratedJob $TimeGeneratedJob -PassThru | Add-Member NoteProperty Source $Source -PassThru |
Add-Member NoteProperty EntryType $EntryType -PassThru | Add-Member NoteProperty Message $MessageJob -PassThru | Add-Member NoteProperty InstanceID $InstanceID -PassThru

# Determine the subject according to the result of the backup:
if ($MessageJob.contains("Success"))
{
$subject1 = "Veeam Endpoint Backup finished with Success."
}
else
{
if ($MessageJob.contains("no info"))
{
$subject1 = "Veeam Endpoint Backup finished with Warning!! Check Body for details."
$script:WarningMessage += 1
}
else
{
#$subject1 = "Veeam Endpoint Backup failed!! Check Body for details."
#$script:ErrorMessage += 1
}
}

if ($MessageJob.contains("Failed"))
{
$subject1 = "Veeam Endpoint Backup Job failed, Check Body for details."
$script:ErrorMessage += 1
}
elseif ($MessageJob.contains("Warning"))
{
$subject1 = "Veeam Endpoint Backup Job finished with Warning Check Body for details."
$script:WarningMessage += 1
}
$subject = "Veeam Endpoint Backup"
                #Export values
$script:NbSrvMessage +=1
$script:Output += $Object
$script:subject = $subject
$script:TimeGeneratedJob = $TimeGeneratedJob
$script:MessageJob = $Message

}
}
#process of the script
Process
{
if ($ServerList)
{
foreach ($computer in $ServerList)
{
GetEventlog $computer
}
#output message resulte
Write-Host "Script finished with : " $script:subject
write-host "Job Message : " $script:MessageJob
write-host "Finished Time Job at : "  $script:TimeGeneratedJob
}
}
# the end process task
End
{
# created the header og the email
#definir the color
if ($script:WarningMessage) { $Status = 2 }
if ($script:ErrorMessage) { $Status = 0 }

if ($Status -eq 1)
{
$colorTagTable = "background-color:#3d7e00"
$messageinfo = "Success"
}
elseif ($Status -eq 2)
{
$colorTagTable = "background-color:#FFD700"
$messageinfo = "Warning"
}
else
{
$colorTagTable = "background-color:#fc0200"
$messageinfo = "Error"
}

# the header
$htmlbody = ""
$date = Get-Date
$header = @"
 
<table cellspacing="0" cellpadding="0" width="100%" border="0">
 
     <tr>
         <td style="width: 80%;border: none;$colorTagTable;" ><H2>Veeam Endpoint Backup</H2>
       </br> 
<div><b>Total : $script:NbSrvMessage Servers</div></b> 
            </br>
             <div>Created by $env:username at $date.</div>
         </td>
          <td style="border: none;$colorTagTable;" >
             finished with: <b>$messageinfo</b>
<div>
<div><b>Errors: $script:ErrorMessage Backup error</b></div>
<div><b>Warning: $script:WarningMessage  Backup warning</b></div>
        </td>
    </tr>
</table>
"@
#merge body
$htmlbody += $header
#Formating the result in HTML format converting for google mail
$Body = $Output | Select $Table | ConvertTo-HTML -Body $htmlbody
$Body = $Body | AlternatingRows
$Body = $Body -replace '<body>', '<body style = "font-size:12px;font-family:Arial;color:#000000;font-weight:normal">'
$Body = $Body -replace '<table>', '<table style = "border-width:1px;cellpadding=10;border-style:solid;border-collapse:collapse;width:100%">'
$Body = $Body -replace '<th>', '<th style = "font-size:12px;border-width:1px;padding:10px;border-style:solid;background-color:#96C5EC">'
$Body = $Body -replace '<td>', '<td style = "font-size:10px;border-width:1px;padding:10px;border-style:solid">'

Add-Type -AssemblyName System.Web
$Body = [System.Web.HttpUtility]::HtmlDecode($Body) # this line made the link clickable

#fonction to sending the email
If ($EmailLog)
{
SendEmailStatus -From $From -To $To -Subject $script:Subject -SmtpServer $SmtpServer -BodyAsHtml $true -Body $Body
}

#or in a outpute file
if ($Outpufilehtml)
{
$Body | Out-File $OutpufileHtml

}
$ErrorActionPreference = $script:CurrentErrorActionPreference
}


This looks like better than the last one :), waiting for further improvement of informations on DB side
Enjoy
Alessandro
abelliniSIBA
Novice
 
Posts: 9
Liked: 5 times
Joined: Wed Nov 19, 2014 2:01 pm
Full Name: Alessandro Bellini

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby JudgeFudge » Fri Jan 08, 2016 3:42 pm 3 people like this post

A greet to this absolute fantastic idea and cheers to those who posted their scripts.

Based on that I did one as well.
We use VEB on a few customers and I really liked the idea of getting mail like on VBR.
I researched a few days and made myself a script based on the one in first post and the ones about getting to the database.

Informations you get: Start, End, Duration, Size of stored data, Total size of the Backup files, Free disk space
Timestamp will be the same when the backup is finished, it's just another, because i did it over commandfile over and over again.
When there were no new files in the backup, the data size goes by zero.
If you backup to a repository, please change the SQL query from != to =

You don't have to install anything on the machine, no SQL Connector, no SQL Server, it goes all by .NET

To run the script via Task, do the following:

1. run Powershell as administrator, type in "Set-ExecutionPolicy Unrestricted" enter and confirm
2. set new task with trigger at an event "Veeam Endpoint Backup" also on source.
3. new action start program and in program/script copy "%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe"
4. under arguments "-command "C:\path\to\script\veeam.ps1"
5. Under general, select run unattached, don't save password and run on high privileges

Image

You can format datetime as you wish, even the language in the HTML part. That wouldn't be too difficult.

Code: Select all
###########################################################
#                                                         #
#                  Endpoint Notification                  #
#                                                         #
###########################################################
 
# Set information about sender and recipient
$emailMessage = New-Object System.Net.Mail.MailMessage
$emailMessage.From = "customermail"
$emailMessage.To.Add( "yourmail" )
$hostname = "Servername"
 
# SMTP Server information
$emailSmtpServer = "yourmailserver"
$emailSmtpServerPort = "25"
 
# Put most info into the body of the email:
$TimeGenerated   =          get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-Wide -property TimeGenerated | out-string
$TimeBackupStarted   =      get-eventlog "Veeam Endpoint Backup" -InstanceID 110 -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-Wide -property TimeGenerated | out-string
$Source      =              get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property Source | out-string
$EntryType   =              get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property EntryType | out-string
$Message   =                get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-Wide -property Message -AutoSize | out-string
$InstanceID   =             get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property InstanceID| out-string
 
#Formatting Variables into readable data
$date = Get-Date -format F
$difference = (new-timespan -Start $TimeBackupStarted -End $TimeGenerated)
$duration = "{0:c}" -f $difference
$TimeGenerated = [datetime]$TimeGenerated
$TimeBackupStarted = [datetime]$TimeBackupStarted
$start = "{0:HH:mm:ss}" -f $TimeBackupStarted
$end = "{0:HH:mm:ss}" -f $TimeGenerated
$mb = " MB"
$gb = " GB"
$tb = " TB"
 
# Connecting to the Veeam SQL Database
$key = "hklm:\SOFTWARE\Veeam\Veeam Endpoint Backup"
$User = (get-Item $key).GetValue("SqlLogin")
$Pass = (get-Item $key).GetValue("SqlPassword")
$dbServer = (get-Item $key).GetValue("SqlInstancePipeName")
$db = (get-Item $key).GetValue("SqlDatabaseName")
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$dbServer;Database=$db;uid=$User;password=$Pass;"
 
# SQL querys for the different informations
$QueryStored = "SELECT TOP 1 stored_size FROM [VeeamBackup].[dbo].[ReportSessionView] ORDER BY [creation_time] DESC"
$SqlCmdStored = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdStored.CommandText = $QueryStored
$SqlCmdStored.Connection = $SqlConnection
$SqlAdapterStored = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapterStored.SelectCommand = $SqlCmdStored
$DataSetStored = New-Object System.Data.DataSet
$SqlAdapterStored.Fill($DataSetStored)
 
$QueryDiskspace = "SELECT [free_space] AS [free] FROM [VeeamBackup].[dbo].[BackupRepositories] WHERE (name != 'Default Backup Repository')"
$SqlCmdDiskspace = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdDiskspace.CommandText = $QueryDiskspace
$SqlCmdDiskspace.Connection = $SqlConnection
$SqlAdapterDiskspace = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapterDiskspace.SelectCommand = $SqlCmdDiskspace
$DataSetDiskspace = New-Object System.Data.DataSet
$SqlAdapterDiskspace.Fill($DataSetDiskspace)
 
$QueryBackupsize = "SELECT  SUM(backup_size) FROM [VeeamBackup].[dbo].[WmiServer.RestorePointsView]"
$SqlCmdBackupsize = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdBackupsize.CommandText = $QueryBackupsize
$SqlCmdBackupsize.Connection = $SqlConnection
$SqlAdapterBackupsize = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapterBackupsize.SelectCommand = $SqlCmdBackupsize
$DataSetBackupsize = New-Object System.Data.DataSet
$SqlAdapterBackupsize.Fill($DataSetBackupsize)
 
$SqlConnection.Close()
 
# Converting the Tables to Integer for our querys
[int64]$space_backup = $DataSetStored.Tables[0] | Format-Wide -AutoSize | out-string
[int64]$space_free = $DataSetDiskspace.Tables[0] | Format-Wide -AutoSize | out-string
[int64]$space_all = $DataSetBackupsize.Tables[0] | Format-Wide -AutoSize | out-string
 
#Make sure how much space you used and have left in MB, GB and TB
if($space_backup -ge 1048576 -and $space_backup -lt 1073741824)
{$spacebackup = "{0:n2}" -f ($space_backup /1048576) + $mb}
elseif($space_backup -ge 1073741824 -and $space_backup -lt 1099511627776)
{$spacebackup = "{0:n2}" -f ($space_backup /1073741824) + $gb}
elseif($space_backup -ge 1099511627776)
{$spacebackup = "{0:n2}" -f ($space_backup /1099511627776) + $tb}
else
{$spacebackup = ("0" + $mb)}
 
if($space_free -ge 1048576 -and $space_free -lt 1073741824)
{$spacefree = "{0:n2}" -f ($space_free /1048576) + $mb}
elseif($space_free -ge 1073741824 -and $space_free -lt 1099511627776)
{$spacefree = "{0:n2}" -f ($space_free /1073741824) + $gb}
elseif($space_free -ge 1099511627776)
{$spacefree = "{0:n2}" -f ($space_free /1099511627776) + $tb}
else
{$spacefree = ("0" + $mb)}
 
if($space_all -ge 1048576 -and $space_all -lt 1073741824)
{$spaceall = "{0:n2}" -f ($space_all /1048576) + $mb}
elseif($space_all -ge 1073741824 -and $space_all -lt 1099511627776)
{$spaceall = "{0:n2}" -f ($space_all /1073741824) + $gb}
elseif($space_all -ge 1099511627776)
{$spaceall = "{0:n2}" -f ($space_all /1099511627776) + $tb}
else
{$spaceall = ("0" + $mb)}
 
# Determine the subject and background according to the result of the backup
 
if ($Message.contains("Success")) {
   $subject = "[Success] Endpoint Backup $hostname"
   $bgcolor = "#00B050"
}
 
elseif ($Message.contains("Failed")){ 
   $subject = "[Failed] Endpoint Backup $hostname failed"
   $bgcolor = "#fb9895"
}
 
# Giving the typical Veeam look
$Body =
 
"<meta http-equiv=""Content-Type"" content=""text/html; charset=utf-8"">
<table cellspacing=""0"" cellpadding=""0"" width=""50%"" border=""0"" style=""border-collapse: collapse;"">
<tr>
<td style=""padding: 0px;font-family: Tahoma;font-size: 12px;"">
<table cellspacing=""0"" cellpadding=""0"" width=""100%"" border=""0"" style=""border-collapse: collapse;"">
<tr>
<td style=""background-color: $bgcolor;color: White;font-family: Tahoma;font-weight: bold;font-size: 16px;height: 70px;vertical-align: bottom;padding: 0 0 15px 15px;"">Backup job: $Hostname
<div style=""margin-top: 5px;font-size: 12px;"">$Message</div>
</td></tr><tr>
<td style=""height: 35px;border-top: 1px solid #a7a9ac; background-color: #f3f4f4;font-size: 16px;vertical-align: middle;padding: 5px 0 0 15px;color: #626365; font-family: Tahoma;"">
<span>$date</span></td></tr><tr>
<td style=""padding: 0px;font-family: Tahoma;font-size: 12px;"">
<table width=""100%"" cellspacing=""0"" cellpadding=""0"" class=""inner"" border=""0"" style=""margin: 0px;border-collapse: collapse;"">
<tr>
<td nowrap="""" style=""width:100px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Start time </b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$start</td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Data size</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$spacebackup</td>
<td rowspan=""3"" style=""border: 1px solid #a7a9ac;""><span style=""font-size: 10px;"">&nbsp;</span></td>
</tr><tr>
<td nowrap="""" style=""width:100px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>End time</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$end</td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Total backup size</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$spaceall</td>
</tr><tr>
<td nowrap="""" style=""width:100px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Duration</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$duration</td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Free disk space</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$spacefree</td>
</tr></table></td></tr></table></td></tr><tr><td style=""border:none;"">&nbsp;</td></tr><tr>
<td style=""font-size:12px;color:#626365;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;"">Veeam Endpoint Backup</td>
</tr>
</table>"
 
$SMTPClient = New-Object System.Net.Mail.SmtpClient( $emailSmtpServer , $emailSmtpServerPort )
$SMTPClient.EnableSsl = $true
$emailMessage.Subject = $subject
$emailMessage.IsBodyHtml = $true
$emailMessage.Body = $Body
 
# Send the email
if ($InstanceID.contains("190")) {
   $SMTPClient.Send( $emailMessage )
} else {
   write-host "I don't want messages on 10010 and 10050 Restorepoint-creation or -remove Emails, skip those"
}
 
 
write-host "Script finished with -$instanceID- as the last event-ID"



Even if I think that should be enough information for a VEB,
If you have any suggestions of what else information to put in the mail, feel free to post it. (e.g. exact message why a job failed).
We can see if we can catch it up from the db.
JudgeFudge
Influencer
 
Posts: 15
Liked: 8 times
Joined: Thu Jan 07, 2016 4:54 pm
Location: Germany
Full Name: Chris

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby erwinbentelo » Mon Jan 11, 2016 3:12 pm

I want connect to a sql server. This is the fault. Can you help me?

Code: Select all
Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The serve
r was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Name
d Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
At line:54 char:1
+ $SqlAdapterStored.Fill($DataSetStored)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException
 
Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The serve
r was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Name
d Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
At line:63 char:1
+ $SqlAdapterDiskspace.Fill($DataSetDiskspace)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException
 
Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The serve
r was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Name
d Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
At line:72 char:1
+ $SqlAdapterBackupsize.Fill($DataSetBackupsize)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException


Kind regards,
Erwin
erwinbentelo
Lurker
 
Posts: 1
Liked: never
Joined: Mon Jan 11, 2016 3:09 pm
Full Name: Erwin

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby best74 » Thu Jan 14, 2016 2:40 pm 1 person likes this post

JudgeFudge wrote:A greet to this absolute fantastic idea and cheers to those who posted their scripts.

Based on that I did one as well.
We use VEB on a few customers and I really liked the idea of getting mail like on VBR.
I researched a few days and made myself a script based on the one in first post and the ones about getting to the database.

Informations you get: Start, End, Duration, Size of stored data, Total size of the Backup files, Free disk space
Timestamp will be the same when the backup is finished, it's just another, because i did it over commandfile over and over again.
When there were no new files in the backup, the data size goes by zero.
If you backup to a repository, please change the SQL query from != to =

You don't have to install anything on the machine, no SQL Connector, no SQL Server, it goes all by .NET

To run the script via Task, do the following:

1. run Powershell as administrator, type in "Set-ExecutionPolicy Unrestricted" enter and confirm
2. set new task with trigger at an event "Veeam Endpoint Backup" also on source.
3. new action start program and in program/script copy "%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe"
4. under arguments "-command "C:\path\to\script\veeam.ps1"
5. Under general, select run unattached, don't save password and run on high privileges

You can format datetime as you wish, even the language in the HTML part. That wouldn't be too difficult.

Even if I think that should be enough information for a VEB,
If you have any suggestions of what else information to put in the mail, feel free to post it. (e.g. exact message why a job failed).
We can see if we can catch it up from the db.


Hi, first off, thanks for your script, it is great.

I have a few issues with the script, which i cant seem to solve myself. Plus, i have made some changes, that i want to share.

First the issues:
I get an error when i run the script, concerning "Start time" & End time" in the mail. This results in a start & end time which include the whole date and time string.

Cannot convert value "
14-01-2016 14:28:57
" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
At C:\Script\veeamrap.ps1:30 char:42
+ $TimeGenerated = [datetime]$TimeGenerated <<<<
+ CategoryInfo : NotSpecified: (:) [], RuntimeException
+ FullyQualifiedErrorId : RuntimeException

Cannot convert value "
14-01-2016 14:11:52
" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
At C:\Script\veeamrap.ps1:31 char:50
+ $TimeBackupStarted = [datetime]$TimeBackupStarted <<<<
+ CategoryInfo : NotSpecified: (:) [], RuntimeException
+ FullyQualifiedErrorId : RuntimeException


The next issue i have is that i get "0 MB" in the Free disk space table? (see picture below)

I havent figured out to resolve this, so any help would be appreciated.

Now the changes:
I found out that when Endpoint Backup stops with a warning, the script is missing a section in the "# Determine the subject and background according to the result of the backup"

So i put this one in myself:
Code: Select all
elseif ($Message.contains("Warning")){ 
   $subject = "[Warning] Endpoint Backup $hostname finished with Warning."
   $bgcolor = "#FE9A2E"
}


I also wanted to get the full Event message attached in the email, so i added in the following lines (in bold):

# Put most info into the body of the email:
$EventMessage = get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property Message | out-string

# Giving the typical Veeam look
$Body =
....
<tr><td style=""border:none;"">&nbsp;</td></tr><tr>
<td style=""font-size:12px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;"">$EventMessage</td>
</tr>
<tr><td style=""border:none;"">&nbsp;</td></tr><tr>
<td style=""font-size:12px;color:#626365;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;"">Veeam Endpoint Backup</td>
</tr>
</table>"

This gives me the following output:
Image
best74
Novice
 
Posts: 9
Liked: 1 time
Joined: Thu Jan 14, 2016 1:16 pm
Full Name: Brian Løfqvist Jensen

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby remi_sic » Tue Jan 19, 2016 11:19 am

Thanks all for this great script :) , i solve the datetime cast issue with this : $TimeGenerated = [datetime]::Parse($TimeGenerated) and
$TimeBackupStarted = [datetime]::Parse($TimeBackupStarted), link to the solution here : http://stackoverflow.com/questions/1435 ... o-datetime
remi_sic
Lurker
 
Posts: 1
Liked: never
Joined: Tue Jan 19, 2016 11:06 am
Full Name: remi

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby JudgeFudge » Tue Jan 19, 2016 1:11 pm

Datetime format is for German location, as I mentioned you have to format it in your Country Datetime format. (you find a lot on google, or stackoverflow)
but yes, i think it should work with the solution a post before, because it takes the complete datetime string and outputs only the time (it just got annoying so i made it this way)
For the zero size Message idk can you send me a message with more information about your VEB environment? Maybe it's just a simple thing

Oh and if you make format-wide you don't have that annoying "message : " in front
original Warning color is #ffd96c just FYI :D

We have also the opportunity to take a detailed message from the local database, if you wish i can try and find out.
JudgeFudge
Influencer
 
Posts: 15
Liked: 8 times
Joined: Thu Jan 07, 2016 4:54 pm
Location: Germany
Full Name: Chris

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby JudgeFudge » Tue Jan 19, 2016 1:42 pm

I tried it myself and since last week it cannot be converted into datetime anymore, WTF?

Code: Select all
$TimeGenerated = [datetime]::parse($TimeGenerated)
$TimeBackupStarted = [datetime]::parse($TimeBackupStarted)
$start = "{0:HH:mm:ss}" -f $TimeBackupStarted
$end = "{0:HH:mm:ss}" -f $TimeGenerated


this should work

Your $EventMessage is the same information as $Message you should have the information just as above, but maybe we can give it a little bit more look.. lets see
JudgeFudge
Influencer
 
Posts: 15
Liked: 8 times
Joined: Thu Jan 07, 2016 4:54 pm
Location: Germany
Full Name: Chris

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby JudgeFudge » Tue Jan 19, 2016 5:01 pm 3 people like this post

Alright thank you best74 for this update i took the warning label with in the script.
What I did:
Took the information from database to get the reason and the detail of why the backup did not succeeded.
If the Backup succeeded than you get the normal Message in the box.
Made the Timestamp again like in the post before (thank you remi_sic)
I tried it with some possible scenarios, so first you get a few screenshots and the full code again instead of every piece.

Image
Image
Image

Code: Select all
###########################################################
#  #
#  Endpoint Notification    #
#  #
###########################################################

# Set information about sender and recipient
$emailMessage = New-Object System.Net.Mail.MailMessage
$emailMessage.From = "yourmail"
$emailMessage.To.Add( "customermail" )
$hostname = "TestBackup"

# SMTP Server Informationen
$emailSmtpServer = "yourmailserver"
$emailSmtpServerPort = "25"

# Put most info into the body of the email:
$TimeGenerated   =get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-Wide -property TimeGenerated | out-string
$TimeBackupStarted   =get-eventlog "Veeam Endpoint Backup" -InstanceID 110 -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-Wide -property TimeGenerated | out-string
$Source      =get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property Source | out-string
$EntryType   =get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property EntryType | out-string
$Message   =get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-Wide -property Message -AutoSize | out-string
$InstanceID   =get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property InstanceID| out-string

#Formatting Variables into readable data
$date = Get-Date -format F
$difference = (new-timespan -Start $TimeBackupStarted -End $TimeGenerated)
$duration = "{0:c}" -f $difference
$TimeGenerated = [datetime]::parse($TimeGenerated)
$TimeBackupStarted = [datetime]::parse($TimeBackupStarted)
$start = "{0:HH:mm:ss}" -f $TimeBackupStarted
$end = "{0:HH:mm:ss}" -f $TimeGenerated
$mb = " MB"
$gb = " GB"
$tb = " TB"

# Connecting to the Veeam SQL Database
$key = "hklm:\SOFTWARE\Veeam\Veeam Endpoint Backup"
$User = (get-Item $key).GetValue("SqlLogin")
$Pass = (get-Item $key).GetValue("SqlPassword")
$dbServer = (get-Item $key).GetValue("SqlInstancePipeName")
$db = (get-Item $key).GetValue("SqlDatabaseName")
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$dbServer;Database=$db;uid=$User;password=$Pass;"

# SQL querys for the different informations
$QueryStored = "SELECT TOP 1 stored_size FROM [VeeamBackup].[dbo].[ReportSessionView] ORDER BY [creation_time] DESC"
$SqlCmdStored = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdStored.CommandText = $QueryStored
$SqlCmdStored.Connection = $SqlConnection
$SqlAdapterStored = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapterStored.SelectCommand = $SqlCmdStored
$DataSetStored = New-Object System.Data.DataSet
$SqlAdapterStored.Fill($DataSetStored)

$QueryDiskspace = "SELECT [free_space] AS [free] FROM [VeeamBackup].[dbo].[BackupRepositories] WHERE (name != 'Default Backup Repository')"
$SqlCmdDiskspace = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdDiskspace.CommandText = $QueryDiskspace
$SqlCmdDiskspace.Connection = $SqlConnection
$SqlAdapterDiskspace = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapterDiskspace.SelectCommand = $SqlCmdDiskspace
$DataSetDiskspace = New-Object System.Data.DataSet
$SqlAdapterDiskspace.Fill($DataSetDiskspace)

$QueryBackupsize = "SELECT  SUM(backup_size) FROM [VeeamBackup].[dbo].[WmiServer.RestorePointsView]"
$SqlCmdBackupsize = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdBackupsize.CommandText = $QueryBackupsize
$SqlCmdBackupsize.Connection = $SqlConnection
$SqlAdapterBackupsize = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapterBackupsize.SelectCommand = $SqlCmdBackupsize
$DataSetBackupsize = New-Object System.Data.DataSet
$SqlAdapterBackupsize.Fill($DataSetBackupsize)

$QueryMessage = "SELECT TOP 1 reason AS Reason, stop_details AS Detail FROM [VeeamBackup].[dbo].[Backup.Model.JobSessions] ORDER BY creation_time DESC"
$SqlCmdMessage = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdMessage.CommandText = $QueryMessage
$SqlCmdMessage.Connection = $SqlConnection
$SqlAdapterMessage = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapterMessage.SelectCommand = $SqlCmdMessage
$DataSetMessage = New-Object System.Data.DataSet
$SqlAdapterMessage.Fill($DataSetMessage)

$SqlConnection.Close()

# Converting the Tables to Integer for our querys
[int64]$space_backup = $DataSetStored.Tables[0] | Format-Wide -AutoSize | out-string
[int64]$space_free = $DataSetDiskspace.Tables[0] | Format-Wide -AutoSize | out-string
[int64]$space_all = $DataSetBackupsize.Tables[0] | Format-Wide -AutoSize | out-string
$reason = $DataSetMessage.Tables[0] | Format-Table -HideTableHeaders -Property Reason -wrap | out-string
$detail = $DataSetMessage.Tables[0] | Format-Table -HideTableHeaders -Property Detail -wrap | out-string
if(!$_.reason)
{$reason = $Message}

#Make sure how much space you used and have left in MB, GB and TB
if($space_backup -ge 1048576 -and $space_backup -lt 1073741824)
{$spacebackup = "{0:n2}" -f ($space_backup /1048576) + $mb}
elseif($space_backup -ge 1073741824 -and $space_backup -lt 1099511627776)
{$spacebackup = "{0:n2}" -f ($space_backup /1073741824) + $gb}
elseif($space_backup -ge 1099511627776)
{$spacebackup = "{0:n2}" -f ($space_backup /1099511627776) + $tb}
else
{$spacebackup = ("0" + $mb)}

if($space_free -ge 1048576 -and $space_free -lt 1073741824)
{$spacefree = "{0:n2}" -f ($space_free /1048576) + $mb}
elseif($space_free -ge 1073741824 -and $space_free -lt 1099511627776)
{$spacefree = "{0:n2}" -f ($space_free /1073741824) + $gb}
elseif($space_free -ge 1099511627776)
{$spacefree = "{0:n2}" -f ($space_free /1099511627776) + $tb}
else
{$spacefree = ("0" + $mb)}

if($space_all -ge 1048576 -and $space_all -lt 1073741824)
{$spaceall = "{0:n2}" -f ($space_all /1048576) + $mb}
elseif($space_all -ge 1073741824 -and $space_all -lt 1099511627776)
{$spaceall = "{0:n2}" -f ($space_all /1073741824) + $gb}
elseif($space_all -ge 1099511627776)
{$spaceall = "{0:n2}" -f ($space_all /1099511627776) + $tb}
else
{$spaceall = ("0" + $mb)}

# Determine the subject and background according to the result of the backup

if ($Message.contains("Success")) {
   $subject = "[Success] Endpoint Backup $hostname"
   $Message2 = "EndpointBackup job $hostname finished with success."
   $bgcolor = "#00B050"


elseif ($Message.contains("Failed")){   
   $subject = "[Failed] Endpoint Backup $hostname failed"
   $Message2 = "EndpointBackup job $hostname finished with failed."
   $bgcolor = "#fb9895"
}
elseif ($Message.contains("Warning")){
   $subject = "[Warning] Endpoint Backup $hostname finished with Warning."   
   $Message2 = "EndpointBackup job $hostname finished with warning."
   $bgcolor = "#ffd96c"
}

# Giving the typical Veeam look
$Body =

"<meta http-equiv=""Content-Type"" content=""text/html; charset=utf-8"">
<table cellspacing=""0"" cellpadding=""0"" width=""50%"" border=""0"" style=""border-collapse: collapse;"">
<tr>
<td style=""padding: 0px;font-family: Tahoma;font-size: 12px;"">
<table cellspacing=""0"" cellpadding=""0"" width=""100%"" border=""0"" style=""border-collapse: collapse;"">
<tr>
<td style=""background-color: $bgcolor;color: White;font-family: Tahoma;font-weight: bold;font-size: 16px;height: 70px;vertical-align: bottom;padding: 0 0 15px 15px;"">Backup job: $Hostname
<div style=""margin-top: 5px;font-size: 12px;"">$Message2</div>
</td></tr><tr>
<td style=""height: 35px;border-top: 1px solid #a7a9ac; background-color: #f3f4f4;font-size: 16px;vertical-align: middle;padding: 5px 0 0 15px;color: #626365; font-family: Tahoma;"">
<span>$date</span></td></tr><tr>
<td style=""padding: 0px;font-family: Tahoma;font-size: 12px;"">
<table width=""100%"" cellspacing=""0"" cellpadding=""0"" class=""inner"" border=""0"" style=""margin: 0px;border-collapse: collapse;"">
<tr>
<td nowrap="""" style=""width:100px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Start time </b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$start</td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Data size</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$spacebackup</td>
<td rowspan=""3"" style=""border: 1px solid #a7a9ac;""><span style=""font-size: 10px;"">&nbsp;</span></td>
</tr><tr>
<td nowrap="""" style=""width:100px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>End time</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$end</td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Total backup size</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$spaceall</td>
</tr><tr>
<td nowrap="""" style=""width:100px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Duration</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$duration</td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Free disk space</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$spacefree</td>
</tr></table></td></tr></table></td></tr><tr><tr><td style=""border:none;"">&nbsp;</td></tr><tr>
<td style=""font-size:12px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;"">$reason<br>$detail</td>
</tr>
<tr><td style=""border:none;"">&nbsp;</td></tr><tr>
<td style=""font-size:12px;color:#626365;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;"">Veeam Endpoint Backup</td>
</tr>
</table>"

$SMTPClient = New-Object System.Net.Mail.SmtpClient( $emailSmtpServer , $emailSmtpServerPort )
$SMTPClient.EnableSsl = $true
$emailMessage.Subject = $subject
$emailMessage.IsBodyHtml = $true
$emailMessage.Body = $Body

# Send the email
if ($InstanceID.contains("190")) {
   $SMTPClient.Send( $emailMessage )
} else {
   write-host "I don't want messages on 10010 and 10050 Restorepoint-creation or -remove Emails, skip those"
}


write-host "Script finished with -$instanceID- as the last event-ID"
JudgeFudge
Influencer
 
Posts: 15
Liked: 8 times
Joined: Thu Jan 07, 2016 4:54 pm
Location: Germany
Full Name: Chris

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby JudgeFudge » Thu Jan 21, 2016 8:50 am

Another Update

So, this time a few things changed again, now in my further post there I was not really satisfied, because you always got the Message from the Eventlog with the name of the Host instead of the name of the Backup Job
So now you get this phrase "EndpointBackup job '$job' finished with success." The name is coming directly from the SQL query. I had horrible whitespace while testing it, the trick did it with the trim-function.
I also did a little bit on the HTML output, it took me a while to make it look acceptable in Outlook.
Maybe you don't see a lot of changes, but believe me they make a huge difference. I hope I am now satisfied with it. And thanks to all for the Ideas.

So here it is again:

Image

Code: Select all
###########################################################
#  #
#  Endpoint Notification    #
#  #
###########################################################

# Set information about sender and recipient
$emailMessage = New-Object System.Net.Mail.MailMessage
$emailMessage.From = "sender@sender.com"
$emailMessage.To.Add( "Recipient@recipient.com" )
$hostname = "TestBackup"

# SMTP Server Informationen
$emailSmtpServer = "yourmailserver.mxserver.com"
$emailSmtpServerPort = "25"

# Put most info into the body of the email:
$TimeGenerated   =get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-Wide -property TimeGenerated | out-string
$TimeBackupStarted   =get-eventlog "Veeam Endpoint Backup" -InstanceID 110 -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-Wide -property TimeGenerated | out-string
$Source      =get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property Source | out-string
$EntryType   =get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property EntryType | out-string
$Message   =get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-Wide -property Message -AutoSize | out-string
$InstanceID   =get-eventlog "Veeam Endpoint Backup" -newest 1 -entrytype Information, Warning, Error -source "Veeam Endpoint Backup" | Format-List -property InstanceID| out-string

#Formatting Variables into readable data
$date = Get-Date -format F
$difference = (new-timespan -Start $TimeBackupStarted -End $TimeGenerated)
$duration = "{0:c}" -f $difference
$TimeGenerated = [datetime]::parse($TimeGenerated)
$TimeBackupStarted = [datetime]::parse($TimeBackupStarted)
$start = "{0:HH:mm:ss}" -f $TimeBackupStarted
$end = "{0:HH:mm:ss}" -f $TimeGenerated
$mb = " MB"
$gb = " GB"
$tb = " TB"

# Connecting to the Veeam SQL Database
$key = "hklm:\SOFTWARE\Veeam\Veeam Endpoint Backup"
$User = (get-Item $key).GetValue("SqlLogin")
$Pass = (get-Item $key).GetValue("SqlPassword")
$dbServer = (get-Item $key).GetValue("SqlInstancePipeName")
$db = (get-Item $key).GetValue("SqlDatabaseName")
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$dbServer;Database=$db;uid=$User;password=$Pass;"

# SQL querys for the different informations
$QueryStored = "SELECT TOP 1 * FROM [VeeamBackup].[dbo].[ReportSessionView] ORDER BY [creation_time] DESC"
$SqlCmdStored = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdStored.CommandText = $QueryStored
$SqlCmdStored.Connection = $SqlConnection
$SqlAdapterStored = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapterStored.SelectCommand = $SqlCmdStored
$DataSetStored = New-Object System.Data.DataSet
$SqlAdapterStored.Fill($DataSetStored)

$QueryDiskspace = "SELECT [free_space] AS [free] FROM [VeeamBackup].[dbo].[BackupRepositories] WHERE (name != 'Default Backup Repository')"
$SqlCmdDiskspace = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdDiskspace.CommandText = $QueryDiskspace
$SqlCmdDiskspace.Connection = $SqlConnection
$SqlAdapterDiskspace = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapterDiskspace.SelectCommand = $SqlCmdDiskspace
$DataSetDiskspace = New-Object System.Data.DataSet
$SqlAdapterDiskspace.Fill($DataSetDiskspace)

$QueryBackupsize = "SELECT  SUM(backup_size) FROM [VeeamBackup].[dbo].[WmiServer.RestorePointsView]"
$SqlCmdBackupsize = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdBackupsize.CommandText = $QueryBackupsize
$SqlCmdBackupsize.Connection = $SqlConnection
$SqlAdapterBackupsize = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapterBackupsize.SelectCommand = $SqlCmdBackupsize
$DataSetBackupsize = New-Object System.Data.DataSet
$SqlAdapterBackupsize.Fill($DataSetBackupsize)

$QueryMessage = "SELECT TOP 1 reason AS Reason, stop_details AS Detail FROM [VeeamBackup].[dbo].[Backup.Model.JobSessions] ORDER BY creation_time DESC"
$SqlCmdMessage = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdMessage.CommandText = $QueryMessage
$SqlCmdMessage.Connection = $SqlConnection
$SqlAdapterMessage = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapterMessage.SelectCommand = $SqlCmdMessage
$DataSetMessage = New-Object System.Data.DataSet
$SqlAdapterMessage.Fill($DataSetMessage)

$SqlConnection.Close()

# Converting the Tables to Integer for our querys
[int64]$space_backup = $DataSetStored.Tables[0] | Format-Wide -AutoSize -Property stored_size | out-string
[int64]$space_free = $DataSetDiskspace.Tables[0] | Format-Wide -AutoSize | out-string
[int64]$space_all = $DataSetBackupsize.Tables[0] | Format-Wide -AutoSize | out-string
$reason = $DataSetMessage.Tables[0] | Format-Table -HideTableHeaders -Property Reason -wrap | out-string
$detail = $DataSetMessage.Tables[0] | Format-Table -HideTableHeaders -Property Detail -wrap | out-string
$job = $DataSetStored.Tables[0] | Format-Wide -AutoSize -Property job_name | out-string
$job=($job).trim()
if(!$_.reason)
{$reason = $Message}

#Make sure how much space you used and have left in MB, GB and TB
if($space_backup -ge 1048576 -and $space_backup -lt 1073741824)
{$spacebackup = "{0:n2}" -f ($space_backup /1048576) + $mb}
elseif($space_backup -ge 1073741824 -and $space_backup -lt 1099511627776)
{$spacebackup = "{0:n2}" -f ($space_backup /1073741824) + $gb}
elseif($space_backup -ge 1099511627776)
{$spacebackup = "{0:n2}" -f ($space_backup /1099511627776) + $tb}
else
{$spacebackup = ("0" + $mb)}

if($space_free -ge 1048576 -and $space_free -lt 1073741824)
{$spacefree = "{0:n2}" -f ($space_free /1048576) + $mb}
elseif($space_free -ge 1073741824 -and $space_free -lt 1099511627776)
{$spacefree = "{0:n2}" -f ($space_free /1073741824) + $gb}
elseif($space_free -ge 1099511627776)
{$spacefree = "{0:n2}" -f ($space_free /1099511627776) + $tb}
else
{$spacefree = ("0" + $mb)}

if($space_all -ge 1048576 -and $space_all -lt 1073741824)
{$spaceall = "{0:n2}" -f ($space_all /1048576) + $mb}
elseif($space_all -ge 1073741824 -and $space_all -lt 1099511627776)
{$spaceall = "{0:n2}" -f ($space_all /1073741824) + $gb}
elseif($space_all -ge 1099511627776)
{$spaceall = "{0:n2}" -f ($space_all /1099511627776) + $tb}
else
{$spaceall = ("0" + $mb)}

# Determine the subject and background according to the result of the backup

if ($Message.contains("Success")) {
   $subject = "[Success] Endpoint Backup $hostname"
   $Message2 = "EndpointBackup job '$job' finished with success."
   $bgcolor = "#00B050"


elseif ($Message.contains("Failed")){   
   $subject = "[Failed] Endpoint Backup $hostname failed"
   $Message2 = "EndpointBackup job '$job' finished with failed."
   $bgcolor = "#fb9895"
}
elseif ($Message.contains("Warning")){
   $subject = "[Warning] Endpoint Backup $hostname finished with Warning."   
   $Message2 = "EndpointBackup job '$job' finished with warning."
   $bgcolor = "#ffd96c"
}

# Giving the typical Veeam look
$Body =

"<meta http-equiv=""Content-Type"" content=""text/html; charset=utf-8"" />
<table cellspacing=""0"" cellpadding=""0"" width=""50%"" border=""0"" style=""border-collapse: collapse;"">
<tr>
<td style=""padding: 0px;font-family: Tahoma;font-size: 12px;"">
<table cellspacing=""0"" cellpadding=""0"" width=""100%"" border=""0"" bordercolor=""#a7a9ac"" style=""border-collapse: collapse;"">
<tr>
<td colspan=""5"" style=""border: 1px solid #a7a9ac;background-color: $bgcolor;color: White;font-family: Tahoma;font-weight: bold;font-size: 16px;height: 70px;vertical-align: bottom;padding: 0 0 15px 15px;"">Backup job: $Hostname
<div style=""margin-top: 5px;font-size: 12px;"">$Message2</div>
</td></tr><tr>
<td colspan=""5"" style=""height: 35px;border: 1px solid #a7a9ac; background-color: #f3f4f4;font-size: 16px;vertical-align: middle;padding: 5px 0 0 15px;color: #626365; font-family: Tahoma;"">
<span>$date</span></td></tr>
<tr>
<td nowrap="""" style=""width:100px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Start time </b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$start</td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Data size</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$spacebackup</td>
<td rowspan=""3"" style=""border: 1px solid #a7a9ac;""><span style=""font-size: 10px;"">&nbsp;</span></td>
</tr><tr>
<td nowrap="""" style=""width:100px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>End time</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$end</td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Total backup size</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$spaceall</td>
</tr><tr>
<td nowrap="""" style=""width:100px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Duration</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$duration</td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;""><b>Free disk space</b></td>
<td nowrap="""" style=""width:150px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;font-size: 12px;"">$spacefree</td>
</tr>
<tr><td colspan=""5"" style=""font-size:10px;padding: 2px 3px 2px 3px;border: 1px solid #a7a9ac;background-color:#f3f4f4;"">&nbsp;</td></tr>
<tr><td colspan=""5"" style=""font-size:12px;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;"">$reason<br>$detail</td></tr>
</table></td></tr>
<tr><td >&nbsp;</td></tr><tr>
<td style=""font-size:12px;color:#626365;padding: 2px 3px 2px 3px;vertical-align: top;border: 1px solid #a7a9ac;font-family: Tahoma;"">Veeam Endpoint Backup</td>
</tr>
</table>"

$SMTPClient = New-Object System.Net.Mail.SmtpClient( $emailSmtpServer , $emailSmtpServerPort )
$SMTPClient.EnableSsl = $true
$emailMessage.Subject = $subject
$emailMessage.IsBodyHtml = $true
$emailMessage.Body = $Body

# Send the email
if ($InstanceID.contains("190")) {
   $SMTPClient.Send( $emailMessage )
} else {
   write-host "I don't want messages on 10010 and 10050 Restorepoint-creation or -remove Emails, skip those"
}


write-host "Script finished with -$instanceID- as the last event-ID"
JudgeFudge
Influencer
 
Posts: 15
Liked: 8 times
Joined: Thu Jan 07, 2016 4:54 pm
Location: Germany
Full Name: Chris

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby best74 » Thu Jan 21, 2016 9:13 am

Brilliant!

Many thanks JudgeFudge, this works like a treat! I now get free disk space and correct time in start time & end time. I have zero powershell scripting experience, so this is very helpful.

oh, one of my co-workers added som email authentication to #the send email section:

# Send the email
if ($InstanceID.contains("190")) {
$SMTPclient.EnableSsl = $true
$SMTPAuthUsername = "username"
$SMTPAuthPassword = "password"
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential($SMTPAuthUsername, $SMTPAuthPassword)
[System.Net.ServicePointManager]::ServerCertificateValidationCallback = { return $true }
$SMTPClient.Send( $emailMessage )
best74
Novice
 
Posts: 9
Liked: 1 time
Joined: Thu Jan 14, 2016 1:16 pm
Full Name: Brian Løfqvist Jensen

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby JudgeFudge » Thu Jan 21, 2016 2:10 pm 1 person likes this post

Thank you, yes I had the same issue at the beginning, the problem was that our password was not encrypted in the script, so we used a mailserver without authentication.
I found a simple one like you posted and it worked but we didn't wanted the passwords on the server of our customer, so this is why we changed.
If you use it intern it should be no problem, you have to know what's the best. But for mail without authentication everything works great yes.
JudgeFudge
Influencer
 
Posts: 15
Liked: 8 times
Joined: Thu Jan 07, 2016 4:54 pm
Location: Germany
Full Name: Chris

Re: Here it is: Powershell script to add VEB-EMails

Veeam Logoby MattMN » Thu Jan 28, 2016 5:35 pm

Is there a way to show the start time and end time date? I assume I'm missing something with the parsing.
MattMN
Lurker
 
Posts: 1
Liked: never
Joined: Mon Jan 04, 2016 4:55 pm
Full Name: Matt

PreviousNext

Return to Veeam Agent for Windows



Who is online

Users browsing this forum: No registered users and 11 guests