Deployable detection rules

//Detect when a group is added to Azure AD with the 'Azure AD roles can be assigned to this group' flag enabled

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago(90d)
| where OperationName == "Add group"
| where parse_json(tostring(TargetResources[0].modifiedProperties))[1].displayName == "IsAssignableToRole"
| where parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue))[0] == true
| extend GroupName = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue))[0])
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['Actor IP Address'] = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| project TimeGenerated, OperationName, GroupName, Actor, ['Actor IP Address']

//Detect Azure AD PIM activiations outside of working hours

//Data connector required for this query - Azure Active Directory - Audit Logs

let timerange=30d;
AuditLogs
// extend LocalTime to your time zone
| extend LocalTime=TimeGenerated + 5h
| where LocalTime > ago(timerange)
// Change hours of the day to suit your company, i.e this would find activations between 6pm and 6am
| where hourofday(LocalTime) !between (6 .. 18)
| where OperationName == "Add member to role completed (PIM activation)"
| extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['Azure AD Role Name'] = tostring(TargetResources[0].displayName)
| project LocalTime, User, ['Azure AD Role Name'], ['Activation Reason']=ResultReason

//Detect when a service principal is added to Azure AD after working hours or on weekends

//Data connector required for this query - Azure Active Directory - Audit Logs

let Saturday = time(6.00:00:00);
let Sunday = time(0.00:00:00);
AuditLogs
| where TimeGenerated > ago(7d)
// extend LocalTime to your time zone
| extend LocalTime=TimeGenerated + 5h
// Change hours of the day to suit your company, i.e this would find activations between 6pm and 6am
| where dayofweek(LocalTime) in (Saturday, Sunday) or hourofday(LocalTime) !between (6 .. 18)
| where OperationName == "Add service principal"
//Exclude service principals created by managed identities (if you have automation tasks running this may trigger), but you can remove the exclusion if required
| where parse_json(tostring(InitiatedBy.app)).displayName != "Managed Service Identity"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend AppId = tostring(AdditionalDetails[1].value)
| extend ['Actor IP Address'] = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| project LocalTime, Actor, ['Actor IP Address'], AppId

//Alert on successful self service password resets at suspicious times

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
// extend LocalTime to your time zone
| extend LocalTime=TimeGenerated + 5h
| where LocalTime > ago(7d)
| where OperationName == "Reset password (self-service)"
| where ResultDescription == "Successfully completed reset."
// Change hours of the day to suit your company, i.e this would find self service password reset events between 11pm and 4am
| where hourofday(LocalTime) !between (4 .. 23)
| extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['IP Address of User'] = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| project LocalTime, OperationName, ResultDescription, User, ['IP Address of User']

//Detect a successful self service password reset or account unlock from an IP address that user hasn't successfully signed into from in the last 30 days

//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Azure Active Directory - Signin Logs

//Find successful password reset and account unlocks in the last day
AuditLogs
| where TimeGenerated > ago (1d)
| where OperationName == "Unlock user account (self-service)" and ResultDescription == "Success" or OperationName == "Reset password (self-service)" and ResultDescription == "Successfully completed reset."
| extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend IPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| project
    ['Reset Unlock or Time']=TimeGenerated,
    OperationName,
    UserPrincipalName,
    IPAddress
//Take the UserPrincipalName of the event and the IP address, join back to sign on logs to find events where the IP address has not been seen from that user
| join kind=leftanti 
    (
    SigninLogs
    | where TimeGenerated > ago(30d)
    | where ResultType == 0
    )
    on UserPrincipalName, IPAddress

//When a user holding a privileged role triggers an Azure AD risk event, retrieve the operations completed by that user
//Lookup the IdentityInfo table for any users holding a privileged role

//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Microsoft Sentinel UEBA

let privusers=
    IdentityInfo
    | where TimeGenerated > ago(21d)
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    | where isnotempty(AssignedRoles)
    | where AssignedRoles != "[]"
    | distinct AccountUPN;
AADUserRiskEvents
| where TimeGenerated > ago (7d)
| where UserPrincipalName in (privusers)
| where RiskDetail != "aiConfirmedSigninSafe"
| project RiskTime=TimeGenerated, UserPrincipalName
| join kind=inner
    (
    AuditLogs
    | where TimeGenerated > ago(7d)
    | extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    )
    on UserPrincipalName
| project-rename OperationTime=TimeGenerated
| project
    RiskTime,
    OperationTime,
    ['Time Between Events']=datetime_diff("minute", OperationTime, RiskTime),
    OperationName,
    Category,
    CorrelationId

//Find users who have failed 3 or more times to set a new password during a SSPR flow. Worth reaching out to them to give them a hand or see if you can onboard them to something like Windows Hello for Business

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where LoggedByService == "Self-service Password Management"
| extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['User IP Address'] = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| sort by TimeGenerated asc 
| summarize ['SSPR Actions']=make_list_if(ResultReason, ResultReason has "User submitted a new password") by CorrelationId, User, ['User IP Address']
| where array_length(['SSPR Actions']) >= 3
| sort by User desc

//Detect users who are trying to use self service password reset but failing as they don't have any authentication methods listed

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where LoggedByService == "Self-service Password Management"
| extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['User IP Address'] = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| sort by TimeGenerated asc 
| summarize ['SSPR Actions']=make_list(ResultReason) by CorrelationId, User, ['User IP Address']
| where ['SSPR Actions'] has "User's account has insufficient authentication methods defined. Add authentication info to resolve this"
| sort by User desc

//Detects when a user activates a PIM role for the first time on weekends or after working hours

//Data connector required for this query - Azure Active Directory - Audit Logs

let Saturday = time(6.00:00:00);
let Sunday = time(0.00:00:00);
let timeframe = 90d;
//Find users who have previously activated PIM roles outside of business hours or on weekends in the last 90 days
//In this example business hours are 6am to 6pm
let knownusers=
    AuditLogs
    | where TimeGenerated > ago(timeframe) and TimeGenerated < ago(7d)
    // extend LocalTime to your time zone
    | extend LocalTime=TimeGenerated + 5h
    // Change hours of the day to suit your company, i.e this would find activations between 6pm and 6am
    | where dayofweek(LocalTime) in (Saturday, Sunday) or hourofday(LocalTime) !between (6 .. 18)
    | where OperationName == "Add member to role completed (PIM activation)"
    | extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | distinct User;
