Using the 'lookup' function set in SQL queries

The lookup function set (see the table below) is used in the WHERE clause of an SQL query to add conditions for assets and accounts to the search query.

Description of the 'lookup' set of functions

Function type

Event field

Asset field

Operator

Description

lookup_assets

 

DeviceAssetID

SourceAssetID

DestinationAssetID

 

name

os

osBuild

source

cii

ramSize

kscRtProtectionState

kscEncryptionState

kscAntiSpamStatus

kscEmailAvStatus

kscDlpStatus

kscStatusID

kscEdrStatus

aistatus

aiscore

=

Allows you to add conditions to the query result for assets whose field values match the value specified in the query.

For values that are number, you must specify a string with the required number.

For example:

lookup_assets(DeviceAssetID, osBuild, '=', '19045.000000')

lookup_assets(DeviceAssetID, ramSize, '=', '3241234234')

kscLastAvBasesUpdate

kscLastInfoUpdate

kscLastUpdate

kscLastSystemStart

lastVisible

ramSize

aiscore

<

>

<=

>=

Allows you to add conditions for assets with field values that are:

  • Less than the value specified in the query
  • Greater than the value specified in the query
  • Less than or equal to the value specified in the query
  • Greater than or equal to the value specified in the query

For values that are number, you must specify a string with the required number.

For example:

lookup_assets(DeviceAssetID, aiscore, '>', '50')

Date values can be specified in the following formats:

  • Number of seconds:

    lookup_assets(DeviceAssetID, lastVisible, '>', '1725971837')

  • Number of milliseconds:

    lookup_assets(DeviceAssetID, lastVisible, '<', '1725971837834')

  • Number of days:

    lookup_assets(DeviceAssetID, lastVisible, '<', 'now-15d')

 

name

os

osBuild

fqdn

software

like

Allows you to add conditions to the query result for assets whose field values match the 'like' expression specified in the query.

For more information about the like operator, please refer to the SQL Language Expressions reference.

lookup_assets(DestinationAssetID, name, 'like','test.company.com')

ipAddress

macAddress

fqdn

software

kscStatusMask

in

Allows you to add to the result asset conditions whose values match the value specified in the query.

For example:

lookup_assets(DeviceAssetID, fqdn , 'in', ['test_fqdn1.company.ru', 'test_fqdn2.company.ru'])

ipAddress

insubnet

Allows you to add conditions for assets with IP addresses in the specified subnet. The IP address is specified in CIDR format.

For example:

lookup_assets(DeviceAssetID, ipAddress, 'insubnet', '10.0.0.0/24')

ipAddress

inrange

Allows you to add conditions for assets with IP addresses in the specified range.

The first IP address specified in the query is the start IP address, and the second is the end address.

For example:

lookup_assets(DeviceAssetID, ipAddress, 'inrange', ['192.168.1.1', '192.168.1.184'])

The query filters assets with IP addresses in the range from 192.168.1.1 to 192.168.1.184.

lookup_accounts

 

SourceAccountID

DestinationAccountID

 

name

guid

domain

cn

dn

employee_id

email

mail_nickname

mobile

sid

sam_name

telephone_number

principal_name

sn

title

division

department

manager

location

company

street_address

physical_delivery_office_name

uac

=

Allows you to add conditions to the query result for accounts whose field values match the value specified in the query.

For values that are number, you must specify a string with the required number.

For example:

lookup_accounts(SourceAccountID, employee_id, '=', '15452')

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

like

Allows you to add conditions to the query result for accounts whose field values match the 'like' expression specified in the query.

For more information about the like operator, please refer to the SQL Language Expressions reference.

lookup_accounts(DestinationAccountID, name, 'like', '_ccount1%')

lookup_assets_category

DeviceAssetID

SourceAssetID

DestinationAssetID

—

—

Finds all events that have associated assets in the specified folder.

For example:

lookup_assets_category(DeviceAssetID, ['Main/Business Impact'])

where Main is the name of the tenant and Business Impact is the name of the folder.

Example SQL queries with a lookup function

You can specify a lookup function when manually creating SQL queries. Example queries with a lookup function

  • All events of the 'events' table with the TenantID and DeviceProduct event fields, as well as the fqdn asset field, whose value contains the win10 substring. The number of rows that can be displayed in the table is 250.

    SELECT TenantID, DeviceProduct

    FROM `events`

    WHERE DeviceAssetID != ''

    AND lookup_assets(DeviceAssetID, fqdn, 'match', 'win10')

    LIMIT 250

  • All events in the 'events' table, with the TenantID and DeviceProduct event fields, and with values of the EmployeeID field, whose value is 15452. The number of rows that can be displayed in the table is 10.

    SELECT TenantID, DeviceProduct

    FROM `events`

    WHERE SourceAccountID != ''

    AND lookup_accounts(SourceAccountID, employee_id, '=', '15452')

    LIMIT 10

  • All events of the 'events' table with the TenantID and DeviceProduct event fields, with the folder in which the asset is located. The number of rows that can be displayed in the table is 100.

    SELECT TenantID, DeviceProduct

    FROM `events`

    WHERE DeviceAssetID != ''

    AND lookup_assets_category(DeviceAssetID, ['Main/Business Impact'])

    LIMIT 100

Page top