Paul Fulbright 5/10/13 Paul Fulbright 5/10/13

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).

4.5
http://pastebin.com/r9752d43

6.0
http://pastebin.com/TFqRs6ew

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

$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)

Paul Fulbright 12/4/11 Paul Fulbright 12/4/11

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.