`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. Also see User-Friendly Time Strings in Comparisons.

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	`capitalize(String)` `concat(String, String)` 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. `decode64(String)` Note: If the string value is a field, it must be a case-sensitive field. `encode64(String)` `indexof(String, String [,int])` `lastindexof(String, String, int)` `length(String)` `literal(String)` `lower(String)` `ltrim(String, Character)` `replace(String, String, String)` `reverse(String)` `rtrim(String, Character)` `substr(String, int [, int])` `todate(String [, format])` `toduration(String)` `tonumber(String)` `trim(String)` `trim(String, Character)` `upper(String)` `urlDecode(String)` `urlEncode(String)`
Numeric Functions	`abs(number)` `ceil(number)` `distance(number, number, number, number)` The value returned by the function is in miles. The numbers are in degrees. `toRadians()` converts degrees to radians. `distance(string, string)` The value returned by the function is in miles. The numbers are in degrees. `floor(number)` `formatduration(number)` `max(number, number)` `min(number, number)` `power(number, int)` `round(number, int)` `sqrt(number)` `tostring(number)`
Date Functions	`dateadd(date, property, amount)` `dateset(date, property, value [, property, value])` `formatdate(ate [,format])` `now()`
Conditional Functions	`cidrmatch(String, String)` `contains(String, String)` `endsWith(String, String)` `if(<expression>, <expression>, <expression>)` `startsWith(String, String)`
Hash Functions	`md5(<value to hash>)` `sha1(<value to hash>)` `sha256(<value to hash>)` `sha512(<value to hash>)`
Trigonometric Functions	`arccos(<number>)` Arc cosine of an angle. `arcsin(<number>)` Arc sine of an angle. `arctan(<number>)` Arc tangent of an angle. `atan2(<numbera>,<numberb>)` Angle in radians on a polar coordinate, for the cartesian coordinates a and b. `cos(<number>)` Trigonometric cosine of an angle. `e()` Value of the mathematical constant e. `pi()` Value of pi. `sin(<number>)` Trigonometric sine of an angle in radians. `tan(<number>)` Tangent of an angle. `toDegrees(<number>)` Convert from radians to degrees. `toRadians(<number>)` Convert from degrees to radians.

Parameters

The following table lists the parameters used in this command, along with their descriptions.

Parameter	Description
`boolean_expression`	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 endsWith(uri, '.com')

*|where startsWith(uri, 'http://oracle')

*|where decode64(value) = decodeValue

*|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)

*| where arccos(angle) != NaN

*| where arcsin(angle) != NaN

*| where arctan(angle) != NaN

*| where atan2(x, y) != null

*| where cos(angle) > 0

*| where sin(angle) > e()

*| where sin(angle) > pi()

*| where sin(angle) > 0

*| where tan(angle) > 0

*| where toDegrees(angle) > 45

* | where toRadians(angle) > 0

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'

The following command calculates the distance (in miles) between two pairs of lat-long coordinates specified in degrees, when the input values are numbers, and checks if that distance is greater than 1000:

* | where distance(lat1, long1, lat2, long2) > 1000

The following command calculates the distance (in miles) between two pairs of lat-long coordinates (in degrees), when the input values are two strings, and checks if that distance is greater than 1000:

* | where distance('lat1,long1', 'lat2,long2') > 1000

Oracle Cloud Infrastructure Documentation

where

`where`