sdselect command syntax details

Syntax

The required syntax is in bold.

| sdselect

[reuse_search_results=<bool>]

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

<from-clause>

[WHERE <eval-expression>]

[GROUPBY ((<field-list> | <eval-func>)... [span=[<unsigned_int>]<timescale>])]

[ORDERBY (<field-list> | <eval-func>)...]

[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>) | <stats-function>(<field>) | <aggregate-stats-function>(<eval-function>)... [AS <string>]

Description: Run a count of all events, a basic count of a field, a statistical function on a field, or an aggregate statistical function on a non-Boolean evaluation function. You have the option of renaming the results as a new field with the AS keyword.

The following table lists the supported statistical functions for sdselect by function type. 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 statistical function that expects fields with numeric values to a field with string 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

You can apply an aggregate statistical function directly to any evaluation function supported by sdselect, with the following exceptions.

• Boolean evaluation functions such as like() and match().

• Date and time evaluation functions.

• Non-function eval expressions that use mathematical, concatenation, or Boolean operators like a > 5 or x + 3.

For more information about sdselect evaluation function support, see Apply evaluation functions to your sdselect searches.

The <eval-function> to which you apply the aggregate stats function can contain a single evaluation function or a chain of evaluation functions, as long as its output is not a Boolean value of true or false.

If an aggregate statistical function requires a numeric value but your evaluation function does not produce a numeric value, Splunk software automatically applies the tonumber() evaluation function to transform the output of the other evaluation function into a numeric value. Here is an example of a search where tonumber() is used for this purpose.

| sdselect sum(tonumber(json_extract(req, "payload.bytes"))) from ...

<eval-func>

Syntax: <evaluation-function>(<field>)... [AS <string>]

Description: Apply evaluation functions to fields that you are selecting with sdselect. You have the option of renaming the results as a new field with the AS keyword.

You can apply any evaluation function supported by sdselect to a selected field, with the following exceptions.

• Boolean evaluation functions such as like() and match().

• Date and time evaluation functions.

• Non-function eval expressions that use mathematical, concatenation, or Boolean operators like a > 5 or x + 3.

The <eval-func> to which you apply the aggregate stats function can contain a single evaluation function or a chain of evaluation functions, as long as its output is not a Boolean value of true or false.

For more information about sdselect evaluation function support, see Apply evaluation functions to your sdselect searches.

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

Optional arguments

reuse_search_results

Syntax: reuse_search_results=<bool>

Description: Specifies whether an sdselect search can reuse the result set from the last successful run of the same sdselect search, as long as that previous search run took place within the previous 24 hours. sdselect searches that reuse search results can benefit from improved search performance and a reduction in data scan unit consumption. The reuse_search_results argument defaults to true, which means that sdselect searches reuse results whenever it is possible for them to do so.

Reuse of sdselect search results is useful for sdselect searches that do not return different result sets within a given time frame. For example, sdselect searches with absolute or fixed date-to-date time ranges are good candidates for search result reuse. If you run such searches with a frequency below 24 hours, you might see an increase in their performance and a reduction in their data scan consumption.

When you run an sdselect search that reuses search results, an informational message appears in the Job Inspector that states that the results for the last successful run of the search have been reused for the current search job. The only exception to this rule are sdselect searches with an all-time time range. When you run an sdselect with an all-time time range, Splunk software displays a warning message under the search bar stating that the results from the last successful run of the all-time search have been reused.

Turn search result reuse off for an sdselect search by adding reuse_search_results=false to the search string. You might turn search result reuse off for sdselect searches that return different result sets each time they run, when you run them on a frequent basis. For example, you might turn off search result reuse for sdselect searches with relative time ranges, such as the last week to date, or the hour before the current hour, when you run those searches more than once in a 24 hour period.

You do not need to turn off search result reuse for an sdselect search with a relative time range when more than 24 hours elapses between runs of the search.

