RDPGate

#######################################
# Name:    Kyle Kowalsky              #
# Date:    May 20, 2016               #
# Purpose: RDP Session Info           #
# Desc:    Get the user and hostname, #
#          start, total, idle, and    #
#          idle total times           #
#######################################

# some global variables
$server = "mwSQL-M1"
$database = "User_Monitor"
$table = "RDP_Session_Logs"

$session = Get-WmiObject -class "Win32_TSGatewayConnection" -namespace "root\cimv2\TerminalServices" -ComputerName mwRDPGate-M1 -Authentication 6

foreach($item in $session) {

    $tunnelID = $item.TunnelId

    $userName = $item.UserName

    $hostName = $item.ConnectedResource

    $startTime = ($item.ConvertToDateTime($item.ConnectedTime)).DateTime

    # Rip apart the ConnectionDuration so we can format it correctly in DD:HH:MM:SS
    $totalTime = $item.ConnectionDuration
        $totalD = $totalTime.Substring(6,2)
        $totalH = $totalTime.Substring(8,2)
        $totalM = $totalTime.Substring(10,2)
        $totalS = $totalTime.Substring(12,2)

        #Proper format
        $totalTime = $totalD+":"+$totalH+":"+$totalM+":"+$totalS

    # Do the same to the IdleTime
    $idleTime = $item.IdleTime

        # the current idle squad
        $idleH = $idleTime.Substring(8,2)
        $idleHs = [int]$idleH*3600
        $idleM = $idleTime.Substring(10,2)
        $idleMs = [int]$idleM*60
        $idleS = $idleTime.Substring(12,2)
        $idleS = [int]$idleS

        # Variables for both math and format versions
        $idleMath = $idleHs+$idleMs+$idleS

        $idleTime = $idleH+":"+$idleM+":"+$idleS

    # If the session was previously in SQL, grab the old IdleTime and break it down
    $prevIdle = (Invoke-Sqlcmd -database $database -serverinstance $server -query "SELECT Top 1 [Idle_Time] AS prevIdle FROM $table WHERE Username = '$userName' AND Hostname = '$hostName' AND Session_Start = '$startTime'").prevIdle

            # the previous idle squad
    if ($prevIdle -ne "000:00:0" -or $prevIdle -ne "00:00:00" -or $prevIdle -ne "000:0:00") # if there is a previous time
        {
            if ($prevIdle -like "*:?") # If seconds are a single digit
            {
                $prevIdle = $prevIdle.PadLeft(8,"0")
                $prevIdle = $prevIdle -replace "-", "" # remove any minus signs

                $pIdleH = $prevIdle.Substring($prevIdle.length-8,3)
                $pIdleHs = [int]$pIdleH*3600 # Convert to seconds for calculation only
                #
                $pIdleM = $prevIdle.Substring($prevIdle.length-4,2)
                $pIdleMs = [int]$pIdleM*60 # Convert to seconds for calculation only
                #
                $pIdleS = $prevIdle.Substring($prevIdle.length-1,1)
                $pIdleS = $pIdleS.PadLeft(2,"0") # Pad the seconds to two digits
                $pIdleS = [int]$pIdleS
            }
            elseif ($prevIdle -like "*:?:*") # If minutes are a single digit
            {
                $prevIdle = $prevIdle.PadLeft(8,"0")
                $prevIdle = $prevIdle -replace "-", ""

                $pIdleH = $prevIdle.Substring($prevIdle.length-8,3)
                $pIdleHs = [int]$pIdleH*3600
                #
                $pIdleM = $prevIdle.Substring($prevIdle.length-4,1)
                $pIdleM = $pIdleM.PadLeft(2,"0")
                $pIdleMs = [int]$pIdleM*60
                #
                $pIdleS = $prevIdle.Substring($prevIdle.length-2,2)
                $pIdleS = [int]$pIdleS
            }
            elseif ($prevIdle -notlike "*:?" -or $prevIdle -notlike "*:?:*")
            {
                $prevIdle = $prevIdle.PadLeft(8,"0")
                $prevIdle = $prevIdle -replace "-", ""

                $pIdleH = $prevIdle.Substring($prevIdle.length-8,2)
                #
                $pIdleHs = [int]$pIdleH*3600
                $pIdleM = $prevIdle.Substring($prevIdle.length-5,2)
                #
                $pIdleMs = [int]$pIdleM*60
                $pIdleS = $prevIdle.Substring($prevIdle.length-2,2)
                #
                $pIdleS = [int]$pIdleS
            }
        } else # if not, make one
        {
            $pIdleHs = '00'
            $pIdleMs = '00'
            $pIdleS = '00'
        }

        # one comparison int
        $pIdleMath = $pIdleHs+$pIdleMs+$pIdleS


    # Get the last Total Idle Time from SQL, and break it down into DD MM HH SS
    $pTIdle = (Invoke-Sqlcmd -database $database -serverinstance $server -query "SELECT Top 1 [Total_Idle_Time] AS pTIdle FROM $table WHERE Username = '$userName' AND Hostname = '$hostName' AND Session_Start = '$startTime'").pTIdle

        # the previous total idle squad
        if ($pTIdle) # if there is a previous time
        {
            $pTIdle = $pTIdle.PadLeft(8,"0")
            $pTIdle = $pTIdle -replace "-", ""

            $pTIdleH = $pTIdle.Substring($pTIdle.length-8,2)
            $pTIdleHs = [int]$pTIdleH*3600
            $pTIdleM = $pTIdle.Substring($pTIdle.length-5,2)
            $pTIdleMs = [int]$pTIdleM*60
            $pTIdleS = $pTIdle.Substring($pTIdle.length-2,2)
            $pTIdleS = [int]$pTIdleS
        } else # if not, make one
        {
            $pTIdleH = '00'
            $pTIdleM = '00'
            $pTIdleS = '00'
        }

        # Make them integers for comparison
        $idleMath = [int]$idleMath
        $pIdleMath = [int]$pIdleMath

        # This IF statement checks what to do with idleMath before calculating the total idle time
        if ($pIdleMath -gt $idleMath) # if the idle timer has reset
            {

                # If there are fewer new seconds than old seconds (like new: 03:40:12 and old: 02:40:16) it'll mess up calculations by resulting in negative numbers. This removes a minute and adds 60 seconds to avoid the problem
                if ($idleS -lt $pIdleS)
                {
                    $idleM -= 1
                    $idleS += 60
                }
                # Adds Seconds, rolls over to minutes as needed, adds leading zeroes
                $tIdleS = [int]$idleS+[int]$pIdleS
                            while ($tIdleS -gt 60)
                                {
                                    $tIdleM += 1
                                    $tIdleS -= 60
                                }
                if ($tIdleS -lt 0)
                    {
                        $tIdleS *= -1
                    }
                $tIdleS = $tIdleS.ToString("00")

                # Minutes
                # if new is less than old, make it greater
                if ($idleM -lt $pIdleM)
                {
                    $idleH -= 1
                    $idleM += 60
                }
                $tIdleM = [int]$idleM+[int]$pIdleM
                            while ($tIdleM -gt 60)
                                {
                                    $tIdleH += 1
                                    $tIdleM -= 60
                                }
                if ($tIdleM -lt 0)
                    {
                        $tIdleM *= -1
                    }
                $tIdleM = $tIdleM.ToString("00")

                # Hours
                $tIdleH = [int]$idleH+[int]$pIdleH
                if ($tIdleH -lt 0)
                    {
                        $tIdleH *= -1
                    }
                $tIdleH = $tIdleH.ToString("00")

            }
        elseif ($idleMath -gt $pIdleMath) # if the user is still Idle
            {

                # Seconds
                if ($idleS -lt $pIdleS)
                {
                    $idleM -= 1
                    $idleS += 60
                }
                $tIdleS = [int]$idleS-[int]$pIdleS
                        while ($tIdleS -gt 60)
                            {
                                $tIdleM += 1
                                $tIdleS -= 60
                            }
                if ($tIdleS -lt 0)
                    {
                        $tIdleS *= -1
                    }
                $tIdleS = $tIdleS.ToString("00")

                # Minutes
                if ($idleM -lt $pIdleM)
                {
                    $idleH -= 1
                    $idleM += 60
                }
                $tIdleM = [int]$idleM-[int]$pIdleM
                        while ($tIdleM -gt 60)
                            {
                                $tIdleH += 1
                                $tIdleM -= 60
                            }
                if ($tIdleM -lt 0)
                    {
                        $tIdleM *= -1
                    }
                $tIdleM = $tIdleM.ToString("00")

                # Hours
                $tIdleH = [int]$idleH-[int]$pIdleH
                if ($tIdleH -lt 0)
                    {
                        $tIdleH *= -1
                    }
                $tIdleH = $tIdleH.ToString("00")

            }

    # Total Idle Seconds

    $tIdleS = [int]$tIdleS+[int]$pTIdleS
             while ($tIdleS -gt 60)
                {
                    $tIdleM += 1
                    $tIdleS -= 60
                }
    $tIdleS = $tIdleS.ToString("00")

    # Total Idle Minutes
    $tIdleM = [int]$tIdleM+[int]$pTIdleM
    $tIdleM = $tIdleM
            while ($tIdleM -gt 60)
                {
                    $tIdleH += 1
                    $tIdleM -= 60
                }
    $tIdleM = $tIdleM.ToString("00")

    # Total Idle Hours

    $tIdleH = [int]$tIdleH+[int]$pTIdleH

    $tIdleH = $tIdleH


    # Adding and Formatting the Total Idle Time
    $totalIdle = [string]$tIdleH+":"+[string]$tIdleM+":"+[string]$tIdleS

    $totalIdle = $totalIdle.PadLeft(8,"0")

    # Out with the old
    Invoke-sqlcmd -database $database -serverinstance $server -query "DELETE FROM $table WHERE Username = '$userName' AND Hostname = '$hostName' AND Session_Start = '$startTime'"

    $cTime = Get-Date

    # In with the new
    Invoke-Sqlcmd -database $database -serverinstance $server -query "INSERT INTO $table (Session_ID,Username,Hostname,Session_Start,Session_Time,Idle_Time,Total_Idle_Time,Last_Update) VALUES ('$tunnelID','$userName','$hostName','$startTime','$totalTime','$idleTime','$totalIdle','$cTime')"
    }