Standalone backup agent for Microsoft Windows servers and workstations (formerly Veeam Endpoint Backup FREE)
graben
Novice
Posts: 4
Liked: 2 times
Joined: Oct 29, 2015 6:30 pm
Contact:

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

Post by graben »

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: Oct 29, 2015 6:30 pm
Contact:

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

Post by graben » 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
abelliniSIBA
Enthusiast
Posts: 32
Liked: 10 times
Joined: Nov 19, 2014 2:01 pm
Full Name: Alessandro Bellini
Location: Italy, Milan
Contact:

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

Post by abelliniSIBA » 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
Veolia
abelliniSIBA
Enthusiast
Posts: 32
Liked: 10 times
Joined: Nov 19, 2014 2:01 pm
Full Name: Alessandro Bellini
Location: Italy, Milan
Contact:

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

Post by abelliniSIBA »

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
Veolia
JudgeFudge
Influencer
Posts: 15
Liked: 8 times
Joined: Jan 07, 2016 4:54 pm
Full Name: Chris
Location: Germany
Contact:

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

Post by JudgeFudge » 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.
erwinbentelo
Lurker
Posts: 1
Liked: never
Joined: Jan 11, 2016 3:09 pm
Full Name: Erwin
Contact:

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

Post by erwinbentelo »

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
best74
Influencer
Posts: 10
Liked: 1 time
Joined: Jan 14, 2016 1:16 pm
Full Name: Brian Løfqvist Jensen
Contact:

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

Post by best74 » 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
remi_sic
Lurker
Posts: 1
Liked: never
Joined: Jan 19, 2016 11:06 am
Full Name: remi
Contact:

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

Post by remi_sic »

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
JudgeFudge
Influencer
Posts: 15
Liked: 8 times
Joined: Jan 07, 2016 4:54 pm
Full Name: Chris
Location: Germany
Contact:

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

Post by JudgeFudge »

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: Jan 07, 2016 4:54 pm
Full Name: Chris
Location: Germany
Contact:

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

Post by JudgeFudge »

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: Jan 07, 2016 4:54 pm
Full Name: Chris
Location: Germany
Contact:

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

Post by JudgeFudge » 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: Jan 07, 2016 4:54 pm
Full Name: Chris
Location: Germany
Contact:

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

Post by JudgeFudge »

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"
best74
Influencer
Posts: 10
Liked: 1 time
Joined: Jan 14, 2016 1:16 pm
Full Name: Brian Løfqvist Jensen
Contact:

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

Post by best74 »

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 )
JudgeFudge
Influencer
Posts: 15
Liked: 8 times
Joined: Jan 07, 2016 4:54 pm
Full Name: Chris
Location: Germany
Contact:

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

Post by JudgeFudge » 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.
MattMN
Lurker
Posts: 1
Liked: never
Joined: Jan 04, 2016 4:55 pm
Full Name: Matt
Contact:

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

Post by MattMN »

Is there a way to show the start time and end time date? I assume I'm missing something with the parsing.
best74
Influencer
Posts: 10
Liked: 1 time
Joined: Jan 14, 2016 1:16 pm
Full Name: Brian Løfqvist Jensen
Contact:

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

Post by best74 »

Hmm, now i'm only receiving backup finished with warning, and not job detail (e.g. low disk space or whatever)
JudgeFudge
Influencer
Posts: 15
Liked: 8 times
Joined: Jan 07, 2016 4:54 pm
Full Name: Chris
Location: Germany
Contact:

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

Post by JudgeFudge »

How is your Environment? Did you check on VEB what the Backups are saying?
Not every Warningmessage writes in the Database. Please check in the VEB when the Backup finished with warning what warning it says.

@MattMN what exactly do you mean by showing the Time and Date? if you want to have the Date after the Time just delete the variable $start and enter $TimeBackupStarted on HTML part same with $end and $TimeGenerated
best74
Influencer
Posts: 10
Liked: 1 time
Joined: Jan 14, 2016 1:16 pm
Full Name: Brian Løfqvist Jensen
Contact:

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

Post by best74 »

Hi, my warning in VEB is the following:
Backup location [\\BACKUP\Backup\Support01] is getting low on free disk space (436,9 GB free of 8,0 TB).
JudgeFudge
Influencer
Posts: 15
Liked: 8 times
Joined: Jan 07, 2016 4:54 pm
Full Name: Chris
Location: Germany
Contact:

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

Post by JudgeFudge » 1 person likes this post

About the part why you don't get the reason.. fucking .NET Framework update -.- had the same problem while testing.

I had to update the code again, made another SQL query. Two weeks ago everything worked and now MS fucked it up again. I think this script will never be finished.
Problem is that the variable $reason will no longer be empty in the new .NET version so that's why you get the $Message instead.
I checked it with !$_.reason if its empty and it was but now it won't be recognized anymore. I work now with [System.DBNull]::Value which is the right query for SQL.
Tried it another hundred times with two Machines where I failed Backups on purpose and with the new query it gives me the right Information again.
Mayor update is the part where i connect to the veeam database and fill my variables.

