Disclaimer: although I have worked and done threat hunting with Kusto Query Language in Defender and Microsoft sentinel, most of the queries I have copied them and customized them to my needs, here is the link: GitHub

​

Here are my custom queries, feel free to use and customize them accordingly: I would advice you to get some sort of  training for Defender and Microsoft Sentinel, involving KQL training! life will be so much better for your investigations.

Also I forgot to mention, some queries will not run in Defender or Sentinel, vice-versa; so by having hands on you will find out.

​

This one needs no introduction:

​

SecurityEvent

  | where EventID == 7045

  | where Process has_any ("cmd.exe", "powershell.exe", "PowerShell_ISE.exe", "%COMSPEC%")

  | where isnotempty(CommandLine)

  | where CommandLine contains "Add-PSSnapin Microsoft.Exchange.Management.Powershell.Snapin"

  | summarize FirstSeen = min(TimeGenerated), LastSeen = max(TimeGenerated) by Computer, Account, CommandLine

  | extend timestamp = FirstSeen, AccountCustomEntity = Account, HostCustomEntity = Computer

​

*****************************************************************************************************

​

//Detecting multistage attacks in Microsoft Sentinel

SigninLogs

| project TimeGenerated, UserPrincipalName, AppDisplayName, ResultType, CorrelationId

| join kind=inner

(AADUserRiskEvents)

on CorrelationId

| project TimeGenerated, UserPrincipalName, CorrelationId, ResultType, DetectionTimingType, RiskState, RiskLevel

 

******************************************************************************

//Detecting multistage attacks in Microsoft Sentinel

SigninLogs

| project TimeGenerated, UserPrincipalName, AppDisplayName, ResultType, CorrelationId

| join kind=inner

(AADUserRiskEvents)

on CorrelationId

| project TimeGenerated, UserPrincipalName, CorrelationId, ResultType, DetectionTimingType, RiskState, RiskLevel

 

// looking for AADUserRiskEvents, and this time joining to our Azure AD Audit table (where Azure AD changes are tracked) looking for events where the same user who flagged a risk event also changed MFA details within a short time frame.

let starttime = 45d;

let timeframe = 4h;

AADUserRiskEvents

| where TimeGenerated > ago(starttime)

| where RiskDetail != "aiConfirmedSigninSafe"

| project RiskTime=TimeGenerated, UserPrincipalName, RiskEventType, RiskLevel, Source

| join kind=inner (

    AuditLogs

    | where OperationName in ("User registered security info", "User deleted security info")

    | where Result == "success"

    | extend UserPrincipalName = tostring(TargetResources[0].userPrincipalName)

    | project SecurityInfoTime=TimeGenerated, OperationName, UserPrincipalName, Result, ResultReason)

    on UserPrincipalName

| project RiskTime, SecurityInfoTime, UserPrincipalName, RiskEventType, RiskLevel, Source, OperationName, ResultReason

| where (SecurityInfoTime - RiskTime) between (0min .. timeframe)

*******************************************************************************

//ooking for a risk event followed by access to an Azure management interface. If a user flagged a risk event, then within four hours signed into Azure, we would be alerted.

 

let starttime = 45d;

let timeframe = 4h;

let applications = dynamic(["Azure Active Directory PowerShell", "Microsoft Azure PowerShell", "Graph Explorer", "ACOM Azure Website"]);

AADUserRiskEvents

| where TimeGenerated > ago(starttime)

| where RiskDetail != "aiConfirmedSigninSafe"

| project RiskTime=TimeGenerated, UserPrincipalName, RiskEventType, RiskLevel, Source

| join kind=inner (

    SigninLogs

    | where AppDisplayName in (applications)

    | where ResultType == "0")

    on UserPrincipalName

| project-rename AzureSigninTime=TimeGenerated

| extend TimeDelta = AzureSigninTime - RiskTime

| project RiskTime, AzureSigninTime, TimeDelta, UserPrincipalName, RiskEventType, RiskLevel, Source

| where (AzureSigninTime - RiskTime) between (0min .. timeframe)

*********************************************************************************

//this time join it to our Office 365 activity logs to find a list of files that a user has downloaded shortly after flagging that risk event.

let starttime = 90d;

let timeframe = 4h;

AADUserRiskEvents

| where TimeGenerated > ago(starttime)

| where RiskDetail != "aiConfirmedSigninSafe"

| project RiskTime=TimeGenerated, UserPrincipalName, RiskEventType, RiskLevel, Source

| join kind=inner (

    OfficeActivity

    | where Operation in ("FileSyncDownloadedFull", "FileDownloaded"))

    on $left.UserPrincipalName == $right.UserId

