Audit Mandatory Office Days Using Advanced Hunting

AADSignInEventsBeta | where ErrorCode == "0" //Success only | where DeviceTrustType contains @"joined" //Corporate devices only for example, Adjust accordingly | extend dayofWeek = dayofweek(TimeGenerated)/1d // Monday is 1, Tuesday is 2 etc. | where dayofWeek == 2 or dayofWeek == 3 //Tuesday and wednesday are mandatory days for example | where not (ipv4_is_in_range(IPAddress, "10.0.0.0/8") or ipv4_is_in_range(IPAddress, "1.1.1.1/8") ) //add office Ranges to exclude them from the results | summarize count() by IPAddress, AccountUpn, City

Explanation

This KQL query is designed to analyze Azure Active Directory sign-in events with specific criteria. Here's a simplified summary of what the query does:

Filters for Successful Sign-ins: It only considers sign-in events where the error code is "0", indicating a successful sign-in.
Focuses on Corporate Devices: It filters the events to include only those from devices that are "joined" to the corporate network, which typically means they are managed or trusted devices.
Selects Specific Days: The query is interested in sign-ins that occur on Tuesdays and Wednesdays. It calculates the day of the week from the event's timestamp and filters for these two days.
Excludes Certain IP Ranges: It excludes sign-ins from specific IP address ranges, such as "10.0.0.0/8" and "1.1.1.1/8", which might represent office networks or other known locations.
Summarizes the Data: Finally, it summarizes the filtered sign-in events by counting the number of occurrences for each combination of IP address, user account (AccountUpn), and city.

In essence, this query is used to identify and count successful sign-ins from corporate devices on specific days, excluding known office locations, and grouping the results by IP address, user, and city.

KQL Search

Audit Mandatory Office Days Using Advanced Hunting

Query

Explanation

Details

Actions