Using the 'enrich' function set in SQL queries

The enrich function set (see the table below) is used in the SELECT clause of an SQL query to enrich the results.

When using this set of functions, you cannot group events by enriched fields.

Widgets and reports enriched by the functions must be owned by no more than one tenant.

The values of asset fields that are specified in SQL queries are case-sensitive.

Description of the 'enrich' set of functions

Function

Event field

Asset field

Description

enrich_assets

DeviceAssetID

DestinationAssetID

SourceAssetID

 

name

os

osBuild

ipAddress

macAddress

fqdn

source

software

cii

ramSize

kscLastAvBasesUpdate

kscLastInfoUpdate

kscLastUpdate

kscLastSystemStart

kscStatusMask

kscRtProtectionState

kscEncryptionState

kscAntiSpamStatus

kscEmailAvStatus

kscDlpStatus

kscStatusID

kscEdrStatus

lastVisible

aistatus

aiscore

Enriches the query result with asset fields based on the ID specified passed to the function.

For example:

enrich_assets(DeviceAssetID, name)

enrich_accounts

SourceAccountID

DestinationAccountID

name

guid

domain

cn

dn

employee_id

email

mail_nickname

mobile

sid

sam_name

telephone_number

principal_name

sn

type

title

division

department

manager

location

company

street_address

physical_delivery_office_name

uac

Enriches the query result with account fields based on the ID specified passed to the function.

For example:

enrich_account(SourceAccountID, name)

This function is available only for tenants that have LDAP enrichment enabled.

enrich_table

Any

—

Enriches the query result with values from a dictionary of the Table type based on event fields in the response from the ClickHouse cluster.

For example:

enrich_table('Main/some-table', [DeviceProduct], 'value')

Only one column of the table can be the key, but the record key can be a compound key.

For example:

enrich_table('Main/Dictionary', [BytesOut|BytesIn], 'value2')

The enrich_table function returns only one column of the table. If you need to call more columns, call the function again specifying the relevant fields.

enrich_dictionary

Any

—

Enriches the query result with values from a dictionary of the Dictionary type based on event fields in the response from the ClickHouse cluster.

For example:

  • enrich_dictionary('Main/some-dict', [SourceAddress]
  • enrich_dictionary('Main/some-dict', [SourceAddress, DeviceAddress])

You can view the path to the dictionary in the Resources → Dictionaries section.

Example SQL queries with an enrich function

You can specify an enrich function when manually creating SQL queries. Example queries with an enrich function

  • All events of the 'events' table with the Name and Message fields, as well as the macAddress and osBuild asset fields to enrich the result with asset data. The number of rows that can be displayed in the table is 250.

    SELECT Name,

    Message,

    enrich_assets(DeviceAssetID, macAddress),

    enrich_assets(DeviceAssetID, osBuild)

    FROM `events`

    WHERE DeviceAssetID != ''

    LIMIT 250

  • All events of the 'events' table with event fields SourceUserName and SourceAddress, as well as values of the mobile field to enrich the result with account data. The number of rows that can be displayed in the table is 10.

    SELECT SourceUserName,

    SourceAddress,

    enrich_accounts(SourceAccountID, mobile) as mobile

    FROM `events`

    WHERE SourceAccountID != ''

    LIMIT 10

  • All events in the events table with DeviceProduct event fields with values from the Main/[OOTB] Linux Auditd record types table. Sorted by Timestamp in descending order. The number of rows that can be displayed in the table is 1000.

    SELECT DeviceProduct,

    enrich_table('Main/[OOTB] Linux. Auditd record types', [DeviceProduct], 'Description') as HTTP

    FROM `events` WHERE Type!=4

    LIMIT 1000

  • All events in the events table with DeviceAction event fields with values from the Main/[OOTB] KEDR dictionary. The number of rows that can be displayed in the table is 250.

    SELECT DeviceAction,

    enrich_dictionary('Main/[OOTB] KEDR. AccountType', [BytesOut]) as accountType

    FROM `events` WHERE Type!=4

    LIMIT 255

Page top