//Find users who activate a PIM role outside of business hours or on weekends for the first time in the last week
AuditLogs
| where TimeGenerated > ago(7d)
| extend LocalTime=TimeGenerated + 5h
| where dayofweek(LocalTime) in (Saturday, Sunday) or hourofday(LocalTime) !between (6 .. 18)
| where OperationName == "Add member to role completed (PIM activation)"
| extend ['Azure AD Role Name'] = tostring(TargetResources[0].displayName)
| extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where User !in (knownusers)
| project LocalTime, User, ['Azure AD Role Name'], ['Activation Reason']=ResultReason

//Find when an Azure AD group is assigned (either permanent or eligble) to an Azure AD PIM assignment

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName in ("Add eligible member to role in PIM completed (permanent)", "Add member to role in PIM completed (permanent)")
| where TargetResources[2].type == "Group"
| extend GroupName = tostring(TargetResources[2].displayName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['Azure AD Role Name'] = tostring(TargetResources[0].displayName)
| project TimeGenerated, OperationName, Actor, GroupName, ['Azure AD Role Name']

//Groups MFA phone registration events into the number that was registered, can be useful to detect threat actors registering multiple accounts to the same numbers for persistence

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago(90d)
| where TargetResources has "PhoneNumber"
| where OperationName has "Update user"
| where TargetResources has "StrongAuthenticationMethod"
| extend InitiatedBy = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend UserPrincipalName = tostring(TargetResources[0].userPrincipalName)
| extend targetResources=parse_json(TargetResources)
| mv-apply tr = targetResources on (
    extend targetResource = tr.displayName
    | mv-apply mp = tr.modifiedProperties on (
    where mp.displayName == "StrongAuthenticationUserDetails"
    | extend NewValue = tostring(mp.newValue)
    ))
| project TimeGenerated, NewValue, UserPrincipalName,InitiatedBy
| mv-expand todynamic(NewValue)
| mv-expand NewValue.[0]
| extend AlternativePhoneNumber = tostring(NewValue.AlternativePhoneNumber)
| extend Email = tostring(NewValue.Email)
| extend PhoneNumber = tostring(NewValue.PhoneNumber)
| extend VoiceOnlyPhoneNumber = tostring(NewValue.VoiceOnlyPhoneNumber)
| project TimeGenerated, UserPrincipalName, InitiatedBy,PhoneNumber, AlternativePhoneNumber, VoiceOnlyPhoneNumber, Email
| where isnotempty(PhoneNumber)
| summarize ['Count of Users']=dcount(UserPrincipalName), ['List of Users']=make_set(UserPrincipalName) by PhoneNumber
| sort by ['Count of Users'] desc

//Detect when an Azure AD guest account is assigned to an Azure AD PIM role

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago (1d)
| where OperationName in ("Add eligible member to role in PIM completed (permanent)", "Add eligible member to role in PIM completed (timebound)", "Add member to role in PIM completed (permanent)", "Add member to role in PIM completed (timebound)")
| extend ['Azure AD Role Name'] = tostring(TargetResources[0].displayName)
| extend Target = tostring(TargetResources[2].userPrincipalName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where Target contains "#ext#"
| project TimeGenerated, OperationName, Actor, Target, ['Azure AD Role Name']

//List the bulk activities attempted by your privileged Azure AD users and parse the results

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName has_all ("(bulk)", "finished")
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| project TimeGenerated, Actor, ResultDescription, OperationName
| parse ResultDescription with * "Total activities count:" ['Total Activity Count'] ";" *
| parse ResultDescription with * "succeeded activities count" ['Total Succeeded'] ";" *
| parse ResultDescription with * "failed activities count" ['Total Failed']
| project
    TimeGenerated,
    Actor,
    OperationName,
    ['Total Activity Count'],
    ['Total Succeeded'],
    ['Total Failed']

//Alert when any users who hold a privileged Azure AD role make MFA configuration changes or an admin changes MFA details on a privileged user

//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Microsoft Sentinel UEBA

//Lookup the IdentityInfo table for any users holding a privileged role
let privusers=
    IdentityInfo
    | where TimeGenerated > ago(21d)
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    | where isnotempty(AssignedRoles)
    | where AssignedRoles != "[]"
    | distinct AccountUPN;
//Lookup MFA configuration events for those privileged users
AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName in~ ("Admin registered security info", "Admin updated security info", "Admin deleted security info", "User registered security info", "User changed default security info", "User deleted security info")
| extend UserPrincipalName = tostring(TargetResources[0].userPrincipalName)
| where UserPrincipalName in~ (privusers)
| project TimeGenerated, OperationName, UserPrincipalName

//Query your Azure Active Directory audit logs for any phone numbers that have been registered to multiple users for MFA

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago (30d)
| where Result == "success"
| where Identity == "Azure Credential Configuration Endpoint Service"
| where OperationName == "Update user"
| extend UserPrincipalName = tostring(TargetResources[0].userPrincipalName)
| extend PhoneNumber = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue))[0].PhoneNumber)
| where isnotempty(PhoneNumber)
| summarize Users=make_set(UserPrincipalName) by PhoneNumber
| extend CountofUsers=array_length(Users)
| where CountofUsers > 1

//Detect when Azure AD Named Locations are changed (either IP or Country) and retrieve the current list 

//Data connector required for this query - Azure Active Directory - Audit Logs

let updatedip=
    AuditLogs
    | where OperationName == "Update named location"
    | mv-expand TargetResources
    | extend modifiedProperties = parse_json(TargetResources).modifiedProperties
    | mv-expand modifiedProperties  
    | extend newValue = tostring(parse_json(modifiedProperties).newValue)
    | mv-expand todynamic(newValue)
    | extend ipRanges = tostring(parse_json(newValue).ipRanges)
    | mv-expand todynamic(ipRanges) 
    | extend cidr = tostring(ipRanges.cidrAddress)
    | where isnotempty(cidr)
    | extend ['Named Location name'] = tostring(TargetResources.displayName)
    | summarize ['IP List']=make_list(cidr) by ['Named Location name'];
let updatedcountries=
    AuditLogs
    | where OperationName == "Update named location"
    | mv-expand TargetResources
    | extend modifiedProperties = parse_json(TargetResources).modifiedProperties
    | mv-expand modifiedProperties  
    | extend newValue = tostring(parse_json(modifiedProperties).newValue)
    | extend countriesAndRegions = tostring(parse_json(newValue).countriesAndRegions)
    | mv-expand todynamic(countriesAndRegions) 
    | where isnotempty(countriesAndRegions)
    | extend ['Named Location name'] = tostring(TargetResources.displayName)
    | summarize ['Country List']=make_list(countriesAndRegions) by ['Named Location name'];
union updatedip, updatedcountries

//Detect when an admin adds a new unverified or verified domain into your Azure AD tenant

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName in ("Add verified domain", "Add unverified domain")
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['Actor IP Address'] = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| extend Domain = tostring(TargetResources[0].displayName)
| project TimeGenerated, OperationName, Actor, ['Actor IP Address'], Domain

