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.

IMPORTANT: 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.

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”. 
 

Tables

Collector.ErrorLog

dbo.ErrorLogSearch

dbo.ErrorLogSearchServerExceptions

INSERT  INTO dbo.ErrorLogSearchServerExceptions ( InstanceID , ErrorSearchID )
SELECT  ( SELECT    InstanceID
            FROM      dbo.Servers
            WHERE     ServerName = 'Server3'
        ) AS InstanceID
        , ID AS ErrorSearchID
FROM    dbo.ErrorLogSearch
WHERE   ID NOT IN ( 3, 5, 10, 12, 13 );
/* Error log search EXCEPTIONS are the searches we do not want to run; 
so in this query, omit the searches we WANT to run, i.e., 3, 5, 10, 12, 13. */

Stored Procedures

dbo.ErrorLogSearchQuery

• Only instances marked IsSQL=1 and IsActive=1 in dbo.Servers will be represented.
• Only error log search terms marked IsActive=1 in dbo.ErrorLogSearch will be represented.

Views

Collector.ErrorLogCurrent

• ServerName
• ServiceLevel
• Version
• Edition
• Descr

Collector.ErrorLogPrevious

• ServerName
• ServiceLevel
• Version
• Edition
• Descr

Jobs

CollectorErrorLog-GOLD

CollectorErrorLog-SILVER

CollectorErrorLog-BRONZE

Executables

ErrorLogGet.exe

• $Query – This parameter refers to the service level that the collector should operate on. Examples: Gold, Silver, Bronze. For more information on this topic, see the article “Executables and Service Levels”.

Setup

​-- 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
        , NULL  -- 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
        , NULL  -- Search2
        , NULL  -- BeginDate
        , NULL  -- EndDate
        , 'DESC'  -- SortOrder
        , 1  -- IsActive
        , 'High priority: Standard corruption search for all servers.'  -- Comment
        );

• 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.

A note on performance

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' );
 
https://minionware.desk.com/customer/portal/articles/2378789-error-log-search-module
http://www.MinionWare.net