• Latest
  • Trending
  • All
  • News
  • Business
  • Politics
  • Science
  • World
  • Lifestyle
  • Tech
Public preview of Microsoft Defender ATP web content filtering is now free for enterprise users

Approximate, partial and combined lookups in Azure Sentinel

December 4, 2020
Telecom Provider Migrates Confidently to Microsoft Azure with Fortinet’s Dynamic Cloud Security Solutions

Veeam Backup & Replication 11: Enhanced data management for a multi-cloud environment

February 25, 2021
8×8 makes raft of updates to platform

Advancing the Orchestration of Distributed Edge Applications, ZEDEDA Integrates with Microsoft Azure IoT

February 25, 2021
A moment of reckoning: the need for a strong and global cybersecurity response

Cloud Security in Banking Market to Witness Huge Growth by 2026 | Microsoft Azure, Trend Micro, Salesforce

February 25, 2021
Innovative solutions for IT workers at home

ZEDEDA Announces Integration with Microsoft Azure IoT to Seamlessly and Securely Orchestrate Distributed Edge Computing Workloads at Scale

February 24, 2021
A moment of reckoning: the need for a strong and global cybersecurity response

ZEDEDA integrates with Microsoft Azure IoT to provide full lifecycle management capabilities

February 24, 2021
Innovative solutions for IT workers at home

SolarWinds Attack: Proof That On-Premises Active Directory Still an Effective Initial Access Vector

February 23, 2021
A moment of reckoning: the need for a strong and global cybersecurity response

Microsoft Affirms Solorigate Attackers Saw Azure, Intune and Exchange Source Code

February 23, 2021
How to use Microsoft Sysmon, Azure Sentinel to log security events

OPS101 – Securing your Hybrid environment – Part 1 – Azure Security Center

February 22, 2021
A moment of reckoning: the need for a strong and global cybersecurity response

Microsoft Ending Azure Information Protection Connections to Microsoft Defender for Endpoint

February 22, 2021
8×8 makes raft of updates to platform

Indonesian Mobile Operator Selects NTT for Microsoft Security Project

February 22, 2021
Microsoft To Build New Azure Cloud Data Centers In Greece

NTT completes Microsoft security project for Indonesian mobile operator

February 19, 2021
A moment of reckoning: the need for a strong and global cybersecurity response

Data insights without limit, security without compromise

February 18, 2021
  • About
  • Advertise
  • Privacy & Policy
  • Contact
Thursday, February 25, 2021
  • Login
