ME Trial 7 – Error Log Search

Hidden, ME Trial / By

minion enterprise-01

A quick glance behind, and ahead

So last time, we introduced the Instance Config module.

This time we’ll look at the Error Log Search module.

The Error Log Search Module

The Error Log Search module allows you to set up specific error log search terms in the dbo.ErrorLogSearch table.  This module automatically gathers any search term matches, and logs them in the Collector.ErrorLog table for alerting or reporting.

NOTE: If you want to be alerted on error log search hits, you must set that up yourself. In future releases, Minion Enerprise may have a generic error log search alert; but for now, feel free to use a scheduled alert, report, or to simply use the collected data ad hoc.

What can we do with this? Well, you can very easily set up custom error log searches and alerts to notify you of specific issues. This is another seriously flexible module. You can search for anything in the SQL Server error log, or in the SQL Agent log. Want to know whenever User1 appears in a log? How about SSPI handshake errors? Or corruption errors? You’ve got it.

Objects in the Error Log Search Module

Tables

  • Collector.ErrorLog – Holds the collected error log data pulled back from each server.
  • dbo.ErrorLogSearch – Holds the searches to run on each instance.
  • dbo.ErrorLogSearchServerExceptions – Holds records of servers that have specific error log search term exceptions. If a server doesn’t have an entry here, then it will automatically take part in all the log searches defined in dbo.ErrorLogSearch.  For example: Let’s say we have 20 error log searches defined in the dbo.ErrorLogSearch table, but for one particular server, we only want to run 5 of the searches.  In this case, our exception server would have 15 entries in this table: one for each search it doesn’t want to take part in.

Views

  • Collector.ErrorLogCurrent – Provides the most recent collection of error log search results.
  • Collector.ErrorLogPrevious – Provides the next-to-most recent collection of error log search results.

Jobs

Like most collections in Minion Enterprise, the Error Log Search jobs are divided up by Service Level. By default, Gold level jobs run more frequently than Silver or Bronze jobs.

  • CollectorErrorLog-BRONZE – Calls the error log search executable ErrorLogGet.exe for all servers with ServiceLevel = ‘Bronze’.
  • CollectorErrorLog-GOLD – Calls ErrorLogGet.exe for all servers with ServiceLevel = ‘Gold’.
  • CollectorErrorLog-SILVER – Calls ErrorLogGet.exe for all servers with ServiceLevel = ‘Silver’.

Setup

To set up an error log search, just insert that search to the dbo.ErrorLogSearch table.  From that time forward, search is now valid for all active, managed servers. (You can make exceptions, but we’ll cover that in the next section.)

Take the example of an enterprise-wide search for corruption errors. DBCC CHECKDB detects corruption, and logs it in the SQL Server error logs in the form of Error 823 and Error 824. So we will define one search for 823, and one for 824:

-- Error log search: Error 823
INSERT INTO dbo.ErrorLogSearch
        ( LogNumber
        , LogType
        , Class
        , Search1
        , Search2
        , BeginDate
        , EndDate
        , SortOrder
        , IsActive
        , Comment
        )
VALUES  ( 0  -- LogNumber
        , 1  -- LogType
        , 'Corruption'  -- Class
        , 'Error: 823'  -- Search1
        , ''  -- Search2
        , NULL  -- BeginDate
        , NULL  -- EndDate
        , 'DESC'  -- SortOrder
        , 1  -- IsActive
        , 'High priority: Standard corruption search for all servers.'  -- Comment
        );


 


-- Error log search: Error 824


INSERT INTO dbo.ErrorLogSearch


        ( LogNumber


        , LogType


        , Class


        , Search1


        , Search2


        , BeginDate


        , EndDate


        , SortOrder


        , IsActive


        , Comment


        )


VALUES  ( 0  -- LogNumber


        , 1  -- LogType


        , 'Corruption'  -- Class


        , 'Error: 824'  -- Search1


        , ''  -- Search2


        , NULL  -- BeginDate


        , NULL  -- EndDate


        , 'DESC'  -- SortOrder


        , 1  -- IsActive


        , 'High priority: Standard corruption search for all servers.'  -- Comment


        );


