Target device retries can be seen from the Provisioning Services Console by expanding the farm, sites, your site, and then servers.  Right click the Provisioning Server and click “show connected devices.”  

The retry data from the Provisioning Services Console is nice, but is almost useless because the target device retries will increment while the target device is running and will reset when the target device is restarted.  For example, a target device has been running for 2 weeks and has 3,000 retires listed in the console.  I don’t know if 3,000 retries happened in the last 2 weeks or the last 2 minutes.

I want to baseline, track, and troubleshoot with this data.  I can accomplish this with a SQL table, Powershell Provisioning Server commandlets, Citrix Provisioning Server, a scheduled task, and SQL reporting.

I’ll create a database called syslog and a table called “PVSDeviceRetries” with these columns…

*note – default value for the created column is getdate()

then a SQL user account called syslog with write permissions to the database.

Next, I’ll need a Powershell script and the Powershell Provisioning Server commandlets.  The script will write the information to the SQL database.

#Author: Phil Lindsey
#Website: http://www.ctxfarmer.com
#Last Modified: 11/4/2010

Clear-Host

$SQLServer = "192.168.35.10"
$SQLDatabase = "Syslog"
$SQLTable = "PvsDeviceRetries"
$SQLUserId = "syslog"
$SQLPassword = "p@ssw0rd"

function get-value{
  param([string]$strText="",[string]$strDelimiter="")
  return $strText.SubString($strText.IndexOf($strDelimiter)+2)
}
function get-name{
  param([string]$strText="",[string]$strDelimiter="")
  return $strText.SubString(0,$strText.IndexOf($strDelimiter))
}
Add-PSSnapin McliPS* -ErrorAction SilentlyContinue
$all = @()
$obj = New-Object System.Collections.ArrayList
$lines = Mcli-Get DeviceInfo -f ServerName,`
								ServerIpConnection,`
								DeviceName,`
								SiteName,`
								CollectionName,`
								Active,`
								Status,`
								diskLocatorName
for($i=0;$i -lt $lines.length;$i++){
  if(($lines[$i].length -gt 0) -and ($lines[$i].contains(":")) -and -not ($lines[$i] -match "Executing: Get ")){
    $name = get-name -strText $lines[$i] -strDelimiter ":"
    $value = get-value -strText $lines[$i] -strDelimiter ":"
	if ($name -eq "status" -and $value.Length -le 0){
	  $value = "0"
	}
	if ($value.Contains(",") -and $name -eq "status"){
	  $obj | Add-Member -membertype noteproperty -name $name -Value $value.Split(",")[0]
	}else{
	  $obj | Add-Member -membertype noteproperty -name $name -Value $value
	}
  }
  if($lines[$i].contains("#") -or (($i+1) -eq $lines.length)){
    $all += $obj
    $obj = New-Object psObject
  }
}
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source="+$SQLServer+";`
Initial Catalog="+$SQLDatabase+"; User Id="+$SQLUserId+";Password="+$SQLPassword+";")

foreach($targetdevice in $all){
  $targetdevice | fl *
  $conn.Open()
  $cmd = $conn.CreateCommand()
  $cmd.CommandText = "INSERT INTO "+$SQLTable+"(`
                      ServerName,`
					  ServerIpConnection,`
                      DeviceName,`
	                  SiteName,`
					  CollectionName,`
					  Active,`
					  Status,`
					  diskLocatorName)`
                      VALUES(`
					  '"+$targetdevice.serverName+"',`
					  '"+$targetdevice.ServerIpConnection+"',`
					  '"+$targetdevice.devicename+"',`
					  '"+$targetdevice.sitename+"',`
					  '"+$targetdevice.collectionname+"',`
					  "+[int]$targetdevice.active+",`
					  "+[int]$targetdevice.status+",`
					  '"+$targetdevice.diskLocatorName+"')"
  $result = $cmd.ExecuteNonQuery()
  $conn.Close()
}

In the next part, I'll setup a scheduled task on the Provisioning Server and create the SQL reports to display the data.