Splunk ESCU

//Visualize the MFA types used by your users, i.e text message, mobile app notification, verification code

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

SigninLogs
| where TimeGenerated > ago (30d)
| where AuthenticationRequirement == "multiFactorAuthentication"
| project AuthenticationDetails
| extend ['MFA Method'] = tostring(parse_json(AuthenticationDetails)[1].authenticationMethod)
| summarize Count=count()by ['MFA Method']
| where ['MFA Method'] != "Previously satisfied" and isnotempty(['MFA Method'])
| sort by Count desc
| render barchart with (title="Types of MFA Methods used")

//Visualize the MFA types used by your users - phone sign in, mobile passcode, push or text message, over time

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

SigninLogs
| where TimeGenerated > ago (180d)
| where AuthenticationRequirement == "multiFactorAuthentication"
| project TimeGenerated, AuthenticationDetails
| extend ['MFA Method'] = tostring(parse_json(AuthenticationDetails)[1].authenticationMethod)
| summarize Count=count()by ['MFA Method'], bin(TimeGenerated, 7d)
| where ['MFA Method'] != "Previously satisfied" and isnotempty(['MFA Method']) 
| render timechart with (ytitle="Count", xtitle="Day", title="MFA methods per week over time")

//Visualize password vs passwordless signins per day

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

SigninLogs
| where TimeGenerated > ago (180d)
| mv-expand todynamic(AuthenticationDetails)
| project TimeGenerated, AuthenticationDetails
| extend AuthMethod = tostring(AuthenticationDetails.authenticationMethod)
| summarize
    Passwordless=countif(AuthMethod in ("Windows Hello for Business", "Passwordless phone sign-in", "FIDO2 security key", "X.509 Certificate")),
    Password=countif(AuthMethod == "Password")
    by bin(TimeGenerated, 1d)
| render timechart with (title="Passwordless vs Password Authentication", ytitle="Count")

//Visualize the different risk types (e.g password spray, unlikely travel) per month

//Data connector required for this query - Azure Active Directory - AAD User Risk Events

AADUserRiskEvents
| where TimeGenerated > ago (180d)
| where isnotempty(RiskEventType)
| summarize Count=count()by RiskEventType, startofmonth(TimeGenerated)
| render columnchart with (kind=unstacked, title="Risk event types per month", xtitle="Month")

//Visualize successful self service password resets and account unlocks over time

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

AuditLogs
| where TimeGenerated > ago (180d)
| where OperationName in ("Reset password (self-service)", "Unlock user account (self-service)")
| summarize
    ['Password Reset']=countif(OperationName == "Reset password (self-service)" and ResultDescription == "Successfully completed reset."),
    ['Account Unlock']=countif(OperationName == "Unlock user account (self-service)" and ResultDescription == "Success")
    by startofweek(TimeGenerated)
| render timechart
    with (
    ytitle="Count",
    xtitle="Day",
    title="Self Service Password Resets and Account Unlocks over time")

//Visualize sign in attempts to your Azure AD tenant by device trust type

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

SigninLogs
| where TimeGenerated > ago(30d)
| extend DeviceTrustType = tostring(DeviceDetail.trustType)
| extend ['Trust Type']=case(isnotempty(DeviceTrustType), strcat=DeviceTrustType,
    isempty(DeviceTrustType), strcat="Untrusted",
    "unknown")
| summarize Count=count()by ['Trust Type'], bin(TimeGenerated, 1d)
| render timechart with (title="Signins to Azure AD by trust type")

//Visualize the difference been total and distinct user sign ins to an app per day

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

//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(90d)
| where AppDisplayName == "Office 365 Exchange Online"
| where ResultType == 0
| summarize ['Total Signins']=count(), ['Distinct user signins']=dcount(UserPrincipalName) by bin(TimeGenerated, 1d)
| render timechart
    with (
    title="Total vs Distinct signins to Exchange Online",
    xtitle="Day",
    ytitle="Count")

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting with Azure AD P2 License

AADSignInEventsBeta
| where Timestamp > ago(90d)
| where Application == "Office 365 Exchange Online"
| where ErrorCode == 0
| summarize ['Total Signins']=count(), ['Distinct user signins']=dcount(AccountUpn) by bin(Timestamp, 1d)
| render timechart

//Visualize your sign ins over a world map
//This visualization is supported in the Kusto Explorer app or the Web UI
//You can install the app here - https://learn.microsoft.com/en-us/azure/data-explorer/kusto/tools/kusto-explorer

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

SigninLogs
| where TimeGenerated > ago (90d)
| extend BeginLat = toreal(parse_json(tostring(LocationDetails.geoCoordinates)).latitude)
| extend BeginLon = toreal(parse_json(tostring(LocationDetails.geoCoordinates)).longitude)
| summarize Count=count() by BeginLon, BeginLat
| project-reorder BeginLon, BeginLat, Count
| where isnotempty(BeginLon)
| render scatterchart with (kind=map)

//Find sign ins where your users signed into other Azure AD tenants as outbound guests

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

SigninLogs
| where AADTenantId == HomeTenantId
| where ResourceTenantId != AADTenantId
| where UserType == "Guest"
| project
    TimeGenerated,
    AppDisplayName,
    UserPrincipalName,
    ResultType,
    Location,
    IPAddress,
    ['Guest Tenant Id']=ResourceTenantId

//Alert when Defender for Identity detects a change in kerberos constrained delegation configuration on a device

//Data connector required for this query - M365 Defender - Identity* tables

IdentityDirectoryEvents
| where ActionType == "Account Constrained Delegation changed"
| extend AF = parse_json(AdditionalFields)
| extend ['Previous Delegation Setting'] = AF.["FROM AccountConstrainedDelegationState"]
| extend ['Current Delegation Setting'] = AF.["TO AccountConstrainedDelegationState"]
| extend ['Device Operating System'] = AF.TargetComputerOperatingSystem
| project
    TimeGenerated,
    TargetDeviceName,
    ['Device Operating System'],
    ['Previous Delegation Setting'],
    ['Current Delegation Setting']

//Detect when the encryption types on a device are changed and parse the previous and current encryption types.

//Data connector required for this query - M365 Defender - Identity* tables or Advanced Hunting license

//If you don't send Defender for Id logs to Sentinel you can use the query in M365 Advanced Hunting directly
IdentityDirectoryEvents
| where ActionType == "Account Supported Encryption Types changed"
| parse AdditionalFields with * 'FROM AccountSupportedEncryptionTypes":"' PreviousEncryption '"' *
| parse AdditionalFields with * 'TO AccountSupportedEncryptionTypes":"' CurrentEncryption '"' *
| project TimeGenerated, TargetDeviceName, PreviousEncryption, CurrentEncryption

//Alert when Defender for Identity detects an account being set to 'password never expires'

//Data connector required for this query - M365 Defender - Identity* tables

//Microsoft Sentinel query
IdentityDirectoryEvents
| where ActionType == "Account Password Never Expires changed"
| extend ['Password never expires previous setting'] = tostring(AdditionalFields.["FROM Account Password Never Expires"])
| extend ['Password never expires current setting'] = tostring(AdditionalFields.["TO Account Password Never Expires"])
| project
    TimeGenerated,
    TargetAccountUpn,
    ['Password never expires current setting'],
    ['Password never expires previous setting']

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

IdentityDirectoryEvents
| where ActionType == "Account Password Never Expires changed"
| extend ['Password never expires previous setting'] = tostring(AdditionalFields.["FROM Account Password Never Expires"])
| extend ['Password never expires current setting'] = tostring(AdditionalFields.["TO Account Password Never Expires"])
| project
    Timestamp,
    TargetAccountUpn,
    ['Password never expires current setting'],
    ['Password never expires previous setting']

//Find user accounts with the 'password not required' flag set in Active Directory

//Data connector required for this query - Microsoft Sentinel UEBA

IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| extend UACFlags = tostring(UserAccountControl[0])
| where UACFlags == "PasswordNotRequired"

//Summarize accounts in our environment that have the same employee id (i.e regular and admin accounts)

//Data connector required for this query - Microsoft Sentinel UEBA

IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where isnotempty(EmployeeId)
| summarize ['Count of accounts']=dcount(AccountUPN), ['List of accounts']=make_set(AccountUPN) by EmployeeId
| sort by ['Count of accounts'] desc

//Find accounts that are considered to have a high blast radius and currently at risk

//Data connector required for this query - Microsoft Sentinel UEBA

IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where BlastRadius == "High"
| where RiskState == "AtRisk"

//Find Azure AD guest accounts that are considered to have a high blast radius

//Data connector required for this query - Microsoft Sentinel UEBA

IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where UserType == "Guest" and BlastRadius == "High"

//Find user accounts that hold an Azure AD privileged role and are considered to have a high blast radius

//Data connector required for this query - Microsoft Sentinel UEBA

IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where isnotempty(AssignedRoles) and AssignedRoles != "[]"
| where BlastRadius == "High"

//Find user accounts that are members of over 150 groups. These can cause issues with SAML claims.
//See https://docs.microsoft.com/en-us/azure/active-directory/hybrid/how-to-connect-fed-group-claims

//Data connector required for this query - Microsoft Sentinel UEBA

IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| extend ['Group Count']=array_length(GroupMembership)
| sort by ['Group Count'] desc 
| where ['Group Count'] > 150

//Visualize accounts by blast radius level

//Data connector required for this query - Microsoft Sentinel UEBA

IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where isnotempty(BlastRadius)
| summarize Count=count()by BlastRadius
| order by Count
| render piechart with (title="Accounts by Microsoft Sentinel EUBA blast radius")

//Summarize the accounts in your environment using cleartext LDAP connections

//Data connector required for this query - M365 Defender - Identity* tables

//Microsoft Sentinel query
IdentityLogonEvents
| where TimeGenerated > ago (30d)
| where LogonType == "LDAP cleartext"
| summarize
    ['Total connection count']=count(),
    ['Distinct destination device count']=dcount(DestinationDeviceName),
    ['List of destination devices']=make_set(DestinationDeviceName)
    by AccountUpn
| sort by ['Distinct destination device count'] desc 

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

IdentityLogonEvents
| where Timestamp > ago (30d)
| where LogonType == "LDAP cleartext"
| summarize
    ['Total connection count']=count(),
    ['Distinct destination device count']=dcount(DestinationDeviceName),
    ['List of destination devices']=make_set(DestinationDeviceName)
    by AccountUpn
| sort by ['Distinct destination device count'] desc

//Summarize NTLM authentications by which source computers & accounts are connecting to the most destination devices

//Data connector required for this query - M365 Defender - Identity* tables

//Microsoft Sentinel query
IdentityLogonEvents
| where TimeGenerated > ago(7d)
| where ActionType == "LogonSuccess"
| where Protocol =~ "Ntlm"
| where LogonType == "Credentials validation"
| summarize ['Target Device List']=make_set(DestinationDeviceName), ['Target Device Count']=dcount(DestinationDeviceName) by DeviceName, AccountName
| sort by ['Target Device Count'] desc 

//Advanced Hunting query
IdentityLogonEvents

//Data connector required for this query - Advanced Hunting license

| where Timestamp > ago(7d)
| where ActionType == "LogonSuccess"
| where Protocol =~ "Ntlm"
| where LogonType == "Credentials validation"
| summarize ['Target Device List']=make_set(DestinationDeviceName), ['Target Device Count']=dcount(DestinationDeviceName) by DeviceName, AccountName
| sort by ['Target Device Count'] desc

//When Azure AD flags a device as non compliant, retrieve the details about the devices from Intune

//Data connector required for this query - Intune data sent to Sentinel workspace

//First find the device name from the 'device no longer compliant' action
let devices=
    AuditLogs
    | where TimeGenerated > ago (1d)
    | where OperationName == "Device no longer compliant"
    | extend DeviceName = tostring(TargetResources[0].displayName)
    | distinct DeviceName;
//Lookup those devices in the IntuneDevices table, and retrieve the latest record
IntuneDevices
| where TimeGenerated > ago (7d)
| summarize arg_max(TimeGenerated, *) by DeviceName
| where DeviceName in (devices)

//When an Intune admin initiates a remote wipe of a managed device, retrieve all the relevant information about the device

//Data connector required for this query - Intune data sent to Sentinel workspace

IntuneAuditLogs
| where TimeGenerated > ago (1d)
| where OperationName == "wipe ManagedDevice"
| extend DeviceId = tostring(parse_json(tostring(parse_json(Properties).TargetObjectIds))[0])
| project TimeGenerated, Actor=Identity, DeviceId
| join kind=inner(
    IntuneDevices
//Go back 7 days to make sure we have information on the device and retrieve the lastest record
    | where TimeGenerated > ago(7d)
    | summarize arg_max(TimeGenerated, *) by DeviceId
    )
    on DeviceId
| project
    TimeGenerated,
    Actor,
    DeviceId,
    Model,
    SerialNumber,
    OS,
    PrimaryUser=UserEmail,
    Ownership,
    ManagedBy,
    LastContact

//Visualize device compliance (compliant, non-compliant, managed by Config Manager, not evaluated or in grace period) per week over time

//Data connector required for this query - Intune data sent to Sentinel workspace

IntuneDevices
| where TimeGenerated > ago (180d)
| summarize arg_max(DeviceName, *) by DeviceName, startofweek(TimeGenerated)
| where isnotempty(CompliantState)
| summarize ComplianceCount=count()by CompliantState, startofweek(TimeGenerated)
| render timechart
    with (
    ytitle="Device Count",
    xtitle="Week",
    title="Device compliance per week over time")

//Visualize the join type (Azure AD joined, Azure AD registered or Hybrid joined) of your MEM/Intune devices per week

//Data connector required for this query - Intune data sent to Sentinel workspace

IntuneDevices
//Gets all data generated in 180 days
| where TimeGenerated > ago(180d) 
//Optionally filter only devices have contact to Intune in 30 days
| where todatetime(LastContact) > ago (30d) 
| summarize arg_max(TimeGenerated, *) by DeviceName, startofweek(TimeGenerated)
| where OS == "Windows"
| summarize JoinSummary=count()by JoinType, startofweek(TimeGenerated)
| where isnotempty(JoinType)
| render columnchart
    with (
    kind=unstacked,
    ytitle="Device Count",
    xtitle="Week",
    title="Device count by join type per week")

//Visualize when your devices last contacted Intune

//Data connector required for this query - Intune data sent to Sentinel workspace

IntuneDevices
| where TimeGenerated > ago(90d)
| where isnotempty(LastContact)
//Retrieve latest record for each DeviceId
| summarize arg_max(TimeGenerated, *) by DeviceId
//Convert string to datetime format
| extend LastContactTime = todatetime(LastContact)
| project DeviceId, LastContactTime
//Exclude devices reporting as 0001-01-01
| where LastContactTime <> todatetime('0001-01-01T00:00:00Z')
//Group by month and render chart
| summarize ['Device Count']=count()by startofmonth(LastContactTime)
| render columnchart with (title="Intune devices by last contact time", xtitle="Month")

//Visualize devices in intune with the same intune and Azure AD Id per week by join type

//Data connector required for this query - Intune data sent to Sentinel workspace

IntuneDevices
| where TimeGenerated > ago (180d)
| summarize arg_max(TimeGenerated, *) by DeviceName, startofweek(TimeGenerated)
| where DeviceId == ReferenceId
| where OS == 'Windows'
| summarize count()by startofweek(TimeGenerated), JoinType
| where isnotempty( JoinType)
| render columnchart with (kind=unstacked, title="Devices with the same Azure AD and Intune device Id per week by join type")

//Searches for access by applications that have not previously accessed an Azure Key Vault in the last 30 days and returns all actions by those applications

//Data connector required for this query - Azure Key Vault