//Find any new operations generated in the Azure AD audit table in the last two weeks compared to the last 180 days, you can adjust the time periods 
//e.g. change 180d to 90d and 14d to 7d would find new events in the last week not seen in the 90 prior to that

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago (180d) and TimeGenerated < ago(14d)
| distinct OperationName, LoggedByService
| join kind=rightanti(
    AuditLogs
    | where TimeGenerated > ago(14d)
    | summarize TotalCount=count(), FirstSeen=min(TimeGenerated), LastSeen=max(TimeGenerated) by OperationName, LoggedByService
    )
    on OperationName, LoggedByService

//Detect when a user activates an Azure AD PIM role never seen by them before

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago(180d) and TimeGenerated < ago(1d)
| where OperationName == "Add member to role completed (PIM activation)"
| extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['Azure AD Role Name'] = tostring(TargetResources[0].displayName)
| distinct User, ['Azure AD Role Name']
| join kind=rightanti (
    AuditLogs
    | where TimeGenerated > ago(1d)
    | where OperationName == "Add member to role completed (PIM activation)"
    | extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend ['Azure AD Role Name'] = tostring(TargetResources[0].displayName)
    )
    on User, ['Azure AD Role Name']
| extend IPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| project TimeGenerated, User, ['Azure AD Role Name']

//Find new operations completed by your privileged Azure AD users not seen before

//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Microsoft Sentinel UEBA

//Lookup the IdentityInfo table for any users holding a privileged role
let privusers=
    IdentityInfo
    | where TimeGenerated > ago(21d)
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    | where isnotempty(AssignedRoles)
    | where AssignedRoles != "[]"
    | distinct AccountUPN;
//Find actions taken by those users previously
AuditLogs
| where TimeGenerated > ago(90d) and TimeGenerated < ago(1d)
| extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where isnotempty(UserPrincipalName)
| where UserPrincipalName in (privusers)
| distinct UserPrincipalName, OperationName
//Find any new actions taken in the last day not seen before from that user
| join kind=rightanti (
    AuditLogs
    | where TimeGenerated > ago(1d)
    | extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | where UserPrincipalName in (privusers)
    | where isnotempty(UserPrincipalName)
    )
    on UserPrincipalName, OperationName
| project TimeGenerated, UserPrincipalName, OperationName, Category, CorrelationId

//Find when a new Azure AD tenant is created by a user in your tenant

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName == "Create Company"
| where Result == "success"
| extend Type = tostring(TargetResources[0].type)
| where Type == "Directory"
| extend ['Actor IP Address'] = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['New Tenant Id'] = tostring(TargetResources[0].id)
| project TimeGenerated, OperationName, Actor, ['Actor IP Address'], ['New Tenant Id']

//Create a pivot table showing all the actions taken by your privileged users

//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Microsoft Sentinel UEBA

//Lookup the IdentityInfo table for any users holding a privileged role
let privusers=
    IdentityInfo
    | where TimeGenerated > ago(21d)
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    | where isnotempty(AssignedRoles)
    | where AssignedRoles != "[]"
    | distinct AccountUPN;
//Search for all actions taken by those users in the last 7 days
AuditLogs
| where TimeGenerated > ago(7d)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where Actor in (privusers)
//Create a pivot table counting each action for each user
| evaluate pivot(OperationName, count(), Actor)
| order by Actor asc

//Alert when the redirect URI list is changed for a service principal

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName == "Update service principal"
| mv-expand TargetResources
| extend modifiedProperties = parse_json(TargetResources).modifiedProperties
| mv-expand modifiedProperties 
| where modifiedProperties.displayName == "AppAddress" 
| extend newValue = tostring(parse_json(modifiedProperties).newValue)
| mv-expand todynamic(newValue)
| extend RedirectURI = tostring(newValue.Address)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['Service Principal Name'] = tostring(TargetResources.displayName)
| summarize ['List of Redirect URIs']=make_list(RedirectURI) by Actor, ['Service Principal Name']

//Summarize and visualize the roles being activated in Azure AD PIM

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName == "Add member to role completed (PIM activation)"
| extend ['Azure AD Role Name'] = tostring(TargetResources[0].displayName)
| summarize Count=count()by ['Azure AD Role Name']
| sort by Count
| render barchart with (title="Count of Azure AD PIM activations by role")

//Create a summary of PIM activations for all your users per week

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago (30d)
| where OperationName == "Add member to role completed (PIM activation)"
| extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend Role = tostring(TargetResources[0].displayName)
| where isnotempty(User)
| summarize
    ['Roles Activated']=make_list(Role),
    ['Times Activated']=make_list(TimeGenerated)
    by User, ['Week Starting']=startofweek(TimeGenerated)
| sort by User asc, ['Week Starting'] desc

//Detect when a user is added and removed from an Azure AD role within a short time frame

//Data connector required for this query - Azure Active Directory - Audit Logs

//Timerange = the amount of data to look back on, timeframe = the time between the role being added and removed
let timerange=7d;
let timeframe=4h;
AuditLogs
| where TimeGenerated > ago (timerange)
| where OperationName == "Add member to role"
| where Result == "success"
//Exclude role additions from Azure AD PIM
| where Identity <> "MS-PIM"
| extend User = tostring(TargetResources[0].userPrincipalName)
| extend Role = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| extend UserWhoAdded = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| project TimeAdded=TimeGenerated, User, Role, UserWhoAdded
| join kind=inner (
    AuditLogs
    | where TimeGenerated > ago (timerange)
    | where OperationName == "Remove member from role"
    //Exclude role removals from Azure AD PIM
    | where Result == "success"
    | where Identity <> "MS-PIM"
    | extend User = tostring(TargetResources[0].userPrincipalName)
    | extend Role = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].oldValue)))
    | extend UserWhoRemoved = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | project TimeRemoved=TimeGenerated, User, Role, UserWhoRemoved
    )
    on User, Role
| extend ['Time User Held Role'] = TimeRemoved - TimeAdded
| where ['Time User Held Role'] < ['timeframe']
| project
    TimeAdded,
    TimeRemoved,
    ['Time User Held Role'],
    User,
    Role,
    UserWhoAdded,
    UserWhoRemoved