Azure Security News
  • Home
    • Home – Layout 1
    • Home – Layout 2
    • Home – Layout 3
    • Home – Layout 4
    • Home – Layout 5
  • News
    • All
    • Business
    • Politics
    • Science
    • World
    A moment of reckoning: the need for a strong and global cybersecurity response

    Cloud Security in Banking Market to Witness Huge Growth by 2026 | Microsoft Azure, Trend Micro, Salesforce

    Innovative solutions for IT workers at home

    ZEDEDA Announces Integration with Microsoft Azure IoT to Seamlessly and Securely Orchestrate Distributed Edge Computing Workloads at Scale

    A moment of reckoning: the need for a strong and global cybersecurity response

    ZEDEDA integrates with Microsoft Azure IoT to provide full lifecycle management capabilities

    Innovative solutions for IT workers at home

    SolarWinds Attack: Proof That On-Premises Active Directory Still an Effective Initial Access Vector

    A moment of reckoning: the need for a strong and global cybersecurity response

    Microsoft Affirms Solorigate Attackers Saw Azure, Intune and Exchange Source Code

    8×8 makes raft of updates to platform

    Indonesian Mobile Operator Selects NTT for Microsoft Security Project

    Microsoft To Build New Azure Cloud Data Centers In Greece

    NTT completes Microsoft security project for Indonesian mobile operator

    A moment of reckoning: the need for a strong and global cybersecurity response

    Data insights without limit, security without compromise

    8×8 makes raft of updates to platform

    What Is Object Storage?

    A moment of reckoning: the need for a strong and global cybersecurity response

    Azure Firewall Premium now in preview

    Trending Tags

    • Donald Trump
    • Future of News
    • Climate Change
    • Market Stories
    • Election Results
    • Flat Earth
  • Tech
    • All
    • Apps
    • Gear
    • Mobile
    • Startup
    Telecom Provider Migrates Confidently to Microsoft Azure with Fortinet’s Dynamic Cloud Security Solutions

    Veeam Backup & Replication 11: Enhanced data management for a multi-cloud environment

    8×8 makes raft of updates to platform

    Advancing the Orchestration of Distributed Edge Applications, ZEDEDA Integrates with Microsoft Azure IoT

    How to use Microsoft Sysmon, Azure Sentinel to log security events

    OPS101 – Securing your Hybrid environment – Part 1 – Azure Security Center

    A moment of reckoning: the need for a strong and global cybersecurity response

    Microsoft Ending Azure Information Protection Connections to Microsoft Defender for Endpoint

    Microsoft To Open Azure Cloud Data Center Region In Spain

    EMC Corporation Townsend security Hewlett-Packard Enterprise Gemalto N.V. Microsoft Azure Google Thales e-security International Business Machines (IBM) Broadcom

    A moment of reckoning: the need for a strong and global cybersecurity response

    Azure Engineer at VillageMD

    Innovative solutions for IT workers at home

    How to Sync On-Premise Active Directory Passwords with Office 365 and Google Apps in Real-Time

    Microsoft Azure Forms Collaboration to Enhance AI in Healthcare

    Azure Defender is now available for all IoT and OT devices

    Telecom Provider Migrates Confidently to Microsoft Azure with Fortinet’s Dynamic Cloud Security Solutions

    Google and Microsoft ID Group Targeting Security Researchers

    Innovative solutions for IT workers at home

    Microsoft Releases Application Guard for Office, Plus Azure Security Center and Azure Defender for IoT Products

    Trending Tags

    • Flat Earth
    • Sillicon Valley
    • Mr. Robot
    • MotoGP 2017
    • Golden Globes
    • Future of News
  • Entertainment
    • All
    • Gaming
    • Movie
    • Music
    • Sports
    Microsoft Flight Simulator Continues to Look Stunning in New Snow Video

    Microsoft Flight Simulator Continues to Look Stunning in New Snow Video

    Meet the woman who’s making consumer boycotts great again

    New campaign wants you to raise funds for abuse victims by ditching the razor

    Twitter tweaks video again, adding view counts for some users

    A beginner’s guide to the legendary Tim Tam biscuit, now available in America

    People are handing out badges at Tube stations to tackle loneliness

    Trump’s H-1B Visa Bill spooks India’s IT companies

    Magical fish basically has the power to conjure its own Patronus

    This Filipino guy channels his inner Miss Universe by strutting in six-inch heels and speedos

    Oil spill off India’s southern coast leaves fisherman stranded, marine life impacted

  • Lifestyle
    • All
    • Fashion
    • Food
    • Health
    • Travel
    Microsoft Seriously Beefs Up Security in Windows Server 2019

    Microsoft Offers More ‘Solorigate’ Advice Using Microsoft 365 Defender Tools

    A moment of reckoning: the need for a strong and global cybersecurity response

    Solar Winds, Office 365 & Shipbuilding…

    Aruba ClearPass Policy Manager Integrates with Microsoft

    Imprivata Expands Collaboration with Microsoft on New Digital Identity Innovations

    Microsoft Seriously Beefs Up Security in Windows Server 2019

    Microsoft Canada’s 10 biggest stories of 2020

    FAA issues new proposed Boeing 737 MAX pilot training procedures

    FAA issues new proposed Boeing 737 MAX pilot training procedures

    AMD breaks revenue records for 2019 and 4Q

    AMD breaks revenue records for 2019 and 4Q

    Microsoft and Analog Devices pair on 3D imaging tech

    Microsoft and Analog Devices pair on 3D imaging tech

    Microsoft is killing off insecure Cloud App Security cipher suites

    Microsoft is killing off insecure Cloud App Security cipher suites

    Rap group call out publication for using their image in place of ‘gang’

    Meet the woman who’s making consumer boycotts great again

    Trending Tags

    • Golden Globes
    • Mr. Robot
    • MotoGP 2017
    • Climate Change
    • Flat Earth
No Result
View All Result
Azure Security News
No Result
View All Result
Home News

Approximate, partial and combined lookups in Azure Sentinel