| project DownloadTime=TimeGenerated, OfficeObjectId, RiskTime, UserId

| where (DownloadTime - RiskTime) between (0min .. timeframe)

| summarize RiskyDownloads=make_set(OfficeObjectId) by UserId

| where array_length( RiskyDownloads) > 10

-----------------------------------------------------------------------------------------------------------------------------------

//this time join it to our Office 365 activity logs to find a list of files that a user has downloaded shortly after flagging that risk event.

let starttime = 90d;

let timeframe = 4h;

AADUserRiskEvents

| where TimeGenerated > ago(starttime)

| where RiskDetail != "aiConfirmedSigninSafe"

| project RiskTime=TimeGenerated, UserPrincipalName, RiskEventType, RiskLevel, Source

| join kind=inner (

    OfficeActivity

    | where Operation in ("FileSyncDownloadedFull", "FileDownloaded"))

    on $left.UserPrincipalName == $right.UserId

| project DownloadTime=TimeGenerated, OfficeObjectId, RiskTime, UserId

| where (DownloadTime - RiskTime) between (0min .. timeframe)

| summarize RiskyDownloads=make_set(OfficeObjectId) by UserId, DownloadTime

| where array_length( RiskyDownloads) > 10

**********************************************************************************

//summarize the list of downloads the risky user has taken, and only alert when it is greater than 10 unique files.

 

//These kind of multistage queries don’t need to be limited to users or identity type events, you can use the same structure to query device data, or anything else that is relevant to you.

 

let timeframe = 48h;

SecurityAlert

| where ProviderName == "MDATP"

| project AlertTime=TimeGenerated,DeviceName=CompromisedEntity, AlertName, DisplayName, Entities, ProductName, Status, VendorName

| join kind=inner (

DeviceLogonEvents

| project TimeGenerated, LogonType, ActionType, InitiatingProcessCommandLine, IsLocalAdmin, AccountName, DeviceName, RemoteIP

| where LogonType in ("Interactive","RemoteInteractive")

| where ActionType == "LogonSuccess"

| where InitiatingProcessCommandLine == "lsass.exe"

) on DeviceName

| where (AlertTime - TimeGenerated) between (0min .. timeframe)

| summarize arg_max(TimeGenerated, *) by DeviceName

| project LogonTime=TimeGenerated, AlertTime, AlertName, DeviceName, AccountName, IsLocalAdmin, LogonType, Entities, DisplayName, ProductName, Status, VendorName

**********************************************************************************

//Keep an eye on your Azure AD guests with Microsoft Sentinel

 

//Visualizes the total amount of guest invites sent to those redeemed

let timerange=180d;

let timeframe=7d;

AuditLogs

| where TimeGenerated > ago (timerange)

| where OperationName in ("Redeem external user invite", "Invite external user")

| summarize

    InvitesSent=countif(OperationName == "Invite external user"),

    InvitesRedeemed=countif(OperationName == "Redeem external user invite")

    by bin(TimeGenerated, timeframe)

| render columnchart

    with (

    title="Guest Invites Sent v Guest Invites Redeemed",

    xtitle="Invites",

    kind=unstacked)

**********************************************************************************

//Creates a list of your applications and summarizes successful signins by members vs guests

let timerange=30d;

SigninLogs

| where TimeGenerated > ago(timerange)

| project TimeGenerated, UserType, ResultType, AppDisplayName

| where ResultType == 0

| summarize

    MemberSignins=countif(UserType == "Member"),

    GuestSignins=countif(UserType == "Guest")

    by AppDisplayName, TimeGenerated

| sort by AppDisplayName 

 

**********************************************************************************

 

//You can quickly see which users haven’t signed in over the last month, having signed in successfully in the preceding 6 months.

let timerange=180d;

let timeframe=30d;

SigninLogs

| where TimeGenerated > ago(timerange)

| where UserType == "Guest" or UserPrincipalName contains "#ext#"

| where ResultType == 0

| summarize arg_max(TimeGenerated, *) by UserPrincipalName

| join kind = leftanti 

    (

    SigninLogs

    | where TimeGenerated > ago(timeframe)

    | where UserType == "Guest" or UserPrincipalName contains "#ext#"

    | where ResultType == 0

    | summarize arg_max(TimeGenerated, *) by UserPrincipalName

    )

    on UserPrincipalName

| project UserPrincipalName, TimeGenerated

**********************************************************************************

//Month by month breakdown of when your Azure AD guests last signed in

SigninLogs

| where TimeGenerated > ago (360d)

| where UserType == "Guest" or UserPrincipalName contains "#ext#"

