SHARE
TWEET

CTX-Report-EdgeSight

alcaron May 6th, 2013 20 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:   5/6/2013 9:57 AM
  4. # Created by:   Paul F
  5. # Filename:     CTX-Report-EdgeSight.ps1
  6. #========================================================================
  7. $start = Get-Date
  8.  
  9. Import-Module ActiveDirectory
  10. function SQL-Connect($server, $port, $db, $userName, $passWord, $query) {
  11.         $conn = New-Object System.Data.SqlClient.SqlConnection
  12.         $ctimeout = 30
  13.         $qtimeout = 120
  14.         $constring = "Server={0},{5};Database={1};Integrated Security=False;User ID={2};Password={3};Connect Timeout={4}" -f $server,$db,$userName,$passWord,$ctimeout,$port
  15.         $conn.ConnectionString = $constring
  16.         $conn.Open()
  17.         $cmd = New-Object System.Data.SqlClient.SqlCommand($query, $conn)
  18.         $cmd.CommandTimeout = $qtimeout
  19.         $ds = New-Object System.Data.DataSet
  20.         $da = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
  21.         $da.fill($ds)
  22.         $conn.Close()
  23.         return $ds
  24. }
  25.  
  26. function Graph-Iterate($arList,$varRow,$varCol,$strPass) {
  27.         Write-Host $arList[$i].depName
  28.         foreach($i in $arList.Keys) {
  29.                 if($arList[$i].duration -ne 0) {
  30.                         if($arList[$i].depName.Length -gt 1) {
  31.                                 $varRow--
  32.                                 if($arList[$i].depName -eq $null){ $arList[$i].depName = "UNKNOWN" }
  33.                                 $sheet.Cells.Item($varRow,$varCol) = $arList[$i].depName
  34.                                 $varRow++
  35.                                 $sheet.Cells.Item($varRow,$varCol) = ("{0:N1}" -f $arList[$i].duration)
  36.                                 $varCol++
  37.                                
  38.                                 if($master -ne $true){ Iterate $arList[$i] $strPass }
  39.                         }
  40.                 }
  41.         }
  42.         return $varcol
  43. }
  44.  
  45. function Iterate($arSub, $strCom) {
  46.         $indSheet = $workbook.Worksheets.Add()
  47.         $sheetName = ("{0}-{1}" -f $strCom,$arSub.depName)
  48.         Write-Host $sheetName
  49.         $nVar = 1
  50.         if($sheetName -eq "CSI-OPP MAX")
  51.         {
  52.                 Write-Host "The Var is:"
  53.                 Write-Host $nVar
  54.                 $sheetName = "{0} {1}" -f $sheetName,$nVar
  55.                 $nVar++
  56.         }
  57.         $strip = [System.Text.RegularExpressions.Regex]::Replace($sheetName,"[^1-9a-zA-Z_-]"," ");
  58.         if($strip.Length -gt 31) { $ln = 31 }else{ $ln = $strip.Length }
  59.         $indSheet.Name = $strip.Substring(0, $ln)
  60.         $count = $arSub.Keys.Count
  61.         $array = New-Object 'object[,]' $count,2
  62.         $arRow = 0
  63.         foreach($y in $arSub.Keys) {
  64.                 if($y -ne "depName" -and $y -ne "duration" -and $y.Length -gt 1) {
  65.                         $t = 0
  66.                         $array[$arRow,$t] = $y
  67.                         $t++
  68.                         $array[$arRow,$t] = $arSub[$y]
  69.                         $arRow++
  70.                 }
  71.         }
  72.         $rng = $indSheet.Range("A1",("B"+$count))
  73.         $rng.Value2 = $array
  74. }
  75.  
  76. function Create-Graph($lSheet,$lTop,$lLeft,$range, $number, $master, $catRange) {
  77.         # Add graph to Dashboard and configure.
  78.         $chart = $lSheet.Shapes.AddChart().Chart
  79.         $chartNum = ("Chart {0}" -f $cvar3)
  80.         $sheet.Shapes.Item($chartNum).Placement = 3
  81.         $sheet.Shapes.Item($chartNum).Top = $top
  82.         $sheet.Shapes.Item($chartNum).Left = $left
  83.         if($master -eq $true) {
  84.                         $sheet.Shapes.Item($chartNum).Height = 500
  85.                         $sheet.Shapes.Item($chartNum).Width = 1220
  86.                 }else{
  87.                         $sheet.Shapes.Item($chartNum).Height = 325
  88.                         $sheet.Shapes.Item($chartNum).Width = 400
  89.                 }
  90.                 $chart.ChartType = 69
  91.                 $chart.SetSourceData($range)
  92.                 $chart.SeriesCollection(1).XValues = $catRange
  93.         }
  94.  
  95. $port = "<port>"
  96. $server = "<sqlserver>"
  97. $db = "<db>"
  98. $user = "<db_user>"
  99. $password = "<pass>"
  100. $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
  101. FROM         dbo.principal AS p INNER JOIN
  102.                      dbo.session AS s ON s.prid = p.prid INNER JOIN
  103.                      dbo.ctrx_session AS cs ON cs.sessid = s.sessid"
  104. #WHERE          p.account_name LIKE 'a[_]%'
  105.  
  106. $userlist = SQL-Connect $server $port $db $user $password $query
  107. $users = @{}
  108. foreach($i in $userlist.Tables) {
  109.         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) {
  110.                 try {
  111.                         $info = Get-ADUser -Identity $i.account_name -Properties DepartmentNumber, Department, Company
  112.                 }
  113.                 catch {
  114.                         $info = @{"Company"="Terminated";"Department"="Invalid";"DepartmentNumber"="0000"}
  115.                 }
  116.                 if($info.Company.Length -lt 2) {
  117.                         $info = @{"Company"="Terminated";"Department"="Invalid";"DepartmentNumber"="0000"}
  118.                 }
  119.                 if($users.Contains($info.Company) -eq $false) {
  120.                         $users[$info.Company] = @{}
  121.                         $users[$info.Company]['duration'] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
  122.                 }else{
  123.                         $users[$info.Company]['duration'] = $users[$info.Company]['duration']+(New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
  124.                 }
  125.                 if($users[$info.Company].Contains(([string]$info.DepartmentNumber)) -eq $false) {
  126.                         $users[$info.Company][([string]$info.DepartmentNumber)] = @{}
  127.                         $users[$info.Company][([string]$info.DepartmentNumber)]['depName'] = $info.Department
  128.                         $users[$info.Company][([string]$info.DepartmentNumber)]['duration'] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
  129.                 }else{
  130.                         $users[$info.Company][([string]$info.DepartmentNumber)]['duration'] = $users[$info.Company][([string]$info.DepartmentNumber)]['duration']+(New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
  131.                 }
  132.                 if($users[$info.Company][([string]$info.DepartmentNumber)].Contains($i.account_name) -eq $false) {
  133.                         $users[$info.Company][([string]$info.DepartmentNumber)][$i.account_name] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
  134.                 }else{
  135.                         $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
  136.                 }
  137.         }elseif($i.account_name -ne "UNKNOWN" -and ([string]$i.logon_time).Length -gt 1 -and ([string]$i.logoff_time).Length -gt 1) {
  138.                 if($i.account_name -like "a_*") {
  139.                         $info = @{"Company"="Administrators";"Department"="Elevated IDs (A)";"DepartmentNumber"="1111"}
  140.                 }else{
  141.                         $info = @{"Company"="Administrators";"Department"="Elevated IDs (H)";"DepartmentNumber"="2222"}
  142.                 }
  143.                 if($users.Contains("Administrators") -eq $false) {
  144.                         $users['Administrators'] = @{}
  145.                         $users['Administrators']['duration'] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
  146.                 }else{
  147.                         $users['Administrators']['duration'] = $users['Administrators']['duration']+(New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
  148.                 }
  149.                 if($users['Administrators'].Contains($info.DepartmentNumber) -eq $false) {
  150.                         $users['Administrators'][$info.DepartmentNumber] = @{}
  151.                         $users['Administrators'][$info.DepartmentNumber]['depName'] = $info.Department
  152.                         $users['Administrators'][$info.DepartmentNumber]['duration'] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
  153.                 }else{
  154.                         $users['Administrators'][$info.DepartmentNumber]['duration'] = $users['Administrators'][$info.DepartmentNumber]['duration']+(New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
  155.                 }
  156.                 if($users['Administrators'][$info.DepartmentNumber].Contains($i.account_name) -eq $false) {
  157.                         $users['Administrators'][$info.DepartmentNumber][$i.account_name] = (New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
  158.                 }else{
  159.                         $users['Administrators'][$info.DepartmentNumber][$i.account_name] = $users['Administrators'][$info.DepartmentNumber][$i.account_name]+(New-TimeSpan $i.logon_time $i.logoff_time).TotalHours
  160.                 }
  161.         }else{
  162.                 if(([string]$i.logon_time).Length -lt 1 -and $i.account_name -ne "UNKNOWN"){ "No logon time: "+$i.account_name }
  163.                 if(([string]$i.logoff_time).Length -lt 1 -and $i.account_name -ne "UNKNOWN"){ "No logoff time: "+$i.account_name }
  164.         }
  165. }
  166.  
  167. # Create Excel object, setup spreadsheet, name main page.
  168. $excel = New-Object -ComObject excel.application
  169. $excel.Visible = $true
  170. $excel.DisplayAlerts = $false
  171. $workbook = $excel.Workbooks.Add()
  172. $row = 1
  173. $col = 1
  174. $sheet = $workbook.Worksheets.Item(1)
  175. $sheet.Name = "Dashboard"
  176.  
  177. # Populate tracking vars.
  178. # $row is the starting row to begin entering data into text cells.
  179. # $cvar tracks $left position, resets when it reaches 3.
  180. # $cvar3 tracks $top position, after every third graph it increments +340.
  181. $row = 202
  182. $col = 2
  183. $cvar = 1
  184. $cvar3 = 1
  185. $top = 10
  186. $left = 10
  187. # Iterate through main element (Companies), $z returns company name (MGTS, MR, etc.).
  188.  
  189. $min = ($sheet.Cells.Item(($row)-1,1).Address()).Replace("$", "")
  190. $tmin = ($sheet.Cells.Item(($row)-1,2).Address()).Replace("$", "")
  191. foreach($q in $users.Keys) {
  192.         $sheet.Cells.Item($row,1) = "Maritz Total Citrix Usage (by hours)"
  193.         $row--
  194.         if($q -eq "114"){ $q = "Training IDs" }
  195.         $sheet.Cells.Item($row,$col) = $q
  196.         $row++
  197.         $sheet.Cells.Item($row,$col) = ("{0:N1}" -f $users[$q].duration)
  198.         $col++
  199. }
  200. $max = ($sheet.Cells.Item($row,($col)-1).Address()).Replace("$", "")
  201. $range = $sheet.Range($min,$max)
  202. $range2 = $sheet.Range($tmin,$max)
  203. Create-Graph $sheet $top $left $range $cvar3 $true $range2
  204. $row++;$row++
  205. $col = 2
  206. $top = ($top)+510
  207. $cvar3++
  208.  
  209. foreach($z in $users.Keys) {
  210.         if($z.Length -gt 1 -and $z -ne "112 MAS"){
  211.                 # Setup chart location vars.
  212.                 if($cvar -eq 1) {
  213.                         $left = 10
  214.                 }elseif($cvar -eq 2){
  215.                         $left = 420
  216.                 }elseif($cvar -eq 3) {
  217.                         $left = 830
  218.                 }
  219.                 $col = 2
  220.                 $sheet.Cells.Item($row,1) = $z
  221.                 # Track chart range minimum cell address.
  222.                 $min = ($sheet.Cells.Item(($row)-1,1).Address()).Replace("$", "")
  223.                 $tmin = ($sheet.Cells.Item(($row)-1,2).Address()).Replace("$", "")
  224.                 # Iterate through secondary element (Departments), $i returns department name.
  225.  
  226.                 # Graph-Iterate Here
  227.                 $vLoc = Graph-Iterate $users[$z] $row $col $z
  228.                
  229.                 # Track chart range maximum cell address.
  230.                 $max = ($sheet.Cells.Item($row,($vLoc)-1).Address()).Replace("$", "")
  231.                 $range = $sheet.Range($min,$max)
  232.                 $range2 = $sheet.Range($tmin,$max)
  233.                
  234.                 Create-Graph $sheet $top $left $range $cvar3 $false $range2
  235.                 $row++;$row++
  236.                 # Increment or reset tracking vars.
  237.                 if($cvar -eq 3) {
  238.                         $top = ($top)+340
  239.                 }
  240.                 if($cvar -eq 1 -or $cvar -eq 2){ $cvar++ }elseif($cvar -eq 3){ $cvar = 1}
  241.                 $cvar3++
  242.         }
  243. }
  244. # Show dashboard page rather than some random department.
  245. $sheet.Activate()
  246.  
  247. New-TimeSpan -Start $start -End (Get-Date)
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