4.3 Understand the Query Syntax, Operators, and Functions

Search supports a variety of search operators and functions.

The search query bar automatically displays related fields and operators as you enter your query. For example, type the word “domain” to see all available fields that might contain that string or name. Type an integer like “22”, and Search displays a list of fields to choose from, such as Destination Port, Source Port or “any port.”

You can also specify a storage group in the query.

4.3.1 Understand the Query Syntax Requirements

Depending on the type of search you create, the query must meet the requirements listed in the following table. Also, Search treats a comma (,) between search items and values as an OR operator.

By default, Search is case-sensitive to support faster performance. However, you can instruct the database to support case-insensitive searches. For more information, see the Administrator's Guide to ArcSight Platform.

Type

Full-text

Field-based

Hashtag (predefined)

Case sensitivity

Case-sensitive

Case-sensitive

Case-insensitive

Exact Match

Keyword treated as keyword*.

  • Example:
  • /Execute matches: /Execute, /Execute/Start, /Execute/Response,/Execute/Query

Enclose value in double quotes.

  • Example:
  • Category Behavior ="/Execute"

n/a

Nesting, including parenthetical clauses, such as (a OR b) AND c

Allowed

Use Boolean operators to connect and nest keywords.

Allowed

Use Boolean operators to connect and nest keywords.

Allowed

Use Boolean operators to connect and nest keywords

Implicit Operators

When you enter two values separated by a space, this is treated as an implicit AND condition.

Example: ssh fail

The AND/OR treatment depends on the operator used in the search.

For example, destinationAddress = 1.1.1.1, 2.2.2.2 is equivalent to destinationAddress = 1.1.1.1 or destinationAddress = 2.2.2.2 ,

while the query destinationAddress != 1.1.1.1, 2.2.2.2 is equivalent to destinationAddress != 1.1.1.1 and destinationAddress != 2.2.2.2

n/a

List Operations

n/a

Performs an inner join or a left join against a custom list.

Syntax for an Inner Join: source address in list CustomListName_CustomColumn Name

Syntax for a Left Join: source address not in list CustomListName_CustomColumnName

n/a
  • Time Format
  • (when searching for events that occurred at a particular time)

No specific format

The query needs to contain the exact timestamp string.

Example: "10:34:35"
  • YYYY-MM-DDYYYY-MM-DD HH:mm
  • YYYY-MM-DD
  • HH:mm:ss.fff

To narrow the time range, use the following operators:

  • in between (><)

  • greater than (>)

  • less than (<)

n/a
  • Special Characters:
  • \ * ' "

Use the backslash (\) as an escape character.

Use the backslash (\) as an escape character.

n/a

Wildcard

Can appear anywhere in the value.

Examples:

  • *log
  • log*
  • lo*g*

Searches for ablog, blog, long, etc.

Can appear anywhere in the field.

Examples:

  • name=*log
  • Searches for ablog, blog, etc. in name field
  • name=“\*log”
  • name=\*log
  • Both search for *log

n/a

Escape a Wildcard Character

Can search for * by escaping the character.

Example:

log\*

Can search for * by escaping the character.

Example:

name=log\*

n/a

4.3.2 Understand the Search Query Functions and Operators

You can specify the following search operators in the query:

Operator

Alternative Operator

Examples

AND

 
  • #Firewall drop and sourceAddress equals 10.0.112.9
  • sourceAddress equals 10.0.112.9 and destinationAddress = 10.0.116.148

OR

 
  • fail OR ssh
  • destinationAddress = 10.0.111.5 OR destinationAddress=10.0.116.148 destinationAddress =10.0.111.5, 10.0.116.48

not equal
  • <>
  • !=

destinationPort not equal 21

equals
  • =
  • ==
  • is equal to
  • equal
  • name equals INVALID password
  • device vendor equals CISCO

greater than
  • >
  • is greater

bytes In greater than 100

less than
  • <
  • is less
  • is lower
  • less

bytes out less than 1000

greater equal than
  • >=
  • gte
  • greater equal
  • End Time greater equal than 2017-07-25
  • End Time greater equal than 2017-07-25 09:07
  • End Time greater equal than 2017-07-25 09:07:43
  • End Time greater equal than 2017-07-25 09:31:22.685

less equal than
  • <=
  • lte
  • less equal

Base Event Count less equal than or equal 50

starts with

startswith

message starts with FIN

does not start with

 

name does not start with FIN

ends with

endswith

message ends with out

does not end with

 

message does not end with out

contains
  • contain
  • like
  • has substring

name contains TCP

does not contain

does not have

name does not contain TCP

in list
  • match
  • in list of
  • device vendor equals CISCO and source address in list customListName_customColumnName
  • device vendor equals CISCO and source address in list badGuyIpList_badGuyIp

not in list
  • not match
  • not in list of
  • source address not in list customListName_customColumnName
  • source address not in list badGuyIpList_badGuyIp

in subnet

n/a

source address in subnet 10.0.0.0/8