| where ResultType == 0

| summarize arg_max(TimeGenerated, *) by UserPrincipalName

| project TimeGenerated, UserPrincipalName

| summarize InactiveUsers=make_set(UserPrincipalName) by startofmonth(TimeGenerated)

*********************************************************************************

//EntitlementGrantsNotFound - The signed in user isn't assigned to a role for the signed in app. Assign the user to the app. To learn more, see the troubleshooting article for error AADSTS50105.

//look at guests accounts that are trying to access your applications but being denied because they aren’t assigned a role, this could potentially be some reconnaissance occurring in your environment.

 

SigninLogs

| where UserType == "Guest"

| where ResultType == "50105"

| project TimeGenerated, UserPrincipalName, AppDisplayName, IPAddress, Location, UserAgent

**********************************************************************************

//EntitlementGrantsNotFound - The signed in user isn't assigned to a role for the signed in app. Assign the user to the app. To learn more, see the troubleshooting article for error AADSTS50105.

//look at guests accounts that are trying to access your applications but being denied because they aren’t assigned a role, this could potentially be some reconnaissance occurring in your environment.

 

SigninLogs

| where UserType == "Guest"

| where ResultType == "50105"

| project TimeGenerated, UserPrincipalName, AppDisplayName, IPAddress, Location, UserAgent

| summarize count()by AppDisplayName

| render piechart

*********************************************************************************

//We can also use our IdentityInfo table again to grab a list of all our guests, then join to our OfficeActivity table to summarize download activities by each of your guests.

//Summarize the total count and the list of files downloaded by guests in your Office 365 tenant

let timeframe=30d;

IdentityInfo

| where TimeGenerated > ago(90d)

| where UserType == "Guest"

| summarize arg_max(TimeGenerated, *) by AccountUPN

| project UserId=tolower(AccountUPN)

| join kind=inner (

    OfficeActivity

    | where TimeGenerated > ago(timeframe)

    | where Operation in ("FileSyncDownloadedFull", "FileDownloaded")

    )

    on UserId

| summarize DownloadCount=count(), DownloadList=make_set(OfficeObjectId) by UserId

//| summarize count()by AppDisplayName

//| render piechart

 

******************************************************************************

//Summarize the total count of files downloaded by each guest domain in your tenant

let timeframe=30d;

IdentityInfo

| where TimeGenerated > ago(21d)

| where UserType == "Guest"

| summarize arg_max(TimeGenerated, *) by AccountUPN, MailAddress

| project UserId=tolower(AccountUPN), MailAddress

| join kind=inner (

    OfficeActivity

    | where TimeGenerated > ago(timeframe)

    | where Operation in ("FileSyncDownloadedFull", "FileDownloaded")

    )

    on UserId

| extend username = tostring(split(UserId,"#")[0])

| parse MailAddress with * "@" userdomain

| summarize count() by userdomain

*******************************************************************************

// Finds guest accounts who were added to a Team and then downloaded documents straight away.

// startime = data to look back on, timeframe = looks for downloads for this period after being added to the Team

let starttime = 30d;

let timeframe = 2h;

let operations = dynamic(["FileSyncDownloadedFull", "FileDownloaded"]);

OfficeActivity

| where TimeGenerated > ago(starttime)

| where OfficeWorkload == "MicrosoftTeams"

| where Operation == "MemberAdded"

| extend UserAdded = tostring(parse_json(Members)[0].UPN)

| where UserAdded contains ("#EXT#")

| project TimeAdded=TimeGenerated, UserId=tolower(UserAdded)

| join kind=inner

    (

    OfficeActivity

    | where Operation in (['operations'])

    )

    on UserId

| project DownloadTime=TimeGenerated, TimeAdded, SourceFileName, UserId

| where (DownloadTime - TimeAdded) between (0min .. timeframe)

****************************************************************************

 

If you have a list of groups you want to monitor, then it’s worth adding them into a watchlist and then querying against that, then you can keep the watchlist current and your query will continue to be up to date.

let watchlist = (_GetWatchlist('PrivilegedADGroups') | project TargetAccount);
SecurityEvent
extend Target = MemberName
| extend Group = TargetAccount
| where EventID in (4728,4729,4732,4733,4756,4757) and TargetAccount in (watchlist)
| project TimeGenerated, Activity, Actor, Target, Group

 

******************************************************************************

From the article we learnt that the Application Administrator role has the ability to add credentials (secrets or certificates) to any existing application in Azure AD. If you have a service principal that has the Global Administrator role or privilege to the MS Graph, then an Application Administrator can generate a new secret for that app and effectively be a Global Administrator and obtain that privilege.

