PowerShell计算VPN日志时长时日期被修改的解决请求

问题场景 #

使用PowerShell脚本计算VPN连接时长时，脚本可正常完成时长计算，但CSV中的日期被错误修改（原日期2022-12-13变为16-Dec-22），需要保留原始日期完成计算。

原始VPN日志CSV #

"date","time","user","remip","srcip","action","dstip","service","dstintf"
"2022-12-13","04:09:38","was@sil.atlas.pk","10.10.50.182","","tunnel-up","","",""
"2022-12-13","04:09:38","was@sil.atlas.pk","10.10.50.182","","tunnel-up","","",""
"2022-12-13","04:09:38","was@sil.atlas.pk","","10.212.134.200","auth-logon","","",""
"2022-12-13","04:09:57","was@sil.atlas.pk","10.10.50.182","","tunnel-down","","",""
"2022-12-13","04:09:57","was@sil.atlas.pk","10.10.50.182","","tunnel-down","","",""
"2022-12-13","04:09:57","was@sil.atlas.pk","","10.212.134.200","auth-logout","","",""
"2022-12-13","04:09:57","was@sil.atlas.pk","10.10.50.182","","tunnel-down","","",""

用户原始脚本 #

$original_file = "F:\SIL\SILTempVPN\vpnlogs2.csv"
$destination_file = "F:\SIL\SILTempVPN\vpnlogs2.csv"
(Get-Content $original_file) | Foreach-Object {
    $_ -replace 'date','Date' `
       -replace 'dstintf','CompanyNetwork' `
       -replace 'user', 'VPNUserName' `
       -replace 'time', 'Time' `
       -replace 'srcip', 'VPNIPAssigned'`
       -replace 'tunnel-up', 'ITRC VPN User Connected'`
       -replace 'tunnel-Down', 'ITRC VPN User shutdown'`
       -replace 'action', 'VPNStatus'`
              -replace 'remip', 'RemoteIPofUser'
    } | Set-Content $destination_file


$csvdata = Get-Content -Path "F:\SIL\SILTempVPN\vpnlogs2.csv"                      |
           ConvertFrom-Csv                             |
           select @{Name="Time"; Expression={[DateTime] $_.time}},
                  RemoteIPofUser,
                  VPNIPAssigned,
                  VPNUserName,
                  VPNStatus,
                  CompanyNetwork,
                  dstip,
                  Service,
                  @{Name="TimeSpan"; Expression={""}}
$h = @{}
$csvdata | ForEach-Object {
  $key = $_.RemoteIPofUser + ';' + $_.VPNUserName; 
  if ($_.VPNStatus -like '*connected') {
    $h[$key] = $_.time
    return
  } 

  if ($h.ContainsKey($key) -and $_.VPNStatus -like '*shutdown') {
    $_.timespan = $_.time - $h[$key]
    $h.remove($key)
  }
}
$csvdata | export-csv -Path "F:\SIL\SILTempVPN\vpnlogs2.csv"

执行后异常结果 #

"Time","RemoteIPofUser","VPNIPAssigned","VPNUserName","VPNStatus","CompanyNetwork","dstip","service","TimeSpan"
"16-Dec-22 4:09:38 AM","10.10.50.182","","was@sil.atlas.pk","ITRC VPN User Connected","","","",""
"16-Dec-22 4:09:38 AM","","10.212.134.200","was@sil.atlas.pk","auth-logon","","","",""
"16-Dec-22 4:09:57 AM","10.10.50.182","","was@sil.atlas.pk","ITRC VPN User shutdown","","","","00:00:19"
"16-Dec-22 4:09:57 AM","","10.212.134.200","was@sil.atlas.pk","auth-logout","","","",""
"16-Dec-22 4:09:57 AM","10.10.50.182","","was@sil.atlas.pk","ITRC VPN User shutdown","","","",""

期望输出结果 #

"Time","RemoteIPofUser","VPNIPAssigned","VPNUserName","VPNStatus","CompanyNetwork","dstip","service","TimeSpan"
"13-Dec-22 4:09:38 AM","10.10.50.182","","was@sil.atlas.pk","ITRC VPN User Connected","","","",""
"13-Dec-22 4:09:38 AM","","10.212.134.200","was@sil.atlas.pk","auth-logon","","","",""
"13-Dec-22 4:09:57 AM","10.10.50.182","","was@sil.atlas.pk","ITRC VPN User shutdown","","","","00:00:19"
"13-Dec-22 4:09:57 AM","","10.212.134.200","was@sil.atlas.pk","auth-logout","","","",""
"13-Dec-22 4:09:57 AM","10.10.50.182","","was@sil.atlas.pk","ITRC VPN User shutdown","","","",""

问题原因 #

1. 脚本仅将Time字段（仅含时间）转换为DateTime类型，未结合Date字段，导致PowerShell自动填充当前日期，造成日期错误。
2. 输出CSV时，DateTime类型被系统区域设置自动格式化，不符合预期格式。

修正后的脚本 #

$original_file = "F:\SIL\SILTempVPN\vpnlogs2.csv"
$destination_file = "F:\SIL\SILTempVPN\vpnlogs2_result.csv"

# 读取原始CSV并转换列名、状态，同时合并日期时间
$csvdata = Import-Csv -Path $original_file | ForEach-Object {
    [PSCustomObject]@{
        Time              = [DateTime]("{0} {1}" -f $_.date, $_.time)
        RemoteIPofUser    = $_.remip
        VPNIPAssigned     = $_.srcip
        VPNUserName       = $_.user
        VPNStatus         = switch ($_.action) {
            "tunnel-up"   { "ITRC VPN User Connected" }
            "tunnel-down" { "ITRC VPN User shutdown" }
            default       { $_.action }
        }
        CompanyNetwork    = $_.dstintf
        dstip             = $_.dstip
        service           = $_.service
        TimeSpan          = ""
    }
}

# 计算VPN连接时长
$sessionMap = @{}
$csvdata | ForEach-Object {
    $key = "$($_.RemoteIPofUser);$($_.VPNUserName)"
    if ($_.VPNStatus -eq "ITRC VPN User Connected") {
        $sessionMap[$key] = $_.Time
        return
    }

    if ($sessionMap.ContainsKey($key) -and $_.VPNStatus -eq "ITRC VPN User shutdown") {
        $_.TimeSpan = $_.Time - $sessionMap[$key]
        $sessionMap.Remove($key)
    }
}

# 格式化输出字段，确保日期格式符合预期
$csvdata | ForEach-Object {
    # 将时间格式化为指定字符串，保留原日期
    $_.Time = $_.Time.ToString("dd-MMM-yy h:mm:ss tt")
    # 将时长转换为字符串（避免CSV自动格式化）
    if ($_.TimeSpan) {
        $_.TimeSpan = $_.TimeSpan.ToString()
    }
} | Export-Csv -Path $destination_file -NoTypeInformation -Encoding UTF8

修正说明 #

• 合并日期时间：将原date和time字段拼接后转换为完整DateTime，确保时间包含原始日志日期。
• 避免覆盖原文件：生成独立的结果文件，防止原始数据丢失。
• 指定输出格式：将Time字段转换为dd-MMM-yy h:mm:ss tt格式的字符串，确保输出日期与原日志一致；TimeSpan转为字符串，避免格式异常。
• 优化状态转换：用switch语句替代多次-replace，逻辑更清晰易维护。

内容的提问来源于stack exchange，提问作者sajjad muslim