SQLEventAnalysis

1.0.9

Classes/SQLActivityGroup.ps1

using namespace System.Collections.Generic
using namespace System.Management.Automation

# Define the base class for our SQL Activity Group
class SQLActivityGroup {
    [int]$SessionId
    [string]$ActivityId
    [datetimeoffset]$FirstTimestamp
    [int]$EventCount
    hidden [System.Collections.ArrayList]$Events
    hidden [hashtable]$_eventGroups = @{}
    
    # Constructor
    SQLActivityGroup([int]$sessionId, [string]$activityId, [System.Collections.ArrayList]$events) {
        $this.SessionId = $sessionId
        $this.ActivityId = $activityId
        $this.Events = $events
        $this.EventCount = $events.Count
        $this.FirstTimestamp = $events.Count -gt 0 ? $events[0].timestamp : [datetimeoffset]::MinValue
        
        # Pre-group events by type for faster access
        foreach ($event in $events) {
            $eventName = $event.name
            if (-not $this._eventGroups.ContainsKey($eventName)) {
                $this._eventGroups[$eventName] = [System.Collections.ArrayList]::new()
            }
            $this._eventGroups[$eventName].Add($event)
        }
    }
    
    # Calculated properties
    [datetime] GetLastTimestamp() {
        return $this.Events.Count -gt 0 ? $this.Events[-1].timestamp : $null
    }
    
    [double] GetDuration() {
        $lastTimestamp = $this.GetLastTimestamp()
        return ($lastTimestamp -ne $null) ? ($lastTimestamp - $this.FirstTimestamp).TotalMilliseconds : 0
    }
    
    [string] GetUsername() {
        foreach ($event in $this.Events) {
            #smell... this is a field that doesn't exist in XE unless the caller has specifically added it
            if ($event.PSObject.Properties.Name -contains "usernameFromContext" -and $event.usernameFromContext) {
                return $event.usernameFromContext
            }
        }
        return $this.Events.username | Select-Object -First 1
    }
    
    [long] GetTotalCPUTime() {
        return ($this.Events | 
            Where-Object { $_.PSObject.Properties.Name -contains "cpu_time" } | 
            Measure-Object -Property cpu_time -Sum).Sum
    }
    
    [long] GetTotalDuration() {
        return ($this.Events | 
            Where-Object { $_.PSObject.Properties.Name -contains "duration" } | 
            Measure-Object -Property duration -Sum).Sum
    }
    
    [bool] HasEventType([string]$eventName) {
        return $this._eventGroups.ContainsKey($eventName)
    }
    
    [array] GetEventsByType([string]$eventName) {
        return $this._eventGroups[$eventName] ?? @()
    }
    
    [array] GetAllEvents() {
        return $this.Events
    }
    
    [string[]] GetEventTypes() {
        return $this._eventGroups.Keys
    }
}