by AZURE SECURITY NEWS EDITOR
December 4, 2020
in News
0
Public preview of Microsoft Defender ATP web content filtering is now free for enterprise users
492
SHARES
1.4k
VIEWS
Share on FacebookShare on Twitter

Many of you found Nir Gafni’s “Implementing Lookups in Azure Sentinel” article useful. A common question you made was how to lookup partial values. For example, does the account name matches a list of patterns? Or is an IP address in a list of subnets? Partial lookups are a significant challenge with lookups in other SIEM products and often require reverting to hard to maintain methods such as nested filters.

In this blog post, I will show you how to implement partial lookups with Azure Sentinel. I will also venture into more advanced areas such as combined multi-condition lookups.

This post of part of a series of blog posts on writing rules in Azure Sentinel:

  • Azure Sentinel correlation rules using lists
  • Azure Sentinel correlation rules using the join operator
  • Implementing Lookups in Azure Sentinel 
  • Approximate, partial and combined lookups (this post)
  • Handling sliding windows in Azure Sentinel rules

Phrase lookup: the has_any operator

The simplest and most efficient way to do partial lookups in Azure Sentinel is to use the “has_any” operator. While the examples in Implementing Lookups in Azure Sentinel used the “in” and “!in” operators which do an exact match, the “has_any” operator searches for any one of a list of lookup phrases in the target field.

Let’s look into a user watchlist example in Linux Syslog messages. We want to alert only when specific users, stored in a list, fail to login with a wrong password.

Let’s start with the lookup table:

let keywords_table = datatable (w: string) ['postgres','nagios','doker'];

I am using the datatable operator to create the table for convenience so that you can use the example. However, you can use any lookup table source as described in implementing Lookups in Azure Sentinel, such as using the “externaldata” data operator, custom tables, or storing the “datatable” operator in a function.  

Next, we need to convert the table to a list:

let lookup = toscalar(keywords_table| summarize l=make_list(w));

This command could be combined with the previous or the next and is separated here to make it more readable.

Lastly, we use the has_any operator to look for only failed password events for the users in the lookup table:

Syslog
| where SyslogMessage startswith "Failed password"
| where SyslogMessage has_any (lookup)

The “has_any” operator is handy, but is limited to a phrase delimited by word boundaries and would not match any substring. We need another solution for substring matching and other partial matching operators.

Watch listing by IP ranges: the mv-apply operator

A widespread watch list scenario that requires partial lookups is selecting events based on IP ranges. IP ranges are usually represented in a CIDR notation. For example, the internal IP address range 192.168.x.x is represented by the CIDR notation 192.168.0.0/16.

As an example, let’s implement an IP range watch list using Azure Sentinel that selects only events that originated in an internal network. The lookup table is the following table, which includes private IP address ranges:

let private_ranges = datatable (ip_range: string) ['192.168.0.0/16', '172.16.0.0/12', '10.0.0.0/8'];

As mentioned, the “datatable” operator can be “externaldata”, a custom table, or a function.  

Here again, we need to convert the table to a list, using the following command:

let lookup = toscalar(private_ranges| summarize l=make_list(ip_range));

Now, that we have the watch list ready, we can use it:

CommonSecurityLog
| where TimeGenerated > ago(5m)
| where …
| mv-apply l=lookup to typeof(string) on
(
  where ipv4_is_match (SourceIP, l)
)
| project-away l

The important operator here is “mv-apply“, which applies the query (in red) to every value in the lookup list over every record.  

Since mv-apply applies every value in the lookup table to every record, it is performance and memory intensive. If not used carefully, your query may fail. Therefore:

  • Ensure that you use it at the end of your query after you filtered by other criteria that are less demanding.
  • Limit the timespan on which you perform the query as much as possible.
  • Use a reasonably sized lookup table.
  • Lastly, the slightly more complex method presented below for allow-listing can be used for watch lists as well and has better performance.

A corner case you may encounter is duplicate results. The result set includes a record for each match, so if an input record matches two elements, it appears twice in the result set. The duplication cannot happen in the example above as the lookup values are mutually exclusive.

In some cases, duplicate records are useful, as you would like to analyze each lookup value the record matched. If not, you might need to add something along the following lines. The query requires that a set of fields uniquely identify an event. For SecurityEvents, “EventOriginId” does the trick:

| summarize arg_max(TimeGenerated, *) by EventOriginId

The solution presented for allow-lists below also overcomes this issue.

There are also a few subtleties when using mv-apply

  • You need to explicitly say what the type of “l” is using the “to typeof()” phrase is.
  • “l” is added to every record and has to be “projected-away”

Allow-listing by username

Since it returns a record for every match, the technique above cannot be used for allow-listing. For example, if you try to exclude private IP ranges by reversing the logic to:

where not(ipv4_is_match (SourceIP, l))

You get 3 repeats of every record that does not match the ranges, and 2 repeats for each one that matches one of them. Certainly not the intended result.

So, how can we allow-list?

The solution is to find using the technique presented for watchlists the list of matching values and apply it using a regular, exact, lookup.

Setting up the lookup table is similar to the examples above. In this case, I combined the table and list creation into one statement:

let lookup=toscalar(datatable(p:string) ['user','admin'] | summarize l=make_list(p));

Next, we use the same mv-apply to check the Account names against the lookup list:

let matched_users = toscalar(
SecurityEvent
| where TimeGenerated > ago(5m)
| where …
| summarize by Account
| mv-apply l=lookup to typeof(string) on
(
  where Account contains l
)
| summarize make_list(Account));

Notice that here we used the “contains” operator. Any operator, complex expressions, or even full queries, can be used in an “mv-apply” sub-query.

Since we do not need the events but only user names to continue the analysis, we can summarize by account (in green) to get a list of unique account names, making this version much more efficient. Lastly, we have created a list of the resulting values (orange), which is a list of account names that contain any of the lookup values.

We use the list generated above as a allow list using the “!in” operator (blue)

SecurityEvent
| where TimeGenerated > ago(5m)
| where …
| where Account !in (matched_users)

We used the same event filter when generating the exclusion list and when using it. While in a simple Azure Sentinel analytics rule which does not filter explicitly by the time this would work, it runs a risk, in some circumstance, that the two runs will not apply to the same event set. The following variant uses the “materialize” operator (magenta) to resolve that:

let lookup=toscalar(datatable(p:string) ['user','admin'] | summarize l=make_list(p));
let events=materialize(SecurityEvent | where TimeGenerated  > ago(5m) | where …);
let matched_users = toscalar(events
| summarize by Account
| mv-apply l=lookup to typeof(string) on
(
  where Account contains l
)
| summarize make_list(Account));
events
| where Account !in (matched_users)

Handling multiple conditions

Ready to get the full strength of Azure Sentinel? The following example demonstrated how to take the technique presented in this blog post a step further. It adds a couple of dimensions:

  • Allowing a different operator to be selected for each lookup value
  • Combining conditions over several fields

First, the lookup table includes, in addition to the username pattern, also an operator. The query supports “contains”, “startswith” and “endswith”. It also includes the condition for the additional field we want to lookup, AccountType in this case.

let lookup=toscalar(datatable(op:string, account:string, accounttype:string) [
    'startswith', '\\admin', 'User',
    'endswith', 'dc$', 'Machine']
| summarize l=make_list(pack('op',op,'account',account, 'accounttype',accounttype)));

Notice that to make all the values available to the mv-apply operation which requires a list input, the list is now make of tuples using KQL “dynamic” type and the “pack” function which creates the dynamic value.

The main template used below for the query is the user allow-listing described above. However, the sub-query used by the mv-apply operator now handles both the operator selection and the additional condition, demonstrating the capabilities available when using mv-apply:

Notice that to make all the values available to the mv-apply operation which requires a list input, the list is now make of tuples using KQL “dynamic” type and the “pack” function which creates the dynamic value.

The main template used below for the query is the user allow-listing described above. However, the sub-query used by the mv-apply operator now handles both the operator selection and the additional condition, demonstrating the capabilities available when using mv-apply:

let events=materialize(SecurityEvent | where TimeGenerated > ago(5m) | where …);
let matched_users = toscalar(
events
| summarize by Account, AccountType
| mv-apply l=lookup on
(
    extend match = case (
        l['op'] == "contains",
            Account contains l['account'] and AccountType == l['accounttype'],
        l['op'] == "startswith",
            Account startswith l['account'] and AccountType == l['accounttype'],
        l['op'] == "endswith",
            Account endswith l['account'] and AccountType == l['accounttype'],
        False
    )
)
| where match | summarize make_list(strcat(AccountType, Account)));
events | where strcat(AccountType, Account) in (matched_users)