//Alert when a user is added directly to an Azure AD role, bypassing PIM

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName has "Add member to role outside of PIM"
| extend RoleName = tostring(TargetResources[0].displayName)
| extend UserAdded = tostring(TargetResources[2].displayName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| project TimeGenerated, OperationName, RoleName, UserAdded, Actor

//Summarize all groups that have had users added to them via dynamic rules

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName == "Add member to group"
| where Identity == "Microsoft Approval Management"
| where TargetResources[0].type == "User"
| extend GroupName = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| extend User = tostring(TargetResources[0].userPrincipalName)
| summarize ['Count of Users Added']=dcount(User), ['List of Users Added']=make_set(User) by GroupName
| sort by GroupName asc

//Find users who have not elevated any roles in Azure AD PIM in 30 days

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago (365d)
| project TimeGenerated, OperationName, Result, TargetResources, InitiatedBy
| where OperationName == "Add member to role completed (PIM activation)"
| where Result == "success"
| extend ['Last Role Activated'] = tostring(TargetResources[0].displayName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| summarize arg_max(TimeGenerated, *) by Actor
| project
    Actor,
    ['Last Role Activated'],
    ['Last Activation Time']=TimeGenerated,
    ['Days Since Last Activation']=datetime_diff("day", now(), TimeGenerated)
| where ['Days Since Last Activation'] >= 30
| sort by ['Days Since Last Activation'] desc

//Find users who hold a privileged Azure AD role but haven't completed any activities in Azure AD for 45 days

//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Microsoft Sentinel UEBA

//Lookup the IdentityInfo table for any users holding a privileged role
IdentityInfo
| where TimeGenerated > ago(21d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where isnotempty(AssignedRoles)
| where AssignedRoles != "[]"
| project UserPrincipalName=AccountUPN, AssignedRoles
| join kind=leftanti (
    AuditLogs
    | where TimeGenerated > ago(45d)
    | extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | where isnotempty(UserPrincipalName)
    | distinct UserPrincipalName
    )
    on UserPrincipalName

//Visualize successful vs failed self service password reset attempts in your Azure AD tenant

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago (30d)
| where LoggedByService == "Self-service Password Management"
| extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['User IP Address'] = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| sort by TimeGenerated asc 
//Create a list of all SSPR actions that make up a single correlation id which represents one attempts at completing SSPR
| summarize ['SSPR Actions']=make_list(ResultReason) by CorrelationId, bin(TimeGenerated, 1d)
//Summarize those lists of actions into those that have a successful password reset and those that don't
| summarize
    ['Successful self service password resets']=countif(['SSPR Actions'] has "Successfully completed reset"),
    ['Failed self service password resets']=countif(['SSPR Actions'] !has "User successfully reset password")
    by bin(TimeGenerated, 1d) 
| render timechart with (title="Self service password reset success vs failure", ytitle="Count")

let CombinedSignInLogs = union isfuzzy=True AADNonInteractiveUserSignInLogs, SigninLogs;
  // Combine AADNonInteractiveUserSignInLogs and SigninLogs into a single table
  // Fetch Azure IP address ranges data from a JSON file hosted on GitHub
  let AzureRanges = externaldata(changeNumber: string, cloud: string, values: dynamic)
  ["https://raw.githubusercontent.com/microsoft/mstic/master/PublicFeeds/MSFTIPRanges/ServiceTags_Public.json"] with(format='multijson')
  // Load Azure IP address ranges from the JSON file hosted on GitHub
  | mv-expand values
  // Expand the values column into separate rows
  | extend Name = values.name, AddressPrefixes = tostring(values.properties.addressPrefixes);
  // Create additional columns for the name and address prefixes
  // Identify known locations to be excluded from analysis
  let ExcludedKnownLocations = CombinedSignInLogs
  // Filter the combined logs based on the specified time range
  | where TimeGenerated between (ago(14d)..ago(1d))
  // Filter by specific ResultType
  | where ResultType == 0
  // Summarize the logs by location
  | summarize by Location;
  // Find sign-in locations matching specific criteria
  let MatchedLocations = materialize(CombinedSignInLogs
  // Filter the combined logs based on the specified time range
  | where TimeGenerated > ago(1d)
  // Exclude specific ResultTypes
  | where ResultType !in (50126, 50053, 50074, 70044)
  // Exclude known locations
  | where Location !in (ExcludedKnownLocations));
  // Match IP addresses of matched locations with Azure IP address ranges
  let MatchedIPs = MatchedLocations
  // Use the 'ipv4_lookup' function to match IP addresses with Azure IP address ranges
  | evaluate ipv4_lookup(AzureRanges, IPAddress, AddressPrefixes)
  // Project only the IPAddress column
  | project IPAddress;
  // Exclude IP addresses that are already matched with Azure IP address ranges
  let MaxSetSize = 5; // Set the maximum size limit for make_set
  let ExcludedIPs = MatchedLocations
  // Filter out IP addresses that are already matched
  | where not (IPAddress in (MatchedIPs))
  // Exclude empty or null Location values
  | where isnotempty(Location)
  // Handle dynamic and string column values for LocationDetails and DeviceDetail
  | extend LocationDetails_dynamic = column_ifexists("LocationDetails_dynamic", "")
  | extend DeviceDetail_dynamic = column_ifexists("DeviceDetail_dynamic", "")
  | extend LocationDetails = iif(isnotempty(LocationDetails_dynamic), LocationDetails_dynamic, parse_json(LocationDetails_string))
  | extend DeviceDetail = iif(isnotempty(DeviceDetail_dynamic), DeviceDetail_dynamic, parse_json(DeviceDetail_string))
  // Extract location details (city and state)
  | extend City = tostring(LocationDetails.city)
  | extend State = tostring(LocationDetails.state)
  | extend Place = strcat(City, " - ", State)
  | extend DeviceId = tostring(DeviceDetail.deviceId)
  | extend Result = strcat(tostring(ResultType), " - ", ResultDescription)
  // Summarize the data based on UserPrincipalName, Location, and Category
  | summarize FirstSeen=min(TimeGenerated), LastSeen=max(TimeGenerated),
  make_set(Result, MaxSetSize), make_set(IPAddress, MaxSetSize),
  make_set(UserAgent, MaxSetSize), make_set(Place, MaxSetSize),
  make_set(DeviceId, MaxSetSize) by UserPrincipalName, Location, Category
  // Extract the username prefix and suffix from UserPrincipalName
  | extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0]);
  ExcludedIPs // Output the final result set
  | extend IP = set_IPAddress[0]

let admin_users = (IdentityInfo
  | summarize arg_max(TimeGenerated, *) by AccountUPN
  | where AssignedRoles contains "admin"
  | summarize by tolower(AccountUPN));
  let admin_asn = (SigninLogs
  | where TimeGenerated between (ago(7d)..ago(1d))
  | where tolower(UserPrincipalName) in (admin_users)
  | summarize by AutonomousSystemNumber);
  let admin_locations = (SigninLogs
  | where TimeGenerated between (ago(7d)..ago(1d))
  | where tolower(UserPrincipalName) in (admin_users)
  | summarize by Location);
  let admin_devices = (SigninLogs
  | where TimeGenerated between (ago(7d)..ago(1d))
  | where tolower(UserPrincipalName) in (admin_users)
  | extend deviceId = tostring(DeviceDetail.deviceId)
  | where isnotempty(deviceId)
  | summarize by deviceId);
  SigninLogs
  | where TimeGenerated > ago(1d)
  | where ResultType == 0
  | where tolower(UserPrincipalName) in (admin_users)
  | extend deviceId = tostring(DeviceDetail.deviceId)
  | where AutonomousSystemNumber !in (admin_asn) and deviceId !in (admin_devices) and Location !in (admin_locations)

AzureActivity
| where OperationNameValue =~ "MICROSOFT.INSIGHTS/DIAGNOSTICSETTINGS/DELETE"
| summarize
    TimeGenerated = arg_max(TimeGenerated, Properties),
    ActivityStatusValue = make_set(ActivityStatusValue, 5),
    take_any(Caller, CallerIpAddress, OperationName, ResourceGroup, Resource)
    by CorrelationId, _ResourceId, OperationNameValue
| extend ResourceHierarchy = split(_ResourceId, "/")
| extend MonitoredResourcePath = strcat_array(array_slice(ResourceHierarchy, 0, array_length(ResourceHierarchy)-5), "/")
| join kind=leftanti (
    AzureActivity
    | where OperationNameValue !~ "MICROSOFT.INSIGHTS/DIAGNOSTICSETTINGS/DELETE" and OperationNameValue endswith "/DELETE" and ActivityStatusValue has_any ("Success", "Succeeded")
    | project _ResourceId
) on $left.MonitoredResourcePath == $right._ResourceId
| extend
    Name = iif(Caller has "@", tostring(split(Caller, "@")[0]), ""),
    UPNSuffix = iif(Caller has "@", tostring(split(Caller, "@")[1]), ""),
    AadUserId = iif(Caller has "@", "", Caller)
| project TimeGenerated, Caller, CallerIpAddress, OperationNameValue, OperationName, ActivityStatusValue, ResourceGroup, MonitoredResourcePath, Resource, Properties, Name, UPNSuffix, AadUserId, _ResourceId, CorrelationId

AzureDiagnostics
| where ResourceType == "VAULTS"
| where OperationName == "VaultPatch"
| where ResultType == "Success"
| extend UserObjectAdded = addedAccessPolicy_ObjectId_g
| extend AddedActor = identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_name_s
| extend KeyAccessAdded = tostring(addedAccessPolicy_Permissions_keys_s)
| extend SecretAccessAdded = tostring(addedAccessPolicy_Permissions_secrets_s)
| extend CertAccessAdded = tostring(addedAccessPolicy_Permissions_certificates_s)
| where isnotempty(UserObjectAdded)
| project
AccessAddedTime=TimeGenerated,
ResourceType,
OperationName,
ResultType,
KeyVaultName=Resource,
AddedActor,
UserObjectAdded,
KeyAccessAdded,
SecretAccessAdded,
CertAccessAdded
| join kind=inner 
( 
AzureDiagnostics
| where ResourceType == "VAULTS"
| where OperationName == "VaultPatch"
| where ResultType == "Success"
| extend RemovedActor = identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_name_s
| extend UserObjectRemoved = removedAccessPolicy_ObjectId_g
| extend KeyAccessRemoved = tostring(removedAccessPolicy_Permissions_keys_s)
| extend SecretAccessRemoved = tostring(removedAccessPolicy_Permissions_secrets_s)
| extend CertAccessRemoved = tostring(removedAccessPolicy_Permissions_certificates_s)
| where isnotempty(UserObjectRemoved)
| project
AccessRemovedTime=TimeGenerated,
ResourceType,
OperationName,
ResultType,
KeyVaultName=Resource,
RemovedActor,
UserObjectRemoved,
KeyAccessRemoved,
SecretAccessRemoved,
CertAccessRemoved
)
on KeyVaultName
| extend TimeDelta = abs(AccessAddedTime - AccessRemovedTime)
| where TimeDelta < timeframe
| project KeyVaultName, AccessAddedTime, AddedActor, UserObjectAdded, KeyAccessAdded, SecretAccessAdded, CertAccessAdded, AccessRemovedTime, RemovedActor, UserObjectRemoved, KeyAccessRemoved, SecretAccessRemoved, CertAccessRemoved, TimeDelta
| extend AccountCustomEntity = UserObjectAdded

AzureActivity
// Isolate run command actions
| where OperationNameValue =~ "MICROSOFT.COMPUTE/VIRTUALMACHINES/RUNCOMMAND/ACTION"
// Confirm that the operation impacted a virtual machine
| where Authorization has "virtualMachines"
// Each runcommand operation consists of three events when successful, Started, Accepted (or Rejected), Successful (or Failed).
| summarize StartTime=min(TimeGenerated), EndTime=max(TimeGenerated), max(CallerIpAddress), make_list(ActivityStatusValue) by CorrelationId, Authorization, Caller
// Limit to Run Command executions that Succeeded
| where list_ActivityStatusValue has_any ("Success", "Succeeded")
// Extract data from the Authorization field
| extend Authorization_d = parse_json(Authorization)
| extend Scope = Authorization_d.scope
| extend Scope_s = split(Scope, "/")
| extend Subscription = tostring(Scope_s[2])
| extend VirtualMachineName = tostring(Scope_s[-1])
| project StartTime, EndTime, Subscription, VirtualMachineName, CorrelationId, Caller, CallerIpAddress=max_CallerIpAddress
// Create a join key using  the Caller (UPN)
| extend joinkey = tolower(Caller)
// Join the Run Command actions to UEBA data
| join kind = inner (
    BehaviorAnalytics
    // We are specifically interested in unusual logins
    | where EventSource == "Azure AD" and ActivityInsights.ActionUncommonlyPerformedByUser == "True"
    | project UEBAEventTime=TimeGenerated, UEBAActionType=ActionType, UserPrincipalName, UEBASourceIPLocation=SourceIPLocation, UEBAActivityInsights=ActivityInsights, UEBAUsersInsights=UsersInsights
    | where isnotempty(UserPrincipalName) and isnotempty(UEBASourceIPLocation)
    | extend joinkey = tolower(UserPrincipalName)
) on joinkey
// Create a window around the UEBA event times, check to see if the Run Command action was performed within them
| extend UEBAWindowStart = UEBAEventTime - 1h, UEBAWindowEnd = UEBAEventTime + 6h
| where StartTime between (UEBAWindowStart .. UEBAWindowEnd)
| project StartTime, EndTime, Subscription, VirtualMachineName, Caller, CallerIpAddress, UEBAEventTime, UEBAActionType, UEBASourceIPLocation, UEBAActivityInsights, UEBAUsersInsights
| extend AccountName = tostring(split(Caller, "@")[0]), AccountUPNSuffix = tostring(split(Caller, "@")[1])

let RunCommandData = materialize ( AzureActivity
// Isolate run command actions
| where OperationNameValue =~ "Microsoft.Compute/virtualMachines/runCommand/action"
// Confirm that the operation impacted a virtual machine
| where Authorization has "virtualMachines"
// Each runcommand operation consists of three events when successful, StartTimeed, Accepted (or Rejected), Successful (or Failed).
| summarize StartTime=min(TimeGenerated), EndTime=max(TimeGenerated), max(CallerIpAddress), make_list(ActivityStatusValue) by CorrelationId, Authorization, Caller
// Limit to Run Command executions that Succeeded
| where list_ActivityStatusValue has_any ("Succeeded", "Success")
// Extract data from the Authorization field, allowing us to later extract the Caller (UPN) and CallerIpAddress
| extend Authorization_d = parse_json(Authorization)
| extend Scope = Authorization_d.scope
| extend Scope_s = split(Scope, "/")
| extend Subscription = tostring(Scope_s[2])
| extend VirtualMachineName = tostring(Scope_s[-1])
| project StartTime, EndTime, Subscription, VirtualMachineName, CorrelationId, Caller, CallerIpAddress=max_CallerIpAddress, Scope
| join kind=leftouter (
    DeviceFileEvents
    | where InitiatingProcessFileName == "RunCommandExtension.exe"
    | extend VirtualMachineName = tostring(split(DeviceName, ".")[0])
    | project VirtualMachineName, PowershellFileCreatedTimestamp=TimeGenerated, FileName, FileSize, InitiatingProcessAccountName, InitiatingProcessAccountDomain, InitiatingProcessFolderPath, InitiatingProcessId
) on VirtualMachineName
// We need to filter by time sadly, this is the only way to link events
| where PowershellFileCreatedTimestamp between (StartTime .. EndTime)
| project StartTime, EndTime, PowershellFileCreatedTimestamp, VirtualMachineName, Caller, CallerIpAddress, FileName, FileSize, InitiatingProcessId, InitiatingProcessAccountDomain, InitiatingProcessFolderPath, Scope
| join kind=inner(
    DeviceEvents
    | extend VirtualMachineName = tostring(split(DeviceName, ".")[0])
    | where InitiatingProcessCommandLine has "-File"
    // Extract the script name based on the structure used by the RunCommand extension
    | extend PowershellFileName = extract(@"\-File\s(script[0-9]{1,9}\.ps1)", 1, InitiatingProcessCommandLine)
    // Discard results that didn't successfully extract, these are not run command related
    | where isnotempty(PowershellFileName)
    | extend PSCommand = tostring(parse_json(AdditionalFields).Command)
    // The first execution of PowerShell will be the RunCommand script itself, we can discard this as it will break our hash later
    | where PSCommand != PowershellFileName 
    // Now we normalise the cmdlets, we're aiming to hash them to find scripts using rare combinations
    | extend PSCommand = toupper(PSCommand)
    | order by PSCommand asc
    | summarize PowershellExecStartTime=min(TimeGenerated), PowershellExecEnd=max(TimeGenerated), make_list(PSCommand) by PowershellFileName, InitiatingProcessCommandLine
) on $left.FileName == $right.PowershellFileName
| project StartTime, EndTime, PowershellFileCreatedTimestamp, PowershellExecStartTime, PowershellExecEnd, PowershellFileName, PowershellScriptCommands=list_PSCommand, Caller, CallerIpAddress, InitiatingProcessCommandLine, PowershellFileSize=FileSize, VirtualMachineName, Scope
| order by StartTime asc 
// We generate the hash based on the cmdlets called and the size of the powershell script
| extend TempFingerprintString = strcat(PowershellScriptCommands, PowershellFileSize)
| extend ScriptFingerprintHash = hash_sha256(tostring(PowershellScriptCommands)));
let totals = toscalar (RunCommandData
| summarize count());
let hashTotals = RunCommandData
| summarize HashCount=count() by ScriptFingerprintHash;
RunCommandData
| join kind=leftouter (
hashTotals
) on ScriptFingerprintHash
// Calculate prevalence, while we don't need this, it may be useful for responders to know how rare this script is in relation to normal activity
| extend Prevalence = toreal(HashCount) / toreal(totals) * 100
// Where the hash was only ever seen once.
| where HashCount == 1
| extend timestamp = StartTime
| extend CallerName = tostring(split(Caller, "@")[0]), CallerUPNSuffix = tostring(split(Caller, "@")[1])
| project timestamp, StartTime, EndTime, PowershellFileName, VirtualMachineName, Caller, CallerName, CallerUPNSuffix, CallerIpAddress, PowershellScriptCommands, PowershellFileSize, ScriptFingerprintHash, Prevalence, Scope

//Detect when a resource lock is added or removed from an Azure resource

//Data connector required for this query - Azure Activity 

AzureActivity
| where OperationNameValue in ("MICROSOFT.AUTHORIZATION/LOCKS/WRITE", "MICROSOFT.AUTHORIZATION/LOCKS/DELETE")
| where ActivityStatusValue == "Success"
| extend Activity = case(OperationNameValue == "MICROSOFT.AUTHORIZATION/LOCKS/WRITE", strcat("Resource Lock Added"),
    OperationNameValue == "MICROSOFT.AUTHORIZATION/LOCKS/DELETE", strcat("Resource Lock Removed"),
    "unknown")
| extend ResourceGroup = tostring(parse_json(Properties).resourceGroup)
| extend AzureResource = tostring(parse_json(Properties).resourceProviderValue)
| extend x = tostring(parse_json(Properties).resource)
| parse x with ResourceName "/" *
| parse x with * "microsoft.authorization/" LockName
| project
    TimeGenerated,
    Activity,
    ResourceName,
    ['Azure Resource']=AzureResource,
    ['Azure Subscription Id']=SubscriptionId,
    ['Azure Resource Group']=ResourceGroup,
    LockName

//Detect when an Azure AD service principal is given access to an Azure RBAC scope, i.e contributor to a subscription or resource group

//Data connector required for this query - Azure Activity 

AzureActivity
| where OperationNameValue == "MICROSOFT.AUTHORIZATION/ROLEASSIGNMENTS/WRITE"
| extend ServicePrincipalObjectId = tostring(parse_json(tostring(parse_json(tostring(Properties_d.requestbody)).Properties)).PrincipalId)
| extend ServicePrincipalType = tostring(parse_json(tostring(parse_json(tostring(Properties_d.requestbody)).Properties)).PrincipalType)
| extend Scope = tostring(parse_json(tostring(parse_json(tostring(Properties_d.requestbody)).Properties)).Scope)
| extend RoleAdded = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).Properties)).RoleDefinitionId)
| extend Actor = tostring(Properties_d.caller)
| where ServicePrincipalType == "ServicePrincipal"
| project TimeGenerated, RoleAdded, Scope, ServicePrincipalObjectId, Actor