Search result reuse is available only for Federated Search for Amazon S3 sdselect searches. Federated Analytics does not support the reuse_search_results argument in sdselect searches of remote Amazon Security Lake datasets.

Default: true

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: <boolean-eval-function> | <boolean-nonfunction-eval-expression>

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

When you use the WHERE clause in an sdselect search, you must include an <eval-expression>. The output of this <eval-expression> must be a Boolean value of either true or false. This is true whether the <eval-expression> contains a single evaluation function or a chain of evaluation functions.

The WHERE clause returns only the results for which the <eval-expression> returns true.

The WHERE clause can support all evaluation functions generally supported by sdselect. See Apply evaluation functions to your sdselect searches.

The WHERE clause also supports the following date, time, and Boolean evaluation functions.

Type of function	Supported functions and syntax
Comparison and conditional functions	like(<str>,<pattern>) match(<str>,<regular_expression>)
Date and time functions	now() relative_time(<time>,<specifier>) strftime(<time>,<format>) strptime(<str>,<format>) timestamp_from_unixtime<time> (unique to sdselect) timestamp_to_unixtime<time> (unique to sdselect)

The match() evaluation function requires Java regular expression syntax when you use it in conjunction with sdselect. match() supports perl-compatible regular expression (PCRE) syntax when you use it with other SPL commands such as eval, fieldformat, and where.

The timestamp_from_unixtime() and timestamp_to_unixtime() evaluation functions are unique to sdselect and cannot be used with other SPL commands. See Evaluation functions specific to sdselect.

If you use Federated Analytics, you must use the timestamp_from_unixtime() and timestamp_to_unixtime() evaluation functions in conjunction with the WHERE clause to filter Amazon Security Lake dataset partitions on values of the time_dt field. The values of time_dt have the SQL timestamp data type, which the sdselect command does not natively support.

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

The WHERE clause additionally supports Boolean non-function evaluation expressions that use the following operators: <, >, <=, >=, !=, =, and ==.

The WHERE clause <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> | <eval-func>)... [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 or eval-func.

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.

<eval-func>

Syntax: <eval-func>, ...

Description: Group by the output of evaluation functions.

The GROUPBY clause can support all evaluation functions supported by sdselect, with the following exceptions.

• Boolean evaluation functions such as like() and match().

• Date and time evaluation functions.

• Non-function eval expressions that use mathematical, concatenation, or Boolean operators like a > 5 or x + 3.

An <eval-func> for a GROUPBY clause can contain a single evaluation function or a chain of evaluation functions, as long as its output is not a Boolean value of true or false.

For more information about sdselect evaluation function support, see Apply evaluation functions to your sdselect searches.

<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> | <eval-func>)...

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 or evaluation functions, sdselect sorts search results by the fields and functions in the order that you list them.

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.

If your search includes a GROUPBY clause, the fields you specify in the ORDERBY clause must be aggregated fields, or fields that appear in the GROUPBY clause. If your search does not include a GROUPBY clause, for the ORDERBY clause you can specify any field that exists in the dataset you are searching.

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

<eval-func>

Syntax: <evaluation-function> [ASC | DESC], ...

Description: Order events by the output of evaluation functions.

The ORDERBY clause can support all evaluation functions supported by sdselect, with these exceptions:

• Boolean evaluation functions such as like() and match().

• Date and time evaluation functions.

• Non-function eval expressions that use mathematical, concatenation, or Boolean operators like a > 5 or x + 3.

An <eval-func> for an ORDERBY clause can contain a single evaluation function or a chain of evaluation functions, as long as its output is not a Boolean value of true or false. For more information about sdselect evaluation function support, see Apply evaluation functions to your sdselect searches.

You can optionally use the ASC or DESC modifiers to indicate whether sdselect sorts the output values in ascending or descending order. If you do not specify ASC or DESC for an evaluation function, by default sdselect sorts the output values in ascending order.

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

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

Evaluation functions specific to sdselect

sdselect command examples for Amazon S3