Reference: https://techcommunity.microsoft.com/t5/azure-sentinel/approximate-partial-and-combined-lookups-in-azure-sentinel/ba-p/1393795

Share197Tweet123Share49
AZURE SECURITY NEWS EDITOR

AZURE SECURITY NEWS EDITOR

Related Posts

A moment of reckoning: the need for a strong and global cybersecurity response

Cloud Security in Banking Market to Witness Huge Growth by 2026 | Microsoft Azure, Trend Micro, Salesforce

by AZURE SECURITY NEWS EDITOR
February 25, 2021
0

Latest launched research document on Global Cloud Security in Banking Market study of 111 Pages provides detailed analysis with presentable...

Innovative solutions for IT workers at home

ZEDEDA Announces Integration with Microsoft Azure IoT to Seamlessly and Securely Orchestrate Distributed Edge Computing Workloads at Scale

by AZURE SECURITY NEWS EDITOR
February 24, 2021
0

Native integration with ZEDEDA’s orchestration solution for the distributed edge enables end-to-end remote management of the entire Azure IoT Edge...

A moment of reckoning: the need for a strong and global cybersecurity response

ZEDEDA integrates with Microsoft Azure IoT to provide full lifecycle management capabilities

by AZURE SECURITY NEWS EDITOR
February 24, 2021
0

ZEDEDA announced an integration with Microsoft Azure IoT services that provides customers with full lifecycle management capabilities (edge hardware, OS, Azure IoT Edge...

Innovative solutions for IT workers at home

SolarWinds Attack: Proof That On-Premises Active Directory Still an Effective Initial Access Vector

by AZURE SECURITY NEWS EDITOR
February 23, 2021
0

In December, the disclosure of the supply chain attack against SolarWinds sent shockwaves throughout federal agencies responsible for the security...

  • Trending
  • Comments
  • Latest
Microsoft’s CyberX Acquisition Boosts Security of Azure IoT Lineup

AZURE DEFAULT RESOURCE GROUP AND DEFAULT WORKSPACE: WHAT ARE THEY?

December 14, 2020
Microsoft Seriously Beefs Up Security in Windows Server 2019

TCS Launches Cloud Exponence on Microsoft Azure

January 21, 2021
Microsoft Launches Host of Security Products in Time for RSA

Microsoft to add two new Microsoft 365 security, compliance bundles to its line-up

November 26, 2020

Lady Gaga Pulled Off One of the Best Halftime Shows Ever

0

Barack Obama’s Now Mainly Focusing on Wearing This Casual Backwards Hat

0

Watch Justin Timberlake’s ‘Cry Me a River’ Come to Life in Mesmerizing Dance

0
Telecom Provider Migrates Confidently to Microsoft Azure with Fortinet’s Dynamic Cloud Security Solutions

Veeam Backup & Replication 11: Enhanced data management for a multi-cloud environment

February 25, 2021
8×8 makes raft of updates to platform

Advancing the Orchestration of Distributed Edge Applications, ZEDEDA Integrates with Microsoft Azure IoT

February 25, 2021
A moment of reckoning: the need for a strong and global cybersecurity response

Cloud Security in Banking Market to Witness Huge Growth by 2026 | Microsoft Azure, Trend Micro, Salesforce

February 25, 2021
Azure Security News

Copyright © 2020 - Azure Security

Navigate Site

  • About
  • Advertise
  • Privacy & Policy
  • Contact

Follow Us

No Result
View All Result
  • Home
  • News
    • Politics
    • Business
    • World
    • Science
  • Entertainment
    • Gaming
    • Music
    • Movie
    • Sports
  • Tech
    • Apps
    • Gear
    • Mobile
    • Startup
  • Lifestyle
    • Food
    • Fashion
    • Health
    • Travel

Copyright © 2020 - Azure Security

Welcome Back!

Login to your account below

Forgotten Password?

Create New Account!

Fill the forms below to register

All fields are required. Log In

Retrieve your password

Please enter your username or email address to reset your password.

Log In