none
access protection group state/alerts via powershell? RRS feed

  • Question

  • Hi,
    i need a possibility to access the state (OK or Error) of an Protection Group via powershell in DPM 2010.

    First i tried this:
    $pg = Get-ProtectionGroup -DPMServerName SERVERNAME | where { $_.FriendlyName -eq "PROTECTIONSGROUP_NAME"}
    $pg.CheckForWarnings()
    But the result is always the same. Not depending on the state.

    My next try was breaking it down to the itmes (datasource, etc.) in an Protection Group:

    $pg = Get-ProtectionGroup -DPMServerName SERVERNAME | where { $_.FriendlyName -eq "PROTECTIONSGROUP_NAME"}
    $dsList = get-datasource $pg
    But i cant find an Attribute which depends on the State.
    e.g. $dsList[0].Alert ist always empty..


    Any idea how to get via DPM powershell an authentic state (text or Errorcode) of an ProtectionGroup?

    Thank you in advance

    • Moved by MarcReynolds Friday, May 20, 2011 3:12 PM (From:Data Protection Manager)
    Thursday, December 2, 2010 11:28 AM

Answers

  • after further investigation it seem there is no way to access these information via powershell directly.
    What i need to do is to query the DPM Servers Database.

    this website helped me alot: http://www.orcsweb.com/blog/jeff/dpm-daily-maintenance-script/

    and here is my script. Just for counting errors:

    param ([string] $DPMServerName)
    
    $alertCol = @()
    $DPMServer = $DPMServerName
    
    function New-SQLconnection {
    	Param ([string]$server, [string]$database = "master", [string]$connectionName = $ScriptName)
    	if (test-path variable:\conn) {
    		$conn.close()
    	} else {
    		$conn = new-object ('System.Data.SqlClient.SqlConnection')
    	}
    	$connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
    	$conn.ConnectionString = $connString
    	$conn.StatisticsEnabled = $true
    	$conn.Open()
    	$conn
    }
    
    function Remove-SQLconnection {
    	Param ($connection)
    	$connection.close()
    	$connection = $null
    }
    
    function Close-SQLquery {
    	Param ($query)
    	$query.close()
    	$query = $null
    }
    
    function Query-SQL {
    	Param ($query, $conn, [int]$CommandTimeout = 30)
    	$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    	$sqlCmd.CommandTimeout = $CommandTimeout
    	$sqlCmd.CommandText = $query
    	$sqlCmd.Connection = $conn
    	$Rset = $sqlCmd.ExecuteReader()
    	if ($? -eq $false) {
    		Close-SQLquery $rset
    		[int]$rset = -1
    	}
    	if ($Rset.hasrows -eq $false) {
    		Close-SQLquery $rset
    		[string]$rset = ""
    	}
    	,$Rset ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset
    }
    
    function NonQuery-SQL {
    	Param ($query, $conn, $CommandTimeout = 30)
    	$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    	$sqlCmd.CommandTimeout = $CommandTimeout
    	$sqlCmd.CommandText = $query
    	$sqlCmd.Connection = $conn
    	$RowsAffected = $sqlCmd.ExecuteNonQuery()
    	if ($? -eq $false) {
    		$RowsAffected = -2
    	}
    	$RowsAffected
    }
    
    
    
    #Generate SQL Connection
    $Server = "$DPMServer\MSDPM2010"
    $myConn = New-SQLconnection $Server "DPMDB"
    
    if ($myConn.state -eq "closed") {
    	write-host "Failed to establish a connection to $Server"
    }
    else {
    	$query = "select al.alertid, al.type, al.severity, al.errorXML, al.resolution from tbl_AHP_Alerts al where al.ResolvedTime is null and al.type in (25,27,31,36)"
    	# TEST $query = "select al.alertid, al.type, al.severity, al.errorXML, al.resolution from tbl_AHP_Alerts al where al.type in (25,27,31,36)"
    	$data = Query-SQL $query $myConn
    	if ($data.gettype() -eq [int]) {
    		write-host "Failed to query SQL server $Server"
    	}
    	elseif ($data.gettype() -ne [string]) {
    		while($data.Read()) {
    			#Parse SQL output
    			$dpmAlert = New-Object System.Object
    			$dpmAlert | Add-Member -type NoteProperty -name AlertID -value ([string] $data.GetValue(0))
    			$dpmAlert | Add-Member -type NoteProperty -name AlertType -value ([int] $data.GetValue(1))
    			$dpmAlert | Add-Member -type NoteProperty -name Severity -value ([int] $data.GetValue(2))
    			$errorXML = [string] $data.GetValue(3)
    
    			[System.Xml.XmlDocument] $xmlDoc = new-object System.Xml.XmlDocument
    			$XMLDoc.LoadXML($errorXML)
    			
    			#$dpmAlert | Add-Member -type NoteProperty -name ErrorXML -value $errorXML
    			$dpmAlert | Add-Member -type NoteProperty -name DataSourceID -value ($XMLDoc.ErrorInfo.Parameter | where {$_.name -match "datasourceid"}).Value
    			$dpmAlert | Add-Member -type NoteProperty -name ServerName -value ($XMLDoc.ErrorInfo.Parameter | where {$_.name -match "servername"}).Value
    			$dpmAlert | Add-Member -type NoteProperty -name DataSource -value ($XMLDoc.ErrorInfo.Parameter | where {$_.name -match "datasourcename"}).Value
    			$dpmAlert | Add-Member -type NoteProperty -name ProtectionGroup -value ($XMLDoc.ErrorInfo.Parameter | where {$_.name -match "protectedgroup"}).Value
    			$dpmAlert | Add-Member -type NoteProperty -name Resolution -value ([int] $data.GetValue(4))
    
    			$alertCol += $dpmAlert
    		}
    
    		Close-SQLquery $data
    	}
    }
    
    $alertCol = $alertCol | sort @{expression="Servername";Ascending=$true},@{expression="DataSource";Ascending=$true},@{expression="Severity";Descending=$true},@{expression="AlertType";Descending=$true}
    
    $ErrorCounter=0
    
    
    foreach($alert in $alertCol) {
    	#$AlertID = $alert.AlertID
    	#$ProtectionGroupName = $alert.ProtectionGroup
    	#$DatasourceName = $alert.DataSource
    	#$DataSourceID = $alert.DataSourceID
    	#$ServerName = $alert.ServerName
    
    	$AlertText = ""
    	switch ($alert.AlertType) {
    		25 { $AlertText ="recovery point creation failed"
    		}
    		27 { $AlertText ="replica is inconsistent"
    		}
    		31 { $AlertText ="replica disk threshold exceeded"
    		}
    		36 { $AlertText ="recovery point volume threshold exceeded"
    		}
    	}
    	if($alert -ne $null){
    
    	$ErrorCounter++
    	}		
    }
    
    if($ErrorCounter -eq 0)
    {
    	write-host "OK"
    	
    }
    else
    {
    	write-host $ErrorCounter" Error(s)"
    }
    
    Remove-SQLconnection $myConn
    
    
    • Marked as answer by MC-FSP Friday, December 3, 2010 11:34 AM
    Friday, December 3, 2010 11:34 AM

