Powershell - Creating a Backup Report

[ylevadoux]

Hello,

We are working with Veeam Backup & replication 5. Our Veeam job policy is to backup VMware folders.

I'm trying to create, using powershell, a backup report :
I need to have, for each job, the backup status of each VM.

Is there a way to get this information ?

Yannick

[Alexey D.]

Hello Yannick,

This is how to get status of job itself:

Code: Select all

(Get-VBRJob JobName).GetStatus()

Will ask devs if it's possible at per-VM level.

[ylevadoux]

Hello Alexey

The command you gave me does not work in my environnement :

I put my job name into $myJob variable. Adapting the command you provide :

(Get-VBRJob $myJob).GetStatus()

I have the following error

Get-VBRJob : A positional parameter cannot be found that accepts argument 'Sche
duled - Folder Backup'.
At line:1 char:12
+ (Get-VBRJob <<<< $myJob).GetStatus()
+ CategoryInfo : InvalidArgument: (:) [Get-VBRJob], ParameterBind
ingException
+ FullyQualifiedErrorId : PositionalParameterNotFound,Veeam.Backup.PowerSh
ell.Command.GetVBRJob

To get the $myJob's status, I need to run the following command :

Get-VBRJob | where {$_.Name -eq $myJob}

Anyway, I really look forward a per-vm status using powershell
Thanks
Yannick

[Alexey D.]

Glad you figured out with job status.
As per-VM, devs are saying that this functionality is not implemented, maybe in future it will be possible.

[ylevadoux]

Thank you for answer Alexey.

So, no powershell cmd-let are available to get a per-vm backup Report.

Can you advice on how can we get automaticaly an html file, reporting a per-VM backup status please ?

I know we can have an email notification, but this is not acceptable for my customer. He requires an html backup report with every single VM's status.
The html report available in Veeam B&R console (right click on the session => html report OR the html button availble in the session statistic) would be very good report, but I don't know ho to generate it automatically after the job.

Yannick

[Alexey D.]

I know we can have an email notification, but this is not acceptable for my customer.

Yannick, I guess you are talking about notifications from Enterprise Manager, right? Because EM sends only brief summary.
And if you set notifications from B&R console you would receive reports with VMs' status. Actually, it's the same template as when you click 'HTML Report' button.

[ylevadoux]

Yes Alexey, I only need the 'HTML Report' available in B&R console. In fact, it is more accurate than requested But my need is to get automaticaly the html file, not email notification.
If I have to install Enterprise Manager, I will ! But I was looking for an other simple way, such as powershell (which allow us some report's customization)

So, Enterprise Manager will complete this task ?

[Alexey D.]

Not exactly,

I'm saying that EM sends only brief report as notification, whilst B&R console sends detailed one with processing status.
Please choose on your own which suits you better.

[ylevadoux]

I will explain you more, you'll understand better my need :
I need an automatically generated html file, published on the customer's intranet website, on which we can found every VM's backup status.
So the only information I need is : VM Name, Last session backup status (success or failed) that's all (and that's why powershell was a good idea)

