GitHubContribute in GitHub: Edit online

ref_set_contains()

This function allows the user to query against a reference data set.

An example might be a reference data set that is populated with a set of malicious users, or a set of known vulnerable IP addresses.

Syntax

ref_set_contains (SetName , ColumnName)

ref_set_contains (SetName , "SetEntry")

Arguments

Expr Type Required Description
SetName string ✓ The name of the reference data set that you want to use.
ColumnName string ✓ A named column within the domain model.
SetEntry ✓ The specific set value that you want to search for. The value can be of type long, int, double, datetime, timespan, decimal, string, guid.

Returns

Returns results that relate to a specific reference data set.

Note: Reference data sets must exist before the use of this function. In the following example, user1 exists in the MaliciousUsers reference set.

Example 1

events
    | project data_source_name, name, user_id
    | where ref_set_contains(MaliciousUsers, user_id) and original_time > ago(30d)

This query provides a list of events that have a user_id that matches a user_id that exists in the MaliciousUsers reference set.

Results

data_source_name name user_id
microsoftWindowsSource Failure Audit: Privileged Object Operation Failed user1
microsoftWindowsSource6 Success Audit: Change Password Attempt Succeeded user1

Example 2

events
    | project user_id, src_ip
    | where original_time > ago(30d) and ref_set_contains(MaliciousIPs, src_ip)
    | summarize MaliciousIPCount=count_distinct(src_ip) by user_id

This query returns a list of users with a count of malicious IP addresses that are assigned to them. The IPs are categorized as malicious as they exist in the MaliciousIPs reference set.

Results

user_id MaliciousIPCount
user1 3
user2 4