Query Details
Identity Correlation
Query
// ENTER INFO ON USER(S)/HOSTS(S) INTO THE BELOW LIST // can be any combination of hostnames, usernames, email addresses, SIDs, or AzureAccountIDs let Search = dynamic(["[email protected]", "desktop-1234", "john.smith"]); // step 1: correlate all hosts and identities DeviceInfo | distinct DeviceName, LoggedOnUsers | project DeviceName, CurrentUsers = parse_json(LoggedOnUsers) | mv-apply CurrentUsers on ( project DeviceName, User = CurrentUsers.UserName, SID = CurrentUsers.Sid ) | project DeviceName, Username = tostring(User), OnPremSid = tostring(SID) | join kind=inner IdentityInfo on OnPremSid // step 2: filter for the given values | where DeviceName has_any (Search) or AccountObjectId in (Search) or Username in (Search) or EmailAddress has_any (Search) or OnPremSid in (Search) | extend FullName = strcat(GivenName, " ", Surname) | distinct DeviceName, FullName, JobTitle, EmailAddress, Username, OnPremSid, AccountObjectId
Explanation
This query is searching for information on users or hosts based on a list of input values. The input values can be hostnames, usernames, email addresses, SIDs, or Azure Account IDs.
The query first correlates all hosts and identities by joining the DeviceInfo and IdentityInfo tables.
Then, it filters the results based on the input values. It checks if the DeviceName, AccountObjectId, Username, EmailAddress, or OnPremSid matches any of the values in the input list.
Finally, it extends the results with additional information such as the user's full name and job title, and returns only the distinct DeviceName, FullName, JobTitle, EmailAddress, Username, OnPremSid, and AccountObjectId.
Details
C.J. May
Released: May 16, 2023
Tables
Keywords
Operators