CTX-Report.ps1

#========================================================================
# Created with: SAPIEN Technologies, Inc., PowerShell Studio 2012 v3.1.13
# Created on:   4/22/2013 1:10 PM
# Created by:
# Organization: Maritz, LLC
# Filename:
#========================================================================

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) {
    foreach($i in $arList.Keys) {
        if($arList[$i].duration -ne 0) {
            if($arList[$i].depName.Length -gt 1) {
                $varRow--
                $sheet.Cells.Item($varRow,$varCol) = $arList[$i].depName
                $arList[$i].depName
                $arList[$i].duration
                $varRow++
                $sheet.Cells.Item($varRow,$varCol) = $arList[$i].duration
                $varCol++

                # Iterate Here
                Iterate($arList[$i])
            }
        }
    }
    # 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
    return $varcol
}

function Iterate($arSub) {
        # Create sheet, add cells. Destroy sheet object.
        $indSheet = $workbook.Worksheets.Add()
        $indSheet.Name = $arSub.depName
        $indRow = 1
        $indCol = 1
        # Iterate through tertiary node (Users), $y returns username.
        foreach($y in $arSub.Keys) {
            if($y -ne "depNames" -and $y -ne "duration" -and $y.Length -gt 1) {
                $indSheet.Cells.Item($indRow,$indCol) = $y
                $indCol++
                $indSheet.Cells.Item($indRow,$indCol) = $arSub[$y]
                $indCol--
                $indRow++
            }else{
            }
        }
        $indSheet = $null
    }

function Create-Graph($lSheet,$lTop,$lLeft,$range, $number) {
        # 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
        $sheet.Shapes.Item($chartNum).Height = 325
        $sheet.Shapes.Item($chartNum).Width = 400
        $chart.ChartType = 69
        $chart.SetSourceData($range)
    }


$port = "40013"
$server = "10.65.1.45\fensqlmgtsp216m"
$db = "CitrixRMSummary"
$user = "CitrixRMReadOnly"
$password = ""
$query = "SELECT TOP 2 * FROM CitrixRMIM.LU_USER"
$userlist = SQL-Connect $server $port $db $user $password $query

# Create Array (empty) for department data
$arDept = @{}
foreach($i in $userlist.Tables[0].Rows) {
    $pass = $false
    # Pull sessions for users.
    $uName = $i.USERNAME
    $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
    $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))
    }

    try { $userInfo = Get-ADUser -Identity $uName -Properties DepartmentNumber, Department, Company }
    catch { $pass = $true }

    # Verify account appeared valid.
    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($userInfo.Department -eq "ARG-DATA Capture"){ $userInfo.SamAccountName }
            if($arDept.Contains($userInfo.Company) -eq $false) { $arDept[$userInfo.Company] = @{};$arDept[$userInfo.Company]["comName"] = $userInfo.Company}
            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($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
        }
    }
}
# 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 = 162
$cvar = 1
$cvar3 = 1
$top = 10
$left = 10
# Iterate through main element (Companies), $z returns company name (MGTS, MR, etc.).
foreach($z in $arDept.Keys) {
    # 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
    $vLoc = Graph-Iterate $arDept[$z] $row $col

    #Write-Host "ugh: "+Get-TypeData $vLoc
    Get-Member -InputObject $vLoc
    $vLoc.GetType()

    # Track chart range maximum cell address.
    $max = ($sheet.Cells.Item($row,$col).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()