Oracle Cloud Infrastructure Documentation

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

  • capitalize(String)

  • concat(String, String)

  • contains(String, String)

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

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

  • if(<expression>, <expression>, <expression>)

Hash Functions

  • md5(<value to hash>)

  • sha1(<value to hash>)

  • sha256(<value to hash>)

  • sha512(<value to hash>)
Note

  • 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

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 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'