Loading...

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)
Description:

SQL date

Data type:

Date in YYYY-MM-DD format

Description:

SQL time

Data type:

Time in HH:MM:SS.NNN format

Description:

Computer where the SQL Server processes are running

Data type:

String

Description:

Description of the log event

Data type:

String

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

Procedure

To create the template by manually defining the log columns:

  1. From the CommCell Browser, expand Policies.
  2. Right-click Monitoring Policies and then click Manage Templates.
  3. In the Manage Templates dialog box, click Add.

    Follow the instructions in the Create Template wizard.

  4. On the Please select template type page, select User defined to manually define the columns.
  5. On the Please define columns and delimiters for log file page, do the following:
    1. Under Delimiters, select the Space and Tab check boxes.
    2. Select the Treat consecutive delimiters check box.

      For example, if two consecutive spaces are encountered, they are treated as a single space.

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

    4. Click Add to specify a column.
  6. 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:

    1. In the Column Name box, type SQL time.
    2. 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.

    3. If you want to display the column as a search filter in the Log Monitoring application, select the Facet check box.
    4. Click OK.

    Click Add to specify each of the columns.