ctxfarmer

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.

I use this trigger to track the last update or insert for a record.

Here’s how I create the trigger.

• Add a “Last_Modified” column with data type “datetime” to the table.
• Create a new SQL query in the SQL Server Management Studio.
• Paste the following code into the SQL query.

CREATE TRIGGER dbo.MyTable
ON MyTable
FOR UPDATE,
INSERT AS UPDATE dbo.MyTable
SET Last_Modified = CURRENT_TIMESTAMP
WHERE PKeyColumnName IN (SELECT PKeyColumnName FROM inserted);

• Replace "dbo.MyTable" with the name of the table.
• Replace "MyTable" with the name of the table.
• Replace "PKeyColumnName" with the name of the primary key column for the table.
• Execute the query.