Please Update your code and tell me if it's working again.

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.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;"
$SQLConnection.Open()
$SQLCommand = $SQLConnection.CreateCommand()
$sql = "SELECT TOP 1 stored_size AS size, job_name AS job FROM [VeeamBackup].[dbo].[ReportSessionView] ORDER BY [creation_time] DESC;
SELECT [free_space] AS [free] FROM [VeeamBackup].[dbo].[BackupRepositories] WHERE (name != 'Default Backup Repository')
SELECT  SUM(backup_size) AS backupsize FROM [VeeamBackup].[dbo].[WmiServer.RestorePointsView]
SELECT TOP 1 reason AS Reason, stop_details AS Detail FROM [VeeamBackup].[dbo].[Backup.Model.JobSessions] ORDER BY creation_time DESC"
$SQLCommand.CommandText = $sql
$readAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$readSet = New-Object System.Data.DataSet
$readAdapter.SelectCommand = $SQLCommand
$readAdapter.Fill($readSet) |out-null
Foreach ($row in $readSet.Tables[0].rows) {$space_backup = $row[0]; $job = $row[1]}
Foreach ($row2 in $readSet.Tables[1].rows) {$space_free = $row2[0]}
Foreach ($row3 in $readSet.Tables[2].rows) {$space_all = $row3[0]}
Foreach ($row4 in $readSet.Tables[3].rows) {$reason = $row4[0]; $detail = $row4[1]}
$SQLConnection.Close() 

if($reason -eq [System.DBNull]::Value)
{$reason = $Message}

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 (VEB)"
   $Message2 = "EndpointBackup job '$job' finished with success."
   $bgcolor = "#00B050"}
elseif($Message.contains("Failed"))
{$subject = "[Failed] Endpoint Backup $hostname failed (VEB)"
   $Message2 = "EndpointBackup job '$job' finished with failed."
   $bgcolor = "#fb9895"}
