sdselect command syntax details

Syntax

The required syntax is in bold.

| sdselect

( <field-list> | <stats-func>)...

<from-clause>

[WHERE <eval-expression>]

[GROUPBY (<field-list> [span=[<unsigned_int>]<timescale>])]

[ORDERBY <field-list>]

[LIMIT <unsigned_int>]

Required arguments

<field-list>

Syntax: <field>, …

Description: List 1 or more fields to group results.

Do not place quotation marks around nested fields. Place single quotation marks around the names of flattened fields that begin with numeric characters or contain special characters or spaces. See Special handling for sdselect syntax elements.

<stats-func>

Syntax: (count | count(<field>) | <function>(<field>))... [AS <string>]

Description: Run a count of all events, a basic count of a field, or a statistical function on a field. You have the option of renaming the results with the AS keyword.

The following table lists the supported functions for sdselect by type of function. Use the links in the table to see descriptions and examples for each function. For an overview about using functions with commands, see Statistical and charting functions in the Splunk Cloud Platform Search Reference.

Type of function	Supported functions and syntax
Aggregate functions	avg() count() distinct_count(), dc() estdc() max() median() min() mode() perc<int>() range() stdev() stdevp() sum() var() varp()
Multivalue stats and chart functions	values()
Time functions	earliest() earliest_time() latest() latest_time()

The sdselect command does not support usage of c(), the abbreviated form of count().

The sdselect command does not support usage of the distinct_count() aggregate function or the values() multivalue function in conjunction with any of the time functions.

When you apply a field with string values to a statistical function that expects fields with numeric values, sdselect converts the string field values into numeric field values. For example, say you have a search that starts like this: | sdselect avg(bytes) FROM... If bytes is a field with string values, sdselect converts those string values into numeric values before it applies the values to the avg() function.

If you use either the earliest_time() function or the latest_time() function in a search, you must apply all functions in the search to the same field.

For example, the following search is valid. It uses the earliest_time() function and applies the same field, end_time, to both functions in the search.

| sdselect avg(end_time), earliest_time(end_time) from my_csv_data

The following search is invalid. It uses the earliest_time() function, but it applies different fields, end_time and nap_time, to the functions in the search.

| sdselect avg(end_time), earliest_time(nap_time) from my_csv_data

<from-clause>

Syntax: FROM ( federated:<federated_index_name> | <federated_index_name>)

Description: Use the FROM clause to specify the federated index that maps to an AWS Glue Data Catalog table dataset that you want to search. You can optionally prefix federated index names with federated:.

In Splunk Web, to see a list of federated indexes that you have defined for your deployment, navigate to Settings and then Federated Search and select the Federated Indexes tab.

For more information, see Map a federated index to an AWS Glue Data Catalog dataset.

WHERE clause arguments

Use the WHERE clause to filter results. The WHERE clause is optional.

The sdselect command uses an application of the WHERE clause that is similar to that of the where command. See where in the Splunk Cloud Platform Search Reference.

For additional information about using the WHERE clause, see sdselect command WHERE clause operations.

<where-clause>

Syntax: WHERE <eval-expression>

Description: Use the WHERE clause to filter the search results.

The WHERE clause must precede the GROUPBY, ORDERBY, and LIMIT clauses if you use those clauses in your sdselect search.

<eval-expression>

Syntax: <eval-expression>

Description: A combination of values, variables, operators, and functions that represent the value of your destination field. See sdselect command usage.

If you use the WHERE clause you must include an <eval-expression>. This <eval-expression> must be a Boolean expression, where the expression returns either true or false. The WHERE clause returns only the results for which the <eval-expression> returns true.

The <eval-expression> is case-sensitive with regard to field values. The search head checks the syntax of the eval-expression before it runs the search and returns an error message if the expression is invalid.

The following table lists the supported eval functions for sdselect by type of function. Use the links in the table to see descriptions and examples of each type of function. <eval-expression> supports the following eval functions:

Type of function	Supported functions and syntax
Comparison and conditional functions	like(<str>,<pattern>)
Conversion functions	tonumber(<str>) tostring(<value>)
Date and time functions	now() relative_time(<time>,<specifier>) strftime(<time>,<format>) strptime(<str>,<format>)
Text functions	lower(<str>) upper(<str>)

See Evaluation functions in the Splunk Cloud Platform Search Reference. The <eval-expression> additionally supports Boolean, mathematical, and comparison operators.

The sdselect command supports only the required arguments for the tonumber() and tostring() functions. For the tonumber() function, sdselect does not allow the <base> argument. The sdselect command processes all tonumber() string-to-number conversions in default base 10.

For the tostring() function, the sdselect command does not support the <format> argument. In sdselect searches, you can use tostring() only to facilitate straightforward number-to-string conversions.

There are some restrictions to the usage of date and time eval functions in the sdselect WHERE clause. See Apply date and time eval functions to fields in the WHERE clause.

Nested fields, certain kinds of field names, and literal strings require special handling in the <eval-expression>. See Special handling for sdselect syntax elements.

The <eval-expression> does not support usage of plus ( + ) and dot ( . ) characters to concatenate field names. You can use plus and dot characters to concatenate literal strings that are enclosed within double quotes.

