1. Using Oracle Log Analytics Search
  2. Command Reference
  3. Where Command

Where Command

Use the where command to calculate the value of an expression to be true or false.

Syntax

Operators and Functions Available with the Command

*|where <expression>

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

The following table lists the functions available with the where command.

Category Example

String Functions

  • concat(String, String)

  • indexof (String, String [,int])

  • length(String)

  • literal(String)

  • lower(String)

  • ltrim(String, Character)

  • replace(String, String, String)

  • rtrim(String, Character)

  • substr(String, int [, int])

  • todate(String [, format])

  • toduration(String)

  • tonumber(String)

  • trim(String)

  • trim(String, Character)

  • upper(String)

  • urldecode(String)

Numeric Functions

  • abs(number)

  • ceil9number)

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

Network Functions

cidrmatch(String, String)

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.

For examples of using this command in typical scenarios, see:

Following are some examples of the eval 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 concat(host, concat(':', port)) != hostname
*|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 'User Name' in (host1, host2) = omcuser

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, and sha256 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")