elseif($Message.contains("Warning"))
{$subject = "[Warning] Endpoint Backup $hostname finished with Warning. (VEB)"   
   $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"
best74
Influencer
Posts: 10
Liked: 1 time
Joined: Jan 14, 2016 1:16 pm
Full Name: Brian Løfqvist Jensen
Contact:

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

Post by best74 »

Many thanks, i'll give it shot, and return with my results!
saintdle
Veeam Vanguard
Posts: 103
Liked: 17 times
Joined: Aug 05, 2014 1:13 pm
Full Name: Dean lewis
Contact:

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

Post by saintdle »

Cheers for the time taken to do this and al your modifications, just set this up for a customer who has a single windows server, and use the script to alert on the backup status, as they wont be touching the server via a login and such to check the status themselves
Technical Architect
Veeam Certified Architect
Veeam Vanguard
  • Personal Technical Blog - www.veducate.co.uk
  • Twitter - @saintdle
zigzag
Novice
Posts: 4
Liked: never
Joined: Mar 02, 2016 9:50 am
Contact:

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

Post by zigzag »

Thanks for good working script. I used it on different machines and it works perfectly.

But now I have two machines, where I have trouble. If I start the script manually in the PS, i will get a lot of errors.
I checked the registry and I found the object... Any Ideas?

Thanks a lot

Example:

Code: Select all

Get-Item : Der Pfad "HKLM:\SOFTWARE\Veeam\Veeam Endpoint Backup" kann nicht gefunden werden, da er nicht vorhanden ist.
Bei C:\scripts\veeam.ps1:40 Zeichen:22
+     $User = (get-Item <<<<  $key).GetValue("SqlLogin")
    + CategoryInfo          : ObjectNotFound: (HKLM:\SOFTWARE\...Endpoint Backup:String) [Get-Item], ItemNotFoundExcep
   tion
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemCommand

Sie können keine Methode für einen Ausdruck mit dem Wert NULL aufrufen.
Bei C:\scripts\veeam.ps1:40 Zeichen:37
+     $User = (get-Item $key).GetValue <<<< ("SqlLogin")
    + CategoryInfo          : InvalidOperation: (GetValue:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
JudgeFudge
Influencer
Posts: 15
Liked: 8 times
Joined: Jan 07, 2016 4:54 pm
Full Name: Chris
Location: Germany
Contact:

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

Post by JudgeFudge »

Send you a PM looks more lika problem with VEB or .NET
Dima P.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

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

Post by Dima P. » 2 people like this post

Hi guys,

Thanks to all of you for amazing workarounds! The wait is over: we have added email notifications in version 1.5. You can get the new version from our website. Once you test built-in notifications, don't forget to disable your scripts - otherwise you may be getting too many notifications regarding successful backups :mrgreen:
ftcnet
Enthusiast
Posts: 30
Liked: 3 times
Joined: May 15, 2015 1:59 am
Full Name: Bernard Klatt
Contact:

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

Post by ftcnet »

Thanks for including this very helpful email result feature.

Image

.. where can we find info on these additional subject variables ? .. or are there only the three subject variables that are shown in the example?

It would be helpful if the test email actually included the resulting Subject line (using the variables) instead of just "Veeam Endpoint Backup".
lxk3il
Veeam ProPartner
Posts: 27
Liked: 6 times
Joined: Sep 10, 2015 11:23 am
Full Name: Lukas
Contact:

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

Post by lxk3il » 1 person likes this post

ftcnet wrote: .. where can we find info on these additional subject variables ? .. or are there only the three subject variables that are shown in the example?
As you can see on your screenshot, pressing F1 opens the corresponding helppage, where you can find the currently available parameters:
  • %ComputerName%
  • %JobResult%
  • %CompletionTime%
If Veeam adds some more parameters, they will most liekly update this list.
Dima P.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

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

Post by Dima P. »

Hi guys,

It seems the word ‘additional’ is a bit confusing – VEB has only three variables available (all described in the Help Center article). Let us know if you need some extra subject variables added but do not forget to share your personal use case :wink:
feelgoodeule
Influencer
Posts: 20
Liked: 19 times
Joined: Jul 28, 2009 12:28 pm
Full Name: Michael

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

Post by feelgoodeule » 3 people like this post

Hello together,

first of all: thank you Veeam developers for making my script obsolete. It was very interesting what was developed out of the first idea and how many people contributed great thoughts. Thanks to everybody.

While it was pretty easy to get everything running at the office having an exchangeserver, I still had some difficulties to get it going at home without.

Here for those who search in short:

- create a googlemail or gmail-Account
- Open your Google account > Sign-in & security and ensure that "Allow less secure apps" is set to ON (as said in the VEB-help)
- in VEB-Email-settings: SMTP server settings: smtp.googlemail.com Port 587
- tick "use secure connection"
This worked for me. Hope for you too.

Michael
Invidia
Lurker
Posts: 1
Liked: never
Joined: Apr 08, 2016 7:50 am
Full Name: Andre Coleman
Contact:

Share Backupscript

Post by Invidia »

Hello together,

I wrote a script in the past that runs a "veeam endpoint backup free" job and sends me a e-mail if the backup was successful or not.
In the old version was no possibility to run the job just an week-days. So I used the windows integraded scheduled task programm to let it run from monday till friday.

So here is the script:

Code: Select all

### params ###
$from = "your.email@gmail.com"               
$to = "recipient@gail.com"
$smtpserver = "smtp.gmail.com"
$smtpport = 587
$user = "your.email@gmail.com"
$passwort = "yourpassword"
$date = get-date -format g
$hddname = [System.IO.DriveInfo]::GetDrives() | where {$_.Name -eq "D:\" } | select -ExpandProperty VolumeLabel
$LogFile = "C:\Server\backup.log"                  # phath + filename
### starts the backupjob ###
& "C:\Program Files\Veeam\Endpoint Backup\Veeam.EndPoint.Manager.exe" /backup
### evaluate returncode and set email text ###
if ($LASTEXITCODE -eq 0)
{
    $subject = $date + " Backup was successfull on Server XYZ"
    $body = $date + " Backup alert on Server XYZ. Backup was accomplished on disk " + $hddname + "."
}
elseif ($LASTEXITCODE -ne 0)
{
    $subject = $date + " Backup was unsuccessfull on Server XYZ"
    $body = $date + " Backup alert on Server XYZ. Backup was not accomplished."
}
### send e-mail ###
$smtp = New-Object System.Net.Mail.SmtpClient($smtpserver, $smtpport);
$smtp.EnableSSL = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential  ($user,$passwort);
$smtp.Send($from, $to, $subject, $body);
### create logfile ###
### source: http://hope-this-helps.de/serendipity/archives/399-Funktion-um-Logfile-zu-schreiben-fuer-die-Powershell.html ###
function write_log ($content)
{
    $FileExists = Test-Path $LogFile
    $DateNow = Get-Date -Format "dd.MM.yyyy HH:mm"                      # evaluates the date with following syntax 01.10.2013 10:00
    $FileInp = $content                                               
    If ($FileExists -eq $True){                                        
        Add-Content $LogFile -value $FileInp                            
    } else {                       
       New-Item $Logfile -type file                                     
       Add-Content $LogFile -value $FileInp                             
    }
}
$LogInp = $date + " Backup was accomplished on disk " + $hddname + "." 
write_log ($LogInp)                                                    

Mike Resseler
Product Manager
Posts: 8044
Liked: 1263 times
Joined: Feb 08, 2013 3:08 pm
Full Name: Mike Resseler
Location: Belgium
Contact:

Re: Share Backupscript

Post by Mike Resseler »

Hi Andre,

Thanks for the script however, in version 1.5 you can let VEB send email and we implemented the functionality to backup on weekdays only, and even on the days of your choice so if you upgrade you won't need the script anymore ;-)
Post Reply

Who is online

Users browsing this forum: Bing [Bot] and 12 guests