MS-SQL

Background

The MS-SQL Server log analysis App automatically Collect - Read - Parse - Analyzes - Reports all database generated error logs data of the server and presents a comprehensive set of graphs and reports to analyze DB generated data. Use a predefined set of dashboards and gadgets to visualize and address the system software, code written, and infrastructure during development, testing, and production. This MS-SQL Server log analysis App helps measure, troubleshoot, and optimize your data bases integrity, stability and quality with the several visualization and investigation dashboards.

Steps:

1. MS-SQL Server App is running on the MS-SQL default ERRORLOG.
   When adding/editing the logs to XpoLog it is mandatory to apply the correct log type(s) to each of the logs:
   1. mssql - all logs that the application will analyze must have mssql as a log type.
   2. database - all logs that the application will analyze must have database as a log type.
   3. error  - only the Error log must also be configured to have error as a log type.
   
   
2. By default, the error log is located at: Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files
   Therefore the suitable default log path should be: [INSTALL_DIRECTORY]\Microsoft SQL Server\MSSQL{string}.{string}\MSSQL\Log\ERRORLOG{string}
3. Encoding: ERRORLOG encoding is UCS-2 LE BOM which may not be parsed properly using the default character set. Enter Advanced  Settings -> Regional Settings and choose the character set UTF-16LE
4. Once the required information is set, click next and edit the log pattern, this step is crucial to the accuracy and deployment of the MS-SQL Server App. Use the following pattern for the log:
   MSSQL Error log: 
   {date:Date,yyyy-MM-dd HH:mm:ss.SS} {text:event,ftype=eventName} {regexp:Errorcode,refName=Message;ftype=errorCode,Error: (\d+).*}{regexp:Severity,refName=Message,Severity: (\d+).*}{regexp:State,refName=Message,State: (\d+).*}{regexp:User,refName=Message,(login \u0022|user \u0027)[XPLG_PARAM([^\u0027\u0022]+)].*}{regexp:DB,ftype=database;refName=Message,database \u0027([^\u0027]+).*}{regexp:Failure_Message,refName=Message,Reason: ([^\u005B]*).*}{regexp:Client,ftype=source;refName=Message,\u005BCLIENT:\s([^\u005D]*).*} {string:Message,ftype=message}