如何对Get-WinEvent的Message字段提取执行时长并排序

当前使用的PowerShell查询 #

$CRM_Serverlist = 'Server-114', 'Server-115', 'Server-118', 'Server-P119'
$CRM_Account = 'domain\svcCRM'
$svcCRM_cred = Get-Credential -Credential $CRM_Account
ForEach ($CRM_Server in $CRM_Serverlist) {
   Get-WinEvent -ComputerName $CRM_Server -Credential $svcCRM_cred -FilterHashtable @{
       LogName = 'Application'
       ProviderName='MSCRMPlatform'
       Level = 3 # 1 Critical, 2 Error, 3 Warning, 4 Information
       } | select-object message | Format-List -Property message
   }

当前输出示例（已截断SQL查询） #

Message : Query execution time of 14.6 seconds exceeded the threshold of 10 seconds. Thread: 283; 
          Database: CRM_MSCRM; Server:Server-SQL1; Query: IF EXISTS (SELECT * FROM sys.objects ...

Message : Query execution time of 10.9 seconds exceeded the threshold of 10 seconds. Thread: 54; Database: 
          CRM_MSCRM; Server:Server-SQL1; Query: select "a360_connectionrule0".a360_ConnectionId ...
    
Message : Query execution time of 19.3 seconds exceeded the threshold of 10 seconds. Thread: 272; 
          Database: CRM_MSCRM; Server:Server-SQL1; Query: WITH "incident0Security" as (...
    
Message : Query execution time of 53.6 seconds exceeded the threshold of 10 seconds. Thread: 276; 
          Database: CRM_MSCRM; Server:Server-SQL1; Query: select "incident0".a360_EscalationDate2...

需求 #

从所有服务器的Message字段中提取SQL执行时长，按时长从长到短排序后输出，方便对SQL语句进行调优，期望输出格式如下：

Time: 53.6
Message : Query execution time of 53.6 seconds exceeded the threshold of 10 seconds. Thread: 276; 
          Database: CRM_MSCRM; Server:Server-SQL1; Query: select "incident0".a360_EscalationDate2...

Time: 19.3
Message : Query execution time of 19.3 seconds exceeded the threshold of 10 seconds. Thread: 272; 
          Database: CRM_MSCRM; Server:Server-SQL1; Query: WITH "incident0Security" as (...

Time: 14.6
Message : Query execution time of 14.6 seconds exceeded the threshold of 10 seconds. Thread: 283; 
          Database: CRM_MSCRM; Server:Server-SQL1; Query: IF EXISTS (SELECT * FROM sys.objects ...

Time: 10.9
Message : Query execution time of 10.9 seconds exceeded the threshold of 10 seconds. Thread: 54; Database: 
          CRM_MSCRM; Server:Server-SQL1; Query: select "a360_connectionrule0".a360_ConnectionId ...

解决方案 #

修改后的PowerShell脚本如下：

$CRM_Serverlist = 'Server-114', 'Server-115', 'Server-118', 'Server-P119'
$CRM_Account = 'domain\svcCRM'
$svcCRM_cred = Get-Credential -Credential $CRM_Account

# 统一收集所有服务器的目标日志
$allEvents = foreach ($CRM_Server in $CRM_Serverlist) {
    Get-WinEvent -ComputerName $CRM_Server -Credential $svcCRM_cred -FilterHashtable @{
        LogName = 'Application'
        ProviderName='MSCRMPlatform'
        Level = 3
    } | Select-Object Message
}

# 提取时长、排序并格式化输出
$allEvents | ForEach-Object {
    # 用正则匹配提取执行时长数值
    if ($_.Message -match 'Query execution time of (\d+\.\d+) seconds') {
        [PSCustomObject]@{
            ExecutionTime = [double]$matches[1]
            FullMessage = $_.Message
        }
    }
} | Sort-Object -Property ExecutionTime -Descending | ForEach-Object {
    "Time: $($_.ExecutionTime)"
    "Message : $($_.FullMessage)"
    "" # 添加空行分隔条目
}

关键逻辑说明 #

• 统一收集日志：先把所有服务器的目标日志汇总到变量中，避免边查询边输出，便于后续批量处理。
• 提取时长：通过正则表达式精准匹配消息中的执行时长，转换为数值类型确保排序逻辑正确。
• 排序输出：按执行时长降序排列后，按照需求格式输出每条记录，添加空行提升可读性。

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