let operationlist = dynamic(["SecretGet", "KeyGet", "VaultGet"]);
let starttime = 30d;
let endtime = 1d;
let detection=
    AzureDiagnostics
    | where TimeGenerated between (ago(starttime) .. ago(endtime))
    | where ResourceType == "VAULTS"
    | where ResultType == "Success"
    | where OperationName in (operationlist)
    | where isnotempty(identity_claim_appid_g)
    | project-rename KeyVaultName=Resource, AppId=identity_claim_appid_g
    | distinct KeyVaultName, AppId
    | join kind=rightanti  (
        AzureDiagnostics
        | where TimeGenerated > ago(endtime)
        | where ResourceType == "VAULTS"
        | where ResultType == "Success"
        | where OperationName in (operationlist)
        | where isnotempty(identity_claim_appid_g)
        | project-rename
            KeyVaultName=Resource,
            AppId=identity_claim_appid_g
        | distinct KeyVaultName, AppId)
        on KeyVaultName, AppId;
AzureDiagnostics
| where TimeGenerated > ago(endtime)
| where ResourceType == "VAULTS"
| where ResultType == "Success"
| project-rename
    KeyVaultName=Resource,
    AppId=identity_claim_appid_g
| join kind=inner detection on KeyVaultName, AppId
| project
    TimeGenerated,
    AppId,
    ResourceGroup,
    SubscriptionId,
    KeyVaultName,
    KeyVaultTarget=id_s,
    OperationName

//Searches for access by users who have not previously accessed an Azure Key Vault in the last 30 days and returns all actions by those users

//Data connector required for this query - Azure Key Vault

let operationlist = dynamic(["SecretGet", "KeyGet", "VaultGet"]);
let starttime = 30d;
let endtime = 1d;
let detection=
    AzureDiagnostics
    | where TimeGenerated between (ago(starttime) .. ago(endtime))
    | where ResourceType == "VAULTS"
    | where ResultType == "Success"
    | where OperationName in (operationlist)
    | where isnotempty(identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_upn_s)
    | project-rename KeyVaultName=Resource, UserPrincipalName=identity_claim_appid_g
    | distinct KeyVaultName, UserPrincipalName
    | join kind=rightanti  (
        AzureDiagnostics
        | where TimeGenerated > ago(endtime)
        | where ResourceType == "VAULTS"
        | where ResultType == "Success"
        | where OperationName in (operationlist)
        | where isnotempty(identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_upn_s)
        | project-rename
            KeyVaultName=Resource,
            UserPrincipalName=identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_upn_s
        | distinct KeyVaultName, UserPrincipalName)
        on KeyVaultName, UserPrincipalName;
AzureDiagnostics
| where TimeGenerated > ago(endtime)
| where ResourceType == "VAULTS"
| where ResultType == "Success"
| project-rename
    KeyVaultName=Resource,
    UserPrincipalName=identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_upn_s
| join kind=inner detection on KeyVaultName, UserPrincipalName
| project
    TimeGenerated,
    UserPrincipalName,
    ResourceGroup,
    SubscriptionId,
    KeyVaultName,
    KeyVaultTarget=id_s,
    OperationName

// Detects when an Azure Key Vault firewall is set to allow all by default

//Data connector required for this query - Azure Key Vault

AzureDiagnostics
| where ResourceType == "VAULTS"
| where OperationName == "VaultPatch"
| where ResultType == "Success"
| project-rename ExistingACL=properties_networkAcls_defaultAction_s, VaultName=Resource
| where isnotempty(ExistingACL)
| where ExistingACL == "Deny"
| sort by TimeGenerated desc  
| project
    TimeGenerated,
    SubscriptionId,
    VaultName,
    ExistingACL
| join kind=inner
(
AzureDiagnostics
| project-rename NewACL=properties_networkAcls_defaultAction_s, VaultName=Resource
| where ResourceType == "VAULTS"
| where OperationName == "VaultPatch"
| where ResultType == "Success"
| summarize arg_max(TimeGenerated, *) by VaultName, NewACL
) 
on VaultName
| where ExistingACL != NewACL and NewACL == "Allow"
| project DetectionTime=TimeGenerated1, VaultName, ExistingACL, NewACL, SubscriptionId, IPAddressofActor=CallerIPAddress, Actor=identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_upn_s

// Detects when an IP address has been added to an Azure Key Vault firewall allow list

//Data connector required for this query - Azure Key Vault

AzureDiagnostics
| where ResourceType == "VAULTS"
| where OperationName == "VaultPatch"
| where ResultType == "Success"
| where isnotempty(addedIpRule_Value_s)
| project
    TimeGenerated,
    VaultName=Resource,
    SubscriptionId,
    IPAddressofActor=CallerIPAddress,
    Actor=identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_upn_s,
    IPRangeAdded=addedIpRule_Value_s

// Detects when a service principal (user, group or app) has been granted access to Key Vault data

//Data connector required for this query - Azure Key Vault

AzureDiagnostics
| where ResourceType == "VAULTS"
| where OperationName == "VaultPatch"
| where ResultType == "Success"
| project-rename ServicePrincipalAdded=addedAccessPolicy_ObjectId_g, Actor=identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_name_s, AddedKeyPolicy = addedAccessPolicy_Permissions_keys_s, AddedSecretPolicy = addedAccessPolicy_Permissions_secrets_s,AddedCertPolicy = addedAccessPolicy_Permissions_certificates_s
| where isnotempty(AddedKeyPolicy)
    or isnotempty(AddedSecretPolicy)
    or isnotempty(AddedCertPolicy)
| project
    TimeGenerated,
    KeyVaultName=Resource,
    ServicePrincipalAdded,
    Actor,
    IPAddressofActor=CallerIPAddress,
    AddedSecretPolicy,
    AddedKeyPolicy,
    AddedCertPolicy

// Detects Key Vault operations that could be malicious

//Data connector required for this query - Azure Key Vault

let operationlist = dynamic(
    ["VaultDelete", "KeyDelete", "SecretDelete", "SecretPurge", "KeyPurge", "SecretBackup", "KeyBackup", "SecretListDeleted", "CertificateCreate", "CertificatePurge"]);
AzureDiagnostics
| where ResourceType == "VAULTS" and ResultType == "Success" 
| where OperationName in (operationlist)
| project TimeGenerated,
    ResourceGroup,
    SubscriptionId,
    KeyVaultName=Resource,
    KeyVaultTarget=id_s,
    Actor=identity_claim_upn_s,
    IPAddressofActor=CallerIPAddress,
    OperationName

let DomainNames = dynamic(["irf.services","microsoft-onthehub.com","msofficelab.com","com-mailbox.com","my-sharefile.com","my-sharepoints.com",
"accounts-web-mail.com","customer-certificate.com","session-users-activities.com","user-profile-credentials.com","verify-linke.com","support-servics.net",
"onedrive-sharedfile.com","onedrv-live.com","transparencyinternational-my-sharepoint.com","transparencyinternational-my-sharepoints.com","soros-my-sharepoint.com"]);
(union isfuzzy=true
  (CommonSecurityLog
  | where Message has_any (DomainNames)
  | parse Message with * '(' DNSName ')' *
  | extend AccountName = SourceUserID, DeviceName, IPAddress = SourceIP
  ),
  (_Im_Dns(domain_has_any=DomainNames)
  | where DnsQuery has_any (DomainNames)
  | extend IPAddress = SrcIpAddr, DeviceName = Dvc
  ),
  (VMConnection
  | where RemoteDnsCanonicalNames has_any (DomainNames)
  | parse RemoteDnsCanonicalNames with * '["' DNSName '"]' *
  | extend IPAddress = RemoteIp, DeviceName = Computer
  ),
  (AzureDiagnostics 
  | where ResourceType == "AZUREFIREWALLS"
  | where Category == "AzureFirewallApplicationRule"
  | parse msg_s with Protocol 'request from ' SourceHost ':' SourcePort 'to ' DestinationHost ':' DestinationPort '. Action:' Action
  | where DestinationHost has_any (DomainNames)
  | extend DNSName = DestinationHost 
  | extend IPAddress = SourceHost
  ),
  (AzureDiagnostics
  | where ResourceType == "AZUREFIREWALLS"
  | where Category == "AzureFirewallDnsProxy"
  | project TimeGenerated,Resource, msg_s, Type
  | parse msg_s with "DNS Request: " ClientIP ":" ClientPort " - " QueryID " " Request_Type " " Request_Class " " Request_Name ". " Request_Protocol " " Request_Size " " EDNSO_DO " " EDNS0_Buffersize " " Responce_Code " " Responce_Flags " " Responce_Size " " Response_Duration
  | where Request_Name  has_any (DomainNames)
  | extend DNSName = Request_Name
  | extend IPAddress = ClientIP
  ),
  (AZFWApplicationRule
  | where isnotempty(Fqdn)
  | where Fqdn has_any (DomainNames)  
  | extend DNSName = Fqdn 
  | extend IPAddress = SourceIp
  ),
  (AZFWDnsQuery
  | where isnotempty(QueryName)
  | where QueryName has_any (DomainNames)
  | extend DNSName = QueryName
  | extend IPAddress = SourceIp
  ),
  (
    _Im_WebSession(url_has_any=DomainNames)
    | extend IPAddress=IpAddr, DeviceName=Hostname, AccountName = tostring(split(User, "@")[0]), AccountDomain = tostring(split(User, "@")[1])
  )
)
| where DNSName has_any (DomainNames)
| extend timestamp = TimeGenerated
| extend HostName = tostring(split(DeviceName, ".")[0]), DomainIndex = toint(indexof(DeviceName, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(DeviceName, DomainIndex + 1), DeviceName)

//Create a list of all tables in Sentinel, then iterate through the list to audit the LAQuery log table to see which are being actively used

//Data connector required for this query - Log Analytics diagnostic settings enabled on your Sentinel workspace

let tablenames = search * 
    | summarize make_set($table);
LAQueryLogs
| mv-apply table=toscalar(tablenames) to typeof(string) on (where QueryText contains ['table'])
| summarize QueryCount = count()by ['table']
| order by QueryCount
| render piechart

//Find users querying your Log Analytics/Sentinel data for the first time

//Data connector required for this query - Log Analytics diagnostic settings enabled on your Sentinel workspace

let knownusers=
    LAQueryLogs
    | where TimeGenerated > ago(180d) and TimeGenerated < ago(1d)
    | distinct AADEmail
    | where isnotempty(AADEmail);
LAQueryLogs
| where TimeGenerated > ago(1d)
| where AADEmail !in (knownusers)
| where isnotempty(AADEmail)
| project TimeGenerated, AADEmail, QueryText

//Visualizes queries against your log analytics workspace categorized by users and service principals

//Data connector required for this query - Log Analytics diagnostic settings enabled on your Sentinel workspace

let timeframe=45d;
LAQueryLogs
| where TimeGenerated > ago (timeframe)
| summarize Users=countif(isnotempty(AADEmail)), Playbooks=countif(isempty(AADEmail)) by bin(TimeGenerated, 1d)
| render columnchart with (kind=unstacked, ytitle="Queries Run", title="Queries Run - Users vs Playbooks")

//Visualize how many queries you have run in your Sentinel workspace over the last year

LAQueryLogs
| where TimeGenerated > ago (365d)
| where AADEmail == "[email protected]"
| make-series Count=count() default=0 on TimeGenerated from ago(365d) to now() step 1d
| render timechart with (title="#365daysofKQL queries run per day", ytitle="Count")

let Alert_List= dynamic([
"Phishing link click observed in Network Traffic",
"Phish delivered due to an IP allow policy",
"A potentially malicious URL click was detected",
"High Risk Sign-in Observed in Network Traffic",
"A user clicked through to a potentially malicious URL",
"Suspicious network connection to AitM phishing site",
"Messages containing malicious entity not removed after delivery",
"Email messages containing malicious URL removed after delivery",
"Email reported by user as malware or phish",
"Phish delivered due to an ETR override",
"Phish not zapped because ZAP is disabled"]);
SecurityAlert
| where AlertName in~ (Alert_List)
//Findling Alerts which has the URL
| where Entities has "url"
//extracting Entities
| extend Entities = parse_json(Entities)
| mv-apply Entity = Entities on
    (
    where Entity.Type == 'url'
    | extend EntityUrl = tostring(Entity.Url)
    )
| summarize
    Url=tostring(tolower(take_any(EntityUrl))),
    AlertTime= min(TimeGenerated),
    make_set(SystemAlertId, 100)
    by ProductName, AlertName
// matching with 3rd party network logs and 3p Alerts
| join kind= inner (CommonSecurityLog
    | where DeviceVendor has_any  ("Palo Alto Networks", "Fortinet", "Check Point", "Zscaler")
    | where DeviceProduct startswith "FortiGate" or DeviceProduct startswith  "PAN" or DeviceProduct startswith  "VPN" or DeviceProduct startswith "FireWall" or DeviceProduct startswith  "NSSWeblog" or DeviceProduct startswith "URL"
    | where DeviceAction != "Block"
    | where isnotempty(RequestURL)
    | project
        3plogTime=TimeGenerated,
        DeviceVendor,
        DeviceProduct,
        Activity,
        DestinationHostName,
        DestinationIP,
        RequestURL=tostring(tolower(RequestURL)),
        MaliciousIP,
        SourceUserName=tostring(tolower(SourceUserName)),
        IndicatorThreatType,
        ThreatSeverity,
        ThreatConfidence,
        SourceUserID,
        SourceHostName)
    on $left.Url == $right.RequestURL
// matching successful Login from suspicious IP
| join kind=inner (SigninLogs
    //filtering the Successful Login
    | where ResultType == 0
    | project
        IPAddress,
        SourceSystem,
        SigniningTime= TimeGenerated,
        OperationName,
        ResultType,
        ResultDescription,
        AlternateSignInName,
        AppDisplayName,
        AuthenticationRequirement,
        ClientAppUsed,
        RiskState,
        RiskLevelDuringSignIn,
        UserPrincipalName=tostring(tolower(UserPrincipalName)),
        Name = tostring(split(UserPrincipalName, "@")[0]),
        UPNSuffix =tostring(split(UserPrincipalName, "@")[1]))
    on $left.DestinationIP == $right.IPAddress and $left.SourceUserName == $right.UserPrincipalName
| where SigniningTime between ((AlertTime - 6h) .. (AlertTime + 6h)) and 3plogTime between ((AlertTime - 6h) .. (AlertTime + 6h))

(union isfuzzy=true
(OfficeActivity | where UserAgent != ""),
(OfficeActivity
| where RecordType in ("AzureActiveDirectory", "AzureActiveDirectoryStsLogon")
| extend OperationName = Operation
| parse ExtendedProperties with * 'User-Agent\\":\\"' UserAgent2 '\\' *
| parse ExtendedProperties with * 'UserAgent",      "Value": "' UserAgent1 '"' *
| where isnotempty(UserAgent1) or isnotempty(UserAgent2)
| extend UserAgent = iff( RecordType == 'AzureActiveDirectoryStsLogon', UserAgent1, UserAgent2)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserAgent, SourceIP = ClientIP, Account = UserId, Type, RecordType, Operation
),
(AzureDiagnostics
| where ResourceType =~ "APPLICATIONGATEWAYS"
| where OperationName =~ "ApplicationGatewayAccess"
| extend ClientIP = columnifexists("clientIP_s", "None"), UserAgent = columnifexists("userAgent_s", "None")
| where UserAgent != '-'
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserAgent, SourceIP = ClientIP,  requestUri_s, httpMethod_s, host_s, requestQuery_s, Type
),
(
W3CIISLog
| where isnotempty(csUserAgent)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserAgent = csUserAgent, SourceIP = cIP, Account = csUserName, Type, sSiteName, csMethod, csUriStem
),
(
AWSCloudTrail
| where isnotempty(UserAgent)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserAgent, SourceIP = SourceIpAddress, Account = UserIdentityUserName, Type, EventSource, EventName
),
(SigninLogs
| where isnotempty(UserAgent)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserAgent, SourceIP = IPAddress, Account = UserPrincipalName, Type, OperationName, tostring(LocationDetails), tostring(DeviceDetail), AppDisplayName, ClientAppUsed
),
(AADNonInteractiveUserSignInLogs
| where isnotempty(UserAgent)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserAgent, SourceIP = IPAddress, Account = UserPrincipalName, Type, OperationName, tostring(LocationDetails), tostring(DeviceDetail), AppDisplayName, ClientAppUsed
)
)
// Likely artefact of hardcoding
| where UserAgent startswith "User" or UserAgent startswith '\"'
// Incorrect casing
or (UserAgent startswith "Mozilla" and not(UserAgent contains_cs "Mozilla"))
// Incorrect casing
or UserAgent contains_cs  "(Compatible;"
// Missing MSIE version
or UserAgent matches regex @"MSIE\s?;"
// Incorrect spacing around MSIE version
or UserAgent matches regex  @"MSIE(?:\d|.{1,5}?\d\s;)"
| extend AccountName = split(Account, "@")[0], UPNSuffix = split(Account, "@")[1]

let procList = dynamic(["cmd.exe","ftp.exe","schtasks.exe","powershell.exe","rundll32.exe","regsvr32.exe","msiexec.exe"]);  
imProcessCreate
| where CommandLine has "recycler"
| where Process has_any (procList)
| extend FileName = tostring(split(Process, '\\')[-1])
| where FileName in~ (procList)
| project TimeGenerated, Dvc, User, Process, FileName, CommandLine, ActingProcessName, EventVendor, EventProduct
| extend AccountName = tostring(split(User, @'\')[1]), AccountNTDomain = tostring(split(User, @'\')[0])
| extend HostName = tostring(split(Dvc, ".")[0]), DomainIndex = toint(indexof(Dvc, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Dvc, DomainIndex + 1), Dvc)
| project-away DomainIndex

let Alerts = SecurityAlert
| where AlertName =~ "mass download by a single user"
| where Status != 'Resolved'
| extend ipEnt = parse_json(Entities), accountEnt = parse_json(Entities)
| mv-apply tempParams = ipEnt on (
mv-expand ipEnt
| where ipEnt.Type == "ip" 
| extend IpAddress = tostring(ipEnt.Address)
)
| mv-apply tempParams = accountEnt on (
mv-expand accountEnt
| where accountEnt.Type == "account"
| extend AADUserId = tostring(accountEnt.AadUserId)
)
| extend Alert_TimeGenerated = TimeGenerated
| distinct Alert_TimeGenerated, IpAddress, AADUserId, DisplayName, Description, ProductName, ExtendedProperties, Entities, Status, CompromisedEntity
;
let CA_Events = CloudAppEvents
| where ActionType == "FileDownloaded"
| extend parsed = parse_json(RawEventData)
| extend UserId = tostring(parsed.UserId)
| extend FileName = tostring(parsed.SourceFileName)
| extend FileExtension = tostring(parsed.SourceFileExtension)
| summarize CloudAppEvent_StartTime = min(TimeGenerated), CloudAppEvent_EndTime = max(TimeGenerated), CloudAppEvent_Files = make_set(FileName), FileCount = dcount(FileName) by Application, AccountObjectId, UserId, IPAddress, City, CountryCode
| extend CloudAppEvents_Details = pack_all();
let CA_Alerts_Events = Alerts | join kind=inner (CA_Events)
on $left.AADUserId == $right.AccountObjectId and $left.IpAddress == $right.IPAddress
// Cloud app event comes before Alert
| where CloudAppEvent_EndTime <= Alert_TimeGenerated
| project Alert_TimeGenerated, UserId, AADUserId, IPAddress, CloudAppEvents_Details, CloudAppEvent_Files
;
// setup list to filter DeviceFileEvents for only files downloaded as indicated by CloudAppEvents
let CA_FileList = CA_Alerts_Events | project CloudAppEvent_Files;
CA_Alerts_Events
| join kind=inner ( DeviceFileEvents
| where ActionType in ("FileCreated", "FileRenamed")
| where FileName in~ (CA_FileList)
| summarize DeviceFileEvent_StartTime = min(TimeGenerated), DeviceFileEvent_EndTime = max(TimeGenerated), DeviceFileEvent_Files = make_set(FolderPath), DeviceFileEvent_FileCount = dcount(FolderPath) by InitiatingProcessAccountUpn, DeviceId, DeviceName, InitiatingProcessFolderPath, InitiatingProcessParentFileName//, InitiatingProcessCommandLine
| extend DeviceFileEvents_Details = pack_all()
) on $left.UserId == $right.InitiatingProcessAccountUpn
| where DeviceFileEvent_StartTime >= Alert_TimeGenerated
| join kind=inner (
// get device events where a USB drive was mounted
DeviceEvents
| where ActionType == "UsbDriveMounted"
| extend parsed = parse_json(AdditionalFields)
| extend USB_DriveLetter = tostring(AdditionalFields.DriveLetter), USB_ProductName = tostring(AdditionalFields.ProductName), USB_Volume = tostring(AdditionalFields.Volume)
| where isnotempty(USB_DriveLetter)
| project USB_TimeGenerated = TimeGenerated, DeviceId, USB_DriveLetter, USB_ProductName, USB_Volume
| extend USB_Details = pack_all()
)  
on DeviceId
// USB event occurs after the Alert
| where USB_TimeGenerated >= Alert_TimeGenerated
| mv-expand DeviceFileEvent_Files
| extend DeviceFileEvent_Files = tostring(DeviceFileEvent_Files)
// make sure that we only pickup the files that have the USB drive letter
| where DeviceFileEvent_Files startswith USB_DriveLetter
| summarize USB_Drive_MatchedFiles = make_set_if(DeviceFileEvent_Files, DeviceFileEvent_Files startswith USB_DriveLetter) by Alert_TimeGenerated, USB_TimeGenerated, UserId, AADUserId, DeviceId, DeviceName, IPAddress, CloudAppEvents_Details = tostring(CloudAppEvents_Details), DeviceFileEvents_Details = tostring(DeviceFileEvents_Details), USB_Details = tostring(USB_Details)
| extend InitiatingProcessFileName = tostring(split(todynamic(DeviceFileEvents_Details).InitiatingProcessFolderPath, "\\")[-1]), InitiatingProcessFolderPath = tostring(todynamic(DeviceFileEvents_Details).InitiatingProcessFolderPath)
| extend HostName = tostring(split(DeviceName, ".")[0]), DomainIndex = toint(indexof(DeviceName, '.'))
| extend HostNameDomain = iff(DeviceName != -1, substring(DeviceName, DomainIndex + 1), DeviceName)
| extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
| project-away DomainIndex

let iocs = externaldata(DateAdded:string,IoC:string,Type:string,TLP:string) [@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/Mercury_August2022.csv"] with (format="csv", ignoreFirstRecord=True);
let sha256Hashes = (iocs | where Type =~ "sha256" | project IoC);
let IPList = (iocs | where Type =~ "ip"| project IoC);
let domains = (iocs | where Type =~ "domainname"| project IoC);
let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
(union isfuzzy=true
(CommonSecurityLog
| where SourceIP in (IPList) or DestinationIP in (IPList) or DestinationHostName has_any (domains) or RequestURL has_any (domains) or Message has_any (IPList)
| parse Message with * '(' DNSName ')' * 
| project TimeGenerated, SourceIP, DestinationIP, Message, SourceUserID, RequestURL, DNSName, Type
| extend MessageIP = extract(IPRegex, 0, Message), RequestIP = extract(IPRegex, 0, RequestURL)
| extend IPMatch = case(SourceIP in (IPList), "SourceIP", DestinationIP in (IPList), "DestinationIP", MessageIP in (IPList), "Message", RequestURL has_any (domains), "RequestUrl", "NoMatch")
| extend IPAddress = case(IPMatch == "SourceIP", SourceIP, IPMatch == "DestinationIP", DestinationIP, IPMatch == "Message", MessageIP, "NoMatch")
| extend AccountName = tostring(split(SourceUserID, "@")[0]), AccountUPNSuffix = tostring(split(SourceUserID, "@")[1])
),
(DnsEvents
| where IPAddresses in (IPList) or Name in~ (domains)
| project TimeGenerated, Computer, IPAddresses, Name, ClientIP, Type
| extend IPAddress = IPAddresses, DNSName = Name, Computer
),
(VMConnection
| where SourceIp in (IPList) or DestinationIp in (IPList) or RemoteDnsCanonicalNames has_any (domains)
| parse RemoteDnsCanonicalNames with * '["' DNSName '"]' *
| project TimeGenerated, Computer, Direction, ProcessName, SourceIp, DestinationIp, DestinationPort, RemoteDnsQuestions, DNSName,BytesSent, BytesReceived, RemoteCountry, Type
| extend IPMatch = case( SourceIp in (IPList), "SourceIP", DestinationIp in (IPList), "DestinationIP", "None") 
| extend IPAddress = case(IPMatch == "SourceIP", SourceIp, IPMatch == "DestinationIP", DestinationIp, "NoMatch"), File = ProcessName
),
(Event
| where Source == "Microsoft-Windows-Sysmon"
| where EventID == 3
| extend EvData = parse_xml(EventData)
| extend EventDetail = EvData.DataItem.EventData.Data
| extend SourceIP = tostring(EventDetail.[9].["#text"]), DestinationIP = tostring(EventDetail.[14].["#text"]), Image = tostring(EventDetail.[4].["#text"])
| where SourceIP in (IPList) or DestinationIP in (IPList)
| project TimeGenerated, SourceIP, DestinationIP, Image, UserName, Computer, Type
| extend IPMatch = case( SourceIP in (IPList), "SourceIP", DestinationIP in (IPList), "DestinationIP", "None")
| extend AccountNT = UserName, File = tostring(split(Image, '\\', -1)[-1]), IPAddress = case(IPMatch == "SourceIP", SourceIP, IPMatch == "DestinationIP", DestinationIP, "None")
), 
(OfficeActivity
| where ClientIP in (IPList) 
| project TimeGenerated, UserAgent, Operation, RecordType, UserId, ClientIP, Type
| extend IPAddress = ClientIP, AccountUPN = UserId, AccountUPNName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
),
(DeviceNetworkEvents
| where RemoteUrl has_any (domains) or RemoteIP in (IPList) or InitiatingProcessSHA256 in (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, Computer = DeviceName, InitiatingProcessSHA256, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, InitiatingProcessFolderPath, InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, RemoteIP, RemoteUrl, RemotePort, LocalIP, Type
| extend IPAddress = RemoteIP, FileHash = InitiatingProcessSHA256
| extend AccountUPN = InitiatingProcessAccountName, AccountUPNName = tostring(split(InitiatingProcessAccountName, "@")[0]), AccountUPNSuffix = tostring(split(InitiatingProcessAccountName, "@")[1])
),
(WindowsFirewall
| where SourceIP in (IPList) or DestinationIP in (IPList) 
| project TimeGenerated, Computer, CommunicationDirection, SourceIP, DestinationIP, SourcePort, DestinationPort, Type
| extend IPMatch = case( SourceIP in (IPList), "SourceIP", DestinationIP in (IPList), "DestinationIP", "None")
| extend IPAddress = case(IPMatch == "SourceIP", SourceIP, IPMatch == "DestinationIP", DestinationIP, "None")
),
(AzureDiagnostics 
| where ResourceType == "AZUREFIREWALLS"
| where Category == "AzureFirewallApplicationRule"
| parse msg_s with Protocol 'request from ' SourceHost ':' SourcePort 'to ' DestinationHost ':' DestinationPort '. Action:' Action
| where isnotempty(DestinationHost)
| where DestinationHost has_any (IPList) or DestinationHost has_any (domains) 
| extend DNSName = DestinationHost, IPAddress = SourceHost
),
(AzureDiagnostics
| where ResourceType == "AZUREFIREWALLS"
| where Category == "AzureFirewallNetworkRule"
| where msg_s has_any (IPList)
| parse msg_s with Protocol " request from " SourceIP ":" SourcePortInt:int " to " TargetIP ":" TargetPortInt:int *
| parse kind=regex flags=U msg_s with * ". Action\\: " Action1a "\\."
| parse msg_s with * ". Policy: " Policy ". Rule Collection Group: " RuleCollectionGroup "." *
| parse msg_s with * " Rule Collection: "  RuleCollection ". Rule: " Rule 
| extend IPAddress = SourceIP
),
(AzureDiagnostics
| where ResourceType == "AZUREFIREWALLS"
| where Category == "AzureFirewallDnsProxy"
| where msg_s has_any (domains)
| parse msg_s with "DNS Request: " SourceIP ":" SourcePortInt:int " - " QueryID:int " " RequestType " " RequestClass " " hostname ". " protocol " " details
| extend
    ResponseDuration = extract("[0-9]*.?[0-9]+s$", 0, msg_s),
    SourcePort = tostring(SourcePortInt),
    QueryID = tostring(QueryID)
| project TimeGenerated,SourceIP,hostname,RequestType,ResponseDuration,details,msg_s
| extend IPAddress = SourceIP
),
(AZFWApplicationRule
| where Fqdn has_any (domains) or Fqdn has_any (IPList)
| extend IPAddress = SourceIp
),
(AZFWDnsQuery
| where isnotempty(QueryName)
| where QueryName has_any (domains)
| extend DNSName = QueryName, IPAddress = SourceIp
),
(AZFWNetworkRule
| where DestinationIp has_any (IPList)
| extend IPAddress = SourceIp
),
(CommonSecurityLog
| where FileHash in (sha256Hashes)
| project TimeGenerated, Message, SourceUserID, FileHash, Type
| extend Algorithm = "SHA256", FileHash = tostring(FileHash), AccountUPN = SourceUserID, AccountUPNName = tostring(split(SourceUserID, "@")[0]), AccountUPNSuffix = tostring(split(SourceUserID, "@")[1])
),
(imFileEvent
| where TargetFileSHA256 has_any (sha256Hashes)
| extend AccountNT = ActorUsername, Computer = DvcHostname, IPAddress = SrcIpAddr, CommandLine = ActingProcessCommandLine, FileHash = TargetFileSHA256
| project Type, TimeGenerated, Computer, AccountNT, IPAddress, CommandLine, FileHash, Algorithm = "SHA256"
),
(DeviceFileEvents
| where SHA256 has_any (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, Computer = DeviceName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, InitiatingProcessFolderPath, InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, InitiatingProcessSHA256, Type
| extend Algorithm = "SHA256", FileHash = tostring(InitiatingProcessSHA256), CommandLine = InitiatingProcessCommandLine,Image = InitiatingProcessFolderPath
| extend AccountUPN = InitiatingProcessAccountName, AccountUPNName = tostring(split(InitiatingProcessAccountName, "@")[0]), AccountUPNSuffix = tostring(split(InitiatingProcessAccountName, "@")[1])
),
(DeviceImageLoadEvents
| where SHA256 has_any (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, Computer = DeviceName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, InitiatingProcessFolderPath, InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, InitiatingProcessSHA256, Type
| extend Algorithm = "SHA256", FileHash = tostring(InitiatingProcessSHA256), CommandLine = InitiatingProcessCommandLine,Image = InitiatingProcessFolderPath
| extend AccountUPN = InitiatingProcessAccountName, AccountUPNName = tostring(split(InitiatingProcessAccountName, "@")[0]), AccountUPNSuffix = tostring(split(InitiatingProcessAccountName, "@")[1])
),
(Event
| where Source =~ "Microsoft-Windows-Sysmon"
| where EventID == 1
| extend EvData = parse_xml(EventData)
| extend EventDetail = EvData.DataItem.EventData.Data
| extend Image = EventDetail.[4].["#text"], CommandLine = EventDetail.[10].["#text"], Hashes = tostring(EventDetail.[17].["#text"])
| extend Hashes = extract_all(@"(?P<key>\w+)=(?P<value>[a-zA-Z0-9]+)", dynamic(["key","value"]), Hashes)
| extend Hashes = column_ifexists("Hashes", dynamic(["", ""])), CommandLine = column_ifexists("CommandLine", "")
| mv-expand Hashes
| where Hashes[0] =~ "SHA256" and Hashes[1] has_any (sha256Hashes) 
| project TimeGenerated, EventDetail, AccountNT = UserName, Computer, Type, Source, Hashes, CommandLine, Image
| extend Type = strcat(Type, ": ", Source), FileHash = tostring(Hashes[1]), Algorithm = tostring(Hashes[0])
)
)
| extend AccountNTName = tostring(split(AccountNT, "\\")[1]), AccountNTDomain = tostring(split(AccountNT, "\\")[0])
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| project-away DomainIndex

let mde_threats = dynamic(["Behavior:Win32/SuspAzureRequest.A", "Behavior:Win32/SuspAzureRequest.B", "Behavior:Win32/SuspAzureRequest.C", "Behavior:Win32/LaunchingSuspCMD.B"]);
DeviceInfo
| extend DeviceName = tolower(DeviceName)
| join kind=inner ( SecurityAlert
| where ProviderName == "MDATP"
| extend ThreatName = tostring(parse_json(ExtendedProperties).ThreatName)
| extend ThreatFamilyName = tostring(parse_json(ExtendedProperties).ThreatFamilyName)
| where ThreatName in~ (mde_threats) or ThreatFamilyName in~ (mde_threats)
| extend CompromisedEntity = tolower(CompromisedEntity)
) on $left.DeviceName == $right.CompromisedEntity
| summarize by bin(TimeGenerated, 1d), DisplayName, ThreatName, ThreatFamilyName, PublicIP, AlertSeverity, Description, tostring(LoggedOnUsers), DeviceId, TenantId, CompromisedEntity, tostring(LoggedOnUsers), ProductName, Entities
| extend HostName = tostring(split(CompromisedEntity, ".")[0]), DomainIndex = toint(indexof(CompromisedEntity, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(CompromisedEntity, DomainIndex + 1), CompromisedEntity)
| project-away DomainIndex

// ADHealth Monitoring Agent Registry Key
let aadHealthMonAgentRegKey = "\\REGISTRY\\MACHINE\\SOFTWARE\\Microsoft\\Microsoft Online\\Reporting\\MonitoringAgent";
// Filter out known processes
let aadConnectHealthProcs = dynamic ([
    'Microsoft.Identity.Health.Adfs.DiagnosticsAgent.exe',
    'Microsoft.Identity.Health.Adfs.InsightsService.exe',
    'Microsoft.Identity.Health.Adfs.MonitoringAgent.Startup.exe',
    'Microsoft.Identity.Health.Adfs.PshSurrogate.exe',
    'Microsoft.Identity.Health.Common.Clients.ResourceMonitor.exe',
    'Microsoft.Identity.Health.AadSync.MonitoringAgent.Startup.exe',
    'Microsoft.Identity.AadConnect.Health.AadSync.Host.exe',
    'Microsoft.Azure.ActiveDirectory.Synchronization.Upgrader.exe',
    'miiserver.exe'
]);
(union isfuzzy=true
(
SecurityEvent
| where EventID == '4656'
| where EventData has aadHealthMonAgentRegKey
| extend EventData = parse_xml(EventData).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, Computer, EventID)
| extend ObjectName = column_ifexists("ObjectName", ""),
    ObjectType = column_ifexists("ObjectType", "")
| where ObjectType == 'Key'
| where ObjectName == aadHealthMonAgentRegKey
| extend SubjectUserName = column_ifexists("SubjectUserName", ""),
    SubjectDomainName = column_ifexists("SubjectDomainName", ""),
    ProcessName = column_ifexists("ProcessName", "")
| extend Process = split(ProcessName, '\\', -1)[-1],
    Account = strcat(SubjectDomainName, "\\", SubjectUserName)
| where Process !in (aadConnectHealthProcs)
| summarize StartTime = max(TimeGenerated), EndTime = min(TimeGenerated), count() by EventID, Account, Computer, Process, SubjectUserName, SubjectDomainName, ObjectName, ObjectType, ProcessName
),
(
WindowsEvent
| where EventID == '4656' and EventData has aadHealthMonAgentRegKey
| extend ObjectType = tostring(EventData.ObjectType)
| where ObjectType == 'Key'
| extend ObjectName = tostring(EventData.ObjectName)
| where ObjectName == aadHealthMonAgentRegKey
| extend ProcessName = tostring(EventData.ProcessName)
| extend Process = tostring(split(ProcessName, '\\')[-1])
| where Process !in (aadConnectHealthProcs)
| extend Account =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend SubjectUserName = tostring(EventData.SubjectUserName)
| extend SubjectDomainName = tostring(EventData.SubjectDomainName)
| summarize StartTime = max(TimeGenerated), EndTime = min(TimeGenerated), count() by EventID, Account, Computer, Process, SubjectUserName, SubjectDomainName, ObjectName, ObjectType, ProcessName
),
(
SecurityEvent
| where EventID == '4663'
| where ObjectType == 'Key'
| where ObjectName == aadHealthMonAgentRegKey
| extend Process = tostring(split(ProcessName, '\\', -1)[-1])
| where Process !in (aadConnectHealthProcs)
| summarize StartTime = max(TimeGenerated), EndTime = min(TimeGenerated), count() by EventID, Account, Computer, Process, SubjectUserName, SubjectDomainName, ObjectName, ObjectType, ProcessName
),
  (
WindowsEvent
| where EventID == '4663' and EventData has aadHealthMonAgentRegKey
| extend ObjectType = tostring(EventData.ObjectType)
| where ObjectType == 'Key'
| extend ObjectName = tostring(EventData.ObjectName)
| where ObjectName == aadHealthMonAgentRegKey
| extend ProcessName = tostring(EventData.ProcessName)
| extend Process = tostring(split(ProcessName, '\\')[-1])
| where Process !in (aadConnectHealthProcs)
| extend Account =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend SubjectUserName = tostring(EventData.SubjectUserName)
| extend SubjectDomainName = tostring(EventData.SubjectDomainName)
| summarize StartTime = max(TimeGenerated), EndTime = min(TimeGenerated), count() by EventID, Account, Computer, Process, SubjectUserName, SubjectDomainName, ObjectName, ObjectType, ProcessName
)
)
// You can filter out potential machine accounts
//| where AccountType != 'Machine'
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend Name = tostring(split(Account, "\\")[1]), NTDomain = tostring(split(Account, "\\")[0])
| project-away DomainIndex

// ADHealth Monitoring Agent Registry Key
let aadHealthMonAgentRegKey = "\\REGISTRY\\MACHINE\\SOFTWARE\\Microsoft\\MicrosoftOnline\\Reporting\\MonitoringAgent";
// Filter out known processes
let aadConnectHealthProcs = dynamic ([
    'Microsoft.Identity.Health.Adfs.DiagnosticsAgent.exe',
    'Microsoft.Identity.Health.Adfs.InsightsService.exe',
    'Microsoft.Identity.Health.Adfs.MonitoringAgent.Startup.exe',
    'Microsoft.Identity.Health.Adfs.PshSurrogate.exe',
    'Microsoft.Identity.Health.Common.Clients.ResourceMonitor.exe',
    'Microsoft.Identity.Health.AadSync.MonitoringAgent.Startup.exe',
    'Microsoft.Identity.AadConnect.Health.AadSync.Host.exe',
    'Microsoft.Azure.ActiveDirectory.Synchronization.Upgrader.exe',
    'miiserver.exe'
]);
(union isfuzzy=true
(
SecurityEvent
| where EventID == '4656'
| where EventData has aadHealthMonAgentRegKey
| extend EventData = parse_xml(EventData).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, Computer, EventID)
| extend ObjectName = column_ifexists("ObjectName", ""),
    ObjectType = column_ifexists("ObjectType", "")
| where ObjectType == 'Key'
| where ObjectName == aadHealthMonAgentRegKey
| extend SubjectUserName = column_ifexists("SubjectUserName", ""),
    SubjectDomainName = column_ifexists("SubjectDomainName", ""),
    ProcessName = column_ifexists("ProcessName", "")
| extend Process = split(ProcessName, '\\', -1)[-1],
    Account = strcat(SubjectDomainName, "\\", SubjectUserName)
| where Process !in (aadConnectHealthProcs)
| summarize StartTime = max(TimeGenerated), EndTime = min(TimeGenerated), count() by EventID, Account, Computer, Process, SubjectUserName, SubjectDomainName, ObjectName, ObjectType, ProcessName
),
  ( WindowsEvent
| where EventID == '4656' and EventData has aadHealthMonAgentRegKey
| extend ObjectType = tostring(EventData.ObjectType)
| where ObjectType == 'Key'
| extend ObjectName = tostring(EventData.ObjectName)
| where ObjectName == aadHealthMonAgentRegKey
| extend ProcessName = tostring(EventData.ProcessName)
| extend Process = tostring(split(ProcessName, '\\')[-1])
| where Process !in (aadConnectHealthProcs)
| extend Account =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend SubjectUserName = tostring(EventData.SubjectUserName)
| extend SubjectDomainName = tostring(EventData.SubjectDomainName)
| summarize StartTime = max(TimeGenerated), EndTime = min(TimeGenerated), count() by EventID, Account, Computer, Process, SubjectUserName, SubjectDomainName, ObjectName, ObjectType, ProcessName
),
(
SecurityEvent
| where EventID == '4663'
| where ObjectType == 'Key'
| where ObjectName == aadHealthMonAgentRegKey
| extend Process = tostring(split(ProcessName, '\\', -1)[-1])
| where Process !in (aadConnectHealthProcs)
| summarize StartTime = max(TimeGenerated), EndTime = min(TimeGenerated), count() by EventID, Account, Computer, Process, SubjectUserName, SubjectDomainName, ObjectName, ObjectType, ProcessName
),
( WindowsEvent
| where EventID == '4663' and EventData has aadHealthMonAgentRegKey
| extend ObjectType = tostring(EventData.ObjectType)
| where ObjectType == 'Key'
| extend ObjectName = tostring(EventData.ObjectName)
| where ObjectName == aadHealthMonAgentRegKey
| extend ProcessName = tostring(EventData.ProcessName)
| extend Process = tostring(split(ProcessName, '\\')[-1])
| where Process !in (aadConnectHealthProcs)
| extend Account =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend SubjectUserName = tostring(EventData.SubjectUserName)
| extend SubjectDomainName = tostring(EventData.SubjectDomainName)
| summarize StartTime = max(TimeGenerated), EndTime = min(TimeGenerated), count() by EventID, Account, Computer, Process, SubjectUserName, SubjectDomainName, ObjectName, ObjectType, ProcessName
)
)
// You can filter out potential machine accounts
//| where AccountType != 'Machine'
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend Name = tostring(split(Account, "\\")[1]), NTDomain = tostring(split(Account, "\\")[0])

let cmdTokens0 = dynamic(['vbscript','jscript']);
let cmdTokens1 = dynamic(['mshtml','RunHTMLApplication']);
let cmdTokens2 = dynamic(['Execute','CreateObject','RegRead','window.close']);
(union isfuzzy=true 
(SecurityEvent
| where TimeGenerated >= ago(14d)
| where EventID == 4688
| where CommandLine has @'\Microsoft\Windows\CurrentVersion'
| where not(CommandLine has_any (@'\Software\Microsoft\Windows\CurrentVersion\Run', @'\Software\Microsoft\Windows\CurrentVersion\RunOnce'))
// If you are receiving false positives, then it may help to make the query more strict by uncommenting one or both of the lines below to refine the matches
//| where CommandLine has_any (cmdTokens0)
//| where CommandLine has_all (cmdTokens1)
| where CommandLine has_all (cmdTokens2)
| project TimeGenerated, Computer, Account, Process, NewProcessName, CommandLine, ParentProcessName, _ResourceId
),
(WindowsEvent
| where TimeGenerated >= ago(14d)
| where EventID == 4688 and EventData has_all(cmdTokens2) and  EventData has @'\Microsoft\Windows\CurrentVersion'
| where not(EventData has_any (@'\Software\Microsoft\Windows\CurrentVersion\Run', @'\Software\Microsoft\Windows\CurrentVersion\RunOnce'))
| extend CommandLine = tostring(EventData.CommandLine)
| where CommandLine has @'\Microsoft\Windows\CurrentVersion'
| where not(CommandLine has_any (@'\Software\Microsoft\Windows\CurrentVersion\Run', @'\Software\Microsoft\Windows\CurrentVersion\RunOnce'))
// If you are receiving false positives, then it may help to make the query more strict by uncommenting one or both of the lines below to refine the matches
//| where CommandLine has_any (cmdTokens0)
//| where CommandLine has_all (cmdTokens1)
| where CommandLine has_all (cmdTokens2)
| extend Account =  strcat(EventData.SubjectDomainName,"\\", EventData.SubjectUserName)
| extend NewProcessName = tostring(EventData.NewProcessName)
| extend Process=tostring(split(NewProcessName, '\\')[-1])
| extend ParentProcessName = tostring(EventData.ParentProcessName)  
| project TimeGenerated, Computer, Account, Process, NewProcessName, CommandLine, ParentProcessName, _ResourceId)
| extend Name = tostring(split(Account, "\\")[1]), NTDomain = tostring(split(Account, "\\")[0])
| extend DnsDomain = tostring(strcat_array(array_slice(split(Computer, '.'), 1, -1), '.')), HostName = tostring(split(Computer, '.', 0)[0]))

(union isfuzzy=true 
(SecurityEvent
| where EventID == 4688
| where Process =~ 'rundll32.exe' 
| where CommandLine has_all ('Execute','RegRead','window.close')
| project TimeGenerated, Computer, SubjectAccount = Account, SubjectUserName, SubjectDomainName, SubjectUserSid, Process, ProcessId, NewProcessName, CommandLine, ParentProcessName, _ResourceId
),
(WindowsEvent
| where EventID == 4688 and EventData has 'rundll32.exe' and EventData has_any ('Execute','RegRead','window.close')
| extend NewProcessName = tostring(EventData.NewProcessName)
| extend Process=tostring(split(NewProcessName, '\\')[-1])
| where Process =~ 'rundll32.exe' 
| extend CommandLine = tostring(EventData.CommandLine)
| where CommandLine has_all ('Execute','RegRead','window.close')
| extend SubjectAccount =  strcat(EventData.SubjectDomainName,"\\", EventData.SubjectUserName)
| extend ParentProcessName = tostring(EventData.ParentProcessName)  
| project TimeGenerated, Computer, SubjectAccount, SubjectUserName = tostring(EventData.SubjectUserName), SubjectDomainName = tostring(EventData.SubjectDomainName), SubjectUserSid = tostring(EventData.SubjectUserSid), Process, NewProcessName, CommandLine, ParentProcessName, _ResourceId
)
)
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| project-away DomainIndex

imProcessCreate
| where Process hassuffix 'rundll32.exe'
| where CommandLine  has_any ('Execute','RegRead','window.close')
| project TimeGenerated, Dvc, User, Process, CommandLine, ActingProcessName, EventVendor, EventProduct
| extend AccountName = tostring(split(User, @'\')[1]), AccountNTDomain = tostring(split(User, @'\')[0])
| extend HostName = tostring(split(Dvc, ".")[0]), DomainIndex = toint(indexof(Dvc, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Dvc, DomainIndex + 1), Dvc)
| project-away DomainIndex

let query_frequency = 15m;
let missing_period = 1h;
//Enter a reference list of hostnames for your DC servers
let DCServersList = dynamic (["DC01.simulandlabs.com","DC02.simulandlabs.com"]);
//Alternatively, a Watchlist can be used
//let DCServersList = _GetWatchlist('HostName-DomainControllers') | project HostName;
Heartbeat
| summarize arg_max(TimeGenerated, *) by Computer
| where Computer in (DCServersList)
//You may specify the OS type of your Domain Controllers
//| where OSType == 'Windows'
| where TimeGenerated between (ago(query_frequency + missing_period) .. ago(missing_period))
| project TimeGenerated, Computer, OSType, Version, ComputerEnvironment, Type, Solutions
| sort by TimeGenerated asc