时间戳

首页 > 解决方案 > 使用 PowerSell 在 4 分钟窗口内捕获冗余事件的错误

sql - 使用 PowerSell 在 4 分钟窗口内捕获冗余事件的错误

问题描述

我有一个使用数据库中的 SQL 查询成功捕获应用程序事件的脚本。如果找到 - 它将写入事件日志并向支持团队发送电子邮件。现在，团队希望在四分钟的时间内进行双重检查。您可能会得到两次 ErrorA 和 ErrorD - 并且 ErrorB 和 ErrorC 不会再次发生。

您将如何在第一个循环中进行内部检查。所以第一次你有 ErrorA.1st 和第二次检查两分钟后你会看到 ErrorA.1st = ErrorA.2nd，因此发送电子邮件？

while($true) ### Endless loop - continuely looking for ERRORS to trap for two actions below (Write event log and send email)
{
$connString = "data source=sqlservername,1433;Initial catalog=HugsDB;Integrated Security=True;"
$date= $((get-date).AddSeconds(-120).ToString("MM-dd-yyyy HH:mm:ss"))
$QueryText = "select statement that graps all errors $date"
#SETUP SQL VALUES####
$SqlConnection = new-object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connString
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = $QueryText
#### query the database 
$DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $SqlCommand
$dataset = new-object System.Data.Dataset
$rowCount = $DataAdapter.Fill($dataset)
$sqlConnection.Close()
$sqlConnection.Dispose()

#### IF QUERY FINDS ERRORS @ exact time of query ( could be 1 or more devices reporting an error ) write an event log message & send team an email

        if($rowCount -gt 0) {
        ### assign a unique variable to each unique error on 1st find.
        ForEach ($row in $dataset.Tables[0].Rows) {
        [int]$incre = 0
        $row.exciter_name = $incre.$row.exciter_name

             }
 
        ## sleep 2 minutes before checking to see if we have a repeat of any of the finds in second query
        Start-Sleep -Seconds 120
        ## Another query used to see if a reoccurance of same error.
        ## If the same error occurs send email and write error to event log.
        if($rowCount -gt 0) {
        ForEach ($row in $dataset.Tables[0].Rows) {
        ## NOT SURE HOW TO DO A CHECK TO LOOK FOR REOCCURANCE TO GENERATE EVENT.
        write-Eventlog -LogName Exciter_Log -Source Exciter_Health –EventID 108 -Message "PACE Exciter Health Alert"
        Send-MailMessage -smtpserver "$SMTPServer" -from "$EmailFrom" -to "$EmailTo" -subject "$Subject" -bodyAsHtml "$Body" -credential $anonCredentials
        #################################################################################################################################################
#Second BREAK
Start-Sleep -Seconds 120

}

标签： sqlpowershell

解决方案

听起来您需要跟踪以前的消息并将它们与新消息进行比较。我从代码中假设实际上重复哪个错误消息并不重要，但只有那个是。在下面的示例中，我所做的是在名为 $currentErrors 的变量中捕获所有唯一消息。完成捕获消息后，您需要检查这些先前的错误是否出现在当前集合中。如果发现一个重复错误，$alert 标志设置为 true 并执行您的错误处理。最后，它将所有当前错误消息移动到以前的错误消息中，以便在下次运行时检查。

$currentErrors = @()
$alert = $false
if ($rowCount -gt 0)
{

    foreach ($row in $dataset.Tables[0].Rows)
    {
        # not sure what these 2 lines do?
        [int]$incre = 0
        $row.exciter_name = $incre.$row.exciter_name

        if ($currentErrors -notcontains $row.exciter_name) { $currentErrors += $row.exciter_name } # add error message to $currentErrors if not already added
    }

    # on first run previousErrors will be null so this foreach will do nothing
    foreach ($error in $previousErrors)
    {
        if ($currentErrors -contains $error)
        {
            # previous error found in current errors
            $alert = $true
            break # exit loop once duplicate error is found
        }
    }

    if ($alert)
    {
        Write-EventLog -LogName Exciter_Log -Source Exciter_Health –EventID 108 -Message "PACE Exciter Health Alert"
        Send-MailMessage -smtpserver "$SMTPServer" -from "$EmailFrom" -to "$EmailTo" -subject "$Subject" -bodyAsHtml "$Body" -credential $anonCredentials
    }

    # move currentErrors into previousErrors for next loop
    $previousErrors = $currentErrors
    Start-Sleep -Seconds 120
    
}

推荐阅读