where
Use the where
command to calculate the value of an expression to be true or false.
Topics:
Syntax
*|where <expression>
Operators Available with the Command
The following table lists the operators available with the
where
command.
Category | Example |
---|---|
Arithmetic Operators |
+ , - ,
* , / ,
% |
Comparison Operators |
= , != ,
< , > ,
<= , >= |
Logical Operators |
and , or ,
not |
Conditional Operators |
if(<expression>,<expression>,<expression>) |
Multiple Comparison Operators |
in , not in |
Functions Available with the Command
The following table lists the functions available with the
where
command.
Category | Example |
---|---|
String Functions |
|
Numeric Functions |
|
Date Functions |
|
Conditional Functions |
|
Hash Functions |
|
-
For the
concat()
function, you can input numeric data types like integer, float, or long. The numeric fields with be automatically converted to the corresponding string values. -
You can use
||
to concatenate n number of inputs. Here too, you can input numeric data types which will be automatically converted to the corresponding string values.
Parameters
The following table lists the parameters used in this command, along with their descriptions.
Parameter | Description |
---|---|
|
Specify the expression for which the true or false value needs to be calculated. |
User-Friendly Time Strings in Comparisons
The new capability allows a human readable string to be used to manipulate time in
the query. In the past, this required using toDuration()
and
providing the duration in a very specific format.
Example 1: Add a duration to a timestamp field.
*
| eval '10mins. after End Time' = 'Event End Time' + 10mins
| fields 'Event End Time', '10mins. after End Time'
Example 2: Find jobs that took more than 2 hours.
* | where 'Event End Time' - Time > 2hrs
Example 3: Find items that took more than a day or less than few milliseconds.
*
| link
| where Count > 1000 and
('End Time' - 'Start Time' > 3hour or 'End Time' - 'Start Time' < 2ms)
The time string can be value used in the span
parameter for
timestats
. See the list of values for
timescale
in timestats.
For examples of using where
command in typical
scenarios, see:
Following are some examples of the where
command.
*|where severity = FATAL
*|where 'Client Host City' = 'redwood city'
*|where upper(severity) = FATAL
*|where length(URI) >= 40
*|where replace('aabbcc', 'bb', 'xx') = aaxxcc
*| where capitalize(severity) = Fatal
*|where concat(host, concat(':', port)) != hostname
* | where contains(uri, '.com')
* | where encode64(uri) = encodeValue
* | where lastindexOf(uri, '.com') != -1
* | where reverse(Command) = smaraptini
*|where host || ':' || port != hostname
*|where substr('aabbcc', 2, 4) = bb
*|where round('Content Size') = 1000
*|where floor('Content Size') > 1000
*|where max('Content Size In', ''Content Size Out') < 1000
*|where urlDecode('http%3A%2F%2Fexample.com%3A893%2Fsolr%2FCORE_0_0%2Fquery') = URI
* | where urlEncode(uri) = field
*|where 'User Name' in (host1, host2)
The following example shows the use of the if
conditional function:
* | where if(Status = '200', OK, ERROR) = ERROR
The following example compares the IP addresses in the field srvrhostip
to a subnet range.
*|where cidrmatch(srvrhostip, '192.0.2.254/25')
The following example returns the string value of the field Delay.
*|where Status = literal(Delay)
The following example removes the matching character from both the ends.
*|where trim(Command,"\") = initparams
The following example removes the matching character from the left end.
*|where ltrim('Error ID',0) = 76890
The following example removes the matching character from the right end.
*|where rtrim('OS Process ID',2) = 3123
The following example compares the string Start Time
with 1/1/18
in the date format MM/dd/yy
.
*|where 'Start Time' > toDate('1/1/18', 'MM/dd/yy')
The following example calculates the difference between the values of End Time
and Start Time
and compares the string with the duration of 0:0:45
.
*|where 'End Time' - 'Start Time' > toDuration('0:0:45')
The following example specifies the format of the duration as 0:0:45.000
.
*|where formatDuration('End Time' - 'Start Time') = '0:0:45.000'
The following examples illustrate the use of date functions.
*|where 'Start Time' > dateAdd(now(), hour, -1)
*|where 'Start Time' > dateSet(now(), hour, 0, minute, 0, sec, 0, msec, 0)
*|where formatDate('Start Time', 'MM/dd/yyyy') = '01/15/2018'
*|where 'Start Time' - now() > 45000
The following example calculates the position of .com
in the uri
string and evaluates if it is not equal to -1
.
*| where indexOf(uri, '.com') != -1
You can use the md5, sha1, sha256, and sha512 hash functions with the where command
to filter log data. The following example evaluates if the value of the field
user
is md5("jack")
.
*|where user = md5("jack")
The following command separates out the content of the entity field into two parts.
For example, the entity value host-phx-1.oraclevcn.com
with pattern
would split into two virtual fields named Host and Domain, with domain
value oraclevcn.com
:
* | extract field = Entity '(?P<Host>\w+)\.?(?P<Domain>.*)'
| where contains(Domain, 'oraclevcn.com')
| timestats count as logrecords by 'Log Source'