We can view secrets generated on an app in the AuditLogs table –

AuditLogs
| where OperationName contains "Update application – Certificates and secrets management"
| extend AppId = tostring(AdditionalDetails[1].value)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend AppDisplayName = tostring(TargetResources[0].displayName)
| project TimeGenerated, OperationName, AppDisplayName, AppId, Actor

 

******************************************************************************

From the Specterops article, one of the red flags we mentioned was Azure AD roles being assigned to service principals, we often worry about excessive privilege for users, but forget about apps & service principals. We can detect a role being added to service principals –

AuditLogs
| where OperationName == "Add member to role"
| where TargetResources[0].type == "ServicePrincipal"
| extend ServicePrincipalObjectID = tostring(TargetResources[0].id)
| extend AppDisplayName = tostring(TargetResources[0].displayName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend RoleAdded = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| project TimeGenerated, Actor, RoleAdded, ServicePrincipalObjectID, AppDisplayName

 

******************************************************************************

 

//Defending Azure Active Directory with Azure Sentinel

//users signing onto Azure management portals and interfaces

SigninLogs

| where AppDisplayName in ("Azure Active Directory PowerShell","Microsoft Azure PowerShell","Graph Explorer", "ACOM Azure Website")

| project TimeGenerated, UserPrincipalName, AppDisplayName, Location, IPAddress, UserAgent

 

//look back on the last 90 days (or whatever time frame suits you), then detect users accessing these applications for the first time. This could be a sign of a compromised account being used for reconnaissance.

 

let timeframe = startofday(ago(90d));

let applications = dynamic(["Azure Active Directory PowerShell", "Microsoft Azure PowerShell", "Graph Explorer", "ACOM Azure Website"]);

SigninLogs

| where TimeGenerated > timeframe and TimeGenerated < startofday(now())

| where AppDisplayName in (applications)

| project UserPrincipalName, AppDisplayName

| join kind=rightanti

    (

    SigninLogs

    | where TimeGenerated > startofday(now())

    | where AppDisplayName in (applications)

    )

    on UserPrincipalName, AppDisplayName

| where ResultType == 0

| project TimeGenerated, UserPrincipalName, ResultType, AppDisplayName, IPAddress, Location, UserAgent

 

//detect the assignment of roles to users, if you use Azure AD PIM we can also exclude activations from our query

 

AuditLogs

| where Identity <> "MS-PIM"

| where OperationName == "Add member to role"

| extend Target = tostring(TargetResources[0].userPrincipalName)

| extend RoleAdded = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))

| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

| extend ActorIPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)

| project TimeGenerated, OperationName, Target, RoleAdded, Actor, ActorIPAddress

 

//If you have a lot of users being moved in and out of roles you can reduce the query down to a selected set of privileged roles if required

 

let roles=dynamic(["Global Admininistrator","SharePoint Administrator","Exchange Administrator"]);

AuditLogs

| where OperationName == "Add member to role"

| where Identity <> "MS-PIM"

| extend Target = tostring(TargetResources[0].userPrincipalName)

| extend RoleAdded = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))

| where RoleAdded in (roles)

| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

| extend ActorIPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)

| project TimeGenerated, OperationName, Target, RoleAdded, Actor, ActorIPAddress

 

//if you use Azure AD PIM you can be alerted when users are assigned roles outside of the PIM platform (which you can do via Azure AD PowerShell as an example)

 

AuditLogs

| where OperationName startswith "Add member to role outside of PIM"

| extend RoleAdded = tostring(TargetResources[0].displayName)

| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

| extend ActorIPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)

| extend TargetAADUserId = tostring(TargetResources[2].id)

| project TimeGenerated, OperationName, TargetAADUserId, RoleAdded, Actor, AADTenantId

 

//Defending Shared Identity

//filter out when a privileged Azure AD user has reset their own on premise password – we want events where someone has reset another persons privileged Azure AD account

 

let timeframe=1d;

IdentityInfo

| where TimeGenerated > ago(60d)

| where isnotempty(AssignedRoles)

| where AssignedRoles != "[]"

| summarize arg_max(TimeGenerated, *) by AccountUPN

| project AccountUPN, AccountName, AccountSID

| join kind=inner (

    SecurityEvent

    | where TimeGenerated > ago(timeframe)

    | where EventID == "4724"

    | project

        TimeGenerated,

        Activity,

        SubjectAccount,

        TargetAccount,

        TargetSid,

        SubjectUserSid

    )

    on $left.AccountSID == $right.TargetSid

| where SubjectUserSid != TargetSid

