KQL Search

Query Details

HomeAI ToolsDevice Query

Multiple Azure AAD Power Shell Anomaly

Query

let _SuccessResultTypes = toscalar(
    _GetWatchlist("ResultType-SignInLogsErrorCodes")
    | where Notes has_all ("[Success]", "[Complete]") and isnotempty(ResultDescription)
    | summarize make_list(ResultType)
);
let _PowerShellAppExpectedResource = 
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "PowerShellAppExpectedResource"
    | project AppId = tostring(SourceResource), ResourceIdentity = tostring(DestinationResource)
;
SignInLogsTables
| where ResultType in (_SuccessResultTypes)
| where AppDisplayName has_any ("CLI", "PowerShell", "Management Shell")
| join kind=leftanti _PowerShellAppExpectedResource on AppId, ResourceIdentity
| invoke UnifySignInLogs()
| project
    TimeGenerated,
    CreatedDateTime,
    Category,
    UserPrincipalName,
    UserDisplayName,
    IPAddress,
    Location,
    ResultType,
    AppDisplayName,
    ResourceDisplayName,
    AppId,
    ResourceIdentity,
    ResourceTenantId,
    ClientAppUsed,
    UserAgent,
    Status,
    LocationDetails,
    DeviceDetail,
    AuthenticationDetails,
    AuthenticationProcessingDetails,
    OriginalRequestId,
    CorrelationId

Explanation

This query retrieves sign-in logs data and filters it based on certain conditions.

First, it gets a list of success result types from a watchlist called "ResultType-SignInLogsErrorCodes" that have notes containing both "[Success]" and "[Complete]" and have a non-empty result description.

Then, it gets a list of expected significant activities from another watchlist called "Activity-ExpectedSignificantActivity" where the activity is "PowerShellAppExpectedResource" and projects the app ID and resource identity.

Next, it filters the sign-in logs tables based on the success result types obtained earlier, where the app display name contains either "CLI", "PowerShell", or "Management Shell".

After that, it performs a left anti join with the expected PowerShell app resources based on the app ID and resource identity.

Then, it invokes the "UnifySignInLogs" function.

Finally, it projects various columns from the sign-in logs data.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: October 10, 2023

Tables

SignInLogsTables

Keywords

Devices,Intune,User,SignInLogs,ResultType,Notes,ResultDescription,PowerShellAppExpectedResource,Activity,AppId,ResourceIdentity,AppDisplayName,CLI,PowerShell,ManagementShell,TimeGenerated,CreatedDateTime,Category,UserPrincipalName,UserDisplayName,IPAddress,Location,ResourceDisplayName,ResourceTenantId,ClientAppUsed,UserAgent,Status,LocationDetails,DeviceDetail,AuthenticationDetails,AuthenticationProcessingDetails,OriginalRequestId,CorrelationId

Operators

toscalar_GetWatchlistwherehas_allisnotemptysummarizemake_list_GetWatchlistwhereprojecttostringtostringwhereprojectwherejoininvokeproject

Actions