GROUPBY clause arguments

You can use the GROUPBY clause to organize search results by field values or time spans. The GROUPBY clause is optional.

<groupby-clause>

Syntax: GROUPBY (<field-list> [span=[<unsigned_int>]<timescale>])

Description: Group search results together according to field values and time ranges.

If you use GROUPBY you must specify a field-list.

You can optionally specify a span for a GROUPBY clause. If you specify a span, the <field-list> must include the name of the Unix time field for the federated index invoked in the sdselect search. See Map a federated index to an AWS Glue Data Catalog table dataset for more information about the Unix time field.

The GROUPBY clause must follow the WHERE clause if you are using both clauses in conjunction with sdselect.

The GROUPBY clause must precede the ORDERBY and LIMIT clauses, if you use either of those clauses.

If you use the GROUPBY clause in a sdselect search without an ORDERBY clause, sdselect sorts the search results by the fields according to the following sequence:

1. By the order that you have listed the fields in the GROUPBY clause.

2. By the values of the fields in the GROUPBY clause in ascending alphanumeric order.

For information about how ORDERBY clause sort operations interact with the GROUPBY clause, see GROUPBY and ORDERBY event sort interoperation.

<field-list>

Syntax: <field>, ...

Description: Specify 1 or more fields by which to group results. If you specify a time field in the <field-list>, you must also specify a <span> argument. Separate each field name in the field list with a comma.

Nested fields, certain kinds of other field names, and literal strings require special handling in the GROUPBY clause. See Special handling for sdselect syntax elements.

<span>

Syntax: span=[<unsigned_int>]<timescale>

Description: The <span> of each time bin. If you use the GROUPBY clause to group by a time field, use the <span> argument to group the time buckets. You can specify time spans such as GROUPBY <Unix time field> span=1h or GROUPBY <Unix time field> span=5d.

The sdselect command does not support auto as a value for the span argument.

<timescale>

Syntax: <sec> | <min> | <hr> | <day> | <month> | <year>

Description: Time scale units. The sdselect command does not support subseconds.

Default: 1 second

The following table describes the different kinds of time scale units that span supports and the valid values for each type of time scale unit.

Time scale	Syntax	Description
<sec>	s | sec | secs | second | seconds	Time scale in seconds.
<min>	m | min | mins | minute | minutes	Time scale in minutes.
<hr>	h | hr | hrs | hour | hours	Time scale in hours.
<day>	d | day | days	Time scale in days.
<month>	mon | month | months	Time scale in months.
<year>	y | yr | year | years	Time scale in years.

ORDERBY clause arguments

You can use the ORDERBY clause to sort the search results. The ORDERBY clause is optional.

<orderby-clause>

Syntax: ORDERBY <field-list>

Description: Sort search results by the values of the field or fields specified for the clause.

The ORDERBY clause must follow the WHERE and GROUPBY clauses if you use either clause in your sdselect search.

The ORDERBY clause must precede the LIMIT clause.

<field-list>

Syntax: <field> [ASC | DESC], ...

Description: You must specify at least 1 field for an ORDERBY clause. You can optionally use the ASC or DESC modifiers to indicate whether sdselect sorts events by the field values in ascending or descending order. If you do not specify ASC or DESC for a field, by default sdselect sorts the field in ascending order.

If you specify multiple fields for an ORDERBY clause, separate the fields and their ASC or DESC modifiers by commas. When you specify multiple ORDERBY fields, sdselect sorts search results by the fields in the order that you list the fields.

For example, say your search has the following ORDERBY clause: ORDERBY id name city. In this case, the ORDERBY clause sorts the search results first by id. Then, the clause sorts rows that have matching id values by name. Finally, the clause sorts rows with matching id and name values by city.

Nested fields, certain kinds of field names, and literal strings require special handling in the ORDERBY clause. See Special handling for sdselect syntax elements.

The fields you specify in the ORDERBY clause must be aggregated fields, or fields that appear in the GROUPBY clause. If you want to use a renamed aggregated field, the ORDERBY clause must refer to the field by its rename. The following ORDERBY clause example renames an aggregated field from scan_count to scan_avg:

| sdselect avg(scan_count) AS scan_avg FROM my_csv_data GROUPBY sid,action ORDERBY scan_avg DESC

For more information about how ORDERBY clause sort operations interact with the GROUPBY clause, see GROUPBY and ORDERBY event sort interoperation.

LIMIT clause arguments

You can use the LIMIT clause to specify the maximum number of search results to return. The LIMIT clause is optional.

<limit-clause>

Syntax: LIMIT <unsigned_int>

Description: Set the maximum number of search results that an sdselect search can return.

Default: 100,000 results

You must place the LIMIT clause after the WHERE, GROUPBY, and ORDERBY clauses, if you use any of them in the search.

For information about changing the default number of results returned by an sdselect search without a LIMIT clause, see Control the maximum number of returned results if a LIMIT clause is not present.

See also

sdselect command

sdselect command overview

sdselect command usage

sdselect command WHERE clause operations

Use time fields in sdselect searches

sdselect command examples for Amazon S3