Example: Creating a Monitoring Policy Template for SQL Server Logs
To create a template for Microsoft SQL Server logs, you must first gather the properties for the template and then create the template in the CommCell Console.
Gathering the Properties for the Template
Consider that the SQL log has the following structure:
2014-08-12 12:33:42.28 SQLClient SQL Server is starting at normal priority base (=7)
Based on the sample log line, you can determine that there are four columns, where each column has the following properties:
|Column 1||Column 2||Column 3||Column 4*|
|2014-08-12||12:33:42.28||SQLClient||SQL Server is starting at normal priority base (=7)|
Date in YYYY-MM-DD format
Time in HH:MM:SS.NNN format
Computer where the SQL Server processes are running
Description of the log event
|* In SQL logs, the information that comes after the third column represents the log event description. Therefore, "SQL", "Server", and the rest of the characters, should not be considered separate columns. Instead, the information should be merged into a single column.|
You can also determine that there are two types of delimiting characters used:
- Columns 1, 2 and 3 are separated by a single space.
- Columns 3 and 4 are separated by a tab.
To create the template by manually defining the log columns:
- From the CommCell Browser, expand Policies.
- Right-click Monitoring Policies and then click Manage Templates.
- In the Manage Templates dialog box, click Add.
Follow the instructions in the Create Template wizard.
- On the Please select template type page, select User defined to manually define the columns.
- On the Please define columns and delimiters for log file page, do the following:
- Under Delimiters, select the Space and Tab check boxes.
- Select the Treat consecutive delimiters check box.
For example, if two consecutive spaces are encountered, they are treated as a single space.
- In the Type of log file to be monitored list, select All Files (*.*).
Select this option to ensure that the template recognizes SQL log files even if they have an extension other than .log or .txt.
- Click Add to specify a column.
- On the Add Column page, specify the properties that you gathered for one of the columns.
For example, to define the column displaying the time, do the following:
- In the Column Name box, type SQL time.
- In the Column Datatype box, select Time, and in the Column Type Format box, type HH:MM:SS.NNN.
This format might be available in the list.
- If you want to display the column as a search filter in the Log Monitoring dashboard, select the Facet check box.
- Click OK.
Click Add to specify each of the columns.