PowerShell script exchange
Post Reply
ylevadoux
Novice
Posts: 8
Liked: never
Joined: Mar 02, 2011 12:16 am
Full Name: Yannick Levadoux
Contact:

Powershell - Creating a Backup Report

Post by 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.

Re: Powershell - Creating a Backup Report

Post by 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
Novice
Posts: 8
Liked: never
Joined: Mar 02, 2011 12:16 am
Full Name: Yannick Levadoux
Contact:

Re: Powershell - Creating a Backup Report

Post by 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.

Re: Powershell - Creating a Backup Report

Post by 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
Novice
Posts: 8
Liked: never
Joined: Mar 02, 2011 12:16 am
Full Name: Yannick Levadoux
Contact:

Re: Powershell - Creating a Backup Report

Post by 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.

Re: Powershell - Creating a Backup Report

Post by Alexey D. »

ylevadoux wrote: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
Novice
Posts: 8
Liked: never
Joined: Mar 02, 2011 12:16 am
Full Name: Yannick Levadoux
Contact:

Re: Powershell - Creating a Backup Report

Post by 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.

Re: Powershell - Creating a Backup Report

Post by 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
Novice
Posts: 8
Liked: never
Joined: Mar 02, 2011 12:16 am
Full Name: Yannick Levadoux
Contact:

Re: Powershell - Creating a Backup Report

Post by 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
Expert
Posts: 205
Liked: 5 times
Joined: Nov 22, 2010 7:57 pm
Full Name: DS
Contact:

Re: Powershell - Creating a Backup Report

Post by 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
Expert
Posts: 205
Liked: 5 times
Joined: Nov 22, 2010 7:57 pm
Full Name: DS
Contact:

Re: Powershell - Creating a Backup Report

Post by 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
Influencer
Posts: 10
Liked: never
Joined: Feb 14, 2011 8:09 am
Full Name: David Keis
Contact:

Re: Powershell - Creating a Backup Report

Post by davidkeis »

ylevadoux wrote: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
Novice
Posts: 8
Liked: never
Joined: Mar 02, 2011 12:16 am
Full Name: Yannick Levadoux
Contact:

Re: Powershell - Creating a Backup Report

Post by 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
Novice
Posts: 8
Liked: never
Joined: Mar 02, 2011 12:16 am
Full Name: Yannick Levadoux
Contact:

Re: Powershell - Creating a Backup Report

Post by 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
Expert
Posts: 144
Liked: never
Joined: May 06, 2010 11:13 am
Full Name: Mike Beevor
Contact:

Re: Powershell - Creating a Backup Report

Post by Beevoir »

Brilliant post, thank you Yannick!

odruard
Enthusiast
Posts: 40
Liked: 5 times
Joined: Jan 25, 2011 2:12 pm
Full Name: Olivier Druard
Contact:

Retrieve VMs in jobs with Powershell

Post by 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.
Product Manager
Posts: 24256
Liked: 1863 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Powershell - Creating a Backup Report

Post by 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
Enthusiast
Posts: 40
Liked: 5 times
Joined: Jan 25, 2011 2:12 pm
Full Name: Olivier Druard
Contact:

Re: Powershell - Creating a Backup Report

Post by 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
Influencer
Posts: 13
Liked: never
Joined: Feb 18, 2009 3:40 am
Contact:

Re: Powershell - Creating a Backup Report

Post by 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
Novice
Posts: 8
Liked: never
Joined: Mar 02, 2011 12:16 am
Full Name: Yannick Levadoux
Contact:

Re: Powershell - Creating a Backup Report

Post by ylevadoux »

clix wrote: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
Lurker
Posts: 1
Liked: never
Joined: Jun 21, 2011 12:30 pm
Full Name: Hermelin IT-partner AB

Power Shell to list result of every vm in backup job

Post by 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
Expert
Posts: 282
Liked: 26 times
Joined: Nov 10, 2010 6:51 pm
Full Name: Seth Bartlett
Contact:

Re: Powershell - Creating a Backup Report

Post by 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".
Skype: Sethbartlett88 - Make sure to label who you are and why you want to add me ;)
Twitter: @sethbartlett
If my post was helpful, please like it. Sometimes twitter is quicker to hit me up if you need me.

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest