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

Question

这是我开始使用的PowerShell查询：

$CRM_Serverlist = 'Server-114', 'Server-115', 'Server-118', 'Server-P119'
$CRM_Account = 'domainsvcCRM'
$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进行操作，并尝试查看EventLogRecord对象的Properties属性，看看是否存在执行时间值。

$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

2. 检查其Properties属性：

$evt.Properties

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

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

3. 然后假设我们可以在那里找到这个值，整个脚本将改为这个值，而不是使用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