not in subnet

n/a

source address not in subnet 10.0.0.0/8

|

(Pipeline operator)

n/a

Combine various search functions separated by the | operator:

  • ssh | eval test1 = abs ( 40 )
  • ssh | eval test1 = sin ( Bytes In )

eval <expression> name

n/a

| eval URL_Length = length ( Request URL )

rename

n/a

| rename source address as NewSourceAddress

where

n/a
  • | where Bytes In >= 3000| where Category Outcome = /Success

4.3.3 Understand the Functions for Building Eval Expressions

The Eval function allows you to define and name an expression that is returned in the search. To build an eval expression, you can use the following functions:

Comparison and Conditional Functions

Function

Description

Example

coalesce(X[, Y, Z,N, ...])

Returns the value of the first non-null expression in the list. If all expressions evaluate to null, then COALESCE returns null. The list is up to 20 elements long.

In the list of expressions all elements must be of same type.

The only supported types are numeric and string. X can be a number, field or expression.

... | eval newField = coalesce(null, null,2,3)

Returns: 2

nullif(X,Y)

Compares two expressions. If the expressions are not equal, the function returns the first expression (expression1). If the expressions are equal, the function returns null.

X and Y can be a number, field or expression. Y must have same data type that X.
  • ... | eval newField = nullif(2, 3)
  • Returns: 2
  • ... | eval newField = nullif(2, 2)
  • Returns: null

Cryptographic Function

Function

Description

Example

md5(X)

Calculates the MD5 hash of string, returning the result as a string in hexadecimal.

X must be a string.

... | eval newField = md5('123')

Returns: 202cb962ac59075b964b07152d234b70

Informational Function

Function

Description

Example

isnull(X)

Returns true if the X is null otherwise returns false.

... | eval newField = isnull(2)

Returns: false

Mathematical Functions

Function

Description

Example

abs(X)

Takes a number, X, and returns its absolute value.

X can be a number, field or expression.

The function assigns the evaluated value to the new field.

If the value of X is 3 or -3, the function assigns the evaluated value of 3 to the field absnum:

  • ...| eval absnum=abs(number)
  • ...| eval absnum = abs(bytesIn)
  • ...| eval absnum = abs(1 - bytesIn)

cbrt(X)

Takes one numeric argument, X, and returns its cube root.

... | eval n=cbrt(2)

Returns: 8

ceiling(X)

Rounds a number, X, up to the next highest integer.

X can be a number, field or expression.
  • ... | eval n=ceil(1.9)
  • ... | eval n=ceiling(1.9)

Returns: n=2

exp(X)

Takes a number, X, and returns eX.

X can be a number, field or expression.

... | eval y=exp(3)

Returns: y=20.0855369231877

floor(X)

Rounds a number, X, down to the nearest whole integer.

X can be a number, field or expression.

... | eval n=floor(1.9)

Returns: 1

mod(X, Y)

Returns the modulo of X and Y. (X%Y; the remainder of X divided by Y.)
  • ... | eval newField = mod(25,10)
  • Returns: 5

power(X,Y)

Returns a value representing one number raised to the power of another number. X is the base and Y the exponent.

X and Y can be a number, field or expression.

... | eval newField = power(2, 3)

Returns: 8

round(X, Y)

Rounds X to the nearest integer. Y is the precision to use, if omitted the default precision is zero.

X can be a number, field or expression. Y is a numeric value to indicate the precision.
  • ... | eval n=round(1.4)
  • Returns: 1
  • ... | eval n=round(1.5)
  • Returns: 2

sign(X)

Returns a value of -1, 0, or 1 representing the arithmetic sign of the argument.
  • ... | eval newField = sign(-8.4)
  • Returns: -1
  • ... | eval newField = sign(4)
  • Returns: 1
  • ... | eval newField = sign(0)
  • Returns: 0

sqrt(X)

Takes one numeric argument, X, and returns its square root.

X can be a number, field or expression.

... | eval n=sqrt(9)

Returns: 3

trunc(X,Y)

Returns the expression value truncated (toward zero).

X can be a number, field or expression. Y is a numeric value to indicate the precision.
  • ... | eval newField = trunc(1.9)
  • Returns: 1
  • ... | eval newField = trunc(2.89999, 2)
  • Returns: 2.89

Statistical Functions

Function

Description

Example

greatest(X,Y[,Z,N, ...])

Returns the largest value in a list of expressions. The list is up to 20 elements long.

In the list of expressions all elements must be of same type.

The only supported types are numeric and string. X can be a number, field or expression.
  • ... | eval newField = greatest(7, 5, 9)
  • Returns: 9
  • ... | eval newField = greatest('sit', 'site', 'sight')
  • Returns: site
  • ... | eval newField = greatest(bytesIn, 100)
  • Returns: 100, when bytesIn is less than 100

least(X,Y[,Z,N, ...])

Returns the smallest value in a list of expressions. The list is up to 20 elements long.

In the list of expressions all elements must be of same type.

The only supported types are numeric and string. X can be a number, field or expression.
  • ... | eval newField = least(7, 5, 9)
  • Returns: 5
  • ... | eval newField = least('sit', 'site', 'sight')
  • Returns: sight
  • ... | eval newField = least(bytesIn, 100)
  • Returns: 100, when bytesIn is greater than 100

randomint(X)

Returns a random number between 0 and X-1.

X can be any positive integer between the values 1 and 9,223,372,036,854,775,807.

... | eval newField = randomint(10)

Returns: a random number between 0 and 9

Text Functions

Function

Description

Example

length(X)

Returns the character length of a string, X.
  • ... | eval n=length(field)
  • Returns: the length of (field). If the field is 256 characters long, it returns n=256.
  • ... | eval n=length(“abc”)
  • Returns: n=3 (abc is a literal string, surrounded by double quotes)

lower(X)

Takes a string argument, X, and returns the lowercase version.
  • ... | eval name=lower("USERNAME" )
  • ... | eval name=tolower("USERNAME" )

Returns: the value of the field username in lowercase. If the username field contains FRED BROWN, it returns name=fredbrown.

substr(X,Y,Z)

This function returns a new string that is a substring of string X.The substring begins with the character at index Y and extends up to the character at index Z-1.The index is a number that indicates the location of the characters in string X, from left to right, starting with zero.

Y can be negative.

Z cannot be negative.
  • ...| eval n=substr("ArcSight", 5, 6)
  • Returns: “g”
  • ...| eval n=substr("ArcSight", 2, 6)
  • Returns: “cSig”
  • ...| eval n=substr("ArcSight", 0, 3)
  • Returns: “Arc”

trim(X)

ltrim(X)

rtrim(X)

trim(X) removes all spaces from both sides of the string X.

ltrim(X) removes all spaces from the left side of the string X.

rtrim(X) removes all spaces from the right side of the string X.

For the sake of these examples, assume that X is a literal string and _ represents any number of space characters.

  • ... | eval trimmed=ltrim(“_string_”)
  • Returns: trimmed=“string_”
  • ... | eval trimmed=rtrim(“_string_”)
  • Returns: trimmed=“_string”
  • ... | eval trimmed=trim(“_string_”)
  • Returns: “string”

upper(X)

Takes one string argument and returns the uppercase version.
  • ... | eval name=upper(“username”)
  • ... | eval name=toupper(“username”)

Returns: the value of the field username in uppercase. If username contains fred brown, it returns name=FRED BROWN.

Trigonometry Functions

Function

Description

Example

 

 

 

acos(X)

Takes one numeric argument, X, and returns its trigonometric inverse cosine.

...| eval newField = acos(0.3)

Returns: 1.2661036727795

asin(X)

Takes one numeric argument, X, and returns its trigonometric inverse sine.

...| eval newField = asin(3)

Returns: 0.304692654015398

atan(X)

Takes one numeric argument, X, and returns its trigonometric inverse tangent.

...| eval newField = atan(3)

Returns: 0.291456794477867

atan2(X,Y)

Returns a value representing the trigonometric inverse tangent of the arithmetic dividend of the arguments.

...| eval newField = atan2(2,1)

Returns: 1.10714871

cos(X)

Takes one numeric argument, X, and returns its trigonometric cosine.

...| eval newField = cos(3)

Returns: 2435538

cosh(X)

Takes one numeric argument, X, and returns its hyperbolic cosine.

...| eval newField = cosh(3)

Returns: 10.0676619957778

cot(X)

Takes one numeric argument, X, and returns its trigonometric cotangent.

...| eval newField = cot(3)

Returns: -7.01525255143453

ln(X)

Takes a number, X, and returns its natural log.

X can be a number, field or expression.

... | eval lnBytes=ln(bytesIn)

Returns: the natural log of the value of " bytesIn". If "bytesIn" contains 100, returns 4.605170186.

log(X, Y)

Returns the logarithm to the specified base of the argument.

X is the base and Y can be a number, field or expression. X is optional. If not specified, it will take 10 as the default value.
  • ... | eval test1= log (10,2)
  • Returns: 0.301
  • ... | eval test1 = log (2)
  • Returns: 0.301 as it takes the default base as 10

log10(X)

(Evaluates the log of number X with base 10.

X can be a number, field or expression.

... | eval num=log10(10000)

Returns: 4

sin(X)

Takes one numeric argument, X, and returns its trigonometric sine.

...| eval newField = sin(3)

Returns: 0.141120008059867

sinh(X)

Takes one numeric argument, X, and returns its hyperbolic sine.

...| eval newField = sinh(3)

Returns: 10.0178749274099

tan(X)

Takes one numeric argument, X, and returns its trigonometric tangent.

...| eval newField = tan(3)

Returns: -0.142546543074278

tanh(X)

Takes one numeric argument, X, and returns its hyperbolic tangent.

...| eval newField = tanh(3)

Returns: 0.99505475368673