//Detect when the workspace keys to an Azure log analytics workspace are read

//Data connector required for this query - Azure Activity 

AzureActivity
| where OperationNameValue == "MICROSOFT.OPERATIONALINSIGHTS/WORKSPACES/SHAREDKEYS/ACTION"
| extend WorkspaceName = tostring(parse_json(Properties).resource)
| where ActivityStatusValue == "Success"
| project
    TimeGenerated,
    Actor=Caller,
    ['Log Analytics Workspace Name']=WorkspaceName,
    ['Actor IP Address']=CallerIpAddress,
    ['Azure Subscription Id']=SubscriptionId,
    ['Azure Resource Group']=ResourceGroup

//Detect when a user retrieves keys for Azure storage for the first time compared to the previous time range

//Data connector required for this query - Azure Activity 

let knownusers=
    AzureActivity
    | where TimeGenerated > ago(90d) and TimeGenerated < ago(1d)
    | where OperationName == "List Storage Account Keys"
    | where ActivityStatus == "Succeeded"
    | project-rename Actor=Caller
    | distinct Actor;
AzureActivity
| where TimeGenerated > ago(1d)
| where OperationName == "List Storage Account Keys"
| where ActivityStatus == "Succeeded"
| project-rename Actor=Caller
| where Actor !in (knownusers)
| project
    TimeGenerated,
    Actor,
    ['Actor IP Address']=CallerIpAddress,
    ['Storage Account Name']=Resource,
    ['Azure Subscription Id']=SubscriptionId,
    ['Azure Resource Group']=ResourceGroup

