Dans un précédent article, j’expliquais comment utiliser les CmdLet Veeam pour lister les jobs, mais ça ne fonctionne pas forcémment toujours comme on vboudrait en fonction des différentes versions utilisées.

Par contre avec cette nouvelle solution, vous êtes sûr d’y arriver … nous allons chercher les infos directement dans la base SQL.

Le script créé un fichier html à cet endroit : C:\TMP\VEEAM_JOBS.html

Voici le script :

param(
	$Server = "MyServeurSql\VEEAM",
	$Database = "VeeamBackup",
	$Query= "SELECT [id],[name],[target_dir],[progress],[result],[next_run],[latest_result],[schedule_enabled] FROM [VeeamBackup].[dbo].[ReportJobsView] ORDER BY [name] DESC"
	)

# change the SqlConnection
function global:Set-SqlConnection( $Server = $(Read-Host "SQL Server Name"), $Database = $(Read-Host "Database") ) {
  $SqlConnection.ConnectionString = "Server = $Server; Database = $Database; Integrated Security = True"
}

#Fonction qui transforme l'heure intl à l'heure française
$strCurrentTimeZone = (Get-WmiObject win32_timezone).StandardName
Function Get-LocalTime($UTCTime)
{
	#$strCurrentTimeZone = (Get-WmiObject win32_timezone).StandardName
	$strCurrentTimeZone = "Romance Standard Time"
	$TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById($strCurrentTimeZone)
	$LocalTime = [System.TimeZoneInfo]::ConvertTimeFromUtc($UTCTime, $TZ)
	Return $LocalTime
}

function global:Get-SqlDataTable($Query) {

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = $Query
  $SqlCmd.CommandTimeout = 500
  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet
  $SqlAdapter.Fill($DataSet)

  #Ajout d'une colonne (STATUS)
  $Col1 = New-Object system.Data.DataColumn Status,([string])
  $DataSet.Tables[0].Columns.Add($Col1)

  #modification colonne next_run en string
  Foreach ($MaDatarow in $DataSet.Tables[0].Rows)
  {
  	switch ($MaDatarow["result"])
	{
		0 { $MaDatarow["Status"] = "OK";}#break}
		2 { $MaDatarow["Status"] = "FAILED";}#break}
		3 { $MaDatarow["Status"] = "WARNING";}#break}
	}

	switch ($MaDatarow["progress"])
	{
		100 {}#break}
		default {$MaDatarow["Name"] = $MaDatarow["Name"] + " [" + $MaDatarow["progress"] + "%]"}
	}

	switch ($MaDatarow["schedule_enabled"])
	{
		$null{} #break}
		$True {} #break}
		default { if($MaDatarow["next_run"] -ne ""){ $MaDatarow["next_run"] = '/!\ {0}' -f $MaDatarow["next_run"]}}
	}

  }
  Start-Sleep -Seconds 5
  $color = $false
  $htmlFinal = ""
  $htmlTMP = ""
  return $DataSet.Tables[0] | ConvertTo-HTML -Property Name,target_dir,next_run,Status -Fragment |
	ForEach-Object{

		$htmlTMP = $_

		$htmlTMP = $htmlTMP -replace '<td>OK</td>', '<td bgcolor=#33CC66>OK</td>'

		if($htmlTMP -match '<td>WARNING</td>')
		{
			$htmlTMP = $htmlTMP -replace '<tr>', '<tr bgcolor=#FF9900">'
		}
		elseif($_ -match '<td>FAILED</td>')
		{
			$htmlTMP = $htmlTMP -replace '<tr>', '<tr bgcolor="#FF0000">'
		}

		if(($htmlTMP -match '<tr>') -and ($color -eq $true))
		{
				$htmlTMP = $htmlTMP -replace '<tr>', '<tr bgcolor="#CCCCCC">'
		}

		if ($color -eq $true){$color = $false}
		else{$color = $true}
		$htmlTMP | Out-File ("C:\TMP\VEEAM_JOBS.html")-Append
		#$htmlFinal = $htmlFinal + $htmlTMP
	}
	#$htmlFinal | Out-File ("C:\TMP\VEEAM_JOBS.html")
}
# Initialize the SqlConnection variable
Set-Variable SqlConnection (New-Object System.Data.SqlClient.SqlConnection) -Scope Global -Option AllScope -Desc "Personal variable for Sql Query functions"
# Initially create the SqlConnection
Set-SqlConnection $Server $Database

if( $query -gt $null ) {
  Get-SqlDataTable $Query
}

#fermeture de la connexion
$SqlConnection.Close()

Bon scripting …