Let’s go over each value of the insert statements. dbo.ErrorLogSearch holds control columns, the xp_readerrorlog parameters, and a couple of columns useful for reporting and documentation:


    

  • LogNumber – This determines which of the several error logs to search on each instance. 0 is always the current error log, 1 is the next most recent, and so on. For our 823 and 824 search, we definitely want to search the current log. We highly recommend adding a LogNumber=1 search for each of these errors; you never know when a new error log was created, and you don’t want to miss an error this serious.
    • 

  • LogType –  LogType = 1 is the SQL Server error log. 2 is the SQL Agent log. We use 1, as corruption errors won’t show up in the Agent log.
    • 

  • Class – This is a name you give the search, to categorize them for your own purposes. Logon searches might have a class of “Security”. Here we used “Corruption”.
    • 

  • Search1 –  The first search criteria. The collector looks for this exact string within the specified error log.
    • 

  • Search2 –  The second search criteria. These are ANDed together. We could have specified Search1 = “Error”, and Search2 = “823”, which would return all entries with both “Error” and “823”. While that would get us all the corruption errors, it would also return any Error that happened to have the number “823” within the string. So, we search for “Error: 823”, as it will appear in the error log, and leave Search2 NULL.
    • 

  • BeginDate –  This is the earliest date to log collected error log search results for an instance. In our example, we are searching only the SQL Server error log, and we set the BeginDate NULL, meaning ME would return all found instances of the error within the current error log. If we set BeginDate to one week ago, ME would only log errors found in the current log that are dated within the last 7 days. Note that this only applies to the first time the search (collection) runs for a given instance. Subsequent collections use the most recent date for that instance in the Collector.ErrorLog table.
    • 

  • EndDate –  This is the latest date to log collected error log search results for an instance. Both BeginDate and EndDate are meant to be used for targeted searches (where you want to examine a specific date range). For long term searches, both values should be NULL.
    • 

  • SortOrder –  Valid values are ASC and DESC.  This orders the error log search results coming from the servers.
    • 

  • IsActive – You can turn searches on or off using IsActive. This is a great way to keep a list of searches and only use certain ones every now and then.
    • 

  • Comment –  A description of the search, for your own use.
    • 



IMPORTANT: By default, there is no alert on collected errors. There is no way to determine what one SQL Server shop will be interested in. However, it is a simple matter to set up an automatic alert or email to notify you of important collected items. For example, you could set up a weekly email to summarize all found error log searches for Class=“Security” and Class=“Corruption”.


A note on performance


SQL Server error logs are simply text files; they aren’t indexable. So, any search on an error log file must (by definition) search the entire file.  It’s possible, then, that you could see some performance lag during error log searches if the SQL Server error log is extremely large.


To minimize this effect, set up a nightly job to cycle the SQL Server error log on every instance, and configure SQL Server to retain 30 days of logs.  This is good log management that we recommend in any case; it has the added benefit here of helping the performance of this process.


Except or disable log searches


To exempt (or except) a server from one or more specific error log searches, insert a row for the server-error search pari, to the dbo.ErrorLogSearchServerExceptions table:


INSERT INTO dbo.ErrorLogSearchServerExceptions ( InstanceID, ErrorSearchID )


VALUES  ( 9, 2 );


The dbo.CollectionExceptionsServer table allows you to turn off a single collection (module) for a particular instance. So, to turn off error log searches for an entire server, insert a row into dbo.CollectionExceptionsServer for that particular instance, using “Collector.ErrorLog” as the CollectionName:


INSERT INTO dbo.CollectionExceptionsServer ( InstanceID, CollectionName )


VALUES  ( 11 , 'Collector.ErrorLog' );


An example: We have 10 error log searches defined in the dbo.ErrorLogSearch table, and we only wants to run 3 of those seraches on Svr9.  So, we should insret 7 rows to dbo.ErrorLogSearchServerExceptions: one for each search that should NOT run for Svr9:


INSERT INTO dbo.ErrorLogSearchServerExceptions ( InstanceID, ErrorSearchID )


VALUES  ( 9, 2 ),


                ( 9, 3 ),


                ( 9, 4 ),


                ( 9, 7 ),


                ( 9, 8 ),


                ( 9, 9 ),


                ( 9, 10 );


Furthermore, Svr20 should never be searched for any errors. So, insert a row for Svr20 to the dbo.CollectionExceptionsServer table:


INSERT INTO dbo.CollectionExceptionsServer ( InstanceID, CollectionName )


VALUES  ( 20 , 'Collector.ErrorLog' );




That’s a pretty comprehensive guide to the Error Log Search module. Of course, write us with questions and comments any time at https://minionware.desk.com/, and get more information on our Minion Enterprise YouTube playlist.


Next time we’ll talk about Wait Statistics, and frequently asked questions!