如何用PowerShell脚本计算CSV文件中的SSL会话时长？

我有一份CSV文件，数据格式如下：

"Time","remip","user","msg"
"01:10:47","10.16.2.49","efg","SSL tunnel established"
"01:12:47","11.16.2.34","abc","SSL tunnel established"
"01:13:10","11.16.2.34","abc","SSL tunnel shutdown"
"01:13:26","11.16.2.35","xyz","SSL tunnel established"
"01:13:40","10.16.2.49","efg","SSL tunnel shutdown"
"01:14:08","11.16.2.35","xyz","SSL tunnel shutdown"

需要编写PowerShell脚本，计算每条SSL隧道会话的时长，生成如下格式的输出：

"Time","remip","user","msg","Session Time"
"01:10:47","10.16.2.49","efg","SSL tunnel established",""
"01:12:47","11.16.2.34","abc","SSL tunnel established",""
"01:13:10","11.16.2.34","abc","SSL tunnel shutdown","1m:30sec"
"01:13:26","11.16.2.35","xyz","SSL tunnel established",""
"01:13:40","10.16.2.49","efg","SSL tunnel shutdown","2m:53sec"
"01:14:08","11.16.2.35","xyz","SSL tunnel shutdown","42sec"

实现脚本 #

# 替换为你的输入CSV路径和输出路径
$inputCsv = "your_input_file.csv"
$outputCsv = "session_duration_result.csv"

# 读取原始CSV数据
$csvRecords = Import-Csv -Path $inputCsv
# 用哈希表跟踪活跃会话，键为「IP+用户」组合，值为会话开始时间
$activeSessions = @{}

foreach ($record in $csvRecords) {
    $sessionKey = "$($record.remip)_$($record.user)"
    # 先给每条记录添加空的Session Time字段
    $record | Add-Member -MemberType NoteProperty -Name "Session Time" -Value ""

    if ($record.msg -eq "SSL tunnel established") {
        # 解析并记录会话开始时间，指定格式避免区域设置干扰
        $startTime = [datetime]::ParseExact($record.Time, "HH:mm:ss", $null)
        $activeSessions[$sessionKey] = $startTime
    }
    elseif ($record.msg -eq "SSL tunnel shutdown") {
        # 匹配到对应会话的开始记录时计算时长
        if ($activeSessions.ContainsKey($sessionKey)) {
            $startTime = $activeSessions[$sessionKey]
            $endTime = [datetime]::ParseExact($record.Time, "HH:mm:ss", $null)
            $duration = $endTime - $startTime

            # 按要求格式化时长字符串
            $durationParts = @()
            if ($duration.Minutes -gt 0) {
                $durationParts += "$($duration.Minutes)m"
            }
            $durationParts += "$($duration.Seconds)sec"
            $record."Session Time" = $durationParts -join ":"

            # 会话结束，从哈希表移除记录
            $activeSessions.Remove($sessionKey)
        }
    }
}

# 导出处理后的结果到新CSV
$csvRecords | Export-Csv -Path $outputCsv -NoTypeInformation -Encoding UTF8
# 也可以直接在控制台查看格式化结果
# $csvRecords | Format-Table -AutoSize

脚本说明 #

1. 会话匹配：通过「远程IP+用户」的组合作为唯一标识，用哈希表跟踪每个会话的开始时间，确保建立和关闭记录正确关联。
2. 时间解析：使用ParseExact明确指定时间格式为HH:mm:ss，避免因系统区域设置导致的时间解析错误。
3. 时长格式化：根据时长的分钟数和秒数，自动生成带单位的字符串，没有分钟数时只显示秒数。
4. 结果输出：处理完成后将数据导出到新的CSV文件，也可以直接在控制台查看格式化后的表格。

内容的提问来源于stack exchange，提问作者Sajjad Muslim