CA ArcServe backup

bbackx · Post by **bbackx** » Tue Jan 05, 2010 3:37 am

For those of you who want to monitor the backup on one of your servers, if it's done by ArcServe you can use the following (PowerShell) script to determine status, size, files and remaining capacity of the last backup:

Code: Select all

# ArcServe.ps1
# Created by Ben Backx
# Email: bbackx AT icorda.be
# Version: 0.5
# Created: 10/12/2009
# Last modification: 28/12/2009

# Function:
# ---------
# This script connects to the ArcServe logging database (available
# for version 12.0 and up) and processes the relevant logs.
 
# HostMonitor variables, so we return an answer that HostMonitor
# understands and can process:
[string]$statusAlive       = "scriptRes:Host is alive:"
[string]$statusDead        = "scriptRes:No answer:"
[string]$statusUnknown     = "scriptRes:Unknown:"
[string]$statusNotResolved = "scriptRes:Unknown host:"
[string]$statusOk          = "scriptRes:Ok:"
[string]$statusBad         = "scriptRes:Bad:"
[string]$statusBadContents = "scriptRes:Bad contents:"

################
# ErrorHandler #
################
# Error Handing Function
# Giving an understandable error back to HostMonitor
function ErrorHandler {
	return $statusBad + $Error[0].FullyQualifiedErrorId
}

##################
# GetLatestJobId #
##################
function GetLatestJobId($sqlCmd) {
	# Put the command in our sqlCmd
	$sqlCmd.CommandText = "SELECT top 1 jobid FROM dbo.aslogw WHERE msgtext LIKE '%Start Backup Operation%' ORDER BY jobid DESC"
	
	# Create an adapter to put the data we get from SQL and get the data
	$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	$sqlAdapter.SelectCommand = $sqlCmd
	$dataSet = New-Object System.Data.DataSet
	$sqlAdapter.Fill($dataSet)
	
	return $dataSet.Tables[0].Rows[0][0]
}

###############
# GetCapacity #
###############
function GetCapacity($sqlCmd) {
	$temp = GetLatestJobId($sqlCmd)
	$jobId = $temp[1]
	
	if ($jobId -match "scriptRes:Bad:") {
		#whooops, something went wrong
		return $jobId
	}
	else {
		# Put the command in our sqlCmd
		$sqlCmd.CommandText = "SELECT msgtext FROM dbo.aslogw WHERE msgtext LIKE '%Media Remaining Capacity%' AND jobid = " + $jobId
		
		# Create an adapter to put the data we get from SQL and get the data
		$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
		$sqlAdapter.SelectCommand = $sqlCmd
		$dataSet = New-Object System.Data.DataSet
		$sqlAdapter.Fill($dataSet)
		
		$statusOk += $dataSet.Tables[0].Rows[0][0]
		# Remove unnecessary stuff
		$statusOk = $statusOk.Replace("Media Remaining Capacity : ", "")
		$statusOk = $statusOk.Replace(" MB", "")
		
		return $statusOk
	}
	
}

#############
# GetStatus #
#############
function GetStatus($sqlCmd) {
	$temp = GetLatestJobId($sqlCmd)
	$jobId = $temp[1]
	# Regex needed to remove unnecessary stuff from the SQL-answer
	# This will 'select' everything after a dot (.), including the dot
	[regex]$regx = "\..*"
	
	if ($jobId -match "scriptRes:Bad:") {
		#whooops, something went wrong
		return $jobId
	}
	else {
		# Put the command in our sqlCmd
		$sqlCmd.CommandText = "SELECT top 1 msgtext FROM dbo.aslogw WHERE msgtext LIKE '%Backup Operation%' AND jobid = " + $jobid + " ORDER BY id DESC"
		
		# Create an adapter to put the data we get from SQL and get the data
		$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
		$sqlAdapter.SelectCommand = $sqlCmd
		$dataSet = New-Object System.Data.DataSet
		$sqlAdapter.Fill($dataSet)
		
		$temp = $dataSet.Tables[0].Rows[0][0]
		# Remove unnecessary stuff
		$temp = $temp.Replace("Backup Operation ", "")
		$temp = $regx.Replace($temp, "")
		
		if($temp -eq "failed") {
			$return = $statusBad + $temp
		} else {
			$return = $statusOk + $temp
		}
		
		return $return
	}
}

