{"id":1744,"date":"2014-01-10T00:00:00","date_gmt":"2014-01-10T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-posh-dba-reading-and-filtering-errors\/"},"modified":"2021-08-24T13:39:48","modified_gmt":"2021-08-24T13:39:48","slug":"the-posh-dba-reading-and-filtering-errors","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/the-posh-dba-reading-and-filtering-errors\/","title":{"rendered":"Reading SQL Server Error Logs and Windows Event Logs with PowerShell &#8211; Get-EventLog and SQLPSX Filtering"},"content":{"rendered":"<div id=\"pretty\">\n<div>\n<p>&#8220;I know I&#8217;m searching for something<br \/>Something so undefined<br \/>that it can only be seen<br \/>by the eyes of the blind<br \/>in the middle of the night.&#8221;<\/p>\n<p><strong>Billy Joel<\/strong><\/p>\n<\/div>\n<h2>Contents<\/h2>\n<ul>\n<li><a href=\"#Toc377044661\"> Reading the Windows Event Viewer 2<\/a>\n<ul>\n<li><a href=\"#_Toc377044662\"> Get-EventLog examples. <\/a>\n<ul>\n<li><a href=\"#Toc377044663\"> Getting entries from the Windows Error Log into Excel 3<\/a><\/li>\n<li><a href=\"#Toc377044664\"> Listing the last day that an entry was made in the Application Event Log <\/a><\/li>\n<li><a href=\"#Toc377044665\"> Listing the System Event Log for the past two hours. 4<\/a><\/li>\n<li><a href=\"#Toc377044666\"> listing the Event Log between two time-periods. 4<\/a><\/li>\n<li><a href=\"#Toc377044667\"> Filtering the error log by the Error types. 5<\/a><\/li>\n<li><a href=\"#Toc377044668\"> Reading errors from just one particular source. 6<\/a><\/li>\n<li><a href=\"#Toc377044669\"> Reading all messages containing a specific string. 6<\/a><\/li>\n<li>S<a href=\"#Toc377044670\">electing events according to a variety of conditions. 6<\/a><\/li>\n<li>S<a href=\"#Toc377044671\">electing the event logs of a number of servers and instances?. <\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#Toc377044672\">Reading the SQL Server Error Log. 7<\/a>\n<ul>\n<li><a href=\"#Toc377044673\"> Accessing SQL Error logs in Online SQL Server Instances. 8<\/a><\/li>\n<li><a href=\"#Toc377044674\"> Accessing SQL Error logs in OffLine SQL Server Instances. 10<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#Toc377044675\"> Applying Filters to the SQL Error Log. 13<\/a>\n<ul>\n<li><a href=\"#Toc377044676\"> SQL Error Log in Online SQL Server Instances. 13<\/a><\/li>\n<li><a href=\"#Toc377044677\"> SQL Error Log in Offline SQL Server Instances. 19<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#Toc377044678\"> Summary. 22<\/a><\/li>\n<\/ul>\n<h2 id=\"Toc377044660\">Introduction<\/h2>\n<p>One of the everyday tasks of any DBA is to look for errors in your database server environment. With SQL Server, we have two major sources of information for doing this: the SQL Server Error Log and the Event Viewer.<\/p>\n<p>When a problem occurs in SQL Server, ranging from a logon failure to a severe error in database mirroring, the first place to look for more information is the SQL Server Error Log. Similarly, if we have a problem related to physical hardware, the disk for example, we will look in the Event Viewer.<\/p>\n<p>Both the SQL Server Error Log and the Event Viewer are designed to be used via their respective graphic user interface. This is fine for one or two servers, but painfully slow for the average DBA who has to read and filter information in many servers. Even when you&#8217;re focusing down on a problem with a single busy server, the added weight of the graphical tool in terms of resources can slow troubleshooting down considerably. It is very important in the day-to-day life of a DBA to have a mechanism to read and filter error messages quickly and unintrusively; a technique for &#8220;mining errors&#8221;.<\/p>\n<p>This is where PowerShell comes in handy. With a relatively simple script, you can read, and filter out just those error messages that you need in a multi-server environment and moreover, format the output to make the information stand out. In this article we will show how to do this, and, if required, include warnings or any other type of event, using the SQL Server Error Log in both an Online and Offline mode as well as messages in the Windows Event Viewer.<\/p>\n<h2 id=\"Toc377044661\">Reading the Windows Event Viewer<\/h2>\n<p>We are going to want to check the server logs automatically for problems or warnings. If, unlike us, you have the time to routinely &#8216;remote&#8217; into each server in turn, then the Windows Event Viewer is the classic way of reading this information.<\/p>\n<p>The official documentation states: &#8220;Windows Event Viewer is a utility that maintains logs about program, security, and system events on your computer. You can use Event Viewer to view and manage the event logs, gather information about hardware and software problems, and monitor Windows security events.&#8221; In other words, the event viewer collects the information about the health of your system.<\/p>\n<p>Every process that starts within the Windows OS opens a communication channel with the OS informing it of its most important actions and events. This means, for example, that if the disk subsystem has a problem or if a service stops, this fact will be viewable in the Windows Event Viewer. In the same way, every SQL Server error message with a severity of 19 or greater is logged in both the SQL Server Error Log and the Event Viewer. Therefore, it&#8217;s important to have a mechanism to constantly monitor\/read the Event Viewer, especially remotely, so you can find information about problems and take any necessary action; perhaps to even prevent a system crash.<\/p>\n<p>PowerShell has a built-in cmdlet to make it easier to access information recorded in Event Viewer, but before we use it, let&#8217;s discuss some basic concepts that will help us to understand how to use it better.<\/p>\n<p>The Event Viewer is a repository for the event logs. With the Event Viewer we can monitor the information about security, and identify hardware, software and system issues. There are three basic Event Logs:<\/p>\n<ul>\n<li>System Log: Stores the events related to the Operational System, as a problem with a driver.<\/li>\n<li>Application Log : Stores the events related to the Applications and programs<\/li>\n<li>Security Log : Stored the events related to security, as invalid logon attempts<\/li>\n<\/ul>\n<p>You can also create a custom event log. There are several third-party tools that have their own event log.<\/p>\n<p>The built-in PowerShell cmdlet to access the Event Viewer is <code>Get-EventLog<\/code>. Figure 7 shows the output when using <code>Get-EventLog<\/code> to read the application event log:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">\tGet-EventLog  \t-LogName  \tApplication <\/pre>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image001.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image001-635x158.png\" alt=\"1921-clip_image001-635x158.png\" width=\"635\" height=\"158\" \/><\/a><\/p>\n<p class=\"caption\">Figure1 &#8211; Properties from Get-EventLog<\/p>\n<p>The <code>Get-EventLog<\/code> cmdlet has a parameter that allows you to read the Events remotely by passing in the name of the Server. Here we are using <code>Get-EventLog<\/code> to read the Security log on server <code>ObiWan<\/code>:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog  -ComputerName  ObiWan  -LogName  Security  <\/pre>\n<h3>Get-EventLog examples<\/h3>\n<h4 id=\"Toc377044663\">Getting entries from the Windows Error Log into Excel<\/h4>\n<div class=\"indent\">\n<p>Two weeks ago your company bought a monitoring software for the SQL Server Servers called <code>ContosoMonitor<\/code> and installed the agent on all servers. This morning you realize that The Servers are not sending monitoring messages. In the installation manual says that every event is recorded by the software in the local Event Viewer, at the Application log but with a specific source named <code>ContosoMonitor<\/code>. You decide to check the Event Viewer for all servers and look for errors from the installed agents, again exporting the output to an Excel spreadsheet with the Servers split into worksheets . You open a PowerShell session from your desktop and type :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-Content c:\\temp\\Servers.txt | ForEach-Object { #A\r\n       Get-Eventlog -ComputerName $_ -LogName Application  -EntryType Error -After (Get-Date).adddays(-1) | \r\n       Sort-Object Time -descending |\r\n       Export-Xls c:\\temp\\ContosoMonitorError.xlsx -AppendWorksheet -WorksheetName $_ #B\r\n}\r\n \r\n#A - Loop in the Servers inside the file Servers.txt\r\n#B - Filter the Event log in the current server of the loop, sorting by descending Date Time  and exporting to a xlsx splitting the servers in worksheets\r\n<\/pre>\n<p>To perform this operation using the Event Viewer GUI, You will need to connect to each Server and filter the Event Viewer by GUI, export to CSV file&#8230;etc. It is a painful process that will leave us wishing we could do it with two command lines of PowerShell. Let&#8217;s talk a little more about the PowerShell solution.<\/p>\n<p>In order to read the Event Viewer, PowerShell has a built-in Cmdlet called <code>Get-EventLog<\/code>. There are some parameters in the <code>Get-EventLog<\/code> that can perform the filtering operation without needing an additional <code>Where-Object<\/code> and using it is faster than using the pipeline. Let&#8217;s take a look.<\/p>\n<div class=\"note\">\n<p class=\"note\">Note You can check out a complete help by typing Get-Help -full <strong>Get-EventLog<\/strong><\/p>\n<\/div>\n<\/div>\n<h4 id=\"Toc377044664\">Listing the last day that an entry was made in the Application Event Log<\/h4>\n<div class=\"indent\">\n<p>This is just a matter of using the <code>-after<\/code> parameter and subtracting 1 day from the actual date :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog   -ComputerName  Obiwan   -LogName  Application       -After ((Get-Date).adddays(-1)) <\/pre>\n<\/div>\n<h4 id=\"Toc377044665\">Listing the System Event Log for the past two hours<\/h4>\n<div class=\"indent\">\n<p>To do this we also use the <code>Get-Date<\/code> methods, but use the <code>-Before <\/code>Parameter:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog   -ComputerName  Obiwan   -LogName  System     -Before ((Get-Date).addHours(-2)) <\/pre>\n<\/div>\n<h4 id=\"Toc377044666\">Listing the Event Log between two time-periods<\/h4>\n<div class=\"indent\">\n<p>To do this, we can join the parameters <code> -after <\/code>and <code>-before<\/code> as well. Imagine if we need list all Securities event logs in the last day, but for the 3 hours ago from the current date\/time:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog   -ComputerName  Obiwan   -LogName  Security   -After ((Get-Date).adddays(-1)     -Before ((Get-Date).addHours(-3)) <\/pre>\n<p>This Table shows the parameters to filter by date\/time:<\/p>\n<table class=\"MsoTableClassic4\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b><i>Parameter Name<\/i><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b><i>Description<\/i><\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>After &lt;Datetime&gt; <\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gets only the events that occur after the specified date and time. Enter a DateTime object, such as the one returnedby the Get-Date cmdlet.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Before &lt;Datetime&gt;<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gets only the events that occur before the specified date and time. Enter a DateTime object, such as the one returned by the Get-Date cmdlet.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><code>Get-EventLog<\/code> has a parameter to filter the event type according to whether they are errors, warnings, information or Audit States, There are also parameters to specify the source of the error and filter by the contents the message itself. This table describes these parameters:<\/p>\n<table class=\"MsoTableClassic4\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b><i>Parameter Name<\/i><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b><i>Description<\/i><\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>EntryType &lt;string[]&gt;<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gets only events with the specified entry type. Valid values are <code>Error<\/code>, <code>Information<\/code>, <code>FailureAudit<\/code>, <code>SuccessAudit<\/code>, and <code>Warning<\/code>. The default is all events.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>-Message &lt;string&gt;<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gets events that have the specified string in their messages. You can use this property to search for messages that contain certain words or phrases. Wildcards are permitted.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>-Source &lt;string[]&gt;<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gets events that were written to the log by the specified sources. Wildcards are permitted.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<h4 id=\"Toc377044667\">Filtering the error log by the Error types<\/h4>\n<div class=\"indent\">\n<p>If we were looking at the Application Log for the ObiWan Server :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog   -ComputerName  Obiwan   -LogName  Application  -EntryType  Error <\/pre>\n<\/div>\n<h4 id=\"Toc377044668\">Reading errors from just one particular source<\/h4>\n<div class=\"indent\">\n<p>If you want to filter by the Source of the all event types. See that wildcards are allowed , so we can use for example , *sql* to filter all events from SQL Server.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog   -ComputerName  Obiwan   -EntryTpe  Error   -LogName  Application  -source  '*sql*'  <\/pre>\n<\/div>\n<h4 id=\"Toc377044669\">Reading all messages containing a specific string<\/h4>\n<div class=\"indent\">\n<p>We can filter by the contents of the message itself. Imagine if you want to filter the word &#8216;&#8221;started&#8221; in the message property :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog   -ComputerName  Obiwan   -LogName  Application  -Message  '*started* ' <\/pre>\n<\/div>\n<h4 id=\"Toc377044670\">Selecting events according to a variety of conditions?<\/h4>\n<div class=\"indent\">\n<p>You can combine the selection of several properties. you may want to filter only the Event Log Application, type Error and source SQL Server in the last day :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog   -ComputerName  Obiwan   -LogName  Application   -EntryTpe  Error  -Source  '*sql*'  -After ((Get-Date).adddays(-1)) <\/pre>\n<p>You may need to query not only the Error entry type, but also Warning. In the table above we see that the <code>EntryType<\/code> is a STRING[] type and this means that I can pass an array with &#8216;Error,Warning&#8217; to the <code>-EntryType<\/code> parameter :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog   -ComputerName  Obiwan   -LogName  Application   -EntryTpe  'Error,Warning'   -Source  '*sql*'  -After ((Get-Date).adddays(-1)) <\/pre>\n<p>This technique also applies to the <code> -Source<\/code> Parameter.<\/p>\n<\/div>\n<h4 id=\"Toc377044671\">Selecting the event logs of a number of servers and instances?<\/h4>\n<div class=\"indent\">\n<p><code>Get-EventLog<\/code> does not accept pipeline input,so I cannot use <code>\"ObiWan\" | Get-EventLog.<\/code><\/p>\n<p>However, , the<code>-Computername<\/code> parameter is a <code>STRING[]<\/code> type, so I can use it with an array. If you want to perform the above operation on the <code>ObiWan<\/code> and <code>QuiGonJinn<\/code> Servers it is just, as the <code> -EntryType<\/code> and <code>-Source<\/code> parameters, use the comma between the names of the servers:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog   -ComputerName @('Obiwan'  ,'QuiGonJinn')  -LogName  Application   -EntryType  Error  -Source  '*sql*'  -After ((Get-Date).adddays(-1)) <\/pre>\n<p>Even better, using a txt file with the name of the servers, you also can do it with the Get-Content Cmdlet<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog   -ComputerName (Get-Content  'c:\\temp\\MyServers')  -LogName  Application   -EntryTpe  Error  -Source  '*sql*'  -After ((Get-Date).adddays(-1)) <\/pre>\n<\/div>\n<h2 id=\"Toc377044672\">Reading the SQL Server Error Log<\/h2>\n<p>Not only does the SQL Server error log write error information but it also records some information about successful operations, such as recovery of a database; and it also includes informational messages, such as the TCP port that SQL Server is listening on. The SQL Server Error Log is simply a repository of events. All these events are logged in order to assist in troubleshooting a potential problem and also to provide key information about the sequence of steps leading up to the problem.<\/p>\n<p>You can view the SQL Server Error Log using SQL Server Management Studio (SSMS). As it is a plain text file you can view it in any text editor From TSQL you can view the results of executing the <code>xp_readerrorlog <\/code>extended stored procedure. By default, the error log is stored at <code>...<br \/> Program Files\\Microsoft SQL Server\\MSSQL.n\\MSSQL\\LOG\\ERRORLOG.<\/code>&#8230; <br \/>The current file is named <code>ERRORLOG<\/code>, and has no extension. The previous files will be named <code>ErrorLog.1, ErrorLog.2<\/code>&#8230;etc. and the SQL Server retains backups of the previous six logs. Figure 1 shows a view for the SQL Server Error Log by the SSMS log viewer.<\/p>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image002.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image002-500x284.png\" alt=\"1921-clip_image002-500x284.png\" width=\"500\" height=\"284\" \/><\/a><\/p>\n<p class=\"caption\">Figure 2- SQL Server Error Log in SQL Server Management Studio<\/p>\n<p>The SSMS user interface works when the SQL Server instance is online, but even works with offline instances in SQL Server 2012 or 2014<\/p>\n<p>The advantage of using PowerShell to read the SQL Server Error Log is that you can filter only the errors and format the output for later reference, for example, writing it to a CSV file or storing it in a SQL Server Table. We&#8217;ll use this technique in some of our DBA checklists in a subsequent article.<\/p>\n<h3 id=\"Toc377044673\">Accessing SQL Error logs in Online SQL Server Instances<\/h3>\n<p>When the SQL Server Instance is online, we can use the SQLPSX <code>Get-SqlErrorLog<\/code> function to read the Error Log. This is part of SQLPSX, but for your convenience I have a stand-alone version that doesn&#8217;t need SQLPSX installed. Let&#8217;s start by using the <code>Get-Help<\/code> cmdlet with the <code>-full<\/code> parameter to see how this function works:<\/p>\n<pre class=\"theme:powershell-output lang:ps\">PS C:\\&gt; Get-Help Get-SqlErrorLog -Full\r\nNAME\r\n    Get-SqlErrorLog\r\n    \r\nSYNOPSIS\r\n    Returns the SQL Server Errorlog.\r\n    \r\nSYNTAX\r\n    Get-SqlErrorLog [-sqlserver] &lt;String[]&gt; [[-lognumber] &lt;Int32&gt;] [&lt;CommonParameters&gt;]\r\n    \r\n    \r\nDESCRIPTION\r\n    The Get-SqlErrorLog function returns the SQL Server Errorlog.\r\n    \r\nPARAMETERS\r\n    -sqlserver &lt;String[]&gt;\r\n        \r\n        Required?                    true\r\n        Position?                    1\r\n        Default value                \r\n        Accept pipeline input?       true (ByValue, ByPropertyName)\r\n        Accept wildcard characters?  \r\n        \r\n    -lognumber &lt;Int32&gt;\r\n        \r\n        Required?                    false\r\n        Position?                    2\r\n        Default value                \r\n        Accept pipeline input?       false\r\n        Accept wildcard characters?  \r\n        \r\n    &lt;CommonParameters&gt;\r\n        This cmdlet supports the common parameters: Verbose, Debug,\r\n        ErrorAction, ErrorVariable, WarningAction, WarningVariable,\r\n        OutBuffer and OutVariable. For more information, type,\r\n        \"get-help about_commonparameters\".\r\n    \r\nOUTPUTS\r\n    System.Data.DataRow\r\n        Get-SqlErrorLog returns an array of System.Data.DataRow.\r\n        \r\n    -------------------------- EXAMPLE 1 --------------------------\r\n    \r\n    C:\\PS&gt;Get-SqlErrorLog \"Z002\\sql2k8\"\r\n        \r\n    This command returns the current SQL ErrorLog on the Z002\\sql2k8 server.\r\nRELATED LINKS\r\n    Get-SqlErrorLog \r\n<\/pre>\n<p>As we can see in the help text we&#8217;ve gotten via the <code>Get-Help<\/code> cmdlet, we can pass in the SQL Server Instance name and the number of the log file, and the default of 0 corresponds to the current log. The <code> -sqlserver<\/code> parameter is mandatory. The SQLPSX version doesn&#8217;t accept pipeline input, so if you use this version you need to use<code> foreach<\/code> loop statment or <code>foreach-object<\/code> cmdlet from an array of the SQL Server instance names . So to list all events in SQL Error Log in the SQL Server Instance <code>R2D2,<\/code> using either version of <code>Get-SqlErrorLog,<\/code> we can use the form:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-SqlErrorLog   -sqlserver  R2D2  <\/pre>\n<p>Or using <code>foreach <\/code>loop statment :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">$Servers  =  Get-Content  c:\\teste\\Servers.txt  ForEach ($Server in $servers) { \r\n    Get-SqlErrorLog -sqlserver $Server\r\n} \r\n<\/pre>\n<p>And <code>foreach-object <\/code>cmdlet<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-Content c:\\teste\\Servers.txt | \r\nForEach-Object { \r\n    Get-SqlErrorLog -sqlserver $_\r\n} \r\n\t<\/pre>\n<div class=\"note\">\n<p class=\"note\">Note If you want performance, avoid the pipeline and the <code>foreach-object <\/code>cmdlet approach. Use the <code>foreach<\/code> loop statment. We will discuss this approach in later articles.<\/p>\n<\/div>\n<p>Figure 3 illustrates the output:<\/p>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image003.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image003-635x302.png\" alt=\"1921-clip_image003-635x302.png\" width=\"635\" height=\"302\" \/><\/a><\/p>\n<p class=\"caption\">Figure 3- Get-SQLErrorLog output<\/p>\n<p>Because the event description is truncated to fit the screen in this format, we can improve the formatting by piping the output to the <code>Format-List<\/code> cmdlet, as shown in Figure 3:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-SqlErrorLog   -sqlserver  R2D2  |  Format-List <\/pre>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image004.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image004-635x269.png\" alt=\"1921-clip_image004-635x269.png\" width=\"635\" height=\"269\" \/><\/a><\/p>\n<p class=\"caption\">Figure 4- Get-SQLErrorLog output piping to Format-List<\/p>\n<p>In the same way, if we want to list the SQL Server Error Log events in log file number 2, we can just use the <code>-lognumber<\/code> parameter:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-SqlErrorLog   -sqlserver  &lt;SQLInstanceName&gt;   -lognumber  2 <\/pre>\n<h3 id=\"Toc377044674\">Accessing SQL Error logs in OffLine SQL Server Instances<\/h3>\n<p>SQL Server 2012 introduced a new feature that allows the Error Log to be read even if the instance is offline. SQL Server has two WMI providers, for Server Events and for Computer Management.Two new WMI classes have been added to the Management provider: the <code>SqlErrorLogFile<\/code> and <code>SqlErrorLogEvent<\/code> classes.<\/p>\n<p>To access these two classes you need to connect to the &#8230; <br \/> <code>Root\\Microsoft\\SqlServer\\ComputerManagement11<\/code> <br \/>&#8230; WMI namespace. Unlike the WMI for Server Events that has a namespace to each instance, the provider for Computer Management covers all SQL Server instances on the machine. You will need to specify the correct instance within the WQL (WMI Query Language).<\/p>\n<p>The account under which the script runs needs to have read permissions, locally or remotely, on the <code>Root\\Microsoft\\SqlServer\\ComputerManagement11<\/code> WMI namespace. It also needs permission to access the folder that contains the SQL Server Error Log File.<\/p>\n<p>The <code>SqlErrorLogFile<\/code> WMI class contains information about the log file itself as we can see in the table 7.1:<\/p>\n<table class=\"MsoTableClassic4\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b><i>Property<\/i><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b><i>Description<\/i><\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>ArchiveNumber<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>The number of the file. Corresponds to the extension of the ERRORLOG file, as 0,1,2&#8230;etc.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>InstanceName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>SQL Server Instance Name<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>LastModified<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Date and time the file was last modified, using WMI date\/time format<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>LogFileSize<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>The size of the log file in bytes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Name<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Name of the file, ERRORLOG.X<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>This WMI Class is interesting if you want to know about the physical log file, but this is not our focus. Because we want the event descriptions and information, we need to use the <code>SqlErrorLogEvent<\/code> WMI Class. Table 7.2 shows the properties from the <code> SqlErrorLogEvent <\/code>class:<\/p>\n<table class=\"MsoTableClassic4\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b><i>Property<\/i><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b><i>Description<\/i><\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>FileName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Name of the SQL Server Error Log file, ERRORLOG.1,.2,.3..etc<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>InstanceName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>SQL Server Instance Name<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>LogDate<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Date and time the event was recorded in the LOG File. Using WMI date\/dime format<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Message<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>The event message<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>ProcessInfo<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Source of the Event (SPID)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>To access the SQL Server Error Log for the default SQL Server instance in the Server R2D2, let&#8217;s use the <code>Get-WMIObject <\/code>cmdlet:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-WmiObject   -computername  R2d2  -Class  \"SqlErrorLogEvent\"   -Namespace  \"Root\\Microsoft\\SqlServer\\ComputerManagement11\"  <\/pre>\n<p>Figure 5 display the cmdlet&#8217;s output :<\/p>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image005.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image005-635x208.png\" alt=\"1921-clip_image005-635x208.png\" width=\"635\" height=\"208\" \/><\/a><\/p>\n<p class=\"caption\">Figure 5- Get-WMIObject Output in the SQLErrorLogEvent Class<\/p>\n<p>You&#8217;ll see that, as well as the error log date and message, we&#8217;re also being distracted by some irrelevant information. As we can see in Figure 4, there are some properties that start with &#8220;__&#8221;. They are called WMI System Properties and are in every WMI Class. Unfortunately the <code>Get-WMIObject<\/code> cmdlet does not provide a parameter to suppress them in the output. An alternative is to select the properties you want to show, piping the output from <code>Get-WMIObject<\/code> to the <code> Select-Object<\/code> cmdlet :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-WmiObject   -Class  \"SqlErrorLogEvent\"  -ComputerName  R2D2  -Namespace  \"Root\\Microsoft\\SqlServer\\ComputerManagement11\"  |\r\nSelect-object  Filename,InstanceName,  Logdate,Message,ProcessInfo  <\/pre>\n<p>But we still have a problem. The LogDate is incomprehensible. Unlike the <code>Get-SQLErrorLog<\/code> where the property<code> LogDate<\/code> is a <code>System.DateTime<\/code> type that uses the OS date\/time format, the <code>LogDate <\/code>property in the WMI Class is a <code> System.String<\/code> and has its own format. You can read more about this in &#8220;<a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ee198928.aspx\">Working with Dates and Times using WMI<\/a>&#8221; at Microsoft Technet. Figure 6 illustrates this:<\/p>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image006.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image006-635x217.png\" alt=\"1921-clip_image006-635x217.png\" width=\"635\" height=\"217\" \/><\/a><\/p>\n<p class=\"caption\">Figure 6- Get-WMIObject Output using Select-Object and displaying the LogDate in a WMI format.<\/p>\n<p>This means that we need to convert the WMI date\/time format to the system date\/time format. Fortunately WMI has a method to perform this operation called <code> ConvertToDateTime<\/code>. We can just use it in the <code>Select-Object<\/code> step:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-WmiObject  -Class \"SqlErrorLogEvent\"  -ComputerName R2D2 -Namespace \"Root\\Microsoft\\SqlServer\\ComputerManagement11\"|\r\nSelect-object FileName,\r\n       InstanceName,\r\n       @{Expression={$_.ConvertToDateTime($_.LogDate)};Label = 'Logdate'},\r\n       Message,\r\n       Processinfo\r\n\t<\/pre>\n<p>Uhaa!!! Now we have a friendly-view format to the <code>Logdate <\/code>property, as the Figure 7 shows :<\/p>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image007.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image007-635x162.png\" alt=\"1921-clip_image007-635x162.png\" width=\"635\" height=\"162\" \/><\/a><\/p>\n<p class=\"caption\">Figure 7- <code>Get-WMIObject<\/code> Output using Select-Object and displaying the <code>LogDate<\/code> property in a user-friendly format<\/p>\n<p>This has now given us a way of gathering information about a SQL Server instance even if SQL Server is offline. We have accessed the log remotely in PowerShell by using WMI. This greatly increases our chances of solving a problem server even if the instance is offline.<\/p>\n<h2 id=\"Toc377044675\">Applying Filters to the SQL Error Log<\/h2>\n<p>If SQL Server does not start, or users have problems logging in, then you really have to search for possible errors. It is a good practice to check for errors and warnings just to be proactive and look for problems before they happen.. So far we saw how to read the events in the SQL Error Log and the Windows Event Viewer, but, as a DBA, we are interested on filtering these events, to look for specific errors and warnings or events that happen at a specific time. This covers how we filter the log messages.<\/p>\n<h3 id=\"Toc377044676\">SQL Error Log in Online SQL Server Instances<\/h3>\n<p>Imagine the situation, where you have been informed that two hours ago the SQL Server <code>ObiWan<\/code>, part of a simple active\/passive cluster, was refusing connections from an XPTO. The Server in question is extremely busy and therefore it is likely to be a bad idea to use a resource-intensive graphic-user interface to diagnose it. You need to find out about what may have happened by filtering the Error Log looking for any error messages from two hours ago. You need to urgently inform your boss of the problem once you have enough information to be able to give an estimate of how long it will take to fix the problem. But the use of SSMS is out of the question. In fact we not only have to read the Error Log from two hours ago, but the filter the information looking for specific errors, but let&#8217;s approach the problem in stages.<\/p>\n<p>PowerShell can easily work with date\/time intervals because we can directly use the <code>Get-Date<\/code> properties. We already covered the <code>Get-Date<\/code> cmdlet in article 2, but let&#8217;s take a look a bit more deeply on it.<\/p>\n<p>As everything in PowerShell is an object with its properties and methods, <code>Get-Date<\/code> returns an instance of <code> System.DateTime<\/code> and this class has a method named <code>Adddays<\/code>. If you don&#8217;t know about this method, Figure 8 display the output of only the methods that start with &#8220;ADD&#8221; (using the PowerShell cmdlet <code>Get-Member<\/code>) because it is all the matters to us now.<\/p>\n<pre class=\"theme:powershell-output lang:ps\">Get-Date | Get-Member<\/pre>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image008.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image008-635x209.png\" alt=\"1921-clip_image008-635x209.png\" width=\"635\" height=\"209\" \/><\/a><\/p>\n<p class=\"caption\">Figure 8 &#8211; Only the methods that start with &#8220;ADD&#8221; from the Get-Date cmdlet<\/p>\n<p>In section 7.1, &#8220;Reading the SQL Server Error Log&#8221;, we saw that this function returns the properties <code>SQLInstanceName<\/code> ,<code>LogDate<\/code>, <code>ProcessInfo<\/code> and <code> Text<\/code> . To filter by date\/time we will use the <code>LogDate<\/code> property and to list the SQL Error Log in the last five days we&#8217;ll just use the <code>Where-Object<\/code> cmdlet , filtering the <code>LogDate<\/code> property using <code>Get-Date<\/code> and a negative number of the days, in this case, -5 , in the <code>Adddays<\/code> method :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-SqlErrorLog -sqlserver ObiWan | \r\nWhere-object { $_.logdate -ge ((Get-Date).adddays(-5)) } \r\n<\/pre>\n<p>But if we want the events from the last 24 hours only? Just use the <code>AddHours<\/code> method:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-SqlErrorLog -sqlserver ObiWan | \r\nWhere-object { $_.logdate -ge ((Get-Date).adddhours(-24)) } \r\n<\/pre>\n<p>The process to filter for Errors is similar, but we will need to pipe to the <code>Where-Object<\/code> Cmdlet to filter the errors but in this case we will use the Text property to look for strings that signify an error:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-SqlErrorLog -sqlserver ObiWan |\r\nWhere-object { (     $_.text -like '*Error*' `\r\n                     -or $_.text -like \"*Fail*\"`\r\n                     -or $_.text -like \"*dump*\"`\r\n                     -or $_.text -like '*IO requests taking longer*'`\r\n                     -or $_.text -like '*is full*' `\r\n                  ) -and ($_.text  -notlike '*found 0 errors*')`\r\n                     -and ($_.text  -notlike '*without errors*')`\r\n             }\r\n\t<\/pre>\n<p>You can see the difficulty that we&#8217;ve had to &#8216;code around&#8217;, can&#8217;t you? Although the Event Viewer has a property that specifies whether the event is an Error, Warning or Information message, the <code>Get-SQLErrorLog <\/code>does not return this information and the error messages are embedded within in the message itself. The warnings sometimes contain text which contains the word &#8216;error&#8217; but which aren&#8217;t actually error events. We don&#8217;t want to see those. This means that we need to filter &#8220;Error&#8221; but exclude &#8220;found 0 errors &#8220;or &#8220;without errors&#8221; and include some messages that do not have the &#8220;error&#8221; inside it, but characterizes an error or warning, as &#8220;is full&#8221; or &#8220;IO request taking longer&#8221;<\/p>\n<p>&#8216;In this example the trick is to use the operators <code>-or<\/code> and <code>-and<\/code> to filter exactly what you need. We can, however, produce neater code by using a RegEx string.<\/p>\n<p>PowerShell works very well with Regex and, generally speaking, most of the string comparisons can be turned to a Regex Expression. It isn&#8217;t easy to understand the Regex patterns, but the result is clear code, without a bunch of the <code>-and<\/code>\/<code>-or<\/code> operators. The same filter conditions used in the example above can be rewritten as:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-SqlErrorLog  -sqlserver  ObiWan  |\r\nWhere-object {$_.Text  -match  '(Error|Fail|IO requests taking longer|is full)'  -and  $_.Text  -notmatch  '(without errors|found 0 errors)' }<\/pre>\n<p>We see how to filter by date\/time and by errors\/warnings separately, but most of the time we prefer to have both type of events together so we just put the two together. In the next example, we filter by errors\/warnings in the last 24 hours on <code>ObiWan<\/code> SQL Server Instance, the code is:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-SqlErrorLog  -sqlserver  ObiWan  |\r\nWhere-object { (      $_.logdate  -ge ((Get-Date).addhours(-24)))`\r\n     -and  $_.Text  -match  '(Error|Fail|IO requests taking longer|is full)'  -and  $_.Text  -notmatch  '(without errors|found 0 errors)'} <\/pre>\n<p class=\"Code\">Now if you are checking for errors on all the servers that you&#8217;re responsible for, you will want to perform the same process, but for more than one SQL Server Instance. In our case let&#8217;s do it to the servers <code>ObiWan<\/code> and <code>QuiGonJinn<\/code>. Remembering the <code>Get-Help<\/code> from <code>Get-SQLErrorLog<\/code> in the first section of this article we noticed that the parameter <code>-sqlserver<\/code> accepts pipeline input and it is a <code>STRING []<\/code> type. This applies to the version attached to this article: I&#8217;ve already shown how to use the SQLPSX version. In the rest of these examples, I&#8217;ll be using the enhanced version which can be downloaded from the head of this article.<\/p>\n<p class=\"Code\">I can pass a list of the servers:<\/p>\n<p>&#8230; by pipeline &#8230;<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">'ObiWan',  'QuiGonJinn'  | Get-SqlErrorLog   |\r\nWhere-object { (      $_.logdate  -ge ((Get-Date).addhours(-24)))`\r\n                    -and  $_.Text  -match  '(Error|Fail|IO requests taking longer|is full)'` \r\n                     -and  $_.Text  -notmatch  '(without errors|found 0 errors)'}<\/pre>\n<p>&#8230; having a txt file with the servers and using the Get-Content Cmdlet by pipeline<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-Content  c:\\temp\\Servers.txt  | \r\nGet-SqlErrorLog   |\r\nWhere-object { (      $_.logdate  -ge ((Get-Date).addhours(-24)))`\r\n                     -and  $_.Text  -match  '(Error|Fail|IO requests taking longer|is full)'`\r\n                    -and  $_.Text  -notmatch  '(without errors|found 0 errors)'} <\/pre>\n<p>&#8230; or having a txt file with the servers and using the <code>Get-Content<\/code> Cmdlet by array in the <code>-sqlserver<\/code> parameter because it is a <code>STRING[]<\/code> , just type:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-SqlErrorLog  -sqlserver (Get-Content  c:\\temp\\Servers.txt)   |\r\nWhere-object { (      $_.logdate  -ge ((Get-Date).addhours(-24)))`\r\n                     -and  $_.Text  -match  '(Error|Fail|IO requests taking longer|is full)'` \r\n                     -and  $_.Text  -notmatch  '(without errors|found 0 errors)'}<\/pre>\n<p>You can also obtain the list of SQL Server instance names from rows in a database table. In this case, I&#8217;m using a database called <code>SQLServerRepository<\/code> with a table called <code>tbl_SQLServerInstanceNames<\/code> on SQL Server instance <code>R2D2<\/code>. The table structure is pretty simple, just one column called <code>SQLServerInstanceName<\/code>.<\/p>\n<p>In this case, you first need to query this table to return the SQL Server instance names using the <code>Invoke-SQLCMD2<\/code> function that is part of the SQLPSX toolkit, and pipe the information to the <code>Get-SQLErrorLog <\/code>cmdlet:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Invoke-Sqlcmd2   -ServerInstance  R2D2  -Query  \"Select SQLServerInstanceName from tbl_SQLServerInstanceNames\"  -Database  \"SQLServerRepository\"  |\r\nGet-SqlErrorLog  -sqlserver (Get-Content  c:\\temp\\Servers.txt)   |\r\nWhere-object { (      $_.logdate  -ge ((Get-Date).addhours(-24)))`\r\n                      -and  $_.Text  -match  '(Error|Fail|IO requests taking longer|is full)'`\r\n                     -and  $_.Text  -notmatch  '(without errors|found 0 errors)'} <\/pre>\n<p>You will notice that the output is sorting by ascending date of the LogDate Property . But what if we want to display the messages in descending order? To do this, we can just pipe the <code>Where-Object<\/code> cmdlets output to the Sort-Object designating the <code>LogDate<\/code> property and using the <code> -descending<\/code> switch parameter :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Invoke-Sqlcmd2   -ServerInstance  R2D2  -Query  \"Select SQLServerInstanceName from tbl_SQLServerInstanceNames\"  -Database  \"SQLServerRepository\"  |\r\nGet-SqlErrorLog  -sqlserver (Get-Content  c:\\temp\\Servers.txt)   |\r\nWhere-object { (      $_.logdate  -ge ((Get-Date).addhours(-24)))`\r\n            -and  $_.Text  -match  '(Error|Fail|IO requests taking longer|is full)'`\r\n           -and  $_.Text  -notmatch  '(without errors|found 0 errors)'}|  Sort-Object  Logdate  -descending <\/pre>\n<p>Now, to return to our scenario where that SQL Server was refusing connections, we need to filter messages from two hours ago and search for some error that might give us a clue as to what the problem is. To be more accurate, we will filter the time to ten minutes before two hours, or 130 minutes. From your desktop, you type:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-SqlErrorLog  -sqlserver  ObiWan   |\r\nWhere-object { (      $_.logdate  -ge ((Get-Date).addminutes(-130)))`\r\n                     -and  $_.Text  -match  '(Error|Fail|IO requests taking longer|is full)'`\r\n                     -and  $_.Text  -notmatch  '(without errors|found 0 errors)'}<\/pre>\n<p>In the output you see some interesting messages. As we can see in the Figure 9, the date\/time of the errors are suspiciously close together and they are close to the date\/time you were informed that SQL Server starts to refuse connections. The Text property displays the exact date\/time that SQL Server stops responding to connections (logon error) and it was after the Dump Error.<\/p>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image009.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image009-635x181.png\" alt=\"1921-clip_image009-635x181.png\" width=\"635\" height=\"181\" \/><\/a><\/p>\n<p class=\"caption\">Figure 9 Reading and Filtering the SQL Server Error Log to solve the connection refused problem<\/p>\n<p>At this point the cause of the problem will become obvious just from reading the output, the question has been answered by a PowerShell one-liner.. The server <code> ObiWan<\/code> is part of a cluster and because of the Dump error, it experienced a failover. For the duration of the failover, where the mechanism stopped the SQL Server service in one node and started it in the other, the connections were refused. It is a normal behavior during a failover. Your job now is to research why the dump happened, but that task is out of the scope of this article.<\/p>\n<p>I&#8217;ve described the bare bones here. In fact, the text message is truncated to fit the screen and as so you&#8217;d usually want to pipe the command line above to the Out-GridView Cmdlet to get a better way of inspecting the errors:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-SqlErrorLog  -sqlserver  ObiWan   |\r\nWhere-object { (      $_.logdate  -ge ((Get-Date).addminutes(-130)))`\r\n                     -and  $_.Text  -match  '(Error|Fail|IO requests taking longer|is full)'  -and  $_.Text  -notmatch  '(without errors|found 0 errors)'}Where-object { (    $_.logdate  -ge ((Get-Date).addminutes(-130)))`\r\n                      -and ($_.text   -notlike  '*found 0 errors*')`\r\n                      -and ($_.text   -notlike  '*without errors*')`\r\n                      -and (  $_.text  -like  '*Error*' `\r\n                      -or  $_.text  -like  \"*Fail*\"`\r\n                      -or  $_.text  -like  \"*dump*\"`\r\n                      -or  $_.text  -like  '*IO requests taking longer*'`\r\n                       -or  $_.text  -like  '*is full*') `\r\n             }  |  Out-GridView<\/pre>\n<p>The text messages are easy to read as the Figure 10 shows:<\/p>\n<p class=\"illustration\"><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image010.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image010-635x310.png\" alt=\"1921-clip_image010-635x310.png\" width=\"635\" height=\"310\" \/><\/a><\/p>\n<p class=\"caption\">Figure 10 Using the <code>Out-GridView<\/code> Cmdlet to achieve more User- friendly view of the Error Log<\/p>\n<p>The <code>Out-GridView<\/code> Cmdlet has a plus. It has the filter options. This means that you use it as well. The Figure 10 is also displaying these options.<\/p>\n<p>The SQL Server Error Log is a repository of events, whether they are errors, warnings or simple information messages. To filter for errors we need to include and exclude some messages at the same line as we did in the conditions above. The message &#8220;is full&#8221; was added, but &#8220;Without Errors&#8221; was added to our exclusion list. This means that if there is a line with both expressions it will be discarded. You may want to add more expressions on that condition to filter your needs more accurately. At some point, your filter conditions could become a bit unmanageable because you could find yourself changing the filter whilst exploring errors in the log. You really need something a bit more simple than the code we&#8217;ve done above. Possibly the best answer to this is to use a Regex but hide the complexity. By using PowerShell&#8217;s feature of variable-substitution in a string, we can keep things simpler. you can create a variable to <code>-Match<\/code> and <code>-NoMatch<\/code> operators, add all the conditions that you want, and use this in the <code>Where-Object<\/code>. This way it is easier for you understand, remove and add new filters for messages whatever you want or need and the search conditions for the <code> Where-Object<\/code> Cmdlet are clearer to read. The code would look like:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">$match  =  '(Error|Fail|IO requests taking longer|is full)'\r\n$nomatch  =  '(without errors|found 0 errors)'\r\nGet-SqlErrorLog  -sqlserver  ObiWan   |\r\nwhere {  $_.Text  -match  $match  -and  $_.Text  -notmatch  $nomatch }<\/pre>\n<p>To add a new message to the match condition, for example &#8220;Warning&#8221; it is just put it at the end of the string:<\/p>\n<pre class=\"theme:powershell-output lang:ps\">$match  =  '(Error|Fail|IO requests taking longer|is full|warning)' <\/pre>\n<p>The same process is used for the <code>-nomatch<\/code> conditions.<\/p>\n<h3 id=\"Toc377044677\">SQL Error Log in Offline SQL Server Instances<\/h3>\n<p>Imagine it: You&#8217;re at your desk analyzing the new &#8216;Always On&#8217; project and you notice a report that , for some reason, the Servers R2D2 and ObiWan stopped start to refuse connections. After you solve the problem and not stop the production, your action could to consolidate the Error Log of the two Servers in the last half hour in an excel spreadsheet, each server in separate worksheets so that you can analyze the events.<\/p>\n<p>From your desktop you just type:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">\"R2D2\",\"ObiWan\" | ForEach-Object { #A\r\n       Get-WmiObject -Class \"SqlErrorLogEvent\"  -ComputerName $_ -Namespace \"Root\\Microsoft\\SqlServer\\ComputerManagement11\"| #B\r\n       Where-Object {$_.ConvertToDateTime($_.LogDate) -ge (Get-Date).addminutes(-30)} | #C\r\n       select        InstanceName,\r\n       @{Expression={$_.ConvertToDateTime($_.LogDate)};Label = 'Logdate'},\r\n       Message,\r\n       Processinfo |\r\n       Sort-Object LogDate -Descending | #D\r\n       Export-Xls -Path \"c:\\Log.xlsx\" -AppendWorksheet -WorksheetName $_ #E\r\n}\r\n \r\n#A - Loop for R2D2 and ObiWan Servers\r\n#B - Accessing the SQL Server Error Log WMI at the server in the current loop\r\n#C - Selecting the properties to display and changing the Logdate property from WMI Date format to the OS date format.\r\n<\/pre>\n<p>As the Figure 11 illustrates, an excel spreadsheet called Log.xlsx is created with the Servers R2D2 and ObiWan split into worksheets, with the last half hour events in descending date\/time order:<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1921-clip_image011small.png\" alt=\"1921-clip_image011small.png\" width=\"370\" height=\"473\" \/><\/p>\n<p class=\"caption\">Figure 11 Consolidated Error Log from ObiWan and R2D2 servers<\/p>\n<p>The same operation by SSMS would be to:<\/p>\n<ul>\n<li>Create a CSV File to each Server<\/li>\n<li>Turn the CSV into an Excel spreadsheet<\/li>\n<li>Sort the date\/time in descending order<\/li>\n<li>Copy each Excel spreadsheet to a new one as a worksheet<\/li>\n<\/ul>\n<p>This is a relatively complex task if compared to just two command lines of PowerShell<\/p>\n<p>We already covered way that you can read the SQL Error Log when the instance is offline by using the WMI class <code>SQLErrorLogEvent<\/code>, which is part of the WMI Computer Management Provider, and <code>Get-WMIObject <\/code>Cmdlet. The process by which one would filter in this case is a bit different to date\/time and to choose the SQL Server instance. First let&#8217;s see the date\/time process.<\/p>\n<p>In the section of this article on offline SQL Server Instances, we saw that the WMI Classes have their own date\/time format and so we need to convert this format to have a friendly-view format, or the system format. To filter by date\/time we need to do the same to the <code>LogDate<\/code> Property but now using the <code>Where-Object<\/code> Cmdlet. In the example below, we are filtering the last one day event messages:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-WmiObject  -Class \"SqlErrorLogEvent\"  -ComputerName R2D2 -Namespace \"Root\\Microsoft\\SqlServer\\ComputerManagement11\"|\r\nWhere-Object { $_.ConvertToDateTime($_.LogDate)  -ge ((Get-Date).adddays(-1))} |\r\nselect FileName,\r\n       InstanceName,\r\n       @{Expression={$_.ConvertToDateTime($_.LogDate)};Label = 'Logdate'},\r\n       Message,             \r\n       Processinfo\r\n \r\n<\/pre>\n<p>We also noticed that the WMI <code>Computer Management<\/code> Provider, unlike WMI for <code>Server Events<\/code>, reports on all SQL Server instances in the Server. This means that, so far, we only read the Error Log from the default SQL Server Instance. The <code>-computername<\/code> parameter in the <code>GET-WMIObject<\/code> refers to the name of the Server, not the SQL Server Instance.<\/p>\n<p>Now imagine that you have five SQL Server Instances in the Server <code>ObiWan <\/code>and you need to read the Error Log from the fourth instance called ObiWan\\INST4, which is, of course, offline. How to perform this operation? In this case, my friend, the Windows Query Language (WQL) is your best and only friend.<\/p>\n<p>In order to read the Error Log in the Server R2D2, specifically the SQL Server Instance R2D2\\INST4, we first need to query the <code>InstanceName<\/code> Property &#8216;INST4&#8217; and so we will use the <code>-Query<\/code> property :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">$WQL = \"Select * from SqlErrorLogEvent where InstanceName = 'INST4'\" \r\nGet-WmiObject  -Query $WQL -ComputerName Obiwan -Namespace \"Root\\Microsoft\\SqlServer\\ComputerManagement11\"|\r\nselect Filename,\r\n       InstanceName,\r\n       @{Expression={$_.ConvertToDateTime($_.LogDate)};Label = 'Logdate'},\r\n       Processinfo,\r\n       message\r\n<\/pre>\n<p>To filter errors, we can do the same process with the <code>Get-SQLErrorLog<\/code> cmdlet using <code> Where-Object,<\/code> or we can use the WQL as well. In this case we need to create the conditions in the WQL using the Message property:<\/p>\n<pre class=\"theme:powershell-output lang:ps\">$WQL  =  \"Select * from SqlErrorLogEvent where (Message like '%Error%' or Message like '%Fail%' ) and (not message like '%Found 0 Errors%') and (not message like '%without errors%')\" <\/pre>\n<p>And to query only the SQL Server Instance INST4, it is just a case of adding the condition in the WQL:<\/p>\n<pre class=\"theme:powershell-output lang:ps\">$WQL  =  \"Select * from SqlErrorLogEvent where (Message like '%Error%' or Message like '%Fail%' ) and (not message like '%Found 0 Errors%') and (not message like '%without errors%')and (InstanceName = 'INST4')\"<\/pre>\n<p>To sort by date, it is also the same process. Just pipe the Sort-Object by <code>LogDate<\/code> before <code>Select-Object<\/code> and after G<code>et-WMIObject.<\/code><\/p>\n<h2 id=\"Toc377044678\">Summary<\/h2>\n<p>In this article we discuss how to read and effectively filter errors, warnings or any other type of event, using the SQL Server Error Log in an Online and Offline SQL Server Instance. We also discovered how to use the Event Viewer and its parameters to filter the events searching for possible issues in the System, Security and Applications Event Logs. <code> <\/code><\/p>\n<p class=\"note\">In the example code, we use the enhanced version of <code>Get-SQLErrorLog<\/code> that does not require the installation of SQLPSX, and which accepts both string arrays and pipleline input. The SQLPSX version can, however, be used for most examples and can be made to operate on several instances by means of the techniques described in the article. The <code>Get-SQLErrorLog<\/code> can be downloaded from the link below.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Reading and filtering SQL Server Error Logs and Windows Event Viewer logs with PowerShell &#8211; Get-EventLog for the Windows event log, SQLPSX Get-SqlErrorLog for online SQL Server instances, and file-based reading for offline instances. Includes practical filtering examples for login errors, specific event IDs, and date ranges.&hellip;<\/p>\n","protected":false},"author":221715,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4170,4635,5920,4150,4151,5651],"coauthors":[6819],"class_list":["post-1744","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database-administration","tag-powershell","tag-powershell-event-log-error","tag-sql","tag-sql-server","tag-the-posh-dba"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1744","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221715"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1744"}],"version-history":[{"count":12,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1744\/revisions"}],"predecessor-version":[{"id":71669,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1744\/revisions\/71669"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1744"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1744"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1744"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1744"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}