KQL Search

This query is designed to analyze Azure Active Directory sign-in events and associated identity information. Here's a simple breakdown of what it does:

1. Data Source: It starts by looking at the AADSignInEventsBeta table, which contains sign-in event data.

2. Joining Tables: It performs a left outer join with the IdentityInfo table using the AccountUpn (User Principal Name) as the key. This means it combines data from both tables, keeping all records from AADSignInEventsBeta and adding matching records from IdentityInfo where available.

3. Optional Filtering: There's a commented-out line that suggests you could further filter the results to include only those records where the Department contains "Cyber" or the JobTitle contains "architect". This line is not currently active in the query.

4. Summarizing Data: It summarizes the data by finding the most recent LastPasswordChangeTimestamp for each user (AccountUpn) and CreatedDateTime. This means it groups the data by user and sign-in event date, and for each group, it selects the record with the latest password change time.

5. Selecting Columns: Finally, it projects (selects) only the AccountUpn, CreatedDateTime, and LastPasswordChangeTimestamp columns for the output.

In summary, this query retrieves sign-in events and identity information, finds the most recent password change time for each user and sign-in event date, and outputs a simplified list of users with their sign-in event date and last password change time.