SHOW:
|
|
- or go back to the newest paste.
1 | #======================================================================== | |
2 | # Created with: SAPIEN Technologies, Inc., PowerShell Studio 2012 v3.1.13 | |
3 | # Created on: 4/22/2013 1:10 PM | |
4 | - | # Created by: Paul Fulbright |
4 | + | # Created by: |
5 | # Organization: Maritz, LLC | |
6 | - | # Filename: CTX-CitrixReport.ps1 |
6 | + | # Filename: |
7 | #======================================================================== | |
8 | ||
9 | Import-Module ActiveDirectory | |
10 | ||
11 | function SQL-Connect($server, $port, $db, $userName, $passWord, $query) { | |
12 | $conn = New-Object System.Data.SqlClient.SqlConnection | |
13 | $ctimeout = 30 | |
14 | $qtimeout = 120 | |
15 | $constring = "Server={0},{5};Database={1};Integrated Security=False;User ID={2};Password={3};Connect Timeout={4}" -f $server,$db,$userName,$passWord,$ctimeout,$port | |
16 | $conn.ConnectionString = $constring | |
17 | $conn.Open() | |
18 | $cmd = New-Object System.Data.SqlClient.SqlCommand($query, $conn) | |
19 | $cmd.CommandTimeout = $qtimeout | |
20 | $ds = New-Object System.Data.DataSet | |
21 | $da = New-Object System.Data.SqlClient.SqlDataAdapter($cmd) | |
22 | $da.fill($ds) | |
23 | $conn.Close() | |
24 | return $ds | |
25 | } | |
26 | ||
27 | function Graph-Iterate($arList,$varRow,$varCol) { | |
28 | foreach($i in $arList.Keys) { | |
29 | if($arList[$i].duration -ne 0) { | |
30 | if($arList[$i].depName.Length -gt 1) { | |
31 | - | $password = "3N11GQcr" |
31 | + | $varRow-- |
32 | - | $query = "SELECT * FROM CitrixRMIM.LU_USER" |
32 | + | $sheet.Cells.Item($varRow,$varCol) = $arList[$i].depName |
33 | $arList[$i].depName | |
34 | $arList[$i].duration | |
35 | $varRow++ | |
36 | $sheet.Cells.Item($varRow,$varCol) = $arList[$i].duration | |
37 | $varCol++ | |
38 | ||
39 | # Iterate Here | |
40 | Iterate($arList[$i]) | |
41 | } | |
42 | } | |
43 | } | |
44 | # Because PowerShell is dumb as shit and thinks "return" means "exit, and puke out all the output". | |
45 | # It's ok, I remember my first time using a programming language too... | |
46 | Write-Host $varCol | |
47 | return $varcol | |
48 | } | |
49 | ||
50 | function Iterate($arSub) { | |
51 | # Create sheet, add cells. Destroy sheet object. | |
52 | $indSheet = $workbook.Worksheets.Add() | |
53 | $indSheet.Name = $arSub.depName | |
54 | $indRow = 1 | |
55 | $indCol = 1 | |
56 | # Iterate through tertiary node (Users), $y returns username. | |
57 | - | #if($userInfo.Department -eq "ARG-DATA Capture"){ $userInfo.SamAccountName }ny] = @{};$arDept[$userInfo.Company]["comName"] = $userInfo.Company} |
57 | + | foreach($y in $arSub.Keys) { |
58 | if($y -ne "depNames" -and $y -ne "duration" -and $y.Length -gt 1) { | |
59 | $indSheet.Cells.Item($indRow,$indCol) = $y | |
60 | $indCol++ | |
61 | $indSheet.Cells.Item($indRow,$indCol) = $arSub[$y] | |
62 | $indCol-- | |
63 | $indRow++ | |
64 | }else{ | |
65 | } | |
66 | } | |
67 | $indSheet = $null | |
68 | } | |
69 | ||
70 | function Create-Graph($lSheet,$lTop,$lLeft,$range, $number) { | |
71 | # Add graph to Dashboard and configure. | |
72 | $chart = $lSheet.Shapes.AddChart().Chart | |
73 | $chartNum = ("Chart {0}" -f $cvar3) | |
74 | $sheet.Shapes.Item($chartNum).Placement = 3 | |
75 | $sheet.Shapes.Item($chartNum).Top = $top | |
76 | $sheet.Shapes.Item($chartNum).Left = $left | |
77 | $sheet.Shapes.Item($chartNum).Height = 325 | |
78 | $sheet.Shapes.Item($chartNum).Width = 400 | |
79 | $chart.ChartType = 69 | |
80 | $chart.SetSourceData($range) | |
81 | } | |
82 | ||
83 | ||
84 | $port = "40013" | |
85 | - | "`r`nCVAR: "+$cvar |
85 | + | |
86 | $db = "CitrixRMSummary" | |
87 | $user = "CitrixRMReadOnly" | |
88 | $password = "" | |
89 | $query = "SELECT TOP 2 * FROM CitrixRMIM.LU_USER" | |
90 | $userlist = SQL-Connect $server $port $db $user $password $query | |
91 | ||
92 | # Create Array (empty) for department data | |
93 | $arDept = @{} | |
94 | foreach($i in $userlist.Tables[0].Rows) { | |
95 | $pass = $false | |
96 | # Pull sessions for users. | |
97 | - | foreach($i in $arDept[$z].Keys) { |
97 | + | |
98 | - | if($arDept[$z][$i].duration -ne 0) { |
98 | + | |
99 | - | if($arDept[$z][$i].depName.Length -gt 1) { |
99 | + | |
100 | - | $row-- |
100 | + | |
101 | - | $sheet.Cells.Item($row,$col) = $arDept[$z][$i].depName |
101 | + | |
102 | - | $arDept[$z][$i].depName |
102 | + | |
103 | - | $arDept[$z][$i].duration |
103 | + | |
104 | - | $row++ |
104 | + | |
105 | - | $sheet.Cells.Item($row,$col) = $arDept[$z][$i].duration |
105 | + | |
106 | - | $col++ |
106 | + | |
107 | catch { $pass = $true } | |
108 | ||
109 | # Verify account appeared valid. | |
110 | - | $max = ($sheet.Cells.Item($row,($col)-1).Address()).Replace("$", "") |
110 | + | |
111 | # Check the user appears normal (a_ and h_ accounts likely wont have department numbers). | |
112 | - | $chart = $sheet.Shapes.AddChart().Chart |
112 | + | |
113 | - | $chartNum = ("Chart {0}" -f $cvar3) |
113 | + | |
114 | - | $sheet.Shapes.Item($chartNum).Placement = 3 |
114 | + | #if($userInfo.Department -eq "ARG-DATA Capture"){ $userInfo.SamAccountName } |
115 | - | $sheet.Shapes.Item($chartNum).Top = $top |
115 | + | if($arDept.Contains($userInfo.Company) -eq $false) { $arDept[$userInfo.Company] = @{};$arDept[$userInfo.Company]["comName"] = $userInfo.Company} |
116 | - | $sheet.Shapes.Item($chartNum).Left = $left |
116 | + | |
117 | - | $sheet.Shapes.Item($chartNum).Height = 325 |
117 | + | |
118 | - | $sheet.Shapes.Item($chartNum).Width = 400 |
118 | + | |
119 | - | $chart.ChartType = 69 |
119 | + | |
120 | - | $chart.SetSourceData($range) |
120 | + | |
121 | }else{ | |
122 | if($arDept.Contains("Elevated") -eq $false) { $arDept["Elevated"] = @{} } | |
123 | $arDept["Elevated"][$userInfo.SamAccountName] = "{0:N0}" -f ($duration/60) | |
124 | # Write-Host "FAILURE!" | |
125 | # Write-Host $userInfo.DepartmentNumber[0] | |
126 | # Write-Host $userInfo.SamAccountName | |
127 | - | } |
127 | + | |
128 | } | |
129 | } | |
130 | # Create Excel object, setup spreadsheet, name main page. | |
131 | $excel = New-Object -ComObject excel.application | |
132 | $excel.Visible = $true | |
133 | $excel.DisplayAlerts = $false | |
134 | $workbook = $excel.Workbooks.Add() | |
135 | $row = 1 | |
136 | $col = 1 | |
137 | $sheet = $workbook.Worksheets.Item(1) | |
138 | $sheet.Name = "Dashboard" | |
139 | ||
140 | # Populate tracking vars. | |
141 | # $row is the starting row to begin entering data into text cells. | |
142 | # $cvar tracks $left position, resets when it reaches 3. | |
143 | # $cvar3 tracks $top position, after every third graph it increments +340. | |
144 | $row = 162 | |
145 | $cvar = 1 | |
146 | $cvar3 = 1 | |
147 | $top = 10 | |
148 | $left = 10 | |
149 | # Iterate through main element (Companies), $z returns company name (MGTS, MR, etc.). | |
150 | foreach($z in $arDept.Keys) { | |
151 | # Setup chart location vars. | |
152 | if($cvar -eq 1) { | |
153 | $left = 10 | |
154 | }elseif($cvar -eq 2){ | |
155 | $left = 420 | |
156 | }elseif($cvar -eq 3) { | |
157 | $left = 830 | |
158 | } | |
159 | $col = 2 | |
160 | $sheet.Cells.Item($row,1) = $arDept[$z].comName | |
161 | # Track chart range minimum cell address. | |
162 | $min = ($sheet.Cells.Item(($row)-1,1).Address()).Replace("$", "") | |
163 | # Iterate through secondary element (Departments), $i returns department name. | |
164 | ||
165 | # Graph-Iterate Here | |
166 | $vLoc = Graph-Iterate $arDept[$z] $row $col | |
167 | ||
168 | #Write-Host "ugh: "+Get-TypeData $vLoc | |
169 | Get-Member -InputObject $vLoc | |
170 | $vLoc.GetType() | |
171 | ||
172 | # Track chart range maximum cell address. | |
173 | $max = ($sheet.Cells.Item($row,$col).Address()).Replace("$", "") | |
174 | $range = $sheet.Range($min,$max) | |
175 | ||
176 | Create-Graph $sheet $top $left $range $cvar3 | |
177 | $row++;$row++ | |
178 | # Increment or reset tracking vars. | |
179 | if($cvar -eq 3) { | |
180 | $top = ($top)+340 | |
181 | } | |
182 | if($cvar -eq 1 -or $cvar -eq 2){ $cvar++ }elseif($cvar -eq 3){ $cvar = 1} | |
183 | $cvar3++ | |
184 | } | |
185 | # Show dashboard page rather than some random department. | |
186 | $sheet.Activate() |