Help Center> Application Operations Management> User Guide>
View PDF
Structuring Logs

Structuring Logs

This function only applies to CN North-Beijing1, CN East-Shanghai2, and CN South-Guangzhou regions.

Data includes structured and unstructured data. Structured data can be described using digits or unified data models and has a fixed length and format. Unstructured data has an irregular or incomplete structure and does not have a predefined data model. It is inconvenient to use two-dimensional logic tables of databases to display such data.

Logs are structured by log bucket. Logs in a log bucket are structured using extraction rules. Specifically, logs with a fixed format or high similarity are extracted and irrelevant logs are filtered out.

Precautions

  • Logs are structured by log bucket. Before structuring logs, ensure that you have created a log bucket according to Adding Log Buckets.
  • Most logs in the log bucket must follow certain rules. Otherwise, log structuring is meaningless.
  • You can add only one extraction rule for each log bucket. Click Edit to modify a rule, or click Delete to delete a rule and then add a new one.

Structuring Original Logs

You can extract original logs in log buckets by adding extraction rules and combine the extracted logs for SQL query and analysis.

The following shows how to structure original logs of a Tomcat server:

  1. Log in to the AOM console. In the navigation pane, choose Log > Log Structuring.
  2. Create a log bucket according to Adding Log Buckets. If you have already created a log bucket, skip this step.

    After a log bucket is created, its information is automatically synchronized to the Log Structuring page, as shown in the following figure.

    Figure 1 Log bucket

  3. On the Log Structuring page, select a target log bucket and click Add Extraction Rules.
  4. Select an example log: Query and analyze original logs in the log bucket by time and keyword to preliminarily find the log content to be structured, and then select an example log.

    In the following figure, fields to be structured include IP address, date, method, URL, version, return code, and request size. Select the first log in the list as an example log and click Next.

    Figure 2 Selecting an example log

  5. Select an extraction method.

    • Regular expression: Use regular expression to extract fields.
    • Special characters (not supported currently): Use delimiters such as commas (,), spaces, or other special characters to extract fields.
    Figure 3 Selecting an extraction method

  6. Select a field.

    In the example log, select the log content to be structured. In the dialog box that is displayed, set a name for the selected content. Obtain the fields, such as ip, date, method, url, version, code, and size in sequence, as shown in the following figure.

    If an original log does not comply with the extraction rule, it cannot be extracted and only the original content is displayed.

    Figure 4 Selecting fields

  7. Click Next. Confirm the information and click OK to structure logs.

    After original logs of the Tomcat server are structured, they can be regarded as a two-dimensional table of a database. You can use SQL statements to query and analyze fields, such as ip, date, method, url, version, code, and size.

SQL Query and Analysis

After log structuring, wait about 1–2 minutes for SQL query and analysis.

The following shows how to locate faults in the Tomcat server through SQL query and analysis.

  1. In the navigation pane, choose Log > Log Buckets.
  2. Because SQL query is performed by log bucket, select a target log bucket on the Bucket Log tab page and click Structured logs.

    Figure 5 Configuring structured logs

  3. SQL query and analysis: Set a time range and filter criterion. Enter an SQL statement in the search box. For details about SQL statements supported by AOM, see SQL Query Syntax.

    For example, to query the number of requests whose HTTP return code is greater than or equal to 500 in the last six hours, perform the following operations:

    Select Last 6 hours from the drop-down list in the upper right corner and enter an SQL statement, such as select count(*) where code >= 500 in the search box.

    • For common and complex SQL statements, click to create statistical rules. For details, see Creating Statistical Rules. In the subsequent query, you do not need to manually enter a keyword in the text box. Instead, you can directly click the text box and select a desired statistical rule and SQL statement. After statistical rules are created, AOM collects statistics on values returned by SQL statements and generates metrics. You can view the data trends in a metric graph.
    • For the SQL statement which returns a single value, for example, select count(*) where code >= 500, statistical rules can be created. For the SQL statement which returns multiple values, for example, select count(*) group by ip, statistical rules cannot be created.