| project PasswordResetTime=TimeGenerated, Activity, ActorAccountName=SubjectAccount, TargetAccountUPN=AccountUPN,TargetAccountName=TargetAccount

 

//n Azure AD admin resets a password it is logged as a ‘Reset password (by admin)’ action in Azure Sentinel, we can retrieve the actor, the target and the outcome

AuditLogs

| where OperationName == "Reset password (by admin)"

| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

| extend Target = tostring(TargetResources[0].userPrincipalName)

| project TimeGenerated, OperationName, Result, Actor, Target

 

//An attacker could go further and use a service principal to leverage Microsoft Graph to initiate a password reset in Azure AD and have it written back to on-premise. This activity is shown in the AuditLogs table

 

AuditLogs

| where OperationName == "POST UserAuthMethod.ResetPasswordOnPasswordMethods"

| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

| project TimeGenerated, OperationName, Actor, CorrelationId

| join kind=inner

    (AuditLogs

    | where OperationName == "Reset password (by admin)"

    | extend Target = tostring(TargetResources[0].userPrincipalName)

    | where Result == "success"

    )

    on CorrelationId

| project GraphPostTime=TimeGenerated, PasswordResetTime=TimeGenerated1, Actor, Target

 

//detect changes to groups in Azure Sentinel

SecurityEvent

| extend Actor = Account

| extend Target = MemberName

| extend Group = TargetAccount

| where EventID in (4728,4729,4732,4733,4756,4757) and Group == "DOMAIN\\ad.security.appowners"

| project TimeGenerated, Activity, Actor, Target, Group

 

//detect application permission additions with

AuditLogs

| where OperationName == "Add app role assignment to service principal"

| extend AppPermissionsAdded = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))

| extend AppId = tostring(TargetResources[1].id)

| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

| extend ActorIPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)

| project TimeGenerated, OperationName, AppId, AppPermissionsAdded,Actor, ActorIPAddress

 

//And delegated permissions additions with

 

AuditLogs

| where OperationName == "Add delegated permission grant"

| extend DelegatedPermissionsAdded = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue)))

| extend AppId = tostring(TargetResources[1].id)

| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

| extend ActorIPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)

| project TimeGenerated, OperationName, AppId, DelegatedPermissionsAdded,Actor, ActorIPAddress

 

//investigate consent actions, including the permissions that have been granted

AuditLogs

| where OperationName contains "Consent to application"

| extend AppDisplayName = tostring(TargetResources[0].displayName)

| extend Consent = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[4].newValue)))

| parse Consent with * "Scope:" PermissionsConsentedto ']' *

| extend UserWhoConsented = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

| extend AdminConsent = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue)))

| extend AppType = tostring(TargetResources[0].type)

| extend AppId = tostring(TargetResources[0].id)

| project TimeGenerated, AdminConsent, AppDisplayName, AppType, AppId, PermissionsConsentedto, UserWhoConsented

 

//detect a role being added to service principals

AuditLogs

| where OperationName == "Add member to role"

| where TargetResources[0].type == "ServicePrincipal"

| extend ServicePrincipalObjectID = tostring(TargetResources[0].id)

| extend AppDisplayName = tostring(TargetResources[0].displayName)

| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

| extend RoleAdded = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))

| project TimeGenerated, Actor, RoleAdded, ServicePrincipalObjectID, AppDisplayName

 

//detect changes in redirect URI’s

AuditLogs

| where OperationName contains "Update application"

| where Result == "success"

| extend UpdatedProperty = tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].displayName)

| where UpdatedProperty == "AppAddress"

| extend NewRedirectURI = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue))[0].Address)

| where isnotempty( NewRedirectURI)

| project TimeGenerated, OperationName, UpdatedProperty, NewRedirectURI

 

//Service principal sign-ins are held in the AADServicePrincipalSignInLogs table in Azure Sentinel, the structure is similar to regular sign ins so you can look in trends in the data much like interactive sign-ins and start to detect anything out of the ordinary.

 

AADServicePrincipalSignInLogs

| where ResultType == "0"

| project TimeGenerated, AppId, ResourceDisplayName

| summarize SPSignIn=count()by bin(TimeGenerated, 15m), ResourceDisplayName

| render timechart

 

//Service principals can generate errors on logons too, an error 7000215 in the AADServicePrincipalSignInLogs table is an invalid secret, or the service principal equivalent of a wrong password

 

AADServicePrincipalSignInLogs

| where ResultType == "7000215"

| summarize count()by AppId, ResourceDisplayName, TimeGenerated, IPAddress, Id, Location, OperationName, ServicePrincipalName, Type, ServicePrincipalId

​

​