The HTML report available on B&R console cannot be generated automatically (or I don't know how) => this is not ok for us
If EM can do this, even with a brief report that will be ok

I will try EM

Thanks for answers
Yannick

[flavor4real]

Hello Guys,
I wanted to see if there is a solution for ylevadoux question? I'm interested in a report of that kind too. Thx

[flavor4real]

Nevermind guys. I just got introduced to the Veeam Backup Enterprise Manager. I fixed my FIPS validated cryptographic algorithms issue and now i'm taking a look into that product. thx anyway

[davidkeis]

Hello Alexey

The command you gave me does not work in my environnement :

I put my job name into $myJob variable. Adapting the command you provide :
I have the following error
To get the $myJob's status, I need to run the following command :
Anyway, I really look forward a per-vm status using powershell
Thanks
Yannick

As Yannick wrote above, It does not seem possible to get the job status by calling the

Code: Select all

 (Get-VBRJob $myJob).GetStatus()

I do get some info when calling my job like this:

Code: Select all

Get-VBRJob | where {$_.Name -eq $myJob}

But i dont get any information about job status (succes / failed).

What I get is just some basic info for the job:

Code: Select all

Id                : f3c3e720-c9e8-4933-8af8-fd8184294dfc
Info              : Veeam.Backup.Model.CDbBackupJobInfo
JobTargetType     : Backup
Description       : Created by DKVAM-AD1\Administrator at 16-02-2011 08:14:23.
Name              : backup dkvam-ad2
IsScheduleEnabled : True

There is no status info on the last backup executed. Is it possible to get that sort of information?

-I'd like to wrap that information into a mail, and then send it when all backup jobs are finished, so I will get one mail with status from all my backup jobs.

[ylevadoux]

Hello davidkeis,

Put the result in $myStatus variable, then your job's result is in $myStatus.Info.LatestStatus

Try this:

$myJob = "my job name"
$myStatus = Get-VBRJob | where {$_.Name -eq $myJob}
Write-Host $myStatus.Info.LatestStatus

[ylevadoux]

Hello,

After opening a case to Veeam support, I had the statement for a SQL query to get my information. Support warn us using this query because it may not work after Veeam updates.

Basic SQL query is:

SELECT creation_time, object_name, status, reason FROM [Backup.Model.BackupTaskSessions] WHERE end_time > $today'sdate;

It works in our environnement which is the following

Veeam server Windows Server 2003 SP2
Veeam Backup & replication 5.0.1.198 (32 bit)
SQL Server 2005

I adapat the previous SQL query to our environnement in order to create a powershell script which runs every night.
This script create a (very) basic HTML report.

Thanks to support guy which was reactive and professionnal.

##############################################################################################
# Veeam reporting script
# Yannick Levadoux levadoux@fr.ibm.com
#
# Created on May 4th, 2011
#
# *****************
# Script Purpose
# *****************
#
# This script run SQL queries on Veeam SQL database in order to create a Veeam per-VM report
# Report Type : HTML
#
#
#
##############################################################################################

##############################################
# MAIN FUNCTION
# -------------
#
# Invoke-Sqlcmd2 (http://poshcode.org/1791)
# Adapted version of the function found on the web site
# This function run a SQL query with 3 main parameters
# ServerInstance, Database and Query
#
#
##############################################

function Invoke-Sqlcmd2
{
param
(
[string]$ServerInstance,
[string]$Database,
[string]$Query,
[Int32]$QueryTimeout=30
)

$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $ServerInstance,$Database
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables[0]
}


##############################################
# HTML Function
# -------------
#
# - HTMLTab-Green : Add a Green cell to tab (for Success Status)
# - HTMLTab-Yellow : Add a Yellow cell to tab (for Warning Status)
# - HTMLTab-Red : Add a Red cell to tab (for Failed Status)
# - HTMLTab-Title : Add a title cell
# - HTMLClose : Close html page
#
##############################################

Function HTMLTab-Green ($myServer,$myStatus)
{
$Report = @"
<TR> <TD bgColor='#00ff00'><font size="-1"> $($myServer) </TD> <TD bgColor='#00ff00'><font size="-1"> $($myStatus) </TD> </TR>

"@
Return $Report
}

Function HTMLTab-Yellow ($myServer,$myStatus)
{
$Report = @"
<TR> <TD bgColor='#ffff00'><font size="-1"> $($myServer) </TD> <TD bgColor='#ffff00'><font size="-1"> $($myStatus) </TD> </TR>

"@
Return $Report
}


Function HTMLTab-Red ($myServer,$myStatus)
{
$Report = @"
<TR> <TD bgColor='#ff0000'><font size="-1"> $($myServer) </TD> <TD bgColor='#ff0000'><font size="-1"> $($myStatus) </TD> </TR>

"@
Return $Report
}


Function HTMLTab-Title ($myJob)
{
$Report = @"
<TR> <TD bgColor=#e0e0f9 colSpan=7> <DIV align=center><B> $($myJob) </B></DIV></TD></TR>

"@
Return $Report
}

Function HTMLClose ($time)
{
$Report = @"
</TABLE>

<BR>

Generated in $($time) sec

</BODY>
</HTML>
"@
Return $Report
}




##############################################
# MAIN PROCESSING
##############################################

# ********************
# Preparation stuff
# ********************

# Date / Time
$begint = get-date

# Output file
$filelocation = "C:\temp\"
$filelocation += (Get-Date -UFormat "%Y_%m_%d_%H_%M_")
$filelocation += "VeeamReporting.htm"

# Assuming that we work with the same SQL Server instance and the same database, let's initiate this parameters
$mySQLInstance = "VeeamServer\VEEAM_INST"
$mySQLDatabase = "VeeamBackup_DB"

# Initialize Veeam Job list
$VeeamJobList = "My First Veeam Job","My Second Veeam Job", "My Third Veeam Job"


#Initiate HTML page
$MyReport = ""
$MyReport = @"
<HTML><HEAD><TITLE>Veeam Backup Report</TITLE></HEAD>
<BODY>
<H1>
Veeam Backup Report
</H1>
<b><u>Report created on $(Get-Date)</u></b>
<p>
<TABLE border="1" width="100%">

"@


####################################
# Foreach Veeam Job loop
####################################


foreach ($VeeamJob in $VeeamJobList)
{
#Add a title line to tab
$MyReport += HTMLTab-Title $VeeamJob

# Get SQL Job id
$mySQLQuery = "select id, name from [Bjobs] where name = " + "'" + $VeeamJob + "'"
$VeeamJobId = Invoke-Sqlcmd2 $mySQLInstance $mySQLDatabase $mySQLQuery

# Get latest Session id :
$mySQLQuery = "select id FROM [Backup.Model.JobSessions] where end_time = (select MAX(end_time) FROM [Backup.Model.JobSessions] WHERE job_id = " + "'" + $VeeamJobId.id + "') AND job_id = " + "'" + $VeeamJobId.id + "'"
$VeeamSessionId = Invoke-Sqlcmd2 $mySQLInstance $mySQLDatabase $mySQLQuery

# Run query including job id and session id
$mySQLQuery = "SELECT [Backup.Model.BackupTaskSessions].object_name, [Backup.Model.BackupTaskSessions].status, [Backup.Model.BackupTaskSessions].reason FROM [Backup.Model.BackupTaskSessions], [Backup.Model.JobSessions] WHERE [Backup.Model.BackupTaskSessions].session_id = " + "'" + $VeeamSessionId.id + "' AND [Backup.Model.JobSessions].job_id = " + "'" + $VeeamJobId.id + "' AND [Backup.Model.BackupTaskSessions].session_id = [Backup.Model.JobSessions].id"
$VMStatus = Invoke-Sqlcmd2 $mySQLInstance $mySQLDatabase $mySQLQuery

###########################
# Foreach VM in Veeam Job
###########################

foreach ($srv in $VMStatus)
{
if ($srv.Status -eq 0)
{
$MyReport += HTMLTab-Green $srv.Object_Name
}
Elseif ($srv.Status -eq 3)
{
$MyReport += HTMLTab-Yellow $srv.Object_Name $srv.reason
}
Else
{
$MyReport += HTMLTab-red $srv.Object_Name $srv.reason
}
}
}


##############################################
# SCRIPT END
# ----------
#
# - Figure out how many time the script take to run
# - Close HTML Page
#
##############################################
$endt = Get-Date
$elapsed = (($endt - $begint).TotalSeconds)
$elapsed = [math]::Round([decimal]$elapsed, 2)

$MyReport += HTMLClose $elapsed
$MyReport | Out-File $filelocation

# Debug
# Open html page
# Invoke-Item $filelocation
# END Debug

[Beevoir]

Brilliant post, thank you Yannick!

[odruard]

[merged into existing discussion]

Hello all

I'm looking for a way to retrieve all VMs backed up in a job.

Such a command retrieve the objects in a job :

Code: Select all

ForEach ($Job in Get-VBRJob) { $Job.GetObjectsInJob() | ForEach { $_.Location } }

However, for organizational reasons, we don't add VMs directly in jobs.
Jobs are configured to back up folders (the folders you can see in "VMs and Templates" view from vSphere Client).
Then jobs are definitely configured, and if we add a VM in the group, this VM will be automatically backed up without any modification in Veeam Backup.

The command line show here upper give only the list of VMs groups.
Same problem should occurs if you back up datastores lists instead of VM lists.

Does anybody knows the way to retrieve the Backup Status for each VM ?

Thanks for your help.
Olivier

[Vitaliy S.]

Hi Olivier, try using the script from Yannick on the previous page.

Furthermore, you may also want to look at this script from David - PowerShell script

Thanks.

[odruard]

I didn't found these examples. Thanks.

I quiclky tested Yannick's one. I retreive VM's lists.
Need to be adapted to my needs, but seems a good starting point.

I didn't yet tried David's one. Need to be modified first, as I saw it start jobs.

Thanks,

Olivier

[clix]

Great script Yannick, thank you.

I just need to figure out a way to show if a job is disabled/unscheduled as the report will continue to show a backup being successful/failed/warning even though it's not running.

Cheers,

Manuel

[ylevadoux]

Great script Yannick, thank you.

I just need to figure out a way to show if a job is disabled/unscheduled as the report will continue to show a backup being successful/failed/warning even though it's not running.

Cheers,

Manuel

Hello,

I would use something like this (Using built-in Veeam Powershell cmd-let) :

$myJob = Get-VBRJob | where {$_.Name -eq "Your Job Name"}

All information regarding scheduling are now located in $myJob.info.ScheduleOptions

More specificly $myJob.info.ScheduleOptions.NextRun is empty if there is no schedule, and it contain a date/time if it is scheduled.
Depending your need, add the job (or not) to the job list to be reported.

[hermelin]

[merged]

Hi

We need a power shell script that lists the backup result of every VM in a backup job. We also need to list the date on which the backup was done.

We have tried the following but it only tells us the result of the entire backup job. Not the VMs in the job.
$job = GetVBRJob
foreach($j in $job){$j.findlastsession() | ft -autosize >> c:\temp\backupresult.txt }

/Mattias

[Sethbartlett]

You need to do the following:

Code: Select all

$job = Get-VBRBackup | ?{$_.JobName -eq "JobName"}

foreach($v in $job.GetOibs()) { echo $v.VmName $v.IsConsistent}

This will output the name of the VM, the next line will give you true/false on whether the point was consistent. If you start a job and let one VM finish and then stop the job and run this, you will see inconsistent for your other VMs in the job and they will show as "false".