SQL Query Syntax

AOM allows you to query and analyze structured logs using SQL statements, including aggregate and mathematical functions. For example, the MIN function is used to calculate the minimum value in the num column.

  • SQL Statements
    Table 1 lists the SQL statements supported by AOM.
    Table 1 SQL statement description

    Statement

    Description

    Example

    AS

    Used to rename a column name.

    select day as day1 , month as month1

    BETWEEN

    Used in the WHERE clause to select a data range between two values, which are in the numeric, text, or date type.

    select * where day between 0 and 20

    COUNT DISTINCT

    Used to query the number of values in a column after duplication.

    select count(distinct name)

    GROUP BY

    Used with aggregate functions to group results of one or more columns.

    select * group by (year),(month)

    LIKE

    Used to search for the specified pattern in a column in the WHERE clause.

    select * where name like 'zhang%'

    ORDER BY

    Used to sort a single column or multiple columns.

    select * order by num asc

    WHERE

    Used to specify the standard to be selected.

    select * where num > 11 or num <= 10
  • Aggregate Functions

    An aggregate function is used to collect statistics on a specified column of structured logs. The aggregate function returns a single value, and is often used with the SELECT statement and the GROUP BY statement. Table 2 lists the aggregate functions supported by AOM.

    Table 2 Aggregate function description

    Function

    Description

    Example

    count(*)

    Used to calculate the number of tuples.

    select count(*)

    count (<column name>)

    Used to calculate the number of values in a column.

    select count(num)

    min (<column name>)

    Used to calculate the minimum value in a column.

    select min(num)

    max (<column name>)

    Used to calculate the maximum value in a column.

    select max(num)

    avg (<column name>)

    Used to calculate the average value in a column.

    select avg(num)

    sum (<column name>)

    Used to calculate the sum of values in a column.

    select sum(num)

    stats (<column name>)

    Used to calculate the number, minimum value, maximum value, average value, and sum of values in a column.

    select stats(num)
  • Mathematical Functions

    A mathematical function is used to perform mathematical operations of a specified column of structured logs. The mathematical function returns multiple values, supporting operators such as + - * / %. It is often used with the SELECT statement. Table 3 lists the mathematical functions supported by AOM.

    Example:

    select log(num) * 2 as log_num where num > 0

    Aggregate and mathematical functions cannot be used at the same time. For example, you can use the select log(num + 2) * 3 statement, but cannot use the select log(sum(num) + 2) * 3 statement to search.

    Table 3 Mathematical function description

    Function

    Description

    Example

    abs (<column name>)

    Used to get absolute values in a column.

    select abs (num)

    cbrt (<column name>)

    Used to get the cube root of values in a column.

    select cbrt(num)

    ceil (<column name>)

    Used to get the least integers greater than or equal to values in a column.

    select ceil(num)

    floor (<column name>)

    Used to get the greatest integers less than or equal to values in a column.

    select floor(num)

    exp (<column name>)

    Used to get exponents with natural constants of values in a column as the base.

    select exp(num)

    log (<column name>)

    Used to get the natural logarithm (log) with natural constant e as the base in a column.

    select log(num)

    log2 (<column name>)

    Used to get the natural logarithm (log) with natural constant 2 as the base in a column.

    select log2(num)

    log10 (<column name>)

    Used to get the natural logarithm (log) with natural constant 10 as the base in a column.

    select log10(num)

    pow (<column name>,n)

    Used to get returned values in a column raised to the power of n.

    select pow(num,n)

    rint (<column name>)

    Used to get the integers that are the nearest to values in a column.

    select rint(num)

    round (<column name>)

    Used to round off values in a column.

    select round(num)

    sqrt (<column name>)

    Used to get the square root of values in a column.

    select sqrt(num)