CTX-Citrix-Report.ps1

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

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
}

$port = "40013"
$server = "10.65.1.45\fensqlmgtsp216m"
$db = "CitrixRMSummary"
$user = "CitrixRMReadOnly"
$password = "3N11GQcr"
$query = "SELECT * 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 }ny] = @{};$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{
#           Write-Host "FAILURE!"
#           Write-Host $userInfo.DepartmentNumber[0]
#           Write-Host $userInfo.SamAccountName
        }
    }
}

$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)
$row = 162
$cvar = 1
$cvar3 = 1
$top = 10
$left = 10
foreach($z in $arDept.Keys) {
    "`r`nCVAR: "+$cvar
    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
    $min = ($sheet.Cells.Item(($row)-1,1).Address()).Replace("$", "")
    foreach($i in $arDept[$z].Keys) {
        if($arDept[$z][$i].duration -ne 0) {
            if($arDept[$z][$i].depName.Length -gt 1) {
                $row--
                $sheet.Cells.Item($row,$col) = $arDept[$z][$i].depName
                $arDept[$z][$i].depName
                $arDept[$z][$i].duration
                $row++
                $sheet.Cells.Item($row,$col) = $arDept[$z][$i].duration
                $col++
            }
        }
    }
    $max = ($sheet.Cells.Item($row,($col)-1).Address()).Replace("$", "")
    $range = $sheet.Range($min,$max)
    $chart = $sheet.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)
    $row++;$row++
    if($cvar -eq 3) {
        $top = ($top)+340
    }
    if($cvar -eq 1 -or $cvar -eq 2){ $cvar++ }elseif($cvar -eq 3){ $cvar = 1}
    $cvar3++
}