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.
See also
sdselect command usage | Use time fields in sdselect searches |
This documentation applies to the following versions of Splunk Cloud Platform™: 9.0.2305, 9.1.2308, 9.1.2312, 9.2.2403, 9.2.2406 (latest FedRAMP release)
Feedback submitted, thanks!