Lookup Table Functions

Details of the following lookup table functions are available.

• LookupTableGet
• LookupTableHas

LookupTableGet Function

LookupTableGet (<tableName>,<eventAttribute>,<tableColumn>) searches the Lookup Table <tableName> and matches the keys with <eventAttribute>. If a matching row is found then it returns the <tableColumn> from that row. If the Lookup Table has multiple keys then corresponding event attributes must be specified.

Syntax

LookupTableGet (<tableName>,<eventAttribute>,<tableColumn>)

The number of eventAttributes must match the key fields in the Lookup Table.

In Elasticsearch, nesting is not supported.

In ClickHouse, nesting is not supported.

Scope

Available for rules and EventDB, ClickHouse and Elasticsearch queries.

Example

LookupTable: UserLoc

User (Key)	Country	Department
Bob	U.S.A	Finance
Alice	Germany	Engineering
John	U.S.A	Marketing

The following table shows the LookupTableGet Function applied to events.

phRecvTime	eventType	user	LookupTableGet(UserLoc, user, Country)	LookupTableGet(UserLoc, user,Department)
T1	Login-Success	John	U.S.A	Marketing
T2	Login-Failure	Alice	Germany	Engineering
T3	Login-Success	Alice	Germany	Engineering

LookupTableHas Function

LookupTableHas (<tableName>,<eventAttribute>) works the following way:

• Returns 1 if there is a row in Lookup table <tableName> where the key fields match the specified <eventAttribute>.
• Returns 0 if there is no match.

Syntax

LookupTableHas (<tableName>,<eventAttribute>)

The number of eventAttributes must match the key fields in the Lookup Table.

In Elasticsearch, nesting is not supported.

In ClickHouse, nesting is not supported.

Scope

Available for rules and EventDB, ClickHouse and Elasticsearch queries.

Example

LookupTable: LoginCountry

User (Key)	Country(Key)
Bob	U.S.A
Alice	Germany
John	U.S.A

The following table shows the LookupTableHas Function applied to events.

phRecvTime	eventType	user	srcGeoCountry	LookupTableHas(LoginCountry, user, srcGeoCountry)
T1	Login-Success	John	U.S.A	1
T2	Login-Failure	Alice	France	0
T3	Login-Success	Alice	Germany	1