$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)
