如何在Get-Winevent的消息字段上运行子字符串

Question

这是我开始的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...

我想要做的是从所有服务器的所有消息中提取时间，按时间排序，以便首先列出运行时间最长的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 ...

如有任何建议，将不胜感激。

Answer 1

不过，正如注释中所述，这可能会起作用，最好不要通过regex来执行，而是尝试查看Properties property of EventLogRecord objects，以查看执行时值是否存在。

$re = [regex] '(?<=Query execution time of )[\d.]+'

$CRM_Serverlist | ForEach-Object {
    Get-WinEvent -ComputerName $_ -Credential $svcCRM_cred -FilterHashtable @{
        LogName      = 'Application'
        ProviderName = 'MSCRMPlatform'
        Level        = 3 # 1 Critical, 2 Error, 3 Warning, 4 Information
    }
} |
Select-Object @{
    Name       = 'Time'
    Expression = { [timespan]::FromSeconds($re.Match($_.Message).Value) }
}, Message | Sort-Object Time -Descending

如何通过查看其中一个事件的Properties属性来了解是否可以获得执行时间：

1. 从您的服务器中选择一个事件：

$evt = Get-WinEvent -ComputerName pickOneServer -Credential $cred -FilterHashtable @{
    LogName      = 'Application'
    ProviderName = 'MSCRMPlatform'
    Level        = 3 # 1 Critical, 2 Error, 3 Warning, 4 Information
} -MaxEvents 1

1. 检查它的Properties属性：

$evt.Properties

如果您在这里看到了经过时间的十进制值，那么您可以按索引来选择它，例如，假设经过的时间在索引2中

$evt.Properties.Value[1] # since index starts in 0 in pwsh

1. 然后假设我们可以在那里找到这个值，整个脚本将改为这个值，而不是使用regex来选择值：

$CRM_Serverlist | ForEach-Object {
    Get-WinEvent -ComputerName $_ -Credential $svcCRM_cred -FilterHashtable @{
        LogName      = 'Application'
        ProviderName = 'MSCRMPlatform'
        Level        = 3 # 1 Critical, 2 Error, 3 Warning, 4 Information
    }
} | Select-Object @{
    Name       = 'Time'
    Expression = { [timespan]::FromSeconds($_.Properties.Value[1]) }
}, Message | Sort-Object Time -Descending