Using Regular Expressions and Event Viewer with PowerShell

Today a customer asked me to assist him to filter an Event Viewer log file.

The customer figured out a lot of connection's errors with the Oracle database. The errors were being written to the Event Viewer and, as the number of errors was huge, so it was hard to customer to identify all the errors were of the same type (ORA-02067).

I informed the customer that it was possible to use PowerShell and Regular Expressions (RegEx) to help to retrieve the Event Viewer logs.

Let me show an example of how to retrieve the records of the Application log of Event Viewer. I am filtering the logs by the current date and putting the result in a variable.

$logs = Get-EventLog -LogName Application -after $(get-date).Date

But the customer told me that he had the logs in an Event Viewer file (evtx). Therefore, to read the file and store the values in a variable, I used the following command:

$logs = Get-WinEvent -Path C:\temp\events.evtx

I already knew that most of the Event Viewer events contained the following error message:

Exception message: ORA-02067: transaction or savepoint rollback required

I would like to check if there were other errors Oracle, for example, ORA-0205, rather than ORA-02067. Therefore, I used the following RegEx to extract the value of ORA-?????:

“ORA-[\d\.]+”

Through this pattern, I seek for the word ORA followed by a dash ( "-") and at least one decimal.

To avoid receiving the entire log message, for the registries that satisfy the rule, and have to extract only the value "ORA-?????" within the message, I assigned an identifier (errorcode) for the part I would like to get.

“(?<errorid>ORA-([\d\.]+))”

To search the pattern in the $log array, I used a loop (foreach) where I search item by item and I stored the results found in another array ($ErrorCodes) for further grouping.

 
$errorcodes = @()
foreach ($log in $logs)
{
$log.message  -match “(?<errorid>ORA-([\d\.]+))” | Out-Null

if ($Matches.Count -gt 0)
{
$errorcodes += $matches[“errorid”]
}
}
$errorcodes | group | select name, count

For this scenario, I just found the ORA-02067 error as:

Name              Count
—–                  —–
ORA-02067   926

Another way to run the above command is through the PowerShell accelerator [RegEx], which is an abbreviation for System.Text.RegularExpressions.Regex class of the .NET Framework:

$errorcodes = @() $Matches.Clear() foreach ($log in $logs) { $address = [Regex]::Match($log.message, “ORA-([\d\.]+)”) if ( $address.Success) { $address.value } } $errorcodes | group | select name, count

As now we know that there is only the ORA-02067 error, it is also possible to obtain data from the Event Log through the LIKE filter as:

($logs | ? message -like “*ORA-02067*”).count

I hope this post has been helpful. Be sure to consult other items available at:

https://blogs.msdn.microsoft.com/luisdem/tag/powershell/

For more details about Regular Express, take a look at:

Regular Expression Language – Quick Reference https://msdn.microsoft.com/en-us/library/az24scfc(v=vs.110).aspx