CTX-Report-EdgeSight

#========================================================================
# Created with: SAPIEN Technologies, Inc., PowerShell Studio 2012 v3.1.13
# Created on:   5/6/2013 9:57 AM
# Created by:   Paul F
# Filename:     CTX-Report-EdgeSight.ps1
#========================================================================
$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)