PowerShell script exchange
Post Reply
gk87
Influencer
Posts: 11
Liked: never
Joined: Jun 09, 2017 11:47 am
Contact:

Get last weeks SQL log backup sessions that are in warning

Post by gk87 » Jun 09, 2017 1:21 pm

My goal is to get SQL log backup sessions that have status "Warning" from last week. I can get this information from normal backup sessions but are unable to get this information for SQL log backup sessions.

I'll only include the relevant part in the script for now:

Code: Select all

# Get Veeam SQL log backup jobs
Foreach ($job in $jobs)
{
$jobs += (get-vbrJob -Name $job.Name).FindChildSqlLogBackupJob() | select Name
}
Get-VBRBackupSession does not show the SQL log backups.

I know you have the following options but those only show the currently "working" job. I need to be able to find the status for last weeks SQL log backups.

Code: Select all

$job.FindLastSession()
$job.GetLastResult()
$job.GetLastState()
Any insight in this would be appreciated.

v.Eremin
Veeam Software
Posts: 15142
Liked: 1141 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Get last weeks SQL log backup sessions that are in warni

Post by v.Eremin » Jun 13, 2017 3:07 pm

You can get all SQL log backup sessions by loading assembly:

Code: Select all

asnp VeeamPSSnapin
$Job = Get-VBRJob -name "Name of backup Job"
$SQLJob = $Job.FindChildSqlLogBackupJob()
$Session = [Veeam.Backup.Core.CBackupSession]::GetByJob($SQLJob.Id)
Then, you will need to filter these entities, using CreationTimeUTC (to get last week sessions) and Result (to get only warning sessions) parameters.

Thanks.

gk87
Influencer
Posts: 11
Liked: never
Joined: Jun 09, 2017 11:47 am
Contact:

Re: Get last weeks SQL log backup sessions that are in warni

Post by gk87 » Jun 14, 2017 10:41 am

Thanks for clarifying. That one is new for me but I did manage to get results I wanted for normal backup jobs + SQL backup jobs.

Is there documentation for [Veeam.Backup.Core.CBackupSession] and underlying parts like GetByJob? Is my assumption correct that all type of Veeam sessions (Backup, SqlBackup, Surebackup, replication, ...) can be requested using that assembly? And how can I use [Veeam.Backup.Core.CBackupSession] to get all sessions?

v.Eremin
Veeam Software
Posts: 15142
Liked: 1141 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Get last weeks SQL log backup sessions that are in warni

Post by v.Eremin » Jun 14, 2017 12:46 pm

It's more or less unsupported way to dig deep into product internal structure. So, there is no documentation for that.

Using this approach, you can get sessions of CBackupSession type, which means SureBackup sessions won't be returned, as they have a different type.

To get all CBackupSession sessions, use the following one-liner (use it cautiously, there can be numerous sessions):

Code: Select all

[Veeam.Backup.Core.CBackupSession]::GetAll()

Thanks.

gk87
Influencer
Posts: 11
Liked: never
Joined: Jun 09, 2017 11:47 am
Contact:

Re: Get last weeks SQL log backup sessions that are in warni

Post by gk87 » Jun 15, 2017 12:10 pm

Thanks for your help Vladimir. This should get me going for now.

gk87
Influencer
Posts: 11
Liked: never
Joined: Jun 09, 2017 11:47 am
Contact:

Re: Get last weeks SQL log backup sessions that are in warni

Post by gk87 » Jun 15, 2017 8:17 pm

One other question I'm having is how I can handle the Get-vbrBackupSession cmdlet better for faster troughput. In belows example one test is handled in less than 1 second and the other takes 263 seconds which won't satisfy my monitoring purpose.

Code: Select all

# $date = 1 day back
# Server 1 (Small number of simular jobs)
$vbrSessions = (Get-vbrBackupSession | where {$_.creationTime -gt $date} | Sort creationtime -Descending).count
Time script used to run: 0.69 seconds
Number of sessions collected: 7

# Server 2 (Large number of different jobs)
$vbrSessions = (Get-vbrBackupSession | where {$_.creationTime -gt $date} | Sort creationtime -Descending).count
Time script used to run: 263.69 seconds
Number of sessions collected: 54

v.Eremin
Veeam Software
Posts: 15142
Liked: 1141 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Get last weeks SQL log backup sessions that are in warni

Post by v.Eremin » Jun 16, 2017 11:44 am

Are you on Update 2 already? I'm wondering because Update 2 should have number of enhancements for this matter. Also, check this thread, there are several tweaks mentioned. Thanks.

gk87
Influencer
Posts: 11
Liked: never
Joined: Jun 09, 2017 11:47 am
Contact:

Re: Get last weeks SQL log backup sessions that are in warni

Post by gk87 » Jun 19, 2017 8:06 pm

Ah, that's something we will take into consideration. Some customers are on version 8.5 and some on 9.5 but no update 2 as far as I know so we will look into that.

One more thing I need figured out:
A) If last job is failed or in warning report it.
B) If last job is still running ignore that job and check the job before that one.
C) If last job was Success ignore failed/warnings from previous jobs in timeframe.

This is wat I currently have which I think covers requisite A (first -1) and B (state eq stopped), but not C. Any eyes on this would be great.

Code: Select all

# Get Veeam backup sessions where $date equals yesterday (as an example)
$vbrSessions = Get-vbrBackupSession | where {$_.creationTime -gt $date -and $_state -eq "stopped"} | Sort creationtime -Descending
Foreach ($vbrJob in $vbrJobs) {$jobs += $vbrSessions | where {$_.jobName -eq $vbrjob.name -and $_.result -ne "Success" -and $_.result -ne "None"} | Sort creationtime -Descending | select Name, CreationTime, Result -first 1}

v.Eremin
Veeam Software
Posts: 15142
Liked: 1141 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Get last weeks SQL log backup sessions that are in warni

Post by v.Eremin » Jun 20, 2017 1:02 pm

Points B and C seem to contradict each other slightly - based on the result point C might overwrite point B.

I believe you will need to write a simple cycle. Within it you will:

1) Get the latest backup job session
2) If it's currently running, enter waiting loop
3) Wait till the session is finished
4) Get its final result
5) Generate notification, if the result is either warning or failed

Thanks.

Post Reply

Who is online

Users browsing this forum: MaxKozlov and 5 guests