//Detect when a download URL is generated for an Azure virtual machine disk

//Data connector required for this query - Azure Activity 

AzureActivity
| where OperationNameValue == "MICROSOFT.COMPUTE/DISKS/BEGINGETACCESS/ACTION"
| where ActivityStatusValue == "Success"
| extend DiskName = tostring(Properties_d.resource)
| project
    TimeGenerated,
    Actor=Caller,
    ['Actor IP Address']=CallerIpAddress,
    ['Azure Subscription Id']=SubscriptionId,
    ['Azure Resource Group']=ResourceGroup,
    DiskName

imProcessCreate
| where CommandLine contains "TVqQAAMAAAAEAAA"
| where isnotempty(Process)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), count() by Dvc, ActorUsername, Process, CommandLine, ActingProcessName, EventVendor, EventProduct
| extend AccountName = tostring(split(ActorUsername, @'\')[1]), AccountNTDomain = tostring(split(ActorUsername, @'\')[0])
| extend HostName = tostring(split(Dvc, ".")[0]), DomainIndex = toint(indexof(Dvc, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Dvc, DomainIndex + 1), Dvc)
| project-away DomainIndex

//Find which users have attempted to connect to virtual machines using RDP or SSH in Azure Bastion

//Data connector required for this query - Azure Bastion

MicrosoftAzureBastionAuditLogs
| parse TargetResourceId with * 'VIRTUALMACHINES/' ['Virtual Machine Name']
| project
    TimeGenerated,
    Message,
    UserName,
    Protocol,
    ['Virtual Machine Name'],
    ['Virtual Machine IP']=TargetVMIPAddress
| sort by TimeGenerated desc

//Summarize your Bastion usage by which users are connecting to which devices via which protocl (RDP or SSH)

//Data connector required for this query - Azure Bastion

MicrosoftAzureBastionAuditLogs 
| where TimeGenerated > ago (30d)
| where Message == "Successfully Connected."
| summarize
    ['Count of RDP Devices']=dcountif(TargetVMIPAddress, Protocol == "rdp"),
    ['List of RDP Devices']=make_set_if(TargetVMIPAddress, Protocol == "rdp"),
    ['Count of SSH Devices']=dcountif(TargetVMIPAddress, Protocol == "ssh"),
    ['List of SSH Devices']=make_set_if(TargetVMIPAddress, Protocol == "ssh")
    by UserName
| sort by ['Count of RDP Devices'] desc

let failureCountThreshold = 10;
let successCountThreshold = 1;
// let authenticationWindow = 20m; // Implicit in the analytic rule query period 
imAuthentication
| where TargetUserType != "NonInteractive"
| summarize 
      StartTime = min(TimeGenerated), 
      EndTime = max(TimeGenerated), 
      IpAddresses = make_set (SrcDvcIpAddr, 100),
      ReportedBy = make_set (strcat (EventVendor, "/", EventProduct), 100),
      FailureCount = countif(EventResult=='Failure'),
      SuccessCount = countif(EventResult=='Success')
  by 
      TargetUserId, TargetUsername, TargetUserType 
| where FailureCount >= failureCountThreshold and SuccessCount >= successCountThreshold
| extend
      IpAddresses = strcat_array(IpAddresses, ", "), 
      ReportedBy = strcat_array(ReportedBy, ", ")
| extend
  Name = iif(
      TargetUsername contains "@"
          , tostring(split(TargetUsername, '@', 0)[0])
          , TargetUsername
      ),
  UPNSuffix = iif(
      TargetUsername contains "@"
      , tostring(split(TargetUsername, '@', 1)[0])
      , ""
  )

let lookback_start = 7d;
let lookback_end = 1d;
let timedelta = 5s;
// Get a list of previously seen DLLs being loaded
let known_dlls = (Event
| where TimeGenerated between(ago(lookback_start)..ago(lookback_end))
| where EventID == 7
| extend EvData = parse_xml(EventData)
| extend EventDetail = EvData.DataItem.EventData.Data
| extend LoadedItems = parse_json(tostring(parse_json(tostring(EvData.DataItem)).EventData)).["Data"]
| mv-expand LoadedItems
| where tostring(LoadedItems.["@Name"]) =~ "ImageLoaded"
| extend DLL = tostring(LoadedItems.["#text"])
| summarize by DLL);
// Get Image Load events related to svchost.exe
Event
| where Source =~ "Microsoft-Windows-Sysmon"
// Image Load Event in Sysmon
| where EventID == 7
| extend EvData = parse_xml(EventData)
| extend EventDetail = EvData.DataItem.EventData.Data
| extend Images = parse_json(tostring(parse_json(tostring(EvData.DataItem)).EventData)).["Data"]
| mv-expand Images
// Parse out executing process
| where tostring(Images.["@Name"]) =~ "Image"
| extend Image = tostring(Images.["#text"])
| where Image endswith "\\svchost.exe"
// Parse out loaded DLLs
| extend LoadedItems = parse_json(tostring(parse_json(tostring(EvData.DataItem)).EventData)).["Data"]
| mv-expand LoadedItems
| where tostring(LoadedItems.["@Name"]) =~ "ImageLoaded"
| extend DLL = tostring(LoadedItems.["#text"])
| extend Image = tostring(Image)
| extend ImageLoadTime = TimeGenerated
// Join with processes with a command line related to COM Event System
| join kind = inner(Event
| where Source =~ "Microsoft-Windows-Sysmon"
// Sysmon process execution events
| where EventID == 1
| extend RenderedDescription = tostring(split(RenderedDescription, ":")[0])
| extend EventData = parse_xml(EventData).DataItem.EventData.Data
| mv-expand bagexpansion=array EventData
| evaluate bag_unpack(EventData)
| extend Key = tostring(column_ifexists('@Name', "")), Value = column_ifexists('#text', "")
| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, RenderedDescription, MG, ManagementGroupName, Type, _ResourceId)
| extend ParentImage = tostring(column_ifexists("ParentImage", "NotAvailable"))
// Command line related to COM Event System
| where ParentImage endswith "\\svchost.exe"
//| where ParentCommandLine has_all (" -k LocalService"," -p"," -s EventSystem")
| extend ProcessExecutionTime = TimeGenerated) on $left.Image == $right.ParentImage
// Check timespan between DLL load and process creation
| extend delta =  ProcessExecutionTime - ImageLoadTime
| where ImageLoadTime <= ProcessExecutionTime and delta <= timedelta
// Filter to only newly seen DLLs
| where DLL !in (known_dlls)
| extend ParentCommandLine = tostring(column_ifexists("ParentCommandLine", "NotAvailable"))
| project-reorder ImageLoadTime, ProcessExecutionTime , Image, ParentCommandLine, DLL
| extend Hashes = tostring(column_ifexists("Hashes", "NotAvailable, NotAvailable"))
| extend Hashes = split(Hashes, ",")
| mv-apply Hashes on (summarize FileHashes = make_bag(pack(tostring(split(Hashes, "=")[0]), tostring(split(Hashes, "=")[1]))))
| extend SHA1 = tostring(FileHashes.SHA1)
| extend HashAlgo = "SHA1"
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend Name = tostring(split(UserName, "\\")[1]), NTDomain = tostring(split(UserName, "\\")[0])

let guids = dynamic(["{ddc05a5a-351a-4e06-8eaf-54ec1bc2dcea}","{1f486a52-3cb1-48fd-8f50-b8dc300d9f9d}","{4590f811-1d3a-11d0-891f-00aa004b2e24}", "{4de225bf-cf59-4cfc-85f7-68b90f185355}", "{F56F6FDD-AA9D-4618-A949-C1B91AF43B1A}"]);
  let mde_data = DeviceRegistryEvents
  | where ActionType =~ "RegistryValueSet"
  | where RegistryKey contains "HKEY_LOCAL_MACHINE\\SOFTWARE\\Classes\\CLSID"
  | where RegistryKey has_any (guids)
  | where RegistryValueData has "System32\\spool\\drivers\\color";
  let event_data = SecurityEvent
  | where EventID == 4657
  | where ObjectName contains "HKEY_LOCAL_MACHINE\\SOFTWARE\\Classes\\CLSID"
  | where ObjectName has_any (guids)
  | where NewValue has "System32\\spool\\drivers\\color"
  | extend RegistryKey = ObjectName, RegistryValueData = NewValue, DeviceName=Computer, InitiatingProcessFileName = Process, InitiatingProcessAccountName=SubjectUserName, InitiatingProcessAccountDomain = SubjectDomainName;
  union mde_data, event_data
  | extend HostName = tostring(split(DeviceName, ".")[0]), DomainIndex = toint(indexof(DeviceName, '.'))
  | extend HostNameDomain = iff(DomainIndex != -1, substring(DeviceName, DomainIndex + 1), DeviceName)

//Detection rule for App Gateway rule hits for log4j vulnerability. Retrieve attacked host, malicious IP and malicious User Agent

//Data connector required for this query - Azure Diagnostics (Application Gateways)

AzureDiagnostics
| where details_data_s contains "jndi"
| parse-where details_data_s with * 'User-Agent:' MaliciousHost 
| project TimeGenerated, Target=hostname_s, Actor=clientIp_s, MaliciousHost

//Detect uri directly where starts with /$ or contains ldap
AzureDiagnostics
| where TimeGenerated > ago(1d)
| where ResourceType == "APPLICATIONGATEWAYS"
| project TimeGenerated, host_s, originalRequestUriWithArgs_s, clientIP_s
| where originalRequestUriWithArgs_s startswith "/$" or originalRequestUriWithArgs_s contains "jndi"
| parse-where originalRequestUriWithArgs_s with * '://' MaliciousHost

//Parse all request uris over the last 30 days and create a new column from the string between / and ://
//Example - from /${jndi:ldap:// we parse ${jndi:ldap: to a new column called HeaderUri

//Data connector required for this query - Azure Diagnostics (Application Gateways)

//Look up the last 3 days of data to find any new HeaderUri strings between / and :// not seen for the previous 30 days
AzureDiagnostics
| where TimeGenerated > ago(30d) and TimeGenerated < ago(3d)
| where ResourceType == "APPLICATIONGATEWAYS"
| project TimeGenerated, host_s, originalRequestUriWithArgs_s, clientIP_s
| parse-where originalRequestUriWithArgs_s with * '/' HeaderUri '://' *
| distinct host_s, HeaderUri
| join kind=rightanti (
    AzureDiagnostics
    | where TimeGenerated > ago(3d)
    | where ResourceType == "APPLICATIONGATEWAYS"
    | project TimeGenerated, host_s, originalRequestUriWithArgs_s, clientIP_s
    | parse-where originalRequestUriWithArgs_s with * '/' HeaderUri '://' *
    | project TimeGenerated, originalRequestUriWithArgs_s, host_s, HeaderUri)
    on host_s, HeaderUri
| parse-where originalRequestUriWithArgs_s with * '://' MaliciousHost '/' *
| project TimeGenerated, originalRequestUriWithArgs_s, HeaderUri, MaliciousHost, Target=host_s