All replies

  • Check the "State" property of a datasource object.  We use that along with "Activity" (and several other properties) to generate web based reports that closely match what you see in the DPM UI.

    Here is what I've come up with (so far) to help map state/activity to what the DPM UI shows:

     

     Activity  State   Meaning
     -------------- -------------- -----------------------------------
     Idle   Valid   OK
     Idle   Allocated  Manual replica creation pending
     Idle   Invalid  Replica is inconsistent
     Idle   Unallocated Tape Out-only?
     Idle   Missing  Volume missing
     Initializing     Replica creation in progress...
     CreatingSC     Creating recovery point...
     Replicating     Synchronizing...
     Validating     Checking consistency...
     Recovering     Restoring data...
     RecoveringFromTape   Restoring from tape...
    

     

    Edit: sorry, this didn't line up as well as I hoped.

    Thursday, December 2, 2010 1:01 PM
  • Hi Rod,

    thanks for your fast reply. but unfortunately for me it is not working.

    E.g. i have an Protection Group with one Item (DataBase from SQL Agent) in it. This Item is in the State "Recovery point creation failed" at DPM console. (SQL Server is offline)
    $DatasourceItem.Activity displays "Idle" and
    $DatasourceItem.State displays "Valid".

    So i can't use it for evaluation.

    There should be an Attribute containing the real "state" information. Any further ideas?

    reagards

     

    Thursday, December 2, 2010 1:29 PM
  • You're right; I should have mentioned that the method I posted only works to display the TRUE state of a datasource, it does not display failed job information.  "Recovery point creation failed" is a failed job result, the replica is still in a valid/consistent state (which is why the method above says its "OK").
    Thursday, December 2, 2010 1:46 PM
  • ok, now i see! I was searching at the wrong object.. i have to evaluate the result from every DPM Job.

    But i can't find the powershell command to get a DPM Job as object.
    Apparently "Get-Job" only displays "background jobs" from powershell not reagarding DPM Jobs.

    do you have an idea how to access results of DPM Jobs?

    Thursday, December 2, 2010 2:47 PM
  • Sorry, I have never looked into that myself.  For our purposes getting the true status of the replicas was all we needed.  Good luck!
    Thursday, December 2, 2010 3:06 PM
  • after further investigation it seem there is no way to access these information via powershell directly.
    What i need to do is to query the DPM Servers Database.

    this website helped me alot: http://www.orcsweb.com/blog/jeff/dpm-daily-maintenance-script/

    and here is my script. Just for counting errors:

    param ([string] $DPMServerName)
    
    $alertCol = @()
    $DPMServer = $DPMServerName
    
    function New-SQLconnection {
    	Param ([string]$server, [string]$database = "master", [string]$connectionName = $ScriptName)
    	if (test-path variable:\conn) {
    		$conn.close()
    	} else {
    		$conn = new-object ('System.Data.SqlClient.SqlConnection')
    	}
    	$connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
    	$conn.ConnectionString = $connString
    	$conn.StatisticsEnabled = $true
    	$conn.Open()
    	$conn
    }
    
    function Remove-SQLconnection {
    	Param ($connection)
    	$connection.close()
    	$connection = $null
    }
    
    function Close-SQLquery {
    	Param ($query)
    	$query.close()
    	$query = $null
    }
    
    function Query-SQL {
    	Param ($query, $conn, [int]$CommandTimeout = 30)
    	$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    	$sqlCmd.CommandTimeout = $CommandTimeout
    	$sqlCmd.CommandText = $query
    	$sqlCmd.Connection = $conn
    	$Rset = $sqlCmd.ExecuteReader()
    	if ($? -eq $false) {
    		Close-SQLquery $rset
    		[int]$rset = -1
    	}
    	if ($Rset.hasrows -eq $false) {
    		Close-SQLquery $rset
    		[string]$rset = ""
    	}
    	,$Rset ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset
    }
    
    function NonQuery-SQL {
    	Param ($query, $conn, $CommandTimeout = 30)
    	$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    	$sqlCmd.CommandTimeout = $CommandTimeout
    	$sqlCmd.CommandText = $query
    	$sqlCmd.Connection = $conn
    	$RowsAffected = $sqlCmd.ExecuteNonQuery()
    	if ($? -eq $false) {
    		$RowsAffected = -2
    	}
    	$RowsAffected
    }
    
    
    
    #Generate SQL Connection
    $Server = "$DPMServer\MSDPM2010"
    $myConn = New-SQLconnection $Server "DPMDB"
    
    if ($myConn.state -eq "closed") {
    	write-host "Failed to establish a connection to $Server"
    }
    else {
    	$query = "select al.alertid, al.type, al.severity, al.errorXML, al.resolution from tbl_AHP_Alerts al where al.ResolvedTime is null and al.type in (25,27,31,36)"
    	# TEST $query = "select al.alertid, al.type, al.severity, al.errorXML, al.resolution from tbl_AHP_Alerts al where al.type in (25,27,31,36)"
    	$data = Query-SQL $query $myConn
    	if ($data.gettype() -eq [int]) {
    		write-host "Failed to query SQL server $Server"
    	}
    	elseif ($data.gettype() -ne [string]) {
    		while($data.Read()) {
    			#Parse SQL output
    			$dpmAlert = New-Object System.Object
    			$dpmAlert | Add-Member -type NoteProperty -name AlertID -value ([string] $data.GetValue(0))
    			$dpmAlert | Add-Member -type NoteProperty -name AlertType -value ([int] $data.GetValue(1))
    			$dpmAlert | Add-Member -type NoteProperty -name Severity -value ([int] $data.GetValue(2))
    			$errorXML = [string] $data.GetValue(3)
    
    			[System.Xml.XmlDocument] $xmlDoc = new-object System.Xml.XmlDocument
    			$XMLDoc.LoadXML($errorXML)
    			
    			#$dpmAlert | Add-Member -type NoteProperty -name ErrorXML -value $errorXML
    			$dpmAlert | Add-Member -type NoteProperty -name DataSourceID -value ($XMLDoc.ErrorInfo.Parameter | where {$_.name -match "datasourceid"}).Value
    			$dpmAlert | Add-Member -type NoteProperty -name ServerName -value ($XMLDoc.ErrorInfo.Parameter | where {$_.name -match "servername"}).Value
    			$dpmAlert | Add-Member -type NoteProperty -name DataSource -value ($XMLDoc.ErrorInfo.Parameter | where {$_.name -match "datasourcename"}).Value
    			$dpmAlert | Add-Member -type NoteProperty -name ProtectionGroup -value ($XMLDoc.ErrorInfo.Parameter | where {$_.name -match "protectedgroup"}).Value
    			$dpmAlert | Add-Member -type NoteProperty -name Resolution -value ([int] $data.GetValue(4))
    
    			$alertCol += $dpmAlert
    		}
    
    		Close-SQLquery $data
    	}
    }
    
    $alertCol = $alertCol | sort @{expression="Servername";Ascending=$true},@{expression="DataSource";Ascending=$true},@{expression="Severity";Descending=$true},@{expression="AlertType";Descending=$true}
    
    $ErrorCounter=0
    
    
    foreach($alert in $alertCol) {
    	#$AlertID = $alert.AlertID
    	#$ProtectionGroupName = $alert.ProtectionGroup
    	#$DatasourceName = $alert.DataSource
    	#$DataSourceID = $alert.DataSourceID
    	#$ServerName = $alert.ServerName
    
    	$AlertText = ""
    	switch ($alert.AlertType) {
    		25 { $AlertText ="recovery point creation failed"
    		}
    		27 { $AlertText ="replica is inconsistent"
    		}
    		31 { $AlertText ="replica disk threshold exceeded"
    		}
    		36 { $AlertText ="recovery point volume threshold exceeded"
    		}
    	}
    	if($alert -ne $null){
    
    	$ErrorCounter++
    	}		
    }
    
    if($ErrorCounter -eq 0)
    {
    	write-host "OK"
    	
    }
    else
    {
    	write-host $ErrorCounter" Error(s)"
    }
    
    Remove-SQLconnection $myConn
    
    
    • Marked as answer by MC-FSP Friday, December 3, 2010 11:34 AM
    Friday, December 3, 2010 11:34 AM
  • I use this PowerShell script:

    get-datasource | where {$_.computer -ne (&hostname) -and $_.state -ne "Unallocated" -and $_.state -ne "ReplicationStopped" -and ($_.state -ne "Valid" -or $_.currentlyprotected -eq $false -or $_.currentprotectionstate -ne "Protected")} | select protectiongroupname, computer, name, objecttype, state, activity, LatestRecoverypoint, CurrentProtectionState, CurrentlyProtected, diskallocation, alert  | sort state, protectiongroupname, computer, objecttype, name


    Thursday, November 6, 2014 2:06 PM