Get last weeks SQL log backup sessions that are in warning

PowerShell script exchange

Get last weeks SQL log backup sessions that are in warning

Veeam Logoby gk87 » Fri 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.
gk87
Influencer
 
Posts: 10
Liked: never
Joined: Fri Jun 09, 2017 11:47 am

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

Veeam Logoby v.Eremin » Tue 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.
v.Eremin
Veeam Software
 
Posts: 13534
Liked: 1002 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

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

Veeam Logoby gk87 » Wed 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?
gk87
Influencer
 
Posts: 10
Liked: never
Joined: Fri Jun 09, 2017 11:47 am

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

Veeam Logoby v.Eremin » Wed 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.
v.Eremin
Veeam Software
 
Posts: 13534
Liked: 1002 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

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

Veeam Logoby gk87 » Thu Jun 15, 2017 12:10 pm

Thanks for your help Vladimir. This should get me going for now.
gk87
Influencer
 
Posts: 10
Liked: never
Joined: Fri Jun 09, 2017 11:47 am

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

Veeam Logoby gk87 » Thu 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
gk87
Influencer
 
Posts: 10
Liked: never
Joined: Fri Jun 09, 2017 11:47 am

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

Veeam Logoby v.Eremin » Fri 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.
v.Eremin
Veeam Software
 
Posts: 13534
Liked: 1002 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

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

Veeam Logoby gk87 » Mon 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}
gk87
Influencer
 
Posts: 10
Liked: never
Joined: Fri Jun 09, 2017 11:47 am

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

Veeam Logoby v.Eremin » Tue 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.
v.Eremin
Veeam Software
 
Posts: 13534
Liked: 1002 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin


Return to PowerShell



Who is online

Users browsing this forum: No registered users and 17 guests