#################
# GetBackupSize #
#################
function GetBackupSize($sqlCmd) {
	$temp = GetLatestJobId($sqlCmd)
	$jobId = $temp[1]
	[regex]$regx = ".*\(Disk\)(\.)*\ "
	
	if ($jobId -match "scriptRes:Bad:") {
		#whooops, something went wrong
		return $jobId
	}
	else {
		# Put the command in our sqlCmd
		$sqlCmd.CommandText = "SELECT msgtext FROM dbo.aslogw WHERE msgtext LIKE '%Total Size (Disk)%' AND jobid = " + $jobId + " ORDER BY id DESC"
		
		# Create an adapter to put the data we get from SQL and get the data
		$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
		$sqlAdapter.SelectCommand = $sqlCmd
		$dataSet = New-Object System.Data.DataSet
		$sqlAdapter.Fill($dataSet)
		
		$temp = $dataSet.Tables[0].Rows[0][0]
		# Remove unnecessary stuff
		$temp = $regx.Replace($temp, "")
		$temp = $temp.Replace(" MB", "")
		
		return $statusOk + $temp
	}
}

##################
# GetBackupFiles #
##################
function GetBackupFiles($sqlCmd) {
	$temp = GetLatestJobId($sqlCmd)
	$jobId = $temp[1]
	[regex]$regx = ".*\(s\)(\.)*\ "
	
	if ($jobId -match "scriptRes:Bad:") {
		#whooops, something went wrong
		return $jobId
	}
	else {
		# Put the command in our sqlCmd
		$sqlCmd.CommandText = "SELECT msgtext FROM dbo.aslogw WHERE msgtext LIKE '%Total File(s)%' AND jobid = " + $jobId + " ORDER BY id DESC"
		
		# Create an adapter to put the data we get from SQL and get the data
		$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
		$sqlAdapter.SelectCommand = $sqlCmd
		$dataSet = New-Object System.Data.DataSet
		$sqlAdapter.Fill($dataSet)
		
		$temp = $dataSet.Tables[0].Rows[0][0]
		# Remove unnecessary stuff
		$temp = $regx.Replace($temp, "")
		
		return $statusOk + $temp
	}
}


######################
# 'Main' starts here #
######################

# We need 4 arguments: action and SQL-server
$count = $args.Count

if ($count -ne 2) {
	# Problems if we don't have 4 arguments
	$result = $statusUnknown + "Wrong number of parameters"
}
else {
	# If anything goes wrong, this should create a nice error output
	Trap {
		$result =  ErrorHandler
		continue;
	}

	# Make a connection with the SQL-server
	$sqlServer = $args[1]
	$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$sqlConnection.ConnectionString = "Server=$sqlServer;Integrated Security=True;Database=aslog"
	$sqlConnection.Open()
	
	# Create a command object to pass to the functions
	$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$sqlCmd.Connection = $sqlConnection

	# Switch for the different actions, and a default in case of an unknown action
	switch ($args[0])
	{
		"capacity" {
			$temp = GetCapacity($sqlCmd)
			$result = $temp[1]
		} "status" {
			$temp = GetStatus($sqlCmd)
			$result = $temp[1]
		} "size" {
			$temp = GetBackupSize($sqlCmd)
			$result = $temp[1]
		} "files" {
			$temp = GetBackupFiles($sqlCmd)
			$result = $temp[1]
		}
		default {$result = $statusUnknown + "Sorry, this action is not supported"}
	}
	
	# Close the SQL-connection
	$sqlConnection.Close()
}

Write-Host $result

Arguments are what you want (capacity, status, size or files) and the SQL database of ArcServe (usually: SERVERNAME\ARCSERVE_DB).
It uses the SQL-database containing the logs, so it only works for version 12 and upwards.

aca.schaubr · Post by **aca.schaubr** » Tue Feb 22, 2011 8:22 am

Very interesting, thank you