BQL (Bonree Query Language) Syntax Guide
What is BQL?
Bonree Query Language (BQL) is a set of commands for searching data.
It builds functionality by chaining simple, independent commands, similar to Linux pipeline commands:
Query | SPL command1 | SPL command2 | …
Each command has an input and an output. The output of Query becomes the input for SPL1, whose output then becomes the input for SPL2, and so on, to produce the final result.
Understanding BQL Syntax
The following sections describe the syntax used by BQL commands.
Supports Both SPL and SQL Syntax
To simplify the use of Bonree software and user adoption, BQL supports both SPL and SQL syntax modes.
Core SPL commands are inherently part of BQL. For SPL commands not directly included in BQL, you can import them as functions or run SPL directly within BQL searches.
The BQL from command incorporates all SQL SELECT command clauses: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, and OFFSET. You can start a BQL search using the FROM clause.
The following search uses SPL syntax within BQL:
from metrics
| stats max('host.cpu.idle') AS idle BY hostId
| where entityName(hostId, "host") in ("aliyun", "aws")
| eval verified=if(idle =*, 1,0)
| fields idle, hostId, verified
Here is the same search using SQL syntax within BQL:
from metrics
where entityName(hostId, "host") in ("aliyun", "aws")
group by hostId
select max('host.cpu.idle') AS idle, hostId
| eval verified=if(idle =*, 1, 0)
BQL is based on commands separated by the pipe character ( | ). BQL is easy to write and read because you append one command after another, rather than adding increasingly deep nesting as in some search languages.
Required and Optional Parameters
BQL commands consist of required and optional parameters.
- Required parameters are shown in angle brackets < >.
- Optional parameters are enclosed in square brackets [ ].
Consider this command syntax:
bin
[<bin-options>...]
<field> [AS <newfield>]
The required parameter is <field>; to use this command, you must at least specify bin <field>.
The optional parameters are [<bin-options>...] and [AS <newfield>].
Field Names
Field names starting with any character other than a-z, A-Z, or an underscore ( _ ) must be enclosed in single quotes ( ' ).
Field names containing any character other than a-z, A-Z, 0-9, or an underscore ( _ ) must be enclosed in single quotes ( ' ). This includes wildcard characters ( * ), dashes ( - ), and space characters.
Quotation Marks
In BQL, quotes are used for specific reasons. The table below indicates when to use different types of quotes:
| Symbol | Description | Example |
|---|---|---|
| Single Quote (') | Use single quotes to enclose field names containing special characters, spaces, dashes, and wildcards. | FROM metrics SELECT avg(cpu_usage) AS 'Avg Usage' |
| Double Quote (") | Use double quotes to enclose all string values. | FROM logs WHERE user="ladron" |
Escape Characters
When using BQL, use the backslash character ( \ ) to ignore characters that have special meaning in the search, such as quotes, pipe characters, and the backslash character itself.
When to Use Escape Characters
The table below illustrates situations where escape characters are necessary:
| Data Type | Rule |
|---|---|
| String Value | String values must be enclosed in double quotes ("). If a string value contains a single quote ( ' ), that single quote does not need to be escaped. E.g., ... WHERE game_name="Tzolk'in: The Mayan Calendar". If a string value contains a double quote ( " ), that double quote must be escaped. Otherwise, the search will misinterpret the end of the string value. E.g., ... WHERE _raw="The user \"vpatel\" isn't authenticated.". If you didn't escape the quotes around the username "vpatel", the search would interpret the string value as "The user ". Since the search couldn't interpret the rest of the WHERE clause, it would return a syntax error. |
| Field Name | Field names containing anything other than letters, numbers, or underscore ( _ ) characters must be enclosed in single quotes ( ' ). If a field name contains a single quote ( ' ), that single quote must be escaped. Otherwise, the search will misinterpret the end of the field name. In the example below, the field name Berlin's values contains a space and must be enclosed in single quotes. Because the field name Berlin's also contains a single quote, that single quote must be escaped. ``... |
Escaping Backslashes
The most common example of escaping backslashes is Windows file paths. Suppose you want to search for the path C:\windows\temp in events. You must escape the backslashes ( \ ):
...WHERE path="C:\\windows\\temp"
Reserved Words
Some words are reserved for BQL syntax and have predefined meanings in the language.
You cannot use reserved words as identifiers for field names, dataset names, function names, etc.
However, you can use a reserved word if you enclose it in single quotes. For example, you cannot use dedup as a field name, but you can use 'dedup'.
The following is a list of reserved words in BQL (case-insensitive):
| from | fromPromQL | dbquery | eval | fields |
|---|---|---|---|---|
| head | rename | sort | stats | where |
| or | and | not | as | by |
| in | like | between | is | null |
| distinct | select | group | order | having |
| limit | true | false | all | asc |
| desc | offset | query | type | step |
| instant | range | source | fetchsize | maxrows |
| timeout | params |
Command Introduction
- Generating Commands: Commands that generate data, used to produce data, e.g.,
from,dbquery,frompromql. - Streaming Commands: Process data row by row, producing one row for each row processed, e.g.,
bucket,eval,fields,head,rename,where. - Transforming Commands: Require all data to produce a result, e.g.,
stats,sort.
Command Processing Flow
Upon receiving a BQL query, the system finds the first command that cannot be pushed down and pushes all preceding commands down to the engine for distributed processing.
Starting a Search
You might ask yourself: "So how do I start searching my data?"
The first step is to make a decision. You need to ask yourself whether you prefer to search using SQL-like commands or using internet-like keywords.
Start with a Generating Command
Your search must begin with a generating command, which you use to generate search results from your data.
After determining which generating command to use, you must decide which data model to search.
What is a Bonree Data Model?
A data model is a collection of data. It's that simple.
When creating a search, you must determine which data model to search. To identify a data model, specify the data model's name.
| Data Model | Description |
|---|---|
| metrics | Refers to measurement data generated by entities during various activities. |
| logs | Refers to log information generated by entities during various activities. |
| entity | Refers to observable domain objects, typically physical or logical objects. |
| events | Refers to unexpected service interruptions or quality degradation related to entities. |
| entity_relationships | Refers to relationships between entities. |
| records | Refers to contextual information generated by entities during activities. |
| dictionarys | Refers to enumeration constant information in the model, often stored numerically and requiring translation for display. |
| tags | Refers to label information for entities, typically used for management or analysis. |
| meta | Refers to data describing the model, including model attributes, attribute types, etc. |
| profile (Future Support) | |
| tracing (Future Support) |
Searching a Data Model
Searching a data model is straightforward.
Use the from command to read data located in any type of data model. The only required syntax is:
from <datamodel>
This simple search returns all data in the data model. The amount of data might be large. You should try to narrow down the results returned by the data model by adding filters to your search. You can use filters to specify what you are looking for or what you want to exclude from the data.
To show you how this works, here are a few examples:
Querying Metrics (Future Support)
from metrics
where entityName(hostid, "host") in ("aliyun", "aws")
group by hostid
select avg(host.cpu.idle) as idle
| fields idle, entityAttr(hostid, "customizedName", "host"), entityName(hostid, "host")
Querying Logs
from logs(rizhiyi)
where serviceId < 1 and logStatus = "error" AND monitorTime> "2013-22-12:12:21:12" AND TRUE
group by logStatus
select avg(serviceId) as avgValue,logStatus
order by logStatus,avgValue asc
limit 100
offset 0
Querying Events
from events
where event_type = "one.detectionEvent"
select eventLevel, eventType, envId
Querying Entities (Future Support)
FROM entity.host
WHERE kind!="internal"
SELECT kind, status
Searching a Specific Time Range
When creating a search, try to specify only the date or time you are interested in. Specifying a narrow time range is a good way to filter data in your dataset and avoid generating more results than you actually need.
Here are some examples:
- To search data using an exact date range, for example, from 8 PM on Oct 15 to 8 PM on Oct 22, use the time format
%Y-%m-%d:%H:%M:%Sand specify the dates, e.g.,time>="2019-10-15:20:00:00" time<"2019-10-22:20:00:00".
Combining Commands
You can combine commands. The pipe character ( | ) is used to separate the syntax of one command from the syntax of the next command.
The following example reads data from the metrics data model and then pipes that data to the eval command. You can use the eval command to evaluate an expression. The result of the expression is placed into a field in the search results returned to you.
This search takes data from the bytes field in the metrics data model and converts the bytes to kilobytes. The result is stored in a new field named kbytes.
from metrics | eval kbytes = bytes / 1024
Grouping Search Results
The from command also supports aggregation using the GROUP BY clause in conjunction with aggregate function calls in the SELECT clause, as shown below:
FROM metrics WHERE earliest=-5m@m AND latest=@m GROUP BY host SELECT sum(bytes) AS sum, host
Filtering Data
When aggregating data, sometimes you want to filter based on the results of the aggregate functions. Use the HAVING clause to filter after aggregation, as shown below:
FROM entity.host GROUP BY host SELECT sum(bytes) AS sum, host HAVING sum > 1024*1024
This example returns rows only for hosts where the sum of bytes is greater than 1 megabyte (MB).
Single-Value Calculation Functions
Quick Reference
| Function Type | Supported Functions and Syntax | Description |
|---|---|---|
| Comparison & Conditional | if(<predicate>,<true_value>,<false_value>) | Returns <true_value> if the <predicate> expression evaluates to TRUE. Otherwise returns <false_value>. |
in(<value>,<list>) | Returns TRUE if one of the values in the list matches the value you specify. | |
like(<str>,<pattern>) | Returns TRUE if the string value matches the pattern. | |
| Entity Functions | entityName(<entityId>,<entityType>) | Queries entity information using the entity ID and entity type. |
entityAttr(<entityId>,<attrName>,<entityType>) | Queries entity information using the entity ID, entity type, and attribute name. | |
entityTag(<entityId>,<entityType>) | Queries entity information using entity tags. |
Comparison and Conditional Functions
if(<predicate>, <true_value>, <false_value>)
Returns <true_value> if the <predicate> expression evaluates to TRUE; otherwise, the function returns <false_value>.
Basic Example
... | eval err=if(error == 200, "OK", "Error")
in(<value>, <list>)
Returns TRUE if one of the values in the list matches the value you specify. This function takes a comma-separated list of values.
Usage
The following syntaxes are supported:
| Context | Syntax |
|---|---|
| WHERE Clause | ...WHERE in(<value>, [<list>]) or ...WHERE <value> in ([<list>]) |
| eval Command | eval new_field=if(in(<value>, [<list>]), "true_value", "false_value") |
| where Command | where in(<value>, [<list>]) |
Basic Examples
... | where in(status, "400", "401", "403", "404")
... | eval error=if(in(status, "error", "failure", "severe"),"true","false")
... | eval error=if(in(status, "404","500","503"),"true","false") | stats count() by error
like(<str>, <pattern>)
This function returns TRUE only if str matches the pattern. The match can be exact or use wildcards:
- Use the percent sign ( % ) as a wildcard to match multiple characters.
- Use the underscore ( _ ) character as a wildcard to match a single character.
Usage
<str> can be a field name or a string value. <pattern> must be a string expression enclosed in double quotes.
The following syntaxes are supported:
| Context | Syntax |
|---|---|
| WHERE Clause | ...WHERE like(<str>, <pattern>) or ...WHERE <str> LIKE <pattern> |
| eval Command | eval new_field=if(like(<str>, <pattern>), ...) |
| where Command | where like(<str>, <pattern>) |
Basic Examples
... | eval is_a_foo=if(like(field, "foo%"), "yes a foo", "not a foo")
... | where like(ipaddress, "198.%")
Entity Functions
entityName(<entityId>,<entityType>)
Queries entity information using the entity ID and entity type.
Usage
<entityId> is a field name. <entityType> must be a string expression enclosed in double quotes.
The following syntaxes are supported:
| Context | Syntax |
|---|---|
| WHERE Clause | ...where entityName(hostid, "host") in ("aliyun", "aws") |
| eval Command | eval new_field=entityName(hostid, "host") |
| where Command | where in (entityName(hostid, "host"), "aliyun", "aws") |
Basic Example
from metrics
where entityName(hostid, "host") in ("aliyun", "aws")
group by hostId
select avg(host.cpu.idle) as idle, hostId
| fields idle, entityName(hostid, "host")
entityAttr(<entityId>,<attrName>,<entityType>)
Queries entity information using the entity ID, entity type, and attribute name.
Usage
<entityId> is a field name. <attrName> and <entityType> must be string expressions enclosed in double quotes.
The following syntaxes are supported:
| Context | Syntax |
|---|---|
| WHERE Clause | ...where entityAttr(hostid, "customizedName", "host") in ("aliyun", "aws") |
| eval Command | eval new_field=entityAttr(hostid, "customizedName", "host") |
| where Command | where in (entityAttr(hostid, "customizedName", "host"), "aliyun", "aws") |
Basic Example
... | fields idle, entityAttr(hostid, "customizedName", "host"), entityName(hostid, "host")
entityTag(<entityId>,<entityType>)
Queries entity information using entity tags.
Usage
<entityId> is a field name. <entityType> must be a string expression enclosed in double quotes.
The following syntaxes are supported:
| Context | Syntax |
|---|---|
| WHERE Clause | ...where entityTag(hostid, "host") in ("aliyun", "aws") |
| eval Command | eval new_field=entityTag(hostid, "host") |
| where Command | where in (entityTag(hostid, "host"), "aliyun", "aws") |
Basic Example
... | fields idle, entityTag(hostid, "host")
Statistics and Charting Functions
Quick Reference
| Function Type | Supported Functions and Syntax | Description |
|---|---|---|
| Aggregate | avg(<value>) | Returns the average of the values in a field. |
count(<value>) | Returns the count of occurrences in a field. | |
distinctCount(<value>) | Returns the count of distinct values in a field. | |
max(<value>) | Returns the maximum value in a field. | |
min(<value>) | Returns the minimum value in a field. | |
perc(<value>,<percentile>) | Returns the nth percentile of values in a numeric field. | |
sum(<value>) | Returns the sum of the values in a field. | |
| Event Order | first(<value>) | Returns the first seen value in a field. |
last(<value>) | Returns the last seen value in a field. |
Aggregate Functions
avg(<value>)
Returns the average of the values in a field.
Basic Example
... | stats avg(size) BY host
count(<value>)
Returns the count of occurrences in a field.
Basic Example
...| stats count(status) AS count_status BY sourcetype
distinctCount(<value>)
This function returns the number of distinct values in a field.
Basic Example
...| stats distinctCount(device) AS numdevices
max(<value>)
This function returns the maximum value in a field.
Basic Example
This example returns the maximum value of the size field.
... | stats max(size)
min(<value>)
This function returns the minimum value in a field.
Basic Example
from logs | stats min(size), max(size)
perc(<value>,<percentile>)
This function returns an approximate percentile based on the values in a numeric field.
These functions return the nth percentile point of the values in a numeric field. You can think of it as an estimate of the starting point for the top N%. For example, the 95th percentile means that 95% of the values in the field are below the estimate, while 5% of the values in the field are above the estimate.
Valid percentiles are floating-point numbers between 0 and 100, such as 99.95.
Basic Example
...| stats perc(score,95), perc(score,50), perc(score,25)
sum(<value>)
Returns the sum of the values in a field.
Basic Example
...| stats sum(bytes) AS "total bytes" by date_hour
Event Order Functions
first(<value>)
Returns the first seen value in a field.
Basic Example
...| stats first(field1)
last(<value>)
Returns the last seen value in a field.
Basic Example
FROM logs WHERE sourcetype="secure"
| fields _time, source, _raw
| stats last(raw)
Retrieval Commands
Quick Reference
| Command | Description | Example |
|---|---|---|
| eval | Evaluates an expression and puts the resulting value into a search result field. | Example: Create a new field containing a calculated result. Create a new field called in each event. Calculate speed velocity by dividing the value in the distance field by the value in the time field. eval velocity=distance/time |
| fields | Keeps or removes fields from the search results based on the field list you specify. | Example: Specify a list of fields to include in the search results. Return only the host and src fields in the search results. fields host, src |
| from | Retrieves data from a data model (e.g., metrics, logs, time, entities). The from command has flexible syntax, allowing you to start a search using either a FROM clause or a SELECT clause. | Example: Return data from the last 5 minutes in logs. Group the results by host. Calculate the sum of the bytes field. Return the sum and the host field where the sum of bytes is greater than 1 MB. FROM logs WHERE time ≥ 2024-11-27:17:13:12 GROUP BY host SELECT sum(bytes) AS sum, host HAVING sum > 1024*1024 |
| head | Returns the first N specified results in search order. | Example: Return up to 50 results. head 50 |
| rename | Renames one or more fields. | Example: Rename a field containing special characters. Rename the field ip-add to IPAddress. Field names containing anything other than a-z, A-Z, 0-9, or "_" require single quotes. rename 'ip-add' AS IPAddress |
| sort | Sorts all results based on the specified fields. | Example: Sort the results first by the surname field in ascending order, then by the firstname field in descending order. sort surname, -firstname |
| stats | Calculates aggregate statistics on the result set, such as average, count, and sum. | Example: Take the incoming result set and calculate the sum of the bytes field, grouped by the values in the host field. stats sum(bytes) BY host |
| where | Filters search results based on the outcome of a Boolean expression. | Example: Use the like comparison operator and the percent sign ( % ) as a wildcard. This example returns all results where the ipaddress field contains a value starting with "192.". where ipaddress like "192.%" Example: Filter using a field-value pair. where host="www1" |
from Command
Command Syntax Details
FROM <datamodel>
[ WHERE ( <predicate-expression> [<logical-operator> <predicate-expression>] ) ... ]
[ GROUP BY ( <expression>[,<expression>... ] | span ( <field> ) | span ( <field>, [<int>]<timescale> ) | <field> span=( [<int>]<timescale> ) ]
[ (SELECT | SELECT DISTINCT) <expression> [, <expression>] ... ]
[ HAVING <expression> ]
[ ORDER BY <expression>[,<expression>... ] [ASC | DESC] ]
[ LIMIT <integer> ]
[ OFFSET <integer> ]
Required Parameters
datamodel
Syntax: <datamodel>
Description: A Bonree data model.
Optional Parameters
WHERE clause
Syntax: WHERE <predicate-expression> [<logical-operator> <predicate-expression>...]
Description: Filters data using predicate expressions. When specifying multiple predicate expressions, you must specify logical operators between the expressions.
Use the WHERE clause to filter data before using other clauses that involve aggregation. For example, the following search includes aggregation in the SELECT clause. The WHERE clause filters the data by narrowing down events based on a time range. The filtered data is then passed to the SELECT clause:
FROM main WHERE earliest=-5m@m AND latest=@m GROUP BY host SELECT sum(bytes) AS sum, host HAVING sum > 1024*1024
Within <predicate-expression>, the WHERE clause only supports using wildcards with the like function.
Default: None
GROUP BY clause
Syntax: GROUP BY ( <expression>[,<expression>... ] | span ( <field>, [<int>]<timescale> ) )
Description: Supported only in searches. One or more expressions, separated by commas. Expressions can be field names or arbitrary expressions, such as upper(first_name).
If a GROUP BY clause is specified, a SELECT clause must also be specified.
Default: If no GROUP BY clause is specified, the from command returns all rows based on the WHERE clause.
SELECT clause
Syntax: (SELECT | SELECT DISTINCT) <expression> [ AS <field> [,<expression> [AS <field>] ]... ]
Description: Use the SELECT clause to retrieve specific fields. In searches, you can also use SELECT to perform aggregate functions on specified fields, such as max(delay). In searches, use SELECT DISTINCT to retrieve unique combinations of the selected field values. If multiple rows contain the same combination of field values, only one row is returned.
The table below contains several examples showing the dependency between the SELECT and GROUP BY clauses:
| Description | Example |
|---|---|
The SELECT clause has count(), an aggregate function, and the fields host and _time. The GROUP BY clause must include both the host and _time fields. | FROM metrics WHERE sourcetype="web_access" GROUP BY host, _time SELECT count(), host, _time |
The SELECT clause has count(), an aggregate function, the host field, and the aggregate function expression latest(_time) AS _time. The GROUP BY clause must include the host field. | FROM metrics WHERE sourcetype="web_access" GROUP BY host SELECT count(), host, latest(_time) AS _time |
ORDER BY clause
Syntax: ORDER BY <expression>[,<expression>... ] [ASC | DESC]
Description: Supported only in searches. One or more expressions used to sort the results.
Default: None.
HAVING clause
Syntax: HAVING <expression>
Description: Supported only in searches. Use the HAVING clause as a filter for the data. The expression you specify must yield a result of true or false. If the expression is a string, it must be enclosed in double quotes.
Traditionally, you use the HAVING clause to filter data after clauses with aggregation. For example, this search includes aggregation in the SELECT clause. The WHERE clause is used to filter data before the aggregation in the SELECT clause.
FROM main WHERE earliest=-5m@m AND latest=@m GROUP BY host SELECT sum(bytes) AS sum, host HAVING sum > 1024*1024
Default: None
LIMIT clause
Syntax: LIMIT <integer>
Description: Supported only in searches. Used to limit the number of rows returned. For example, return only the first 100 results.
Default: None
OFFSET clause
Syntax: OFFSET <integer>
Description: Supported only in searches. Used to skip a number of matches. For example, if you specify OFFSET 15, the 16th result will be the first one returned. The OFFSET clause is often used in conjunction with the LIMIT clause.
Default: None
Command Examples
1. Searching Multiple Indexes
The following search finds data in the EMEA and APAC indexes:
FROM logs(EMEA, APAC)
WHERE count(orders) > 1000
GROUP BY country
2. Sorting Search Results Using the ORDER BY Clause
Suppose you use the following search to return the number of actions taken, grouped by the productId field.
FROM logs
WHERE status=200 AND host="www4"
GROUP BY productId
SELECT count(action), productId
fromPromQL Command
This command allows you to use PromQL to query Prometheus data and use it as a BQL query statement.
Command Syntax Details
Syntax
fromPromQL query = <string>
[type = <string>]
[step = <string>]
Required Parameters
query
Syntax: query = <string>
Description: A Prometheus PromQL statement.
Default: None
Optional Parameters
type
Syntax: type= <string>
Description: Query type (INSTANT / RANGE).
Default: RANGE
step
Syntax: step = <string>
Description: Data point interval.
Default: None
Command Example
-- Query metric PromQL
fromPromQL query="max_over_time(rate(http_requests_total[5m])[1h:30s])" type=instant step=1
| fields max_over_time
dbquery Command
This command allows you to use SQL to query remote database data and use it as a BQL query statement.
Command Syntax Details
Syntax
dbquery source=<string> query=<string>
[params = <string> ["," <string> ]...]
[fetchsize=<int>]
[maxrows=<int>]
[timeout="<int>]
Required Parameters
source
Syntax: source = <string>
Description: Refers to the data source name (this data source is configured on the configuration page).
Default: None
query
Syntax: query = <string>
Description: The SQL statement or other query statements supported by the target database.
Default: None
Optional Parameters
fetchsize
Syntax: fetchsize = <int>
Description: The number of rows returned from the database at one time. To prevent out-of-memory errors, the query result set is split into parts, and one part is returned to DB Connect at a time. This parameter specifies how many rows are in each part. Depending on the server's maximum heap size and whether the target database table contains any exceptionally large columns, you may need to specify a smaller value for this parameter. The maximum value for this option is 10,000.
Default: 10000
maxrows
Syntax: maxrows = <int>
Description: The maximum number of rows to return. If you do not specify a value for maxrows, dbxquery returns up to 100,000 rows. There is no maximum value for this parameter, but retrieving a large number of records may cause performance issues or out-of-memory messages. In this case, you should try setting maxrows to a lower number manageable by the BQL server hardware.
Default: 100000
timeout
Syntax: timeout = <int>
Description: Query timeout in seconds.
Default: 600
params
Syntax: params = <string> ["," <string> ]...
Description: The values for variables you define in your query or procedure. The values for the parameters are in CSV format.
Default: None
Command Examples
1. Querying ES
dbquery
source="es"
query="{\"query\": {\"term\": { \"status\": \"200\"} }}"
2. Querying Rizhiyi
dbquery
source="rizhiyi"
query="starttime=2024-08-23:19:08:24 endtime=2024-08-23:19:18:24 (('analyze_nginx_access.responseSize':89) AND 'raw_message':\"okhttp\")"
maxrows=100
3. Querying MySQL data and interacting with SQL Server data
dbquery
source="mysql"
query="SELECT * FROM sakila.city"
| append [dbquery source="sql_server" query="SELECT * FROM sakila.actor"]
4. Secondary business processing after querying Rizhiyi
dbquery
source="rizhyi"
query="starttime=2024-08-23:19:08:24 endtime=2024-08-23:19:18:24 (('analyze_nginx_access.responseSize':89) AND 'raw_message':\"okhttp\")"
maxrows=100
| stats max(alert_score) AS score BY endpoint, host
| where endpoint != "_*"
| eval verified=if(alert_score=*, 1,0)
| fields verified score endpoint host
eval Command
Command Syntax Details
Syntax
eval
<assignment_expression> ["," <assignment_expression> ]...
To specify multiple evaluations, separate each <assignment_expression> with a comma.
Required Parameters
assignment_expression
Syntax: <field>=<expression> [, <field>=<expression> ] ...
Description: <field> is the target field name for the result of the <expression>. If the field name already exists in your event, the eval command overwrites the value with the result of the <expression>. Otherwise, the eval command creates a new field using <field>.
Command Examples
1. Using the if function to analyze field values
Create a new field called error in each event. Using the if function, if the status value is 200, set the value in the error field to OK. Otherwise, set the error field value to Problem.
... | eval error = if(status == 200, "OK", "Problem")
2. Separating multiple eval operations with commas
You can specify multiple eval operations by separating them with commas. In the following search, the full_name evaluation concatenates the value from the last_name field with the value from the first_name field using the plus sign ( + ). In this example, there is a comma and space between the last_name and first_name fields. The low_name evaluation uses the lower function to convert the result of the full_name evaluation to lowercase.
... | eval full_name = last_name + ", " + first_name, low_name = lower(full_name)
fields Command
Command Syntax Details
fields [+|-] <field-list>
Required Parameters
field-list
Syntax: <field>, <field>, ...
Description: A comma-separated list of fields to keep or remove. You can use wildcards in field names, but these field names must be enclosed in single quotes. For example: ... | fields host, 'server*'
Optional Parameters
+ | -
Syntax: + | -
Description: If a plus sign ( + ) is specified, only the fields in the field-list are retained in the results. If a minus sign ( - ) is specified, the fields in the field-list are removed from the results. The symbol you specify applies to all fields in the field-list.
Default: +
Command Examples
1. Specifying a list of fields to include in search results
Return only the host and src fields in the search results.
... | fields host, src
2. Specifying a list of fields to remove from search results
Use a minus sign ( - ) to specify which fields to remove from the search results. In this example, the host and ip fields are removed from the results.
... | fields - host, ip
head Command
Command Syntax Details
head <limit>
Required Parameters
limit
Syntax: <integer>
Description: The number of results to return.
Default: None
Command Examples
rename Command
rename
<source-field> AS <target-field> ["," <source-field> AS <target-field>]...
Command Syntax Details
Required Parameters
source-field
Syntax: <string>
Description: The name of the field in the search results to rename. You can use wildcards in the field name. Any name other than a-z, A-Z, 0-9, or underscore ( _ ) characters must be enclosed in single quotes. This includes wildcard characters ( * ).
target-field
Syntax: AS <string>
Description: The name to use as the replacement name for the field. You can use wildcards in the field name. Any name other than a-z, A-Z, 0-9, or underscore ( _ ) characters must be enclosed in single quotes. This includes wildcard characters ( * ).
Command Examples
1. Renaming a single field
Rename the usr field to username.
...| rename usr AS username
2. Renaming a field containing special characters
Rename the field ip-add to IPAddress. Field names containing anything other than a-z, A-Z, 0-9, or "_" require single quotes.
... | rename 'ip-add' AS IPAddress
sort Command
Command Syntax Details
sort
[<count>]
<field>
Required Parameters
<field>
Syntax: <field> [,<field>]...
Description: One or more fields to sort by. When specifying multiple fields, separate the field names with commas.
Optional Parameters
count
Syntax: <integer>
Description: Specifies the maximum number of results to return in the sorted result set. You must specify the count before specifying the fields. If no count is specified, the default limit of 10000 is used.
Default: 10000
Command Examples
1. Specifying different sort orders for each field
This example first sorts the results by the lastname field in ascending order, then by the firstname field in descending order. Since ascending order is the default sort order, you don't need to specify it unless you want to be explicit.
... | sort lastname, -firstname
2. Specifying the number of sorted results to return
This example sorts the results and returns up to 100 sorted results. The results are first sorted in descending order by the size field. If there are duplicate values in the size field, they are sorted in ascending order by the source field.
... | sort 100 -size, +source
stats Command
Command Syntax Details
stats
<aggregation> ...
( [<by-clause>] [span=<time-span>] )
Required Parameters
aggregation
Syntax: <aggregate-function> "("<field> [AS <field>] ")" ["," <aggregate-function> "("<field> [AS <field>] ")" ]...
Description: A statistical aggregation function. The function can be applied to an eval expression or to one or more fields. By default, the field name used in the output is the same as the aggregation function. For example, if your search is ... | stats sum(bytes), the field name in the output is sum(bytes). Use the AS clause to place the result into a new field with a name you specify, for example: ... | stats sum(bytes) AS 'sum of bytes'.
Optional Parameters
by-clause
Syntax: BY <field> [span=<timespan>] ["," <field> [span=<timespan>] ]...
Description: The name of one or more fields used to group the results. You can specify a time span to apply to the grouping. The <by-clause> returns one row for each distinct value in the <by-clause> field(s). You cannot use wildcards to specify multiple fields with similar names. You must specify each field individually.
Default: If no <by-clause> is specified, the stats command returns only one row, which is the aggregation over the entire incoming result set.
Command Examples
1. Calculating the sum of a field
If you only want a simple calculation, you can specify the aggregation without any other parameters. For example:
... | stats sum(bytes)
2. Grouping results by a field
This example takes the incoming result set, calculates the sum of the bytes field, and then groups the sum by the values in the host field.
... | stats sum(bytes) BY host
where Command
Command Syntax Details
where <predicate-expression>
Required Parameters
predicate-expression
Syntax: <predicate-expression>
Description: An expression that, when evaluated, returns TRUE or FALSE.
Command Examples
1. Specifying wildcards
You can only specify wildcards in the where command by using the like function. The percent sign ( % ) is the wildcard you must use in the like function.
In this example, the where command returns search results where the value in the ipaddress field starts with 198..
... | where like(ipaddress, "198.%")