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:
- Expressions within parentheses
- NOT clauses
- AND clauses
- 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 issue 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 wildcards (*) to indicate that it supports all available AWS account ID or AWS region partition key values, you must include a WHERE clause in that same sdselect
search that filters results by either pk_account_id
, pk_region
, or both, to identify precisely the AWS account ID and 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 usage | Use time fields in sdselect searches |
This documentation applies to the following versions of Splunk Cloud Platform™: 9.3.2408
Feedback submitted, thanks!