View difference between Paste ID: r9752d43 and H5zRYJgj

 #========================================================================
 # Created with: SAPIEN Technologies, Inc., PowerShell Studio 2012 v3.1.13
 # Created on:   4/22/2013 1:10 PM
-# Created by:
+# Created by:   Paul F
-# Organization: Maritz, LLC
+
 #========================================================================
 
 $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) {
+function Graph-Iterate($arList,$varRow,$varCol,$strPass) {
 	Write-Host $strPass
 	foreach($i in $arList.Keys) {
 		if($arList[$i].duration -ne 0) {
 			if($arList[$i].depName.Length -gt 1) {
 				$varRow--
-				$arList[$i].depName
+				if($arList[$i].depName -eq $null){ $arList[$i].depName = "UNKNOWN" }
-				$arList[$i].duration
+
 				$varRow++
 				$sheet.Cells.Item($varRow,$varCol) = $arList[$i].duration
 				$varCol++
 
 				# Iterate Here
-				Iterate($arList[$i])
+				if($master -ne $true){ Iterate $arList[$i] $strPass }
 			}
 		}
 	}
-	# Because PowerShell is dumb as shit and thinks "return" means "exit, and puke out all the output".
+
-	# It's ok, I remember my first time using a programming language too...
+
-	Write-Host $varCol
+
 function Iterate($arSub, $strCom) {
 	$indSheet = $workbook.Worksheets.Add()
 	$sheetName = ("{0}-{1}" -f $strCom,$arSub.depName)
-function Iterate($arSub) {
+	Write-Host $sheetName
-		# Create sheet, add cells. Destroy sheet object.
+	$nVar = 1
-		$indSheet = $workbook.Worksheets.Add()
+	if($sheetName -eq "CSI-OPP MAX")
-		$indSheet.Name = $arSub.depName
+	{
-		$indRow = 1
+		Write-Host "The Var is:"
-		$indCol = 1
+		Write-Host $nVar
-		# Iterate through tertiary node (Users), $y returns username.
+		$sheetName = "{0} {1}" -f $sheetName,$nVar
-		foreach($y in $arSub.Keys) {
+		$nVar++
-			if($y -ne "depNames" -and $y -ne "duration" -and $y.Length -gt 1) {
+
-				$indSheet.Cells.Item($indRow,$indCol) = $y
+	$strip = [System.Text.RegularExpressions.Regex]::Replace($sheetName,"[^1-9a-zA-Z_-]"," ");
-				$indCol++
+	if($strip.Length -gt 31) { $ln = 31 }else{ $ln = $strip.Length }
-				$indSheet.Cells.Item($indRow,$indCol) = $arSub[$y]
+	$indSheet.Name = $strip.Substring(0, $ln)
-				$indCol--
+	$count = $arSub.Keys.Count
-				$indRow++
+	$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) {
-		$indSheet = $null
+			$t = 0
 			$array[$arRow,$t] = $y
 			$t++
-function Create-Graph($lSheet,$lTop,$lLeft,$range, $number) {
+			$array[$arRow,$t] = $arSub[$y]
-		# Add graph to Dashboard and configure.
+			$arRow++
-		$chart = $lSheet.Shapes.AddChart().Chart
+
-		$chartNum = ("Chart {0}" -f $cvar3)
+
-		$sheet.Shapes.Item($chartNum).Placement = 3
+	$rng = $indSheet.Range("A1",("B"+$count))
-		$sheet.Shapes.Item($chartNum).Top = $top
+	$rng.Value2 = $array
-		$sheet.Shapes.Item($chartNum).Left = $left
+
-		$sheet.Shapes.Item($chartNum).Height = 325
+
-		$sheet.Shapes.Item($chartNum).Width = 400
+function Create-Graph($lSheet,$lTop,$lLeft,$range, $number, $master) {
 	# 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
-$port = "40013"
+	$sheet.Shapes.Item($chartNum).Left = $left
-$server = "10.65.1.45\fensqlmgtsp216m"
+	if($master -eq $true) {
-$db = "CitrixRMSummary"
+			$sheet.Shapes.Item($chartNum).Height = 500
-$user = "CitrixRMReadOnly"
+			$sheet.Shapes.Item($chartNum).Width = 1220
-$password = ""
+
-$query = "SELECT TOP 2 * FROM CitrixRMIM.LU_USER"
+			$sheet.Shapes.Item($chartNum).Height = 325
 			$sheet.Shapes.Item($chartNum).Width = 400
 		}
 		$chart.ChartType = 69
 		$chart.SetSourceData($range)
 	}
 
 
 $port = "<port>"
-	$query2 = "SELECT * FROM CitrixRMIM.SDB_SESSION WHERE (CitrixRMIM.SDB_SESSION.FK_USERID = {0}) AND (CitrixRMIM.SDB_SESSION.SESSIONSTART > '01/03/2013')" -f $i.PK_USERID
+$server = "<server>"
 $db = "<db>"
 $user = "<db_user>"
 $password = "<pass>"
 $query = "SELECT * FROM CitrixRMIM.LU_USER"
 # For pulling elevated accounts.
 #$query = "SELECT * FROM CitrixRMIM.LU_USER WHERE USERNAME LIKE '%[_]%'"
 $userlist = SQL-Connect $server $port $db $user $password $query
 
 # Create Array (empty) for department data
 $arDept = @{}
 $arTotal = @{}
 foreach($i in $userlist.Tables[0].Rows) {
 	$pass = $false
 	# Pull sessions for users.
 	$uName = $i.USERNAME
-			#if($userInfo.Department -eq "ARG-DATA Capture"){ $userInfo.SamAccountName }
+	$query2 = "SELECT * FROM CitrixRMIM.SDB_SESSION WHERE (CitrixRMIM.SDB_SESSION.FK_USERID = {0}) AND (CitrixRMIM.SDB_SESSION.SESSIONSTART > '01/01/2013') AND (CitrixRMIM.SDB_SESSION.SESSIONSTART < '03/01/2013')" -f $i.PK_USERID
 	$result = SQL-Connect $server $port $db $user $password $query2
 	$duration = 0
 	foreach($i in $result.Tables[0].Rows){
 		[int64]$dur = $i.DURATION.ToString()
 		$duration = ($duration + ($dur / 60000))
 	}
 
-			if($arDept.Contains("Elevated") -eq $false) { $arDept["Elevated"] = @{} }
+
-			$arDept["Elevated"][$userInfo.SamAccountName] = "{0:N0}" -f ($duration/60)
+
-#			Write-Host "FAILURE!"
+
-#			Write-Host $userInfo.DepartmentNumber[0]
+
-#			Write-Host $userInfo.SamAccountName
+
 	if($pass -ne $true) {
 		# Check the user appears normal (a_ and h_ accounts likely wont have department numbers).
 		if($userInfo.DepartmentNumber[0].Length -gt 1 -and $userInfo.SamAccountName.Length -gt 1 -and $userInfo.Company.Length -gt 1) {
 			# Check whether or not the department element exists, if not, create it.
 			if($arDept.Contains($userInfo.Company) -eq $false) { $arDept[$userInfo.Company] = @{};$arDept[$userInfo.Company]["comName"] = $userInfo.Company}
 			if($arTotal.Contains($userInfo.Company) -eq $false) { $arTotal[$userInfo.Company] = @{};$arTotal[$userInfo.Company]["depName"] = $userInfo.Company; $arTotal[$userInfo.Company]["duration"] = $arTotal[$userInfo.Company]["duration"] + ($duration/60) }
 			if($arDept[$userInfo.Company].Contains($userInfo.DepartmentNumber[0]) -eq $false) { $arDept[$userInfo.Company][$userInfo.DepartmentNumber[0]] = @{};$arDept[$userInfo.Company][$userInfo.DepartmentNumber[0]]["depName"] = $userInfo.Department }
 			# Add user element to array with their total session duration.
 			$arDept[$userInfo.Company][$userInfo.DepartmentNumber[0]][$userInfo.SamAccountName] = "{0:N0}" -f ($duration/60)
 			# Update department duration total (pull user total from .Count()).
 			$arDept[$userInfo.Company][$userInfo.DepartmentNumber[0]]["duration"] = $arDept[$userInfo.Company][$userInfo.DepartmentNumber[0]]["duration"] + ($duration/60)
 		}else{
 			if($arTotal.Contains("Administrative") -eq $false) { $arTotal["Administrative"] = @{};$arTotal["Administrative"]["depName"] = "Administrative"; $arTotal["Administrative"]["duration"] = $arTotal["Administrative"]["duration"] + ($duration/60) }
 			if($userInfo.SamAccountName -like "a_*") {
 				if($arDept.Contains("Administrative") -eq $false) { $arDept["Administrative"] = @{};$arDept["Administrative"]["comName"] = "Administrative"}
 				if($arDept["Administrative"].Contains("1337") -eq $false) { $arDept["Administrative"]["1337"] = @{};$arDept["Administrative"]["1337"]["depName"] = "Elevated IDs (A)" }
 				$arDept["Administrative"]["1337"]["duration"] = $arDept["Administrative"]["1337"]["duration"] + ($duration/60)
-$row = 162
+				$arDept["Administrative"]["1337"][$userInfo.SamAccountName] = "{0:N0}" -f ($duration/60)
 			}else{
 				if($arDept.Contains("Administrative") -eq $false) { $arDept["Administrative"] = @{};$arDept["Administrative"]["comName"] = "Administrative"}
 				if($arDept["Administrative"].Contains("1337er") -eq $false) { $arDept["Administrative"]["1337er"] = @{};$arDept["Administrative"]["1337er"]["depName"] = "Elevated IDs (H)" }
 				$arDept["Administrative"]["1337er"]["duration"] = $arDept["Administrative"]["1337er"]["duration"] + ($duration/60)
 				$arDept["Administrative"]["1337er"][$userInfo.SamAccountName] = "{0:N0}" -f ($duration/60)
 			}
-	# Setup chart location vars.
+
-	if($cvar -eq 1) {
+
-		$left = 10
+
-	}elseif($cvar -eq 2){
+
-		$left = 420
+
-	}elseif($cvar -eq 3) {
+
-		$left = 830
+
 $excel.DisplayAlerts = $false
-	$col = 2
+
-	$sheet.Cells.Item($row,1) = $arDept[$z].comName
+
-	# Track chart range minimum cell address.
+
-	$min = ($sheet.Cells.Item(($row)-1,1).Address()).Replace("$", "")
+
-	# Iterate through secondary element (Departments), $i returns department name.
+
 
-	# Graph-Iterate Here
+
-	$vLoc = Graph-Iterate $arDept[$z] $row $col
+
 # $cvar tracks $left position, resets when it reaches 3.
-	#Write-Host "ugh: "+Get-TypeData $vLoc
+
-	Get-Member -InputObject $vLoc
+$row = 202
-	$vLoc.GetType()
+$col = 2
 $cvar = 1
-	# Track chart range maximum cell address.
+
-	$max = ($sheet.Cells.Item($row,$col).Address()).Replace("$", "")
+
-	$range = $sheet.Range($min,$max)
+
 # Iterate through main element (Companies), $z returns company name (MGTS, MR, etc.).
-	Create-Graph $sheet $top $left $range $cvar3
+
-	$row++;$row++
+$min = ($sheet.Cells.Item(($row)-1,1).Address()).Replace("$", "")
-	# Increment or reset tracking vars.
+foreach($q in $arTotal.Keys) {
-	if($cvar -eq 3) {
+	$sheet.Cells.Item($row,1) = "Maritz Total Citrix Usage (by hours)"
-		$top = ($top)+340
+	$row--
 	$sheet.Cells.Item($row,$col) = $arTotal[$q].depName
-	if($cvar -eq 1 -or $cvar -eq 2){ $cvar++ }elseif($cvar -eq 3){ $cvar = 1}
+	$row++
-	$cvar3++
+	$sheet.Cells.Item($row,$col) = $arTotal[$q].duration
 	$col++
 }
-$sheet.Activate()
+$max = ($sheet.Cells.Item($row,($col)-1).Address()).Replace("$", "")
 $range = $sheet.Range($min,$max)
 Create-Graph $sheet $top $left $range $cvar3 $true
 $row++;$row++
 $col = 2
 $top = ($top)+510
 $cvar3++
 
 foreach($z in $arDept.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) = $arDept[$z].comName
 		# Track chart range minimum cell address.
 		$min = ($sheet.Cells.Item(($row)-1,1).Address()).Replace("$", "")
 		# Iterate through secondary element (Departments), $i returns department name.
 
 		# Graph-Iterate Here
 		Write-Host $arDept[$z].comName
 		$vLoc = Graph-Iterate $arDept[$z] $row $col $arDept[$z].comName
 
 		# Track chart range maximum cell address.
 		$max = ($sheet.Cells.Item($row,($vLoc)-1).Address()).Replace("$", "")
 		$range = $sheet.Range($min,$max)
 
 		Create-Graph $sheet $top $left $range $cvar3
 		$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()
 $stop = Get-Date
 New-TimeSpan $start $stop