3.2 Creating the Search Query

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.”

3.2.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.

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

3.2.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 src

where

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

3.2.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 VARCHAR 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

3.2.4 Specify a Group of Fields

Search enables you to quickly select fields that have common groupings. In the query, you can specify a group alias that displays all fields or columns associated with the group. The following table provides some common group aliases.

Group Alias

Includes a list of these fields or columns...

category

All category fields

custom float

All custom float fields

domain

All domain fields

hostname

All hostname columns

id

All ID columns

ip

All IP address columns

ip6

All IPv6 address columns

label

All label columns

mac

All MAC address columns

path

All path columns

port

All port columns

timestamp or time

All time columns (device receipt time, agent receipt time)

uri

All URI columns

url

All URL columns

username or user

All user columns

3.2.5 Specify an Alias for a Field

In the search query, you can enter the alias, or abbreviated term, for a field name rather than entering the full name. For the fields shown in the following table, you can also use the presentable field names, such as Agent Address. Search suggests presentable names.

Field

Aliases

agentAddress
  • agt
  • agent ip

agentHostName

ahost

agentId

aid

agentMacAddress
  • amac
  • agent mac

agentReceiptTime

art

agentTimeZone

atz

agentTranslatedAddress

agent translated ip

agentType

at

agentVersion

av

applicatonProtocol
  • app
  • protocol

baseEventCount

cnt

bytesIn

in

bytesOut

out

categoryBehavior

behavior

categoryDeviceGroup

device group

categoryObject

object

categorySignificance

significance

categoryTechnique

technique

destinationAddress
  • dst
  • destination ip
  • destinationip
  • dst ip
  • dest ip
  • target ip
  • targetip
  • target

destinationHostName
  • dhost
  • destination name

destinationMacAddress
  • dmac
  • destination mac

destinationNtDomain

dntdom

destinationPort
  • dpt
  • destination port
  • dstport
  • dest port
  • targetport
  • target port

destinationProcessId

dpid

destinationProcessName

dproc

destinationTranslatedAddress

destination translated ip

destinationuserId

duid

destinationUserName
  • duser
  • dst user
  • dest user
  • destination user
  • dst usr

destinationUserPrivileges

dpriv

deviceAction

act

deviceAddress
  • dvc
  • deviceaddr
  • deviceip
  • device ip

deviceCustomFloatingPointn

  • Valid values for n are integers between 1 and 4
  • For example: deviceCustomFloatingPoint1

cfpn

For example: cfp1

deviceCustomFloatingPointnLabel

  • Valid values for n are integers between 1 and 4
  • For example: deviceCustomFloatingPoint1Label

cfpnLabel

For example: cfp1Label

deviceCustomIPv6Addressn

  • Valid values for n are integers between 1 and 4
  • For example: deviceCustomIPv6Address2
  • c6an
  • device custom ipv6 n

For example: c6a2

deviceCustomIPv6AddressnLabel

  • Valid values for n are integers between 1 and 4
  • For example: deviceCustomIPv6Address2Label

c6anLabel

For example: c6a2Label

deviceCustomNumbern

  • Valid values for n are integers between 1 and 3
  • For example, deviceCustomNumber3

cnn

For example: cn3

deviceCustomNumbernLabel

  • Valid values for n are integers between 1 and 6
  • For example: deviceCustomNumber6Label

cnnLabel

For example: cn6Label

deviceCustomStringn

  • Valid values for n are integers between 1 and 6
  • For example: deviceCustomString5

Csn

For example: Cs5

deviceEventCategory

cat

deviceHostName

dvchost

deviceMacAddress
  • dvcmac
  • device mac

deviceProcessId

dvcpid

deviceReceiptTime

rt

deviceTimeZone

dtz

deviceTranslatedAddress

device translated ip

endTime

end

eventOutcome

outcome

fileNme

fname

fileSize

fsize

message

msg

requestUrl
  • request
  • URL

sourceAddress
  • src
  • source ip
  • sourceip
  • src ip

sourceHostName

shost

sourceMacAddress
  • smac
  • source mac

sourceNtDomain

sntdomain

sourcePort
  • spt
  • srcport
  • src port

sourceProcessId

spid

sourceProcessName

sproc

sourceTranslatedAddress

source translated ip

sourceUserId

suid

sourceuserName
  • suser
  • src user
  • source user
  • src usr

sourceUserPrivileges

spriv

startTime

start

transportProtocol

proto