Citrix: Creating Reports.
A bit of a different gear here, but here are a couple examples, one using Citrix 4.5 (Resource Manager) andone using Citrix 6.0 (EdgeSight).
$start = Get-Date
Import-Module ActiveDirectory
function SQL-Connect($server, $port, $db, $userName, $passWord, $query) {
$conn = New-Object System.Data.SqlClient.SqlConnection
$ctimeout = 30
$qtimeout = 120
$constring = "Server={0},{5};Database={1};Integrated Security=False;User ID={2};Password={3};Connect Timeout={4}" -f $server,$db,$userName,$passWord,$ctimeout,$port
$conn.ConnectionString = $constring
$conn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand($query, $conn)
$cmd.CommandTimeout = $qtimeout
$ds = New-Object System.Data.DataSet
$da = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
$da.fill($ds)
$conn.Close()
return $ds
}
function Graph-Iterate($arList,$varRow,$varCol,$strPass) {
Write-Host $arList[$i].depName
foreach($i in $arList.Keys) {
if($arList[$i].duration -ne 0) {
if($arList[$i].depName.Length -gt 1) {
$varRow--
if($arList[$i].depName -eq $null){ $arList[$i].depName = "UNKNOWN" }
$sheet.Cells.Item($varRow,$varCol) = $arList[$i].depName
$varRow++
$sheet.Cells.Item($varRow,$varCol) = ("{0:N1}" -f $arList[$i].duration)
$varCol++
if($master -ne $true){ Iterate $arList[$i] $strPass }
}
}
}
return $varcol
}
function Iterate($arSub, $strCom) {
$indSheet = $workbook.Worksheets.Add()
$sheetName = ("{0}-{1}" -f $strCom,$arSub.depName)
Write-Host $sheetName
$nVar = 1
if($sheetName -eq "CSI-OPP MAX")
{
Write-Host "The Var is:"
Write-Host $nVar
$sheetName = "{0} {1}" -f $sheetName,$nVar
$nVar++
}
$strip = [System.Text.RegularExpressions.Regex]::Replace($sheetName,"[^1-9a-zA-Z_-]"," ");
if($strip.Length -gt 31) { $ln = 31 }else{ $ln = $strip.Length }
$indSheet.Name = $strip.Substring(0, $ln)
$count = $arSub.Keys.Count
$array = New-Object 'object[,]' $count,2
$arRow = 0
foreach($y in $arSub.Keys) {
if($y -ne "depName" -and $y -ne "duration" -and $y.Length -gt 1) {
$t = 0
$array[$arRow,$t] = $y
$t++
$array[$arRow,$t] = $arSub[$y]
$arRow++
}
}
$rng = $indSheet.Range("A1",("B"+$count))
$rng.Value2 = $array
}
function Create-Graph($lSheet,$lTop,$lLeft,$range, $number, $master, $catRange) {
# Add graph to Dashboard and configure.
$chart = $lSheet.Shapes.AddChart().Chart
$chartNum = ("Chart {0}" -f $cvar3)
$sheet.Shapes.Item($chartNum).Placement = 3
$sheet.Shapes.Item($chartNum).Top = $top
$sheet.Shapes.Item($chartNum).Left = $left
if($master -eq $true) {
$sheet.Shapes.Item($chartNum).Height = 500
$sheet.Shapes.Item($chartNum).Width = 1220
}else{
$sheet.Shapes.Item($chartNum).Height = 325
$sheet.Shapes.Item($chartNum).Width = 400
}
$chart.ChartType = 69
$chart.SetSourceData($range)
$chart.SeriesCollection(1).XValues = $catRange
}
$port = "<port>"
$server = "<sqlserver>"
$db = "<db>"
$user = "<db_user>"
$password = "<pass>"
$query = "SELECT p.prid, p.account_name, p.domain_name, p.dtfirst, cs.instid, cs.sessid, cs.login_elapsed, cs.dtlast, cs.session_type, s.logon_time, s.logoff_time
FROM dbo.principal AS p INNER JOIN
dbo.session AS s ON s.prid = p.prid INNER JOIN
dbo.ctrx_session AS cs ON cs.sessid = s.sessid"
#WHERE p.account_name LIKE 'a[_]%'
$userlist = SQL-Connect $server $port $db $user $password $query
$users = @{}
foreach($i in $userlist.Tables) {
if($i.account_name -notlike "h_*" -and $i.account_name -notlike "a_*" -and $i.account_name -ne "UNKNOWN" -and ([string]$i.logon_time).Length -gt 1 -and ([string]$i.logoff_time).Length -gt 1) {
try {
$info = Get-ADUser -Identity $i.account_name -Properties DepartmentNumber, Department, Company
}
catch {
$info = @{"Company"="Terminated";"Department"="Invalid";"DepartmentNumber"="0000"}
}
if($info.Company.Length -lt 2) {
$info = @{"Company"="Terminated";"Department"="Invalid";"DepartmentNumber"="0000"}
}
if($users.Contains($info.Company) -eq $false) {
$users[$info.Company] = @{}
$users[$info.Company]['duration'] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}else{
$users[$info.Company]['duration'] = $users[$info.Company]['duration']+(New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}
if($users[$info.Company].Contains(([string]$info.DepartmentNumber)) -eq $false) {
$users[$info.Company][([string]$info.DepartmentNumber)] = @{}
$users[$info.Company][([string]$info.DepartmentNumber)]['depName'] = $info.Department
$users[$info.Company][([string]$info.DepartmentNumber)]['duration'] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}else{
$users[$info.Company][([string]$info.DepartmentNumber)]['duration'] = $users[$info.Company][([string]$info.DepartmentNumber)]['duration']+(New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}
if($users[$info.Company][([string]$info.DepartmentNumber)].Contains($i.account_name) -eq $false) {
$users[$info.Company][([string]$info.DepartmentNumber)][$i.account_name] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}else{
$users[$info.Company][([string]$info.DepartmentNumber)][$i.account_name] = $users[$info.Company][([string]$info.DepartmentNumber)][$i.account_name]+(New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}
}elseif($i.account_name -ne "UNKNOWN" -and ([string]$i.logon_time).Length -gt 1 -and ([string]$i.logoff_time).Length -gt 1) {
if($i.account_name -like "a_*") {
$info = @{"Company"="Administrators";"Department"="Elevated IDs (A)";"DepartmentNumber"="1111"}
}else{
$info = @{"Company"="Administrators";"Department"="Elevated IDs (H)";"DepartmentNumber"="2222"}
}
if($users.Contains("Administrators") -eq $false) {
$users['Administrators'] = @{}
$users['Administrators']['duration'] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}else{
$users['Administrators']['duration'] = $users['Administrators']['duration']+(New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}
if($users['Administrators'].Contains($info.DepartmentNumber) -eq $false) {
$users['Administrators'][$info.DepartmentNumber] = @{}
$users['Administrators'][$info.DepartmentNumber]['depName'] = $info.Department
$users['Administrators'][$info.DepartmentNumber]['duration'] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}else{
$users['Administrators'][$info.DepartmentNumber]['duration'] = $users['Administrators'][$info.DepartmentNumber]['duration']+(New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}
if($users['Administrators'][$info.DepartmentNumber].Contains($i.account_name) -eq $false) {
$users['Administrators'][$info.DepartmentNumber][$i.account_name] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}else{
$users['Administrators'][$info.DepartmentNumber][$i.account_name] = $users['Administrators'][$info.DepartmentNumber][$i.account_name]+(New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
}
}else{
if(([string]$i.logon_time).Length -lt 1 -and $i.account_name -ne "UNKNOWN"){ "No logon time: "+$i.account_name }
if(([string]$i.logoff_time).Length -lt 1 -and $i.account_name -ne "UNKNOWN"){ "No logoff time: "+$i.account_name }
}
}
# Create Excel object, setup spreadsheet, name main page.
$excel = New-Object -ComObject excel.application
$excel.Visible = $true
$excel.DisplayAlerts = $false
$workbook = $excel.Workbooks.Add()
$row = 1
$col = 1
$sheet = $workbook.Worksheets.Item(1)
$sheet.Name = "Dashboard"
# Populate tracking vars.
# $row is the starting row to begin entering data into text cells.
# $cvar tracks $left position, resets when it reaches 3.
# $cvar3 tracks $top position, after every third graph it increments +340.
$row = 202
$col = 2
$cvar = 1
$cvar3 = 1
$top = 10
$left = 10
# Iterate through main element (Companies), $z returns company name (MGTS, MR, etc.).
$min = ($sheet.Cells.Item(($row)-1,1).Address()).Replace("$", "")
$tmin = ($sheet.Cells.Item(($row)-1,2).Address()).Replace("$", "")
foreach($q in $users.Keys) {
$sheet.Cells.Item($row,1) = "Maritz Total Citrix Usage (by hours)"
$row--
if($q -eq "114"){ $q = "Training IDs" }
$sheet.Cells.Item($row,$col) = $q
$row++
$sheet.Cells.Item($row,$col) = ("{0:N1}" -f $users[$q].duration)
$col++
}
$max = ($sheet.Cells.Item($row,($col)-1).Address()).Replace("$", "")
$range = $sheet.Range($min,$max)
$range2 = $sheet.Range($tmin,$max)
Create-Graph $sheet $top $left $range $cvar3 $true $range2
$row++;$row++
$col = 2
$top = ($top)+510
$cvar3++
foreach($z in $users.Keys) {
if($z.Length -gt 1 -and $z -ne "112 MAS"){
# Setup chart location vars.
if($cvar -eq 1) {
$left = 10
}elseif($cvar -eq 2){
$left = 420
}elseif($cvar -eq 3) {
$left = 830
}
$col = 2
$sheet.Cells.Item($row,1) = $z
# Track chart range minimum cell address.
$min = ($sheet.Cells.Item(($row)-1,1).Address()).Replace("$", "")
$tmin = ($sheet.Cells.Item(($row)-1,2).Address()).Replace("$", "")
# Iterate through secondary element (Departments), $i returns department name.
# Graph-Iterate Here
$vLoc = Graph-Iterate $users[$z] $row $col $z
# Track chart range maximum cell address.
$max = ($sheet.Cells.Item($row,($vLoc)-1).Address()).Replace("$", "")
$range = $sheet.Range($min,$max)
$range2 = $sheet.Range($tmin,$max)
Create-Graph $sheet $top $left $range $cvar3 $false $range2
$row++;$row++
# Increment or reset tracking vars.
if($cvar -eq 3) {
$top = ($top)+340
}
if($cvar -eq 1 -or $cvar -eq 2){ $cvar++ }elseif($cvar -eq 3){ $cvar = 1}
$cvar3++
}
}
# Show dashboard page rather than some random department.
$sheet.Activate()
New-TimeSpan -Start $start -End (Get-Date)App-V: Excel 2010, DDE, and variations on installation type.
Here is a fun one.
If you are like me you've had some VERY sketchy results with Office and DDE. There are several bits of information floating around about this and here is my updated current understanding of the issue.
During installation of App-V 4.6 (in this case SP1, not sure if the same applies to RTM) the following keys are written with different values based on, apparently, the type of method used to deploy the client.
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\SoftGrid\4.5\Client\UserInterface\DDELaunchCommand
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\SoftGrid\4.5\Client\UserInterface\DDELaunchMSICommand
When delivered via SCCM as part of a task sequence during an SD deployment DDELaunchCommand points to sfftray.exe and not sftdde.exe, and there is a different string for the MSI launch command as well.
Strangely, taking office 2010 as an example, word used to require ADDING DDE or it would present a "problem sending command to program" error, then at some point it stopped being broken.
Then, Excel stopped working with DDE, the most common search results provided a fruitless suggestion to disable the "Ignore other applications DDE" that doesn't appear to be checked by default in 2010 anymore.
The problem with this is that stripping DDE from the FTA's for excel fixes double clicking on a file locally or on a network share, but when launching a spreadsheet via SharePoint you get an error saying '%*.xslx" could not be found.
Uninstalling and reinstalling the client WILL fix this in almost every case (just make sure to reboot after the uninstall, even if it doesn't prompt you) but the idea is to fix the client for in-production machines.
The default values for SP1 for those two keys are as follows:
Windows Registry Editor Version 5.00[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\SoftGrid\4.5\Client\UserInterface]"DDELaunchMSICommand"="-0Ej4Sf'k=UZDv3uJ4{IRelease_Merge_Modules>OO5hSGuIH?xoVn&wQ(Ex \"<APP>\" <DDE>""DDELaunchCommand"="\"C:\\Program Files (x86)\\Microsoft Application Virtualization Client\\sftdde.exe\" \"<APP>\" <DDE>"
This is from a Win7x64 machine with App-V 4.6 SP1 (no HF's applied).
Update on 2011-12-07 00:30 by Paul Fulbright
Ok, so I sadly do not have nearly enough time to really get to the bottom of this so the following is pure speculation.
I THINK the difference in this case between Excel and, say, Word is that Excel seems to be tripping a MSI self-repair check, it's blink-and-you'll-miss-it fast but I THINK the MSIDDELaunchComand not being fixed was why just changing DDELaunchCommand didn't solve the problem.
Something doesn't set right with me about that but the net result is, setting both keys correctly, works.
REALLY love to hear the excuse for why this happens.