from command: Syntax

For overview information about the SPL2 from command, see from command: Overview.

For examples using the SPL2 from command, see from command: Examples.

Syntax

The SPL2 from command supports different syntaxes in different product contexts:

Searches

In searches, the from command has a flexible syntax which enables you to start the search with either the FROM clause or the SELECT clause. For more information about this flexible syntax, see from command: Usage.

Pipelines

In pipelines, the from command must be written as from $source. The flexible syntax is not supported and there is limited support for the optional clauses.

Syntax for searches

The required syntax is in bold.

FROM <dataset> [ AS <alias>]

[ JOIN <dataset> AS <alias> ON <join-condition>... ] ...

[ WHERE ( <predicate-expression> [<logical-operator> <predicate-expression>] ) ... ]

[ (GROUP BY | GROUPBY) ( <expression>[,<expression>... ] | span ( <field> ) | span ( <field>, [<int>]<timescale> ) | <field> span=( [<int>]<timescale> ) ]

[ (SELECT | SELECT DISTINCT) <expression> [, <expression>] ... ]

[ HAVING <expression> ]

[ (ORDER BY | ORDERBY) <expression>[,<expression>... ] [ASC | DESC] ]

[ LIMIT <integer> ]

[ OFFSET <integer> ]

Syntax for pipelines

The required syntax is in bold.

FROM <dataset>

[ WHERE ( <predicate-expression> [<logical-operator> <predicate-expression>] ) ... ]

[ (SELECT <field-list> ]

In addition to specifying these clauses in the FROM command, you can specify WHERE and SELECT as separate commands in both Edge Processor and Ingest Processor pipelines.

Uppercase and lowercase clause names

The clause names are shown in uppercase in the syntax for readability. You can specify the clause names in uppercase or lowercase. For example, you can specify FROM or from, GROUP BY or group by. However, you cannot specify the names in mixed case, such as Group By.

Required arguments

dataset

Syntax <dataset> [ AS <alias> [ <JOIN-TYPE> ] ]

Description This argument accepts different values in different product contexts.
Searches: This argument must be set to the name of the dataset to retrieve the data from.
  • You can specify more than one dataset by using the union command. See union command: Overview, syntax, and usage.
  • If you start a search with the FROM clause, you only need to specify a <dataset>. If you start a search with the SELECT clause, you must also specify the FROM clause.
  • When you use the JOIN clause, you must specify an alias for the dataset. You have the option of specifying the join type. The default join type is INNER. The supported join types are: INNER and LEFT.
Pipelines: This argument must be set to the $source parameter, which refers to an internal dataset in the Edge Processor or Ingest Processor.
  • The optional clause for specifying a dataset alias and join type are not supported.

Optional arguments

JOIN clause

JOIN

Syntax: ( <join-type> ) <dataset> AS <alias> ON <join-condition> [AND <join-condition>]... )...

Description: Supported only in searches. You can enrich a dataset specified in the FROM clause with data from one or more datasets specified in the JOIN clause. When you use the JOIN clause, you must specify aliases for each of the datasets. The aliases are used to help identify the fields in each of the datasets.

A maximum of 50000 rows from the dataset that you specify in the JOIN clause, sometimes referred to as the right-hand side dataset, can be joined with the dataset specified in the FROM clause. This maximum is set to limit the impact of the JOIN clause on performance and resource consumption.

You have the option of specifying a join type. The default join type is INNER JOIN. You can also specify LEFT JOIN. Valid join types are described in the following table:

Join type Valid keywords Notes
Inner join INNER JOIN or JOIN You can specify the keywords in lowercase. The keywords are shown in uppercase for readability.
Left join LEFT JOIN or LEFT OUTER JOIN You can specify the keywords in lowercase. The keywords are shown in uppercase for readability.
Right join A right join is not supported at this time.

An image that shows two join types using Venn diagrams. The first Venn diagram is labeled Left Join and has two intersecting circles, circle A and circle B. Circle A is completely shaded, including the portion of the circle where it overlaps with circle B. The second Venn diagram is labeled Inner Join and has two intersecting circles, circle A and circle B. Only where the circles overlap is shaded.

The <join-condition> specifies the key fields that each dataset has in common, such as a product ID or supplier ID. The syntax for the <join-condition> is <left-alias>.<left-field>=<right-alias>.<right-field>. The alias is a unique string you use in the search to identify the dataset. For example, if the dataset is orders you could use the alias o. If the orders dataset contains the field productID, the syntax to identify this field is o.productID.

You can specify multiple join conditions by using the AND operator between each condition. For example, ON o.productID=p.pid AND o.supplierID=p.sid.

You can specify multiple JOIN clauses in a search. See from command: Examples.

Default: None. You must specify a join type.

WHERE clause

WHERE

Syntax: WHERE <predicate-expression> [<logical-operator> <predicate-expression>...]

Description: Use predicate expressions to filter your data. When specifying multiple predicate expressions, you must specify a logical operator between the expressions. For information about and examples of the types of predicate expressions you can specify, see Predicate expressions in the SPL2 Search Manual.

Use the WHERE clause to filter your data before using other clauses that contain aggregations. For example, the following search contains an aggregation in the SELECT clause. The WHERE clause filters the data by narrowing down the 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

In the <predicate-expression>, the WHERE clause supports using wildcards only with the like function. For more information, see Conditional and comparison functions.

Pipelines: Data that does not match the <predicate-expression> is not sent to the INTO <destination>. The data will either be dropped, or sent to the default destination. For more information:
  • For Edge Processor, see Partitions in the Use Edge Processors manual.
  • For Ingest Processor, see Partitions in the Use Ingest Processors manual.

Default: None

GROUP BY clause

GROUP BY

Syntax: (GROUP BY | GROUPBY ) ( <expression>[,<expression>... ] | span ( <field> ) | span ( <field>, [<int>]<timescale> ) | <field> span=( [<int>]<timescale> )

Description: Supported only in searches. One or more expressions separated by commas. The expression can be a field name or an arbitrary expression such as first_name + last_name or upper(first_name). See Types of expressions in the SPL2 Search Manual.

  • When specifying a field that contains UNIX time, you can also specify a time span.
  • You cannot use a wildcard character to specify multiple fields with similar names. You must specify each field separately.
  • The GROUP BY clause returns one row for each distinct value in the list of fields.

If the GROUP BY clause is specified, the SELECT clause must also be specified. For examples, see the SELECT clause section Dependencies between SELECT and GROUP BY.

Using a time span

When you use the span function in the GROUP BY clause, the <field> must contain timestamps, such as the _time field.

There are multiple ways to use a span:

Span type Syntax Description and Examples
Auto span span (<field>) The span is calculated automatically based on the time range in the Time Range Picker.

Example:

  • span(_time)
Span using the default time unit.

There are two syntaxes you can use.

span (<field>, <timescale>)

<field> span=<timescale>

You can specify a timescale, such as m for minute and h for hour, without specify a time unit. The default time unit, 1is used.

Examples:

  • span(_time, m), which defaults to a span of 1 minute.
  • _time span=h, which defaults to a span of 1 hour.
Span with a specified time unit.

There are two syntaxes you can use.

span (<field>, <int><timescale>)

<field> span=<int><timescale>

You can specify a timescale with a time unit.

Examples:

  • span (_time, 3h), which specifies a span of 3 hours.
  • _time span=3h, which specifies a span of 3 hours.

Default: If no GROUP BY clause is specified, the from command returns all of the rows, based on the WHERE clause.

SELECT clause

SELECT

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 an aggregate function that specifies a field, 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 <expression> you can use depends on the product context:
    • In searches, the <expression> can be any expression.
    • In pipelines, the <expression> must be a <field-list>.
    • See Types of expressions in the SPL2 Search Manual.
  • In the <expression>, you can specify a single field name or use the asterisk ( * ) wildcard to specify multiple fields, such as 'host*'. The syntax with the asterisk must be enclosed in single quotation marks. For information about when quotation marks are required, see Quotations in the SPL2 Search Manual.
  • If you rename an expression using AS <field>, the name you specify for the field can't be a reserved word. For a list of the reserved words, see Reserved words.
  • In searches, if you specify an <aggregate-function>, only one row is returned with the aggregation over the entire incoming result set.
  • In searches, if an <alias> is used to identify a dataset, such as when you use the JOIN clause, you must use that alias when specifying field names in the SELECT clause.
  • In pipelines, the SELECT DISTINCT syntax is not supported.

Supported hierarchies in searches

The SELECT clause is supported in two different syntax hierarchies. One hierarchy starts with the FROM clause. The other hierarchy starts with the SELECT clause. See from command: Usage.

  • FROM clause hierarchy: When the GROUP BY clause is specified, the SELECT clause must also be specified. The expressions in the SELECT clause must be field names or aggregate functions. For examples, see the section Dependencies between SELECT and GROUP BY.
  • SELECT clause hierarchy: When you start a search with the SELECT clause, the FROM clause is also required.

Dependencies between SELECT and GROUP BY in searches

When you include the GROUP BY clause in a search, the SELECT clause must also be included. The fields you specify in the SELECT clause must also be specified in the GROUP BY clause, unless the field in the SELECT clause is used in an aggregate function.

The following table includes several examples that show the dependency between the SELECT and the 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 main
WHERE sourcetype="web_access"
GROUP BY host, _time 
SELECT count(), host, _time
The SELECT clause has count(), an aggregate function, the host field, and latest(_time) AS _time aggregate function expression. The GROUP BY clause must include the host field. 
FROM main
WHERE sourcetype="web_access"
GROUP BY host
SELECT count(), host, latest(_time) AS _time

Default: None

ORDER BY clause

ORDER BY

Syntax: (ORDER BY | ORDERBY) <expression>[,<expression>... ] [ASC | DESC]

Description: Supported only in searches. One or more expressions to order the results by. See Types of expressions in the SPL2 Search Manual.
  • An expression can be a field name, or an arbitrary expression such as first_name + last_name or upper(first_name). Separate multiple expressions with commas.
  • If the expression is a field name, you can't use a wildcard character ( * ) to specify multiple fields with similar names. You must specify each field separately. You can specify either ascending or descending order.
  • If the field you want to order the results by is the result of an aggregation, such as count(host) or sum(bytes), you can either enclose the field name in single quotation marks, because it contains special characters, or rename the field before using the ORDER BY clause.

Default: None, the order is based on the source. The ORDER BY clause uses lexicographical order. See Lexicographical order in the SPL2 Search Manual.

HAVING clause

HAVING

Syntax: HAVING <expression>

Description: Supported only in searches. Use the HAVING clause as a filter on the data. The expression you specify must result in either true or false. If the expression is a string, it must be enclosed in double quotation marks. See Predicate expressions in the SPL2 Search Manual.

Traditionally you use the HAVING clause to filter data after clauses with aggregations. For example, this search contains an aggregation in the SELECT clause. The WHERE clause is used to filter the 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

LIMIT

Syntax: LIMIT <integer>

Description: Supported only in searches. Use to limit the number of rows returned. For example to return only the first 100 results.

Default: None

OFFSET clause

OFFSET

Syntax: OFFSET <integer>

Description: Supported only in searches. Use to skip past a number of matches. For example if you specify OFFSET 15, the 16th result is the first result that is returned. The OFFSET clause is often used in conjunction with the LIMIT clause.

Default: None