CTX-Report.ps1

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

$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 $strPass
    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) = $arList[$i].duration
                $varCol++

                # Iterate Here
                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) {
    # 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)
    }


$port = "<port>"
$server = "<server>"
$db = "<db>"
$user = "<db_user>"
$password = "<pass>"
$query = "SELECT * FROM CitrixRMIM.LU_USER"
# For pulling elevated accounts.
#$query = "SELECT * FROM CitrixRMIM.LU_USER WHERE USERNAME LIKE '%[_]%'"
$userlist = SQL-Connect $server $port $db $user $password $query

# Create Array (empty) for department data
$arDept = @{}
$arTotal = @{}
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/01/2013') AND (CitrixRMIM.SDB_SESSION.SESSIONSTART < '03/01/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($arDept.Contains($userInfo.Company) -eq $false) { $arDept[$userInfo.Company] = @{};$arDept[$userInfo.Company]["comName"] = $userInfo.Company}
            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) }
            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($arTotal.Contains("Administrative") -eq $false) { $arTotal["Administrative"] = @{};$arTotal["Administrative"]["depName"] = "Administrative"; $arTotal["Administrative"]["duration"] = $arTotal["Administrative"]["duration"] + ($duration/60) }
            if($userInfo.SamAccountName -like "a_*") {
                if($arDept.Contains("Administrative") -eq $false) { $arDept["Administrative"] = @{};$arDept["Administrative"]["comName"] = "Administrative"}
                if($arDept["Administrative"].Contains("1337") -eq $false) { $arDept["Administrative"]["1337"] = @{};$arDept["Administrative"]["1337"]["depName"] = "Elevated IDs (A)" }
                $arDept["Administrative"]["1337"]["duration"] = $arDept["Administrative"]["1337"]["duration"] + ($duration/60)
                $arDept["Administrative"]["1337"][$userInfo.SamAccountName] = "{0:N0}" -f ($duration/60)
            }else{
                if($arDept.Contains("Administrative") -eq $false) { $arDept["Administrative"] = @{};$arDept["Administrative"]["comName"] = "Administrative"}
                if($arDept["Administrative"].Contains("1337er") -eq $false) { $arDept["Administrative"]["1337er"] = @{};$arDept["Administrative"]["1337er"]["depName"] = "Elevated IDs (H)" }
                $arDept["Administrative"]["1337er"]["duration"] = $arDept["Administrative"]["1337er"]["duration"] + ($duration/60)
                $arDept["Administrative"]["1337er"][$userInfo.SamAccountName] = "{0:N0}" -f ($duration/60)
            }
        }
    }
}

# 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("$", "")
foreach($q in $arTotal.Keys) {
    $sheet.Cells.Item($row,1) = "Maritz Total Citrix Usage (by hours)"
    $row--
    $sheet.Cells.Item($row,$col) = $arTotal[$q].depName
    $row++
    $sheet.Cells.Item($row,$col) = $arTotal[$q].duration
    $col++
}
$max = ($sheet.Cells.Item($row,($col)-1).Address()).Replace("$", "")
$range = $sheet.Range($min,$max)
Create-Graph $sheet $top $left $range $cvar3 $true
$row++;$row++
$col = 2
$top = ($top)+510
$cvar3++

foreach($z in $arDept.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) = $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
        Write-Host $arDept[$z].comName
        $vLoc = Graph-Iterate $arDept[$z] $row $col $arDept[$z].comName

        # Track chart range maximum cell address.
        $max = ($sheet.Cells.Item($row,($vLoc)-1).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()
$stop = Get-Date
New-TimeSpan $start $stop