A search and analysis statement consists of two parts: the search condition and the SQL statement, which are separated by a vertical bar |. If you only need to search for logs without performing statistical analysis, you can omit the vertical bar | and the SQL statement.
[Search condition]|[SQL statement]
Search condition: Specifies the condition for log search. Only logs that meet the condition are returned. For example, you can use status:404 to search for application request logs with a response status code of 404. If the search condition is empty or *, it indicates that there is no search condition, and all logs are searched for.
SQL statement: Performs statistical analysis on logs that meet the search condition and returns the statistical analysis result. For example, you can use status:404 | select count(*) as logCounts to count the number of logs with a response status code of 404.
Note:
Searches are based on segmented logs. After segmentation, raw logs can be searched only if they contain the word specified in the search condition. For example, errorMessage cannot be matched with error, as they are different words. In this case, you need to add a wildcard and search for it with error*. For more explanations and examples of segmentation, see Segmentation and Indexing.
STANDARD storage has been selected for log access. STANDARD_IA does not support key-value search. For details, see Log Storage Overview.
Log fields have been extracted (log structuring has been performed) during log collection.
Key-value indexing was enabled for the fields that require search when configuring indexes.
Use an SQL statement
STANDARD storage has been selected for log access. STANDARD_IA does not support SQL statements for statistical analysis. For details, see Log Storage Overview.
Log fields have been extracted (log structuring has been performed) during log collection.
Key-value indexing and statistics were enabled for the fields that require search when configuring indexes.
Search Condition Syntax
Search conditions support
two syntax rules
:
CQL: CLS Query Language, a dedicated search syntax for CLS. It is designed specifically for log search, is easy to use, and is recommended.
Lucene: Open-source Lucene syntax. Since this syntax is not specifically designed for log search, it has many limits on special symbols, case sensitivity, wildcards, and so on, is relatively cumbersome to use, and is prone to syntax errors. Therefore, it is not recommended.
CQL (Recommended)
Lucene
CQL vs Lucene
Syntax rules
Syntax
Description
key:value
Key-value search, which indicates to query logs with a field (key) whose value contains the value. Example: level:ERROR
value
Full-text search, which indicates to query logs with the full text containing the value. Example: ERROR
AND
Logical AND operator, which is case-insensitive. Example: level:ERROR AND pid:1234
OR
Logical OR operator, which is case-insensitive. Example: level:ERROR OR level:WARNING and level:(ERROR OR WARNING)
NOT
Logical NOT operator, which is case-insensitive. Example: level:ERROR NOT pid:1234 and level:ERROR AND NOT pid:1234
()
Parentheses, which control the precedence of logical operations. Example: level:(ERROR OR WARNING) AND pid:1234
Note:
When parentheses are not used, AND has a higher priority than OR.
" "
Phrase search, which uses double quotation marks to enclose a string, requiring logs to contain all the words within the string and in the same sequence. Example: name:"john Smith"
A phrase search has no logical operators, and the phrase used is equivalent to the query character. Example: name:"and"
' '
Phrase search, which uses single quotation marks to enclose a string and is functionally equivalent to "". When the phrase to be searched contains double quotation marks, you can use single quotation marks to enclose the phrase to avoid syntax errors. Example: body:'user_name:"bob"'.
*
Wildcard, which is used to match zero, one, or multiple characters. Example: host:www.test*.com. Fuzzy prefix search is not supported.
>
Range operator, which indicates the left operand is greater than the right operand. Example: status>400 or status:>400
>=
Range operator, which indicates the left operand is greater than or equal to the right operand. Example: status>=400 or status:>=400
<
Range operator, which indicates the left operand is less than the right operand. Example: status<400 or status:<400
<=
Range operator, which indicates the left operand is less than or equal to the right operand. Example: status<=400 or status:<=400
=
Range operator, which indicates the left operand is equal to the right operand. Example: status=400 (equivalent to status:400)
\\
Escape character. An escaped character represents the literal meaning of the character.
When the searched value contains spaces, :, (, ), >, =, <, ", ', or *, it needs to be escaped. Example: body:user_name\\:bob.
When double quotation marks are used for phrase search, only " and * need to be escaped.
When single quotation marks are used for phrase search, only ' and * need to be escaped.
The unescaped * represents fuzzy search.
key:*
Field of the text type: Queries logs containing the field (key), no matter whether the value is empty. Example: url:*
Field of the long/double type: Queries logs containing the field (key) whose value is not empty. Example: response_time:*
key:""
Field of the text type: Queries logs containing the field (key) whose value is empty (the value is also empty if it contains only delimiters). Example: url:""
Field of the long/double type: Queries logs not containing the field (key) or containing the field whose value is empty. Example: response_time:""
Example
Statement
Searching for logs from a specified server
__SOURCE__:127.0.0.1 or __SOURCE__:192.168.0.*
Searching for logs from a specified file
__FILENAME__:"/var/log/access.log"
Searching for logs containing ERROR
ERROR
Searching for failed logs (with a status code greater than 400)
status>400
Searching failed GET requests in logs (status code greater than 400)
method:GET AND status>400
Searching logs with ERROR or WARNING level
level:(ERROR OR WARNING)
Searching logs that are not INFO level
NOT level:INFO
Phrase search
A string is enclosed in double or single quotation marks for search. Example: name:"john Smith" and filepath:"/var/log/access.log". Compared with searches without quotation marks, a phrase search means that the matched logs should contain all the words in the string and in the same sequence as required in the search condition.
Below are two sample logs with the delimiter of /:
#1 filepath:"/var/log/access.log"
#2 filepath:"/log/var/access.log"
When you use filepath:/var/log/access.log for search, the above two logs will be matched, as it does not involve the sequence of words.
When you use filepath:"/var/log/access.log" for search, only the first log will be matched.
Phrase searches have stricter search conditions and are recommended when long strings are searched for.
Note:
Phrase searches support the use of wildcards (for example, filepath:"/var/log/acc*.log") but not at the beginning of words (for example, filepath:"/var/log/*cess.log").
Wildcards in phrase searches can only match the first 128 words meeting the search condition and return all logs containing these 128 words. The more specific the specified words, the more accurate the search results. This limit does not apply to non-phrase searches.
Fuzzy search
To perform a fuzzy search, you need to add wildcards to the middle or end of words. You can use the asterisk * to match zero, one, or multiple characters, for example:
IP:192.168.1.* can be used to match 192.168.1.1 and 192.168.1.34.
host:www.te*t.com can be used to match www.test.com and www.telt.com.
Note:
The asterisk * cannot be used at the beginning of a word. That is, fuzzy prefix search is not supported.
Fields of the long or double type support a value range but not the asterisk * for a fuzzy search. Example: status>400 and status<500.
If you need to use fuzzy search with prefix specified, you can use the following methods.
Adding a delimiter: For example, if the logs are host:www.test.com and host:m.test.com and you want to query logs containing test in the middle, you can add the delimiter . to search for logs with host:test.
Using the strpos function in SQL: For example, * | select * where strpos(host,'test')>0. However, this method has poorer performance compared to specifying a search condition and is not suitable for scenarios with large volumes of log data.
Phrase searches support the use of wildcards (for example, filepath:"/var/log/acc*.log") but not at the beginning of words (for example, filepath:"/var/log/*cess.log"). Wildcards in phrase searches can only match the first 128 words meeting the search condition and return all logs containing these 128 words. The more specific the specified words, the more accurate the search results. This limit does not apply to non-phrase searches.
To use Lucene syntax, switch the syntax rules here:
Syntax rules
Syntax
Description
AND
Logical AND operator. Example: level:ERROR AND pid:1234
OR
Logical OR operator. Example: level:ERROR OR level:WARNING
NOT
Logical NOT operator. Example: level:ERROR NOT pid:1234
()
Grouping operator, which controls the priority of logical operations, for example: (ERROR OR WARNING) AND pid:1234
:
Colon, which indicates the key field for key-value search, for example: level:ERROR
""
Double quotation marks, which quote a phrase to match logs that contain all the words in the phrase and in the same sequence. Example: name:"john Smith"
*
Wildcard, which is used to match zero, one, or multiple characters. Example: host:www.test*.com. Fuzzy prefix search is not supported.
You can also use key:* to query logs that contain the specified field (key). This statement is equivalent to _exists_:key.
?
Wildcard query, which matches a single character, for example: host:www.te?t.com
Similar to *, it does not support fuzzy prefix search.
>
Range operator, greater than a value, for example: status:>400
>=
Range operator, greater than or equal to a value, for example: status:>=400
<
Range operator, less than a value, for example: status:<400
<=
Range operator, less than or equal to a value, for example: status:<=400
TO
Logical TO operator. Example: request_time:[0.1 TO 1.0]
[]
Range operator, inclusive of boundary values, for example: age:[20 TO 30]
{}
Range operator, exclusive of boundary values, for example: age:{20 TO 30}
\\
Escape symbol, with the escaped character representing the symbol itself, for example url:\\/images\\/favicon.ico
If you do not want to use escape characters, you can also use "" to enclose the search words. Example: url:"/images/favicon.ico". However, note that the words in the double quotation marks are considered as a phrase to match logs that contain all the words in the phrase and in the same sequence.
_exists_
_exists_:key returns logs that contain key. For example, _exists_:userAgent means to return logs that contains the userAgent field.
Note:
The syntax is case-sensitive. For example, AND and OR represent logical search operators, while and and or are regarded as common text.
When multiple search conditions are connected with spaces, they are regarded as in the OR logic. For example, warning error is equivalent to warning OR error.
The following special characters in key search words must be escaped: + - && || ! ( ) { } [ ] ^ " ~ * ? : \\
Use () to group search conditions and clarify the precedence when using the AND and OR operators. Example: (ERROR OR WARNING) AND pid:1234.
Example
Statement
Searching for logs from a specified server
__SOURCE__:127.0.0.1 or __SOURCE__:192.168.0.*
Searching for logs from a specified file
__FILENAME__:"/var/log/access.log" or __FILENAME__:\\/var\\/log\\/*.log
Searching for logs containing ERROR
ERROR
Searching for failed logs (with a status code greater than 400)
status:>400
Searching failed GET requests in logs (status code greater than 400)
method:GET AND status:>400
Searching logs with ERROR or WARNING level
level:ERROR OR level:WARNING
Searching logs that are not INFO level
NOT level:INFO
Searching logs on host 192.168.10.10 that are not INFO level
__SOURCE__:192.168.10.10 NOT level:INFO
Searching logs on host 192.168.10.10 from file /var/log/access.log that are not INFO level
(__SOURCE__:192.168.10.10 AND __FILENAME__:"/var/log/access.log") NOT level:INFO
Searching logs on host 192.168.10.10 with ERROR or WARNING level
__SOURCE__:192.168.10.10 AND (level:ERROR OR level:WARNING)
Searching logs with 4XX status codes
status:[400 TO 500}
Searching logs where the container name in metadata is nginx
__TAG__.container_name:nginx
Searching logs where the container name in metadata is nginx and the request latency is greater than 1 s
__TAG__.container_name:nginx AND request_time:>1
Searching for logs containing the message field
message:* or _exists_:message
Searching logs that do not contain the message field
NOT _exists_:message
Fuzzy search
To perform a fuzzy search in CLS, you need to add wildcards to the middle or end of words, either by using the asterisk * to match zero, one, or multiple characters, or using the question mark ? to match a single character. The following are examples:
IP:192.168.1.* can be used to match 192.168.1.1 and 192.168.1.34.
host:www.te*t.com can be used to match www.test.com and www.telt.com.
Note:
The asterisk * or question mar ? cannot be used at the beginning of a word. That is, fuzzy prefix search is not supported.
Fields of the long or double type support a value range but not the asterisk * or the question mark ? for a fuzzy search. Example: status:[400 TO 500}.
If you need to use fuzzy search with prefix specified, you can use the following methods.
Adding a delimiter: For example, if the logs are host:www.test.com and host:m.test.com and you want to query logs containing test in the middle, you can add the delimiter . to search for logs with host:test.
Using the LIKE syntax in SQL: For example, you can use * | select * where host like '%test%', but this approach has poorer performance compared with specifying a search condition and is not suitable for scenarios with large volumes of log data.
LuceneCompared with Lucene syntax, CQL syntax has been significantly optimized for ease of use and has streamlined some less commonly used features. The main differences are as follows:
Function
Lucene
CQL
Logical Operators
Only uppercase is supported, for example, AND NOT OR.
Both uppercase and lowercase are supported, for example, AND and NOT not OR or.
Special character escaping
Many special characters require escaping. For example, to search for /book/user/login/, it must be escaped as \\/book\\/user\\/login\\/.
Fewer special characters require escaping, so you can directly search for /book/user/login/ .
Keyword search
When a keyword contains tokens, the relationship between tokens is OR. For example, when the token delimiter is /, searching for /book/user/login/ is equivalent to book OR user OR login, which will retrieve many irrelevant logs.
When a keyword contains tokens, the relationship between tokens is AND. For example, when the token delimiter is /, searching for /book/user/login/ is equivalent to book AND user AND login, which aligns with common search habits.
Phrase search
When searching for a phrase, wildcards are not supported. For example, "/book/user/log*/" cannot retrieve /book/user/login/ or /book/user/logout/.
When searching for a phrase, wildcards are supported. For example, "/book/user/log*/" can retrieve /book/user/login/ and /book/user/logout/.
Numeric range search
Search using the syntax timeCost:[20 TO 30].
Search using the syntax timeCost>=20 AND timeCost<=30.
Logs with existing fields
Search using _exists_:key, where key is the field name.
Filters grouped and aggregated data. The difference between HAVING and WHERE is that HAVING is executed on data after grouping (GROUP BY) and before ordering (ORDER BY) while WHERE is executed on the raw data before aggregate.
In some complex statistical analysis scenarios, you need to perform statistical analysis on the raw data first and then perform secondary statistical analysis on the analysis results. In this case, you need to nest a SELECT statement into another SELECT statement. This query method is called nested subquery.
You can use SQL functions to perform more advanced analysis and processing of logs, such as parsing geographic information from IP addresses, converting time formats, splitting and concatenating strings, extracting JSON fields, performing arithmetic operations, and counting unique values.
Note:
SQL statements are case-insensitive. SELECT is equivalent to select.
When the SELECT syntax is used to retrieve data from a log topic, the data in a maximum number of 100 rows can be retrieved by default. To retrieve more data, please use LIMIT syntax to specify the number of rows to be retrieved. Up to one million rows can be specified.
Strings must be enclosed in single quotation marks ''. Unquoted characters or those enclosed in double quotation marks "" indicate field or column names. For example, 'status' represents the string status, while status or "status" refers to the log field status. If a string contains a single quotation mark ', use '' (two single quotation marks) to represent the single quotation mark itself. For example, '{''version'': ''1.0''}' represents the raw string {'version': '1.0'}. If a string contains a double quotation mark ", no special processing is required.
Field names in the SELECT syntax must comply with the Column Name Specification. If a field name does not comply with this specification, it must be enclosed in double quotation marks "".
It is unnecessary to add a semicolon at the end to indicate the conclusion of an SQL statement.
Example
Statement
Counting the number of logs of failed GET requests (with a status code greater than 400)
method:GET AND status:>400 | select count(*) as errorCount
Counting the number of logs of failed GET requests (with a status code greater than 400) per minute
method:GET AND status:>400 | select histogram(__TIMESTAMP__, interval 1 minute) as analytic_time_minute, count(*) as errorCount group by analytic_time_minute limit 1000
Counting the top five URLs with the largest number of requests
* | select URL, count(*) as log_count group by URL order by log_count desc limit 5
2. In the left sidebar, click Search and Analysis.
3. At the top of the page, select the log topic you want to search for.
4. Select the input mode for search and analysis statements. CLS provides two modes to input search and analysis statements.
Interaction mode: The system automatically generates search and analysis statements after you click with the mouse to specify search conditions and statistical analysis rules. This mode is easy to use.
Statement mode: You can directly enter search and analysis statements, which must follow the syntax rules. This mode provides high flexibility.
5. Enter the search and analysis statement, select a time range on the right, and click Search to execute the search.
If the search and analysis statement contains only a search condition, you can view logs matching the search condition on the Raw Logs tab page. The logs are sorted in descending order based on the log time by default.
If the search and analysis statement contains an SQL statement, you can view the analysis result on the Chart tab page. You can also view logs meeting the specified search condition on the Raw Logs tab page to facilitate the comparative analysis of statistical results and raw logs.
