Splunk® Cloud Services

SPL2 Search Reference

Acrobat logo Download manual as PDF


Acrobat logo Download topic as PDF

from command syntax details

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 has a flexible syntax, which enables you to start a search with either the FROM clause or the SELECT clause.

  • 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.
For more information about this flexible syntax, see from command usage.

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> ]

Required arguments

dataset
Syntax <dataset> [ AS <alias> [ <JOIN-TYPE> ] ]
Description The name of the dataset to retrieve the data from.
You can specify more than one dataset. Use the indexes dataset function if all of the datasets are indexes. See indexes dataset function. Use the union command if the datasets are a mixture of dataset types. See union command overview.
When you use the JOIN clause, you must specify an alias. You have the option of specifying the join type. The default join type is INNER. The supported join types are: INNER and LEFT.

Optional arguments

JOIN clause

JOIN
Syntax: ( <join-type> ) <dataset> AS <alias> ON <join-condition> [AND <join-condition>]... )...
Description: 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

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

GROUP BY clause

GROUP BY
Syntax: (GROUP BY | GROUPBY ) ( <expression>[,<expression>... ] | span ( <field> ) | span ( <field>, [<int>]<timescale> ) | <field> span=( [<int>]<timescale> )
Description: 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 and perform an aggregate function that specifies a field, such as max(delay). 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> can be any expression. 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 specify an <aggregate-function>, only one row is returned with the aggregation over the entire incoming result set.
  • 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.
  • 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.
Supported hierarchies
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
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: 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: 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: 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: 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

See also

from command
from command overview
from command usage
from command examples
Last modified on 15 May, 2023
PREVIOUS
from command overview 		  NEXT
from command usage

This documentation applies to the following versions of Splunk® Cloud Services: current

Was this documentation topic helpful?


You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters