sdselect command WHERE clause operations

You can use an optional WHERE clause to compare fields in an sdselect search.

Using the WHERE clause to compare fields

The optional sdselect WHERE clause uses the same expression syntax as the eval command. This means that the WHERE clause interprets quoted strings as literals, and if strings are not quoted, the WHERE clause interprets those strings as field names. This means you can use the sdselect WHERE clause to compare 2 different fields.

For example, this search counts events where the field ipaddress is equal to the field clientip:

| sdselect count FROM fed_index_1 WHERE ipaddress=clientip

Meanwhile, this search counts events where the value of the field ipaddress is the literal string clientip:

| sdselect count FROM fed_index_1 WHERE ipaddress="clientip"

Evaluation order for the WHERE clause

The sdselect WHERE clause evaluates Boolean operators in the following order:

1. Expressions within parentheses
2. NOT clauses
3. AND clauses
4. OR clauses

Apply date and time evaluation functions to fields in the WHERE clause

If you use the optional WHERE clause in an sdselect search, you can apply the relative_time(), strftime(), and strptime() date and time evaluation functions to declared and undeclared time fields, constant values, and other evaluation functions. The following subsections provide more information about these elements, and offer examples that show how you apply them to the date and time evaluation functions in sdselect WHERE clauses.

Declared time field element

A declared time field is a time field you have declared as a Time field, Unix time field, or partition Time field in the definition of the federated index that you reference in the search. See Map a federated index to an AWS Glue Data Catalog table dataset.

Time field and partition Time field values must be field names in the AWS Glue table dataset to which the federated index maps.

In sdselect searches, there are some limitations to the declared time fields you can apply to date and time evaluation functions. See Limitations to the field and data types you can apply to date and time functions in the WHERE clause.

Here is an example of an sdselect search that uses a WHERE clause to apply the relative_time() function to a declared time field:

| sdselect count FROM fed_index_1 WHERE relative_time(_time, "+1mon") > now()

In this example, _time is the Unix time field for the fed_index_1 federated index.

Undeclared time field elements

Undeclared time fields are time fields that you have not declared in the Time field, Unix time field, or partition Time field settings in the definition of the federated index that you reference in the search, but which do exist as additional time fields in the AWS Glue table dataset that is mapped to by the federated index. Undeclared time fields can have numeric, string, or SQL timestamp data types.

In sdselect searches, there are some limitations to the undeclared time fields you can apply to date and time evaluation functions. See Limitations to the field and data types you can apply to date and time functions in the WHERE clause.

Here is an example of an sdselect search that uses a WHERE clause to apply the relative_time() function to an undeclared time field:

| sdselect count FROM fed_index_1 WHERE relative_time(startTime, "+1mon") > now()

In this example, startTime is a time field in the AWS Glue table dataset to which fed_index_1 maps. The startTime field has not been declared as a Time field or partition Time field.

Constant value elements

Constant values are specific UNIX time format numbers or strings that can be expressed as date and time format variables, representing a specific date.

In sdselect searches, you can apply constant values to all three date and time evaluation functions.

In the following search example, _time is the Unix time field for the fed_index_1 federated index. The search uses strptime to convert the string value into a numeric UNIX time format value so the search can compare the value against the _time values, which are also in numeric UNIX time format.

| sdselect count FROM fed_index_1 WHERE strptime("2023-04-24 17:48:25.000 -0700", "%Y-%m-%d %H:%M:%S.%Q %z"), "+1y") > _time

Other evaluation functions

You can apply the now(), strptime(), and tonumber() functions to relative_time() and strftime() functions. You can apply the strftime() and tostring() functions to strptime() functions.

In the following search example, _time is the Unix time field for the fed_index_1 federated index. The _time field is also the event start time. The undeclared time field endTime is the event end time. This search returns the count of events that completed within the last hour.

| sdselect count FROM fed_index_1 WHERE _time > relative_time(strptime(endTime, "%Y-%m-%d %H:%M:%S.%Q %z"), "-1h")

For more information about using the Time field and the Unix time field in your searches, see Use time fields in sdselect searches.

Limitations to the field and data types you can apply to date and time functions in the WHERE clause

There are a few limitations to the fields and data types you can apply to the relative_time(), strftime(), and strptime() functions in the WHERE clause. In the following table, a value of "Yes" means you can apply the field or data type to the <time> or <string> required by the function. The strptime() function cannot accept Unix time field values and undeclared fields with numeric data types because the function requires a value with a string data type.

Function	Time field	Unix time field	Partition time field	Undeclared time fields with a numeric data type	Undeclared time fields with a string data type	Undeclared time fields with a SQL timestamp data type
relative_time(<time>,<specifier>)	Yes	Yes	Yes	Yes, but you must use a valid UNIX time format value for relative_time.	Yes, but you must use a valid UNIX time format value for relative_time.	Yes
strftime(<time>,<format>)	Yes	Yes	Yes	Yes, but you must use a valid UNIX time format value for strftime.	Yes, but you must use a valid UNIX time format value for strftime.	Yes
strptime(<string>,<format>)	Yes	No	Yes	No	Yes	Yes

Specify a wildcard with the WHERE clause

Use the like() evaluation function to specify a wildcard in an sdselect WHERE clause. The percent ( % ) symbol is the wildcard you use with the like() function. For more about the like() function, see Comparison and conditional functions in the Splunk Cloud Platform Search Reference.

Filtering on partition keys for a federated index that maps to a Splunk-managed AWS Glue table dataset

This requirement applies specifically to Federated Search for Amazon S3.

If you invoke a federated index that maps to a Splunk-managed AWS Glue table dataset in your sdselect search, and the definition of that federated index uses a wildcard (*) in the AWS Account IDs field to indicate that it supports all available AWS account ID values, you must include a WHERE clause in that same sdselect search that filters results by pk_account_id, to identify precisely the AWS account ID partitions that are involved in the search.

Similarly, if you invoke a federated index that maps to a Splunk-managed AWS Glue table dataset in your sdselect search, and the definition of that federated index uses a wildcard (*) in the AWS Regions field to indicate that it supports all available AWS region values, you must include a WHERE clause in that same sdselect search that filters results by pk_region, to identify precisely the AWS region partitions that are involved in the search.

For example, say the federated index fed_index_2 maps to a Splunk-managed AWS Glue table. The fed_index_2 federated index definition uses a wildcard symbol to indicate that its AWS Glue table uses all available AWS account ID partitions in the AWS CloudTrail dataset that the Glue table is based on.

The following search references the fed_index_2 federated index, so it has to identify which of the AWS account partitions it is running over from the AWS CloudTrail dataset upon which its AWS Glue table is based. It indicates that it is specifically looking at data from the partition associated with AWS account ID 123456789012.

| sdselect count FROM fed_index_2 WHERE pk_account_id="123456789012"

See Map a federated index to a Splunk-managed AWS Glue table dataset.

See also

sdselect command

sdselect command overview

sdselect command syntax details

sdselect command usage

Use time fields in sdselect searches

sdselect command examples for Amazon S3