Powershell - Creating a Backup Report

PowerShell script exchange

Powershell - Creating a Backup Report

Veeam Logoby ylevadoux » Wed Mar 02, 2011 1:01 pm

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

Re: Powershell - Creating a Backup Report

Veeam Logoby Alexey D. » Wed Mar 02, 2011 2:46 pm

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.
Alexey D.
 

Re: Powershell - Creating a Backup Report

Veeam Logoby ylevadoux » Thu Mar 03, 2011 9:06 am

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

Re: Powershell - Creating a Backup Report

Veeam Logoby Alexey D. » Thu Mar 03, 2011 11:59 am

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.
Alexey D.
 

Re: Powershell - Creating a Backup Report

Veeam Logoby ylevadoux » Thu Mar 03, 2011 1:50 pm

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

Re: Powershell - Creating a Backup Report

Veeam Logoby Alexey D. » Thu Mar 03, 2011 3:05 pm

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.
Alexey D.
 

Re: Powershell - Creating a Backup Report

Veeam Logoby ylevadoux » Thu Mar 03, 2011 3:24 pm

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

Re: Powershell - Creating a Backup Report

Veeam Logoby Alexey D. » Thu Mar 03, 2011 3:31 pm

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.
Alexey D.
 

Re: Powershell - Creating a Backup Report

Veeam Logoby ylevadoux » Thu Mar 03, 2011 3:49 pm

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

Re: Powershell - Creating a Backup Report

Veeam Logoby flavor4real » Tue Mar 08, 2011 6:17 pm

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: Mon Nov 22, 2010 7:57 pm
Full Name: DS

Re: Powershell - Creating a Backup Report

Veeam Logoby flavor4real » Tue Mar 08, 2011 8:07 pm

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

Re: Powershell - Creating a Backup Report

Veeam Logoby davidkeis » Fri Apr 01, 2011 1:15 pm

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 :

(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


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. :)
davidkeis
Influencer
 
Posts: 10
Liked: never
Joined: Mon Feb 14, 2011 8:09 am
Full Name: David Keis

Re: Powershell - Creating a Backup Report

Veeam Logoby ylevadoux » Fri Apr 01, 2011 1:43 pm

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: Wed Mar 02, 2011 12:16 am
Full Name: Yannick Levadoux

Re: Powershell - Creating a Backup Report

Veeam Logoby ylevadoux » Fri May 13, 2011 8:28 am

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

ylevadoux
Novice
 
Posts: 8
Liked: never
Joined: Wed Mar 02, 2011 12:16 am
Full Name: Yannick Levadoux

Re: Powershell - Creating a Backup Report

Veeam Logoby Beevoir » Fri May 13, 2011 8:44 am

Brilliant post, thank you Yannick!
Beevoir
Expert
 
Posts: 144
Liked: never
Joined: Thu May 06, 2010 11:13 am
Full Name: Mike Beevor

Next

Return to PowerShell



Who is online

Users browsing this forum: No registered users and 7 guests