SHARE
TWEET

CTX-Report.ps1

alcaron Apr 25th, 2013 13 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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 F
  5. # Filename:    
  6. #========================================================================
  7.  
  8. $start = Get-Date
  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,$strPass) {
  28.         Write-Host $strPass
  29.         foreach($i in $arList.Keys) {
  30.                 if($arList[$i].duration -ne 0) {
  31.                         if($arList[$i].depName.Length -gt 1) {
  32.                                 $varRow--
  33.                                 if($arList[$i].depName -eq $null){ $arList[$i].depName = "UNKNOWN" }
  34.                                 $sheet.Cells.Item($varRow,$varCol) = $arList[$i].depName
  35.                                 $varRow++
  36.                                 $sheet.Cells.Item($varRow,$varCol) = $arList[$i].duration
  37.                                 $varCol++
  38.                                
  39.                                 # Iterate Here
  40.                                 if($master -ne $true){ Iterate $arList[$i] $strPass }
  41.                         }
  42.                 }
  43.         }
  44.         return $varcol
  45. }
  46.  
  47. function Iterate($arSub, $strCom) {
  48.         $indSheet = $workbook.Worksheets.Add()
  49.         $sheetName = ("{0}-{1}" -f $strCom,$arSub.depName)
  50.         Write-Host $sheetName
  51.         $nVar = 1
  52.         if($sheetName -eq "CSI-OPP MAX")
  53.         {
  54.                 Write-Host "The Var is:"
  55.                 Write-Host $nVar
  56.                 $sheetName = "{0} {1}" -f $sheetName,$nVar
  57.                 $nVar++
  58.         }
  59.         $strip = [System.Text.RegularExpressions.Regex]::Replace($sheetName,"[^1-9a-zA-Z_-]"," ");
  60.         if($strip.Length -gt 31) { $ln = 31 }else{ $ln = $strip.Length }
  61.         $indSheet.Name = $strip.Substring(0, $ln)
  62.         $count = $arSub.Keys.Count
  63.         $array = New-Object 'object[,]' $count,2
  64.         $arRow = 0
  65.         foreach($y in $arSub.Keys) {
  66.                 if($y -ne "depName" -and $y -ne "duration" -and $y.Length -gt 1) {
  67.                         $t = 0
  68.                         $array[$arRow,$t] = $y
  69.                         $t++
  70.                         $array[$arRow,$t] = $arSub[$y]
  71.                         $arRow++
  72.                 }
  73.         }
  74.         $rng = $indSheet.Range("A1",("B"+$count))
  75.         $rng.Value2 = $array
  76. }
  77.  
  78. function Create-Graph($lSheet,$lTop,$lLeft,$range, $number, $master) {
  79.         # Add graph to Dashboard and configure.
  80.         $chart = $lSheet.Shapes.AddChart().Chart
  81.         $chartNum = ("Chart {0}" -f $cvar3)
  82.         $sheet.Shapes.Item($chartNum).Placement = 3
  83.         $sheet.Shapes.Item($chartNum).Top = $top
  84.         $sheet.Shapes.Item($chartNum).Left = $left
  85.         if($master -eq $true) {
  86.                         $sheet.Shapes.Item($chartNum).Height = 500
  87.                         $sheet.Shapes.Item($chartNum).Width = 1220
  88.                 }else{
  89.                         $sheet.Shapes.Item($chartNum).Height = 325
  90.                         $sheet.Shapes.Item($chartNum).Width = 400
  91.                 }
  92.                 $chart.ChartType = 69
  93.                 $chart.SetSourceData($range)
  94.         }
  95.  
  96.  
  97. $port = "<port>"
  98. $server = "<server>"
  99. $db = "<db>"
  100. $user = "<db_user>"
  101. $password = "<pass>"
  102. $query = "SELECT * FROM CitrixRMIM.LU_USER"
  103. # For pulling elevated accounts.
  104. #$query = "SELECT * FROM CitrixRMIM.LU_USER WHERE USERNAME LIKE '%[_]%'"
  105. $userlist = SQL-Connect $server $port $db $user $password $query
  106.  
  107. # Create Array (empty) for department data
  108. $arDept = @{}
  109. $arTotal = @{}
  110. foreach($i in $userlist.Tables[0].Rows) {
  111.         $pass = $false
  112.         # Pull sessions for users.
  113.         $uName = $i.USERNAME
  114.         $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
  115.         $result = SQL-Connect $server $port $db $user $password $query2
  116.         $duration = 0
  117.         foreach($i in $result.Tables[0].Rows){
  118.                 [int64]$dur = $i.DURATION.ToString()
  119.                 $duration = ($duration + ($dur / 60000))
  120.         }
  121.        
  122.         try { $userInfo = Get-ADUser -Identity $uName -Properties DepartmentNumber, Department, Company }
  123.         catch { $pass = $true }
  124.        
  125.         # Verify account appeared valid.
  126.  
  127.         if($pass -ne $true) {
  128.                 # Check the user appears normal (a_ and h_ accounts likely wont have department numbers).
  129.                 if($userInfo.DepartmentNumber[0].Length -gt 1 -and $userInfo.SamAccountName.Length -gt 1 -and $userInfo.Company.Length -gt 1) {
  130.                         # Check whether or not the department element exists, if not, create it.
  131.                         if($arDept.Contains($userInfo.Company) -eq $false) { $arDept[$userInfo.Company] = @{};$arDept[$userInfo.Company]["comName"] = $userInfo.Company}
  132.                         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) }
  133.                         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 }
  134.                         # Add user element to array with their total session duration.
  135.                         $arDept[$userInfo.Company][$userInfo.DepartmentNumber[0]][$userInfo.SamAccountName] = "{0:N0}" -f ($duration/60)
  136.                         # Update department duration total (pull user total from .Count()).
  137.                         $arDept[$userInfo.Company][$userInfo.DepartmentNumber[0]]["duration"] = $arDept[$userInfo.Company][$userInfo.DepartmentNumber[0]]["duration"] + ($duration/60)
  138.                 }else{
  139.                         if($arTotal.Contains("Administrative") -eq $false) { $arTotal["Administrative"] = @{};$arTotal["Administrative"]["depName"] = "Administrative"; $arTotal["Administrative"]["duration"] = $arTotal["Administrative"]["duration"] + ($duration/60) }
  140.                         if($userInfo.SamAccountName -like "a_*") {
  141.                                 if($arDept.Contains("Administrative") -eq $false) { $arDept["Administrative"] = @{};$arDept["Administrative"]["comName"] = "Administrative"}
  142.                                 if($arDept["Administrative"].Contains("1337") -eq $false) { $arDept["Administrative"]["1337"] = @{};$arDept["Administrative"]["1337"]["depName"] = "Elevated IDs (A)" }
  143.                                 $arDept["Administrative"]["1337"]["duration"] = $arDept["Administrative"]["1337"]["duration"] + ($duration/60)
  144.                                 $arDept["Administrative"]["1337"][$userInfo.SamAccountName] = "{0:N0}" -f ($duration/60)
  145.                         }else{
  146.                                 if($arDept.Contains("Administrative") -eq $false) { $arDept["Administrative"] = @{};$arDept["Administrative"]["comName"] = "Administrative"}
  147.                                 if($arDept["Administrative"].Contains("1337er") -eq $false) { $arDept["Administrative"]["1337er"] = @{};$arDept["Administrative"]["1337er"]["depName"] = "Elevated IDs (H)" }
  148.                                 $arDept["Administrative"]["1337er"]["duration"] = $arDept["Administrative"]["1337er"]["duration"] + ($duration/60)
  149.                                 $arDept["Administrative"]["1337er"][$userInfo.SamAccountName] = "{0:N0}" -f ($duration/60)
  150.                         }
  151.                 }
  152.         }
  153. }
  154.  
  155. # Create Excel object, setup spreadsheet, name main page.
  156. $excel = New-Object -ComObject excel.application
  157. $excel.Visible = $true
  158. $excel.DisplayAlerts = $false
  159. $workbook = $excel.Workbooks.Add()
  160. $row = 1
  161. $col = 1
  162. $sheet = $workbook.Worksheets.Item(1)
  163. $sheet.Name = "Dashboard"
  164.  
  165. # Populate tracking vars.
  166. # $row is the starting row to begin entering data into text cells.
  167. # $cvar tracks $left position, resets when it reaches 3.
  168. # $cvar3 tracks $top position, after every third graph it increments +340.
  169. $row = 202
  170. $col = 2
  171. $cvar = 1
  172. $cvar3 = 1
  173. $top = 10
  174. $left = 10
  175. # Iterate through main element (Companies), $z returns company name (MGTS, MR, etc.).
  176.  
  177. $min = ($sheet.Cells.Item(($row)-1,1).Address()).Replace("$", "")
  178. foreach($q in $arTotal.Keys) {
  179.         $sheet.Cells.Item($row,1) = "Maritz Total Citrix Usage (by hours)"
  180.         $row--
  181.         $sheet.Cells.Item($row,$col) = $arTotal[$q].depName
  182.         $row++
  183.         $sheet.Cells.Item($row,$col) = $arTotal[$q].duration
  184.         $col++
  185. }
  186. $max = ($sheet.Cells.Item($row,($col)-1).Address()).Replace("$", "")
  187. $range = $sheet.Range($min,$max)
  188. Create-Graph $sheet $top $left $range $cvar3 $true
  189. $row++;$row++
  190. $col = 2
  191. $top = ($top)+510
  192. $cvar3++
  193.  
  194. foreach($z in $arDept.Keys) {
  195.         if($z.Length -gt 1 -and $z -ne "112 MAS"){
  196.                 # Setup chart location vars.
  197.                 if($cvar -eq 1) {
  198.                         $left = 10
  199.                 }elseif($cvar -eq 2){
  200.                         $left = 420
  201.                 }elseif($cvar -eq 3) {
  202.                         $left = 830
  203.                 }
  204.                 $col = 2
  205.                 $sheet.Cells.Item($row,1) = $arDept[$z].comName
  206.                 # Track chart range minimum cell address.
  207.                 $min = ($sheet.Cells.Item(($row)-1,1).Address()).Replace("$", "")
  208.                 # Iterate through secondary element (Departments), $i returns department name.
  209.  
  210.                 # Graph-Iterate Here
  211.                 Write-Host $arDept[$z].comName
  212.                 $vLoc = Graph-Iterate $arDept[$z] $row $col $arDept[$z].comName
  213.                
  214.                 # Track chart range maximum cell address.
  215.                 $max = ($sheet.Cells.Item($row,($vLoc)-1).Address()).Replace("$", "")
  216.                 $range = $sheet.Range($min,$max)
  217.                
  218.                 Create-Graph $sheet $top $left $range $cvar3
  219.                 $row++;$row++
  220.                 # Increment or reset tracking vars.
  221.                 if($cvar -eq 3) {
  222.                         $top = ($top)+340
  223.                 }
  224.                 if($cvar -eq 1 -or $cvar -eq 2){ $cvar++ }elseif($cvar -eq 3){ $cvar = 1}
  225.                 $cvar3++
  226.         }
  227. }
  228. # Show dashboard page rather than some random department.
  229. $sheet.Activate()
  230. $stop = Get-Date
  231. New-TimeSpan $start $stop
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top