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
|
[Topic 295026]