Deployable detection rules

ZoomLogs
| where Event =~ "account.settings_updated"
| extend EnforceLogin = columnifexists("payload_object_settings_schedule_meeting_enfore_login_b", "")
| extend EnforceLoginDomain = columnifexists("payload_object_settings_schedule_meeting_enfore_login_b", "")
| extend GuestAlerts = columnifexists("payload_object_settings_in_meeting_alert_guest_join_b", "")
| where EnforceLogin == 'false' or EnforceLoginDomain == 'false' or GuestAlerts == 'false'
| extend SettingChanged = case(EnforceLogin == 'false' and EnforceLoginDomain == 'false' and GuestAlerts == 'false', "All settings changed",
                            EnforceLogin == 'false' and EnforceLoginDomain == 'false', "Enforced Logons and Restricted Domains Changed",
                            EnforceLoginDomain == 'false' and GuestAlerts == 'false', "Enforced Domains Changed",
                            EnforceLoginDomain == 'false', "Enfored Domains Changed",
                            GuestAlerts == 'false', "Guest Join Alerts Changed",
                            EnforceLogin == 'false', "Enforced Logins Changed",
                            "No Changes")
| extend AccountName = tostring(split(User, "@")[0]), AccountUPNSuffix = tostring(split(User, "@")[1])

//Adjust this threshold to fit environment
let  signin_threshold = 5;
//Make a list of IPs with failed AWS console logins
let aws_fails = AWSCloudTrail
| where EventName == "ConsoleLogin"
| extend LoginResult = tostring(parse_json(ResponseElements).ConsoleLogin)
| where LoginResult != "Success"
| where SourceIpAddress != "127.0.0.1"
| summarize count() by SourceIpAddress
| where count_ >  signin_threshold
| summarize make_set(SourceIpAddress);
//See if any of those IPs have sucessfully logged into Azure AD.
SigninLogs
| where ResultType in ("0", "50125", "50140")
| where IPAddress in (aws_fails)
| extend Reason = "Multiple failed AWS Console logins from IP address"
| extend timestamp = TimeGenerated, AccountName = tostring(split(UserPrincipalName, "@")[0]), AccountUPNSuffix = tostring(split(UserPrincipalName, "@")[1])

//Adjust this threshold to fit your environment
let signin_threshold = 5;
//Make a list of IPs with AAD signin failures above our threshold
let aadFunc = (tableName:string){
let Suspicious_signins =
table(tableName)
| where ResultType !in ("0", "50125", "50140")
| where IPAddress !in ("127.0.0.1", "::1")
| summarize count() by IPAddress
| where count_ >  signin_threshold
| summarize make_set(IPAddress);
Suspicious_signins
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
let Suspicious_signins =
union isfuzzy=true aadSignin, aadNonInt
| summarize make_set(set_IPAddress);
//See if any of those IPs have sucessfully logged into the AWS console
AWSCloudTrail
| where EventName =~ "ConsoleLogin"
| extend LoginResult = tostring(parse_json(ResponseElements).ConsoleLogin)
| where LoginResult =~ "Success"
| where SourceIpAddress in (Suspicious_signins)
| extend Reason = "Multiple failed AAD logins from IP address"
| extend MFAUsed = tostring(parse_json(AdditionalEventData).MFAUsed)
| extend UserIdentityArn = iif(isempty(UserIdentityArn), tostring(parse_json(Resources)[0].ARN), UserIdentityArn)
| extend UserName = tostring(split(UserIdentityArn, '/')[-1])
| extend AccountName = case( UserIdentityPrincipalid == "Anonymous", "Anonymous", isempty(UserIdentityUserName), UserName, UserIdentityUserName)
| extend AccountName = iif(AccountName contains "@", tostring(split(AccountName, '@', 0)[0]), AccountName),
  AccountUPNSuffix = iif(AccountName contains "@", tostring(split(AccountName, '@', 1)[0]), "")
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by Reason, LoginResult, EventTypeName, UserIdentityType, RecipientAccountId, AccountName, AccountUPNSuffix, AWSRegion, SourceIpAddress, UserAgent, MFAUsed
| extend timestamp = StartTime

//Adjust this threshold to fit the environment
let signin_threshold = 5;
//Make a list of all IPs with failed signins to AAD above our threshold
let aadFunc = (tableName:string){
let suspicious_signins =
table(tableName)
| where ResultType !in ("0", "50125", "50140")
| where IPAddress !in ('127.0.0.1', '::1', '')
| summarize count() by IPAddress
| where count_ > signin_threshold
| summarize make_set(IPAddress);
//See if any of these IPs have sucessfully logged into *nix hosts
let linux_logons =
Syslog
| where Facility contains "auth" and ProcessName != "sudo"
| where SyslogMessage has "Accepted"
| extend SourceIP = extract("(([0-9]{1,3})\\.([0-9]{1,3})\\.([0-9]{1,3})\\.(([0-9]{1,3})))",1,SyslogMessage)
| where SourceIP in (suspicious_signins)
| extend Reason = "Multiple failed AAD logins from IP address"
| project TimeGenerated, Computer, HostIP, IpAddress = SourceIP, SyslogMessage, Facility, ProcessName, Reason;
//See if any of these IPs have sucessfully logged into Windows hosts
let win_logons = (union isfuzzy=true
(SecurityEvent
| where EventID == 4624
| where LogonType in (10, 7, 3)
| where IpAddress != "-"
| where IpAddress in (suspicious_signins)
| extend Reason = "Multiple failed AAD logins from IP address"
| project TimeGenerated, Account, AccountType, Computer, Activity, EventID, LogonProcessName, IpAddress, LogonTypeName, TargetUserSid, TargetUserName, TargetDomainName, _ResourceId, Reason
),
(WindowsEvent
| where EventID == 4624 and has_any_ipv4(EventData, toscalar(suspicious_signins))
| extend LogonType = tostring(EventData.LogonType)
| where LogonType in (10, 7, 3)
| extend  IpAddress = tostring(EventData.IpAddress)
| where IpAddress != "-"
| where IpAddress in (suspicious_signins)
| extend Reason = "Multiple failed AAD logins from IP address"
| extend Activity = "4624 - An account was successfully logged on."
| extend TargetUserName = tostring(EventData.TargetUserName), TargetDomainName = tostring(EventData.TargetDomainName)
| extend Account =  strcat(TargetDomainName,"\\", TargetUserName)
| extend TargetUserSid = tostring(EventData.TargetUserSid)
| extend TargetAccount = strcat(EventData.TargetDomainName,"\\", EventData.TargetUserName)
| extend AccountType =case(Account endswith "$" or TargetUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(TargetUserSid), "", "User")
| extend LogonProcessName = tostring(EventData.LogonProcessName)
| project TimeGenerated, Account, AccountType, Computer, Activity, EventID, LogonProcessName, IpAddress, TargetUserSid, TargetUserName, TargetDomainName, _ResourceId, Reason
)
);
union isfuzzy=true linux_logons,win_logons
| extend timestamp = TimeGenerated
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex+1), Computer)
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
union isfuzzy=true aadSignin, aadNonInt

//Adjust this threshold to fit environment
let signin_threshold = 5; 
//Make a list of IPs with failed Windows host logins above threshold
let win_fails = 
SecurityEvent
| where EventID == 4625
| where LogonType in (10, 7, 3)
| where IpAddress != "-"
| summarize count() by IpAddress
| where count_ > signin_threshold
| summarize make_list(IpAddress);
let wef_fails =
WindowsEvent
| where EventID == 4625
| extend LogonType = tostring(EventData.LogonType)
| where LogonType in (10, 7, 3)
| extend IpAddress = tostring(EventData.IpAddress)
| where IpAddress != "-"
| summarize count() by IpAddress
| where count_ > signin_threshold
| summarize make_list(IpAddress);
//Make a list of IPs with failed *nix host logins above threshold
let nix_fails = 
Syslog
| where Facility contains 'auth' and ProcessName != 'sudo' and SyslogMessage has 'from' and not(SyslogMessage has_any ('Disconnecting', 'Disconnected', 'Accepted', 'disconnect', @'[preauth]'))
| extend SourceIP = extract("(([0-9]{1,3})\\.([0-9]{1,3})\\.([0-9]{1,3})\\.(([0-9]{1,3})))",1,SyslogMessage)
| where SourceIP != "" and SourceIP != "127.0.0.1"
| summarize count() by SourceIP
| where count_ > signin_threshold
| summarize make_list(SourceIP);
//See if any of the IPs with failed host logins hve had a sucessful Azure AD login
let aadFunc = (tableName:string){
table(tableName)
| where ResultType in ("0", "50125", "50140")
| where IPAddress in (win_fails) or IPAddress in (nix_fails) or IPAddress in (wef_fails)
| extend Reason=  "Multiple failed host logins from IP address with successful Azure AD login"
| extend timestamp = TimeGenerated, Type = Type
| extend AccountName = tostring(split(UserPrincipalName, "@")[0]), AccountUPNSuffix = tostring(split(UserPrincipalName, "@")[1])
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
union isfuzzy=true aadSignin, aadNonInt

let threshold = 20;
let ReasontoSubStatus = datatable(SubStatus: string, Reason: string) [
    "0xc000005e", "There are currently no logon servers available to service the logon request.",
    "0xc0000064", "User logon with misspelled or bad user account",
    "0xc000006a", "User logon with misspelled or bad password",
    "0xc000006d", "Bad user name or password",
    "0xc000006e", "Unknown user name or bad password",
    "0xc000006f", "User logon outside authorized hours",
    "0xc0000070", "User logon from unauthorized workstation",
    "0xc0000071", "User logon with expired password",
    "0xc0000072", "User logon to account disabled by administrator",
    "0xc00000dc", "Indicates the Sam Server was in the wrong state to perform the desired operation",
    "0xc0000133", "Clocks between DC and other computer too far out of sync",
    "0xc000015b", "The user has not been granted the requested logon type (aka logon right) at this machine",
    "0xc000018c", "The logon request failed because the trust relationship between the primary domain and the trusted domain failed",
    "0xc0000192", "An attempt was made to logon, but the Netlogon service was not started",
    "0xc0000193", "User logon with expired account",
    "0xc0000224", "User is required to change password at next logon",
    "0xc0000225", "Evidently a bug in Windows and not a risk",
    "0xc0000234", "User logon with account locked",
    "0xc00002ee", "Failure Reason: An Error occurred during Logon",
    "0xc0000413", "Logon Failure: The machine you are logging onto is protected by an authentication firewall. The specified account is not allowed to authenticate to the machine"
];
(union isfuzzy=true
    (SecurityEvent
    | where EventID == 4625
    | where AccountType =~ "User"
    | where SubStatus !~ '0xc0000064' and Account !in ('\\', '-\\-')
    // SubStatus '0xc0000064' signifies 'Account name does not exist'
    | extend
        ResourceId = column_ifexists("_ResourceId", _ResourceId),
        SourceComputerId = column_ifexists("SourceComputerId", SourceComputerId),
        SubStatus = tolower(SubStatus)
    | lookup ReasontoSubStatus on SubStatus
    | extend coalesce(Reason, strcat('Unknown reason substatus: ', SubStatus))
    | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), FailedLogonCount = count() by bin(TimeGenerated,10m), EventID,
        Activity, Computer, Account, TargetAccount, TargetUserName, TargetDomainName,
        LogonType, LogonTypeName, LogonProcessName, Status, SubStatus, Reason, ResourceId, SourceComputerId, WorkstationName, IpAddress
    | where FailedLogonCount >= threshold
    ),
    (
    (WindowsEvent
    | where EventID == 4625 and not(EventData has '0xc0000064')
    | extend TargetAccount = strcat(tostring(EventData.TargetDomainName), "\\", tostring(EventData.TargetUserName))
    | extend TargetUserSid = tostring(EventData.TargetUserSid)
    | extend AccountType=case(EventData.TargetUserName endswith "$" or TargetUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(TargetUserSid), "", "User")
    | where AccountType =~ "User"
    | extend SubStatus = tostring(EventData.SubStatus)
    | where SubStatus !~ '0xc0000064' and TargetAccount !in ('\\', '-\\-')
    // SubStatus '0xc0000064' signifies 'Account name does not exist'
    | extend
        ResourceId = column_ifexists("_ResourceId", _ResourceId),
        SourceComputerId = column_ifexists("SourceComputerId", ""),
        SubStatus = tolower(SubStatus)
    | lookup ReasontoSubStatus on SubStatus
    | extend coalesce(Reason, strcat('Unknown reason substatus: ', SubStatus))
    | extend Activity="4625 - An account failed to log on."
    | extend TargetUserName = tostring(EventData.TargetUserName)
    | extend TargetDomainName = tostring(EventData.TargetDomainName)
    | extend LogonType = tostring(EventData.LogonType)
    | extend Status= tostring(EventData.Status)
    | extend LogonProcessName = tostring(EventData.LogonProcessName)
    | extend WorkstationName = tostring(EventData.WorkstationName)
    | extend IpAddress = tostring(EventData.IpAddress)
    | extend LogonTypeName=case(
      LogonType == 2, "2 - Interactive",
      LogonType == 3, "3 - Network",
      LogonType == 4, "4 - Batch",
      LogonType == 5, "5 - Service",
      LogonType == 7, "7 - Unlock",
      LogonType == 8, "8 - NetworkCleartext",
      LogonType == 9, "9 - NewCredentials",
      LogonType == 10, "10 - RemoteInteractive",
      LogonType == 11, "11 - CachedInteractive",
      tostring(LogonType)
    )
    | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), FailedLogonCount = count() by bin(TimeGenerated,10m), EventID,
        Activity, Computer, TargetAccount, TargetUserName, TargetDomainName,
        LogonType, LogonTypeName, LogonProcessName, Status, SubStatus, Reason, ResourceId, SourceComputerId, WorkstationName, IpAddress
    | where FailedLogonCount >= threshold
    )))
| summarize arg_max(TimeGenerated, *) by Computer, TargetAccount, TargetUserName, TargetDomainName
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)

SecurityEvent
| where EventID == 4720 and TargetUserName endswith "$"
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by Computer, SubjectUserName, SubjectDomainName, SubjectAccount, SubjectUserSid, SubjectLogonId, 
TargetUserName, TargetDomainName, TargetAccount, TargetSid, UserPrincipalName
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| project-away DomainIndex

let starttime = 1d;
let TimeDeltaThresholdInSeconds = 60; // we ignore beacons diffs that fall below this threshold
let TotalBeaconsThreshold = 4; // minimum number of beacons required in a session to surface a row
let JitterTolerance = 0.2; // tolerance to jitter, e.g. - 0.2 = 20% jitter is tolerated either side of the periodicity
CommonSecurityLog
| where DeviceVendor == "Fortinet"
| where TimeGenerated > ago(starttime)
// eliminate bad data
| where isnotempty(SourceIP) and isnotempty(DestinationIP) and SourceIP != "0.0.0.0"
// filter out deny, close, rst and SNMP to reduce data volume
| where DeviceAction !in ("close", "client-rst", "server-rst", "deny") and DestinationPort != 161
// map input fields
| project TimeGenerated , SourceIP, DestinationIP, DestinationPort, ReceivedBytes, SentBytes, DeviceAction
// where destination IPs are public
| where ipv4_is_private(DestinationIP) == false
// sort into source->destination 'sessions'
| sort by SourceIP asc, DestinationIP asc, DestinationPort asc, TimeGenerated asc
| serialize
// time diff the contact times between source and destination to get a list of deltas
| extend nextTimeGenerated = next(TimeGenerated, 1), nextSourceIP = next(SourceIP, 1), nextDestIP = next(DestinationIP, 1), nextDestPort = next(DestinationPort, 1)
| extend TimeDeltainSeconds = datetime_diff("second",nextTimeGenerated,TimeGenerated)
| where SourceIP == nextSourceIP and DestinationIP == nextDestIP and DestinationPort == nextDestPort
// remove small time deltas below the set threshold
| where TimeDeltainSeconds > TimeDeltaThresholdInSeconds
// summarize the deltas by source->destination
| summarize count(), StartTime=min(TimeGenerated), EndTime=max(TimeGenerated), sum(ReceivedBytes), sum(SentBytes), makelist(TimeDeltainSeconds), makeset(DeviceAction) by SourceIP, DestinationIP, DestinationPort
// get some statistical properties of the delta distribution and smooth any outliers (e.g. laptop shut overnight, working hours)
| extend series_stats(list_TimeDeltainSeconds), outliers=series_outliers(list_TimeDeltainSeconds)
// expand the deltas and the outliers
| mvexpand list_TimeDeltainSeconds to typeof(double), outliers to typeof(double)
// replace outliers with the average of the distribution
| extend list_TimeDeltainSeconds_normalized=iff(outliers > 1.5 or outliers < -1.5, series_stats_list_TimeDeltainSeconds_avg , list_TimeDeltainSeconds)
// summarize with the smoothed distribution
| summarize BeaconCount=count(), makelist(list_TimeDeltainSeconds), list_TimeDeltainSeconds_normalized=makelist(list_TimeDeltainSeconds_normalized), makeset(set_DeviceAction) by StartTime, EndTime, SourceIP, DestinationIP, DestinationPort, sum_ReceivedBytes, sum_SentBytes
// get stats on the smoothed distribution
| extend series_stats(list_TimeDeltainSeconds_normalized)
// match jitter tolerance on smoothed distrib
| extend MaxJitter = (series_stats_list_TimeDeltainSeconds_normalized_avg*JitterTolerance)
| where series_stats_list_TimeDeltainSeconds_normalized_stdev < MaxJitter
// where the minimum beacon threshold is satisfied and there was some data transfer
| where BeaconCount > TotalBeaconsThreshold and (sum_SentBytes > 0 or sum_ReceivedBytes > 0)
// final projection
| project StartTime, EndTime, SourceIP, DestinationIP, DestinationPort, BeaconCount, TimeDeltasInSeconds=list_list_TimeDeltainSeconds, Periodicity=series_stats_list_TimeDeltainSeconds_normalized_avg, ReceivedBytes=sum_ReceivedBytes, SentBytes=sum_SentBytes, Actions=set_set_DeviceAction
// where periodicity is order of magnitude larger than time delta threshold (eliminates FPs whose periodicity is close to the values we ignored)
| where Periodicity >= (10*TimeDeltaThresholdInSeconds)

// Save as a function in your workspace then invoke via its name, i.e if you save as ADGroupChanges
// ADGroupChanges | where TimeGenerated > ago(1d) | where Action == "Add" and GroupName == "TestGroup1" // will all group additions to "TestGroup1"
// ADGroupChanges | where TimeGenerated > ago(1d) | where Action == "Remove" and Actor == "User1" // will find all group removals by "User1"
// This will parse the SecurityEvent log for any group additions or removals.
SecurityEvent
| project
    TimeGenerated,
    EventID,
    AccountType,
    MemberName,
    SubjectUserName,
    TargetUserName,
    Activity,
    MemberSid
| where EventID in (4728, 4729, 4732, 4733, 4756, 4757)
| parse MemberName with * 'CN=' Subject ',OU=' *
| extend Action = case(EventID in ("4728", "4756", "4732"), strcat("Add"),
    EventID in ("4729", "4757", "4733"), strcat("Remove"), "unknown")
| project
    TimeGenerated,
    Action,
    AccountType,
    Actor=SubjectUserName,
    Subject,
    GroupName=TargetUserName,
    Activity

// Save as a function in your workspace then invoke via its name, i.e if you save as KeyVaultAccess
// KeyVaultAccess | where TimeGenerated > ago(30d) | where Actor == "[email protected]" // will find actions taken by that user
// KeyVaultAccess | where TimeGenerated > ago(30d) | where * contains "Delete" // will find when delete access is added or removed
// This will parse the AzureActivity log for Azure Key Vault access changes.
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,
    RemovedKeyPolicy = removedAccessPolicy_Permissions_keys_s,
    RemovedSecretPolicy = removedAccessPolicy_Permissions_secrets_s,
    RemovedCertPolicy = removedAccessPolicy_Permissions_certificates_s,
    ServicePrincipalRemoved=removedAccessPolicy_ObjectId_g
| project
    TimeGenerated,
    KeyVaultName=Resource,
    ServicePrincipalAdded,
    ServicePrincipalRemoved,
    Actor,
    IPAddressofActor=CallerIPAddress,
    AddedSecretPolicy,
    AddedKeyPolicy,
    AddedCertPolicy,
    RemovedSecretPolicy,
    RemovedKeyPolicy,
    RemovedCertPolicy
| where isnotempty(AddedKeyPolicy)
    or isnotempty(AddedSecretPolicy)
    or isnotempty(AddedCertPolicy)
    or isnotempty(RemovedKeyPolicy)
    or isnotempty(RemovedSecretPolicy)
    or isnotempty(RemovedCertPolicy)

// Save as a function in your workspace then invoke via its name, i.e if you save as ASALogs
// ASALogs | where TimeGenerated > ago(30d) | where Action == "A real IP packet was denied by the ACL"
// ASALogs | where TimeGenerated > ago(30d) | where DstIP == "1.1.1.1"
// This will parse the Syslog messages from a Cisco ASA appliance to separate columns
let asa106017=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Deny IP due to Land Attack"
| where Event == "ASA-2-106017"
| extend Action = Description
| parse SyslogMessage with * 'ASA-2-106017:' Message 'from' x 'to' y
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| project TimeGenerated, Computer, Description, Event, Action, Message, SrcIP, DstIP;
let asa210005=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "LU allocate connection failed"
| extend Action = Description
| where Event == "ASA-3-210005"
| parse SyslogMessage with * 'ASA-3-210005:' Message 'from' SrcVlan ':' x 'to' DstVLan ':' y
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| project TimeGenerated, Computer, Description, Event, Action, Message, SrcVlan, SrcIP,SrcPort, DstVLan, DstIP, DstPort;
let asa106023=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "A real IP packet was denied by the ACL"
| extend Action = Description
| where Event == "ASA-4-106023"
| parse SyslogMessage with * 'ASA-4-106023: Deny ' z ' src ' SrcVlan ':' x 'dst' DstVLan ':' y ' ' *
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| extend Protocol = toupper(z)
| project TimeGenerated, Computer, Description, Event, Action, Protocol, SrcVlan, SrcIP, SrcPort, DstVLan, DstIP, DstPort;
let asa313005=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "No matching connection for ICMP error message"
| extend Action = Description
| where Event == "ASA-4-313005"
| parse SyslogMessage with * 'ASA-4-313005:' Message 'src' SrcVlan ':' x 'dst' DstVLan ':' y ' ' *
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| parse Action with * 'connection for ' Protocol ' error' *
| project TimeGenerated, Computer, Description, Event, Protocol, Action, Message, SrcVlan, SrcIP, DstVLan, DstIP;
let asa410001=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Dropped UDP DNS"
| extend Action = Description
| where Event == "ASA-4-410001"
| parse SyslogMessage with * 'ASA-4-410001:' Message 'from' SrcVlan ':' x 'to' DstVLan ':' y ';' *
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| parse Action with * 'Dropped ' Protocol ' DNS' *
| project TimeGenerated, Computer, Description, Protocol, Event, Action, Message, SrcVlan, SrcIP, SrcPort, DstVLan, DstIP, DstPort;
let asa419002=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Duplicate TCP SYN"
| extend Action = Description
| where Event == "ASA-4-419002"
| parse SyslogMessage with * 'ASA-4-419002:' Message 'from' SrcVlan ':' x 'to' DstVLan ':' y ' ' *
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| parse Action with * 'Duplicate ' Protocol ' SYN' *
| project TimeGenerated, Computer, Description, Protocol, Event, Action, Message, SrcVlan, SrcIP,SrcPort, DstVLan, DstIP, DstPort;
let asa500004=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Invalid transport field for protocol"
| extend Action = Description
| where Event == "ASA-4-500004"
| parse SyslogMessage with * 'ASA-4-500004:' Message ', from' x 'to' y
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| project TimeGenerated, Computer, Description, Event, Action, Message, SrcIP, SrcPort, DstIP, DstPort;
let asa733100=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Drop rate exceeded"
| where Event == "ASA-4-733100"
| extend Action = Description
| parse SyslogMessage with * 'ASA-4-733100:' Message
| project TimeGenerated, Computer, Description, Event, Action, Message;
let asa111008=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "User command executed"
| extend Action = Description
| where Event == "ASA-5-111008"
| parse SyslogMessage with * 'ASA-5-111008:' Message
| project TimeGenerated, Computer, Description, Event, Action, Message;
let asa111010=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "User made configuration change"
| extend Action = Description
| where Event == "ASA-5-111010"
| parse SyslogMessage with * 'ASA-5-111010:' Message
| project TimeGenerated, Computer, Description, Event, Action, Message;
let asa611103=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "User logged out"
| extend Action = Description
| where Event == "ASA-5-611103"
| parse SyslogMessage with * 'ASA-5-611103:' Message
| project TimeGenerated, Computer, Description, Event, Action, Message;
let asa106015=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Deny TCP (no connection)"
| extend Action = Description
| where Event == "ASA-6-106015"
| parse SyslogMessage with * 'ASA-6-106015:' Message 'from' x 'to' y 'flags' Flags 'on interface' DstVLan
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| parse Action with * 'Deny ' Protocol ' (' *
| project TimeGenerated, Computer, Description, Event, Protocol, Action, Message, SrcIP, SrcPort, DstIP, DstPort, Flags, DstVLan;
let asa110002=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Failed to locate egress interface"
| extend Action = Description
| where Event == "ASA-6-110002"
| parse SyslogMessage with * 'ASA-6-110002:' Message 'for' Protocol 'from' SrcVlan ':' x 'to' y
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| project TimeGenerated, Computer, Description, Event, Action, Message, Protocol, SrcVlan, SrcIP, SrcPort, DstIP, DstPort;
let asa113004=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "AAA user authentication Successful"
| extend Action = Description
| where Event == "ASA-6-113004"
| parse SyslogMessage with * 'ASA-6-113004:' Message ': server = ' x ' : user = ' User
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| project TimeGenerated, Computer, Description, Event, Action, Message, SrcIP, User;
let asa113008=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "AAA transaction status ACCEPT"
| extend Action = Description
| where Event == "ASA-6-113008"
| parse SyslogMessage with * 'ASA-6-113008:' Message ' : user = ' User
| project TimeGenerated, Computer, Description, Event, Message, Action, User;
let asa302010=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Connection stats"
| where Event == "ASA-6-302010"
| extend Action = Description
| parse SyslogMessage with * 'ASA-6-302010:' ConnectionsInUse ' in use, ' ConnectionsMostUsed  ' most used';
let asa302013=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Built outbound TCP connection"
| extend Action = Description
| where Event == "ASA-6-302013"
| parse SyslogMessage with * 'connection ' ConnectionId 'for ' DstVlan ':' y ' (' DestNATIp ') to ' SrcVlan ':' x ' (' DstNATIP ')' 
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| parse Action with * ' outbound ' Protocol ' connection' *
| project TimeGenerated, Computer, Description, Event, Action, Protocol, ConnectionId, DstVlan, DstIP, DstPort, SrcVlan, SrcIP, SrcPort, DstNATIP;
let asa302014=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Teardown outbound TCP connection"
| extend Action = Description
| where Event == "ASA-6-302014"
| parse SyslogMessage with * 'connection ' ConnectionId 'for ' DstVlan ':' y ' to ' SrcVlan ':' x ' duration ' Duration ' bytes' *
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| parse Action with * ' outbound ' Protocol ' connection' *
| project TimeGenerated, Computer, Description, Event, Action, Protocol, ConnectionId, DstVlan, DstIP, DstPort, SrcVlan, SrcIP, SrcPort, Duration;
let asa302015=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Built outbound UDP connection"
| extend Action = Description
| where Event == "ASA-6-302015"
| parse SyslogMessage with * 'connection ' ConnectionId 'for ' DstVlan ':' y ' (' DestNATIp ') to ' SrcVlan ':' x ' (' DstNATIP ')' 
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| parse Action with * ' outbound ' Protocol ' connection' *
| project TimeGenerated, Computer, Description, Event, Protocol, Action, ConnectionId, DstVlan, DstIP, DstPort, SrcVlan, SrcIP, SrcPort, DstNATIP;
let asa302016=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Teardown UDP connection"
| extend Action = Description
| where Event == "ASA-6-302016"
| parse SyslogMessage with * 'connection ' ConnectionId 'for ' DstVlan ':' y ' to ' SrcVlan ':' x ' duration ' Duration ' bytes' *
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| parse Action with * 'Teardown ' Protocol ' connection' *
| project TimeGenerated, Computer, Description, Protocol, Event, Action, ConnectionId, DstVlan, DstIP, DstPort, SrcVlan, SrcIP, SrcPort, Duration;
let asa302020=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Built outbound ICMP connection"
| extend Action = Description
| where Event == "ASA-6-302020"
| parse SyslogMessage with * 'faddr ' y ' gaddr ' x ' laddr ' SrcNATIP ' type' *
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| parse Action with * ' outbound ' Protocol ' connection' *
| project TimeGenerated, Computer, Description, Protocol, Event, Action, DstIP, DstPort, SrcPort, SrcIP, SrcNATIP;
let asa302021=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Teardown ICMP connection"
| extend Action = Description
| where Event == "ASA-6-302021"
| parse SyslogMessage with * 'faddr ' y ' gaddr ' x ' laddr ' SrcNATIP ' type' *
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| parse Action with * 'Teardown ' Protocol ' connection' *
| project TimeGenerated, Computer, Description, Protocol, Event, Action, DstIP, DstPort, SrcIP, SrcPort, SrcNATIP;
let asa303002=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "FTP connection"
| extend Action = Description
| where Event == "ASA-6-303002"
| parse SyslogMessage with * 'from ' SrcVlan ':' x ' to ' DstVlan ':' y ',' Message
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| project TimeGenerated, Computer, Description, Event, Action, SrcVlan, SrcIP, SrcPort, DstVlan, DstIP, DstPort, Message;
let asa305011=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Build Nat Translation"
| extend Action = Description
| where Event == "ASA-6-305011"
| parse SyslogMessage with * 'dynamic ' Protocol ' translation from ' SrcVlan ':' x ' to ' DstVlan ':' y
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| project TimeGenerated, Computer, Description, Event, Action, SrcVlan, SrcIP, SrcPort, DstVlan, DstIP, DstPort;
let asa305012=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Teardown Nat Translation"
| extend Action = Description
| where Event == "ASA-6-305012"
| parse SyslogMessage with * 'dynamic ' Protocol ' translation from ' SrcVlan ':' x ' to ' DstVlan ':' y ' duration ' Duration
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| project TimeGenerated, Computer, Description, Event, Action, SrcVlan, SrcIP, SrcPort, DstVlan, DstIP, DstPort, Duration;
let asa315011=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "SSH session"
| extend Action = Description
| where Event == "ASA-6-315011"
| parse SyslogMessage with * 'from ' x 'on interface ' SrcVlan 'for ' Message
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| project TimeGenerated, Computer, Description, Event, Action, SrcIP, SrcVlan, Message;
let asa605005=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Login permitted"
| extend Action = Description
| where Event == "ASA-6-605005"
| parse SyslogMessage with * 'from ' x ' to ' DstVlan ':' y ' for user ' User
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| extend DstIP = split(y,'/')[0]
| extend DstPort = split(y,'/')[1]
| project TimeGenerated, Computer, Description, Event, Action, SrcIP, SrcPort, DstVlan, DstIP, DstPort, User;
let asa611101=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "User authentication succeeded"
| extend Action = Description
| where Event == "ASA-6-611101"
| parse SyslogMessage with * ': IP address: ' x ', Uname: ' User
| extend SrcIP = split(x,'/')[0]
| extend SrcPort = split(x,'/')[1]
| project TimeGenerated, Computer, Description, Event, Action, SrcIP, User;
let asa769007=
Syslog
| parse SyslogMessage with * '%' Event ':' *
| project TimeGenerated, Event, SyslogMessage, Computer
| extend Description = "Image version"
| extend Action = Description
| where Event == "ASA-6-769007"
| parse SyslogMessage with * 'UPDATE: ' ImageVersion
| project TimeGenerated, Computer, Description, Event, Action, ImageVersion;
union asa106017, asa106023, asa210005, asa313005, asa410001, asa419002, asa500004, asa733100, asa111008, asa111010, asa611103, asa106015, asa110002, asa113004, asa113008, asa302010, asa302013, asa302014, asa302015, asa302016, asa302020, asa302021, asa303002, asa305011, asa305012, asa315011, asa605005, asa611101, asa769007
| project TimeGenerated, Computer, Event, Action, Protocol, SrcIP, SrcPort, SrcVlan, SrcNATIP, DstIP, DstPort, DstVlan, DstNATIP ,Duration, Message, User, Flags, ImageVersion, ConnectionsInUse, ConnectionsMostUsed, Description

// Save as a function in your workspace then invoke via its name, i.e if you save as DeviceLookup
// DeviceLookup | where IPAddresses has "10.10.10.10" // will find all devices that have had the IPAddress 10.10.10.10
// DeviceLookup | where ['Logged on Admins'] has "user1" // will find all devices user1 has logged onto as an admin
// DeviceLookup | where array_length( ['Logged on Admins']) > 10 // will find devices with more than 10 local admins logged on 
// This will parse the last 14 days of the DeviceInfo, DeviceLogonEvents and DeviceNetworkInfo tables for information
let deviceinfo=
    DeviceInfo
    | where TimeGenerated > ago (14d)
    | where isnotempty(OSBuild)
    | summarize arg_max(TimeGenerated, *) by DeviceId
    | project DeviceName, DeviceId, OSPlatform, OSBuild;
let logons=
    DeviceLogonEvents
    | where TimeGenerated > ago(14d)
    | project
        DeviceName,
        ActionType,
        LogonType,
        AccountName,
        DeviceId,
        InitiatingProcessCommandLine,
        AdditionalFields,
        IsLocalAdmin
    | where ActionType == "LogonSuccess"
    | where LogonType == "Interactive"
    | where AdditionalFields.IsLocalLogon == true
    | where InitiatingProcessCommandLine == "lsass.exe"
    | summarize
        ['Logged on Users']=make_set_if(AccountName, IsLocalAdmin == "false"),
        ['Logged on Admins']=make_set_if(AccountName, IsLocalAdmin == "true")
        by DeviceId;
let ipaddresses=
    DeviceNetworkInfo
    | where TimeGenerated > ago (14d)
    | mv-expand IPAddresses
    | extend IPAddress = tostring(IPAddresses.IPAddress)
    | summarize IPAddresses=make_set(IPAddress) by DeviceId;
deviceinfo
| lookup logons on DeviceId
| lookup ipaddresses on DeviceId

// Save as a function in your workspace then invoke via its name, i.e if you save as ADFailedLogons
// ADFailedLogons | where TimeGenerated > ago(30d) | where Account == "DOMAIN\username" // will find logon failures by that user
// ADFailedLogons | where TimeGenerated > ago(30d) | where ResultReason == "User logon with expired password" // will find logon failures from accounts with expired passwords
// This will parse the SecurityEvent log for logon failures and add the descriptive reason.
SecurityEvent
| where EventID == "4625"
| extend ResultReason = case(
    Status == "0xc000005e", strcat("There are currently no logon servers available to service the logon request"),
    Status == "0xc0000064", strcat("User logon with misspelled or bad user account"),
    Status == "0xc000006a", strcat("User logon with misspelled or bad password"),
    Status == "0xc000006d", strcat("The cause is either a bad username or authentication information"),
    Status == "0xc000006e", strcat("Indicates a referenced user name and authentication information are valid, but some user account restriction has prevented successful authentication (such as time-of-day restrictions)"),
    Status == "0xc000006f", strcat("User logon outside authorized hours"),
    Status == "0xc0000070", strcat("User logon from unauthorized workstation"),
    Status == "0xc0000071", strcat("User logon with expired password"),
    Status == "0xc0000072", strcat("User logon to account disabled by administrator"),
    Status == "0xc000018b", strcat("Security database on the server does not have a computer account for this workstation trust relationship"),
    Status == "0xc00000dc", strcat("Indicates the Sam Server was in the wrong state to perform the desired operation"),
    Status == "0xc0000133", strcat("Clocks between DC and other computer too far out of sync"),
    Status == "0xc000015b", strcat("The user has not been granted the requested logon type (also called the logon right) at this machine"),
    Status == "0xc000018c", strcat("The logon request failed because the trust relationship between the primary domain and the trusted domain failed"),
    Status == "0xc0000192", strcat("An attempt was made to logon, but the Netlogon service was not started"),
    Status == "0xc0000193", strcat("User logon with expired account"),
    Status == "0xc0000224", strcat("User is required to change password at next logon"),
    Status == "0xc0000225", strcat("Evidently a bug in Windows and not a risk"),
    Status == "0xc0000234", strcat("User logon with account locked"),
    Status == "0xc000015a", strcat("During a logon attempt, the user's security context accumulated too many security IDs"),
    Status == "0xc00002ee", strcat("Failure Reason: An Error occurred during Logon"),
    Status == "0xc0000413", strcat("Logon Failure: The machine you are logging on to is protected by an authentication firewall. The specified account is not allowed to authenticate to the machine"),
    Status == "0x0", strcat("Status OK"),
    "unknown")
| project
    TimeGenerated,
    Status,
    ResultReason,
    Account,
    AccountType,
    Computer,
    AuthenticationPackageName,
    IpAddress

// Save as a function in your workspace then invoke via its name, i.e if you save as GroupChanges
// GroupChanges | where TimeGenerated > ago(1d) | where Action == "Add" and GroupName == "TestGroup1" // Will find all group additions to "TestGroup1"
// GroupChanges | where TimeGenerated > ago(1d) | where Action == "Remove" and Actor == "User1" and Environment == "Azure Active Directory" // will find all group removals by "User1" in Azure Active Directory
// GroupChanges | where TimeGenerated > ago(1d) | where Action == "Add" | Will find all Add actions in both Active Directory and Azure Active Directory
let aaduseradded=
    AuditLogs
    | where OperationName == "Add member to group"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend Target = tostring(TargetResources[0].userPrincipalName)
    | extend GroupName = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
    | where isnotempty(Actor) and isnotempty(Target)
    | extend Environment = strcat("Azure Active Directory")
    | extend Action = strcat("Add")
    | project TimeGenerated, Action, Actor, Target, GroupName, Environment;
let aaduserremoved=
    AuditLogs
    | where OperationName == "Remove member from group"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend GroupName = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].oldValue)))
    | extend Target = tostring(TargetResources[0].userPrincipalName)
    | where isnotempty(Actor) and isnotempty(Target)
    | extend Action = strcat("Remove")
    | extend Environment = strcat("Azure Active Directory")
    | project TimeGenerated, Action, Actor, Target, GroupName, Environment;
let adchanges=
    SecurityEvent
    | project TimeGenerated, EventID, AccountType, MemberName, SubjectUserName, TargetUserName
    | where AccountType == "User"
    | where EventID in (4728, 4729, 4732, 4733, 4756, 4757)
    | extend x = tolower(MemberName)
    | parse x with * 'cn=' Target ',ou=' *
    | extend Action = case(EventID in ("4728", "4756", "4732"), strcat("Add"),
        EventID in ("4729", "4757", "4733"), strcat("Remove"), "unknown")
    | extend Environment = strcat("Active Directory")
    | project TimeGenerated, Action, Actor=SubjectUserName, Target, GroupName=TargetUserName, Environment;
union aaduseradded, aaduserremoved, adchanges

//Single function to summarize a single guest domain. It will retrieve the following information about a specific domain.
//Azure AD Sign in Logs - total sign in count, distinct sign in count, list of applications, count of applications, list of users
//Azure AD Audit Logs - invites sent and redeemed from this domain
//Office 365 - total files downloaded, distinct filese downloaded from this domain
//Office 365 - count of users added to Teams, distinct count of users added to Teams and the list of Teams

//Save as a function in your workspace then invoke via its name, ie UserInvestigation("[email protected]"). Your function requires a parameter as per https://github.com/reprise99/Sentinel-Queries/tree/main/Functions
//The function requires a parameter which is a string, with the name domain and a default value of "gmail.com" (or any domain you wish)

let signins=
SigninLogs
| where TimeGenerated > ago(30d)
| where UserPrincipalName endswith (domain)
| summarize ['Total Signins']=count(), ['Count of Users']=dcount(UserPrincipalName), ['Count of Applications']=dcount(AppDisplayName), ['List of Applications Accessed']=make_set(AppDisplayName), ['List of Users']=make_set(UserPrincipalName), ['List of Locations']=make_set(Location) by Domain=(domain);
let invitedusers=
AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName == "Invite external user"
| extend UserPrincipalName = tostring(TargetResources[0].userPrincipalName)
| where TargetResources contains (domain)
| summarize ['Invited User Count']=dcount(UserPrincipalName), ['List of Users Invited']=make_set(UserPrincipalName) by Domain=(domain);
let redeemedusers=
AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName == "Redeem external user invite"
| extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where UserPrincipalName endswith (domain)
| summarize ['Invite Redeemed User Count']=dcount(UserPrincipalName), ['List of Redeemed Users']=make_set(UserPrincipalName) by Domain=(domain);
let officedownloads=
OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId contains "#EXT#"
| extend ['Guest UserPrincipalName'] = tostring(split(UserId,"#")[0])
| extend ['Guest Domain'] = tostring(split(['Guest UserPrincipalName'],"_")[-1])
| where ['Guest Domain'] =~ (domain)
| summarize ['Total Guest Download Count']=count(), ['Distinct File Download Count']=dcount(OfficeObjectId) by Domain=(domain);
let teamsaccess=
OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation == "MemberAdded"
| mv-expand Members
| extend UserPrincipalName = tostring(Members.UPN)
| where UserPrincipalName contains "#EXT#"
| where CommunicationType == "Team"
| where UserPrincipalName contains (domain)
| summarize ['Count of Guests Added to Teams']=count(), ['Distinct Count of Guests Added to Teams']=dcount(UserPrincipalName), ['Count of Teams with Guests Added']=dcount(TeamName), ['List of Teams with Guests Added']=make_set(TeamName) by Domain=(domain);
signins
| lookup invitedusers on Domain
| lookup redeemedusers on Domain
| lookup officedownloads on Domain
| lookup teamsaccess on Domain

// Save as a function in your workspace then invoke via its name, i.e if you save as SigninRisk
// SigninRisk | where TimeGenerated > ago(30d) | where UserPrincipalName == "[email protected]"
// SigninRisk | where TimeGenerated > ago(30d) | where AssignedRoles contains "Global Administrator"
// This will join the users identity information, sign in data and any risky signins for your query
IdentityInfo
| where TimeGenerated > ago (21d)
| summarize arg_max(TimeGenerated,*) by AccountUPN
| join kind=inner(
SigninLogs) on $left.AccountUPN==$right.UserPrincipalName
| project SigninTime=TimeGenerated1, UserPrincipalName, AppDisplayName, ResultType, AssignedRoles, Location, UserAgent, AuthenticationRequirement, Country, City, CorrelationId
| join kind=inner (
AADUserRiskEvents) on CorrelationId
| project SigninTime, UserPrincipalName, AppDisplayName, ResultType, DetectionTimingType, RiskState, RiskLevel, Location, AssignedRoles, UserAgent, AuthenticationRequirement, Country, City

// Save as a function in your workspace then invoke via its name, i.e if you save as NewDetections
// NewDetections // will find any new detections from your Azure AD Audit logs, Defender for Cloud Apps, Security Alerts, Office Activity and Defender for Endpoint
// NewDetections | where Source == "Office 365 Audit Events" // will find new detections from Office 365
// NewDetections | where Count > 10 // will find new detections that have been seen more than 10 times
// This function looks for new events in Azure AD Audit logs, Defender for Cloud Apps, Security Alerts, Office Activity and Defender for Endpoint in the last week compared to the last 180 days
let newauditevents=
    AuditLogs
    | where TimeGenerated > ago(180d) and TimeGenerated < ago(7d)
    | distinct OperationName, LoggedByService
    | join kind=rightanti (
        AuditLogs
        | where TimeGenerated > ago(7d)
        )
        on OperationName, LoggedByService
    | summarize ['First Time Seen']=min(TimeGenerated), Count=count() by Activity=OperationName, Application="Azure AD", Source="Azure AD Audit Events";
let newdeviceevents=
    DeviceEvents
    | where TimeGenerated > ago(180d) and TimeGenerated < ago(7d)
    | distinct ActionType
    | join kind=rightanti (
        DeviceEvents
        | where TimeGenerated > ago(7d)
        )
        on ActionType
    | summarize ['First Time Seen']=min(TimeGenerated), Count=count()
        by
        Activity=ActionType,
        Application="Defender for EndPoint",
        Source="Defender for Endpoint Device Events";
let newnewofficeactivity=
    OfficeActivity
    | where TimeGenerated > ago(180d) and TimeGenerated < ago(7d)
    | distinct Operation
    | join kind=rightanti (
        OfficeActivity
        | where TimeGenerated > ago(7d)
        )
        on Operation
    | summarize ['First Time Seen']=min(TimeGenerated), Count=count() by Activity=Operation, Application=OfficeWorkload, Source="Office 365 Audit Events";
let newcloudappevents=
    CloudAppEvents
    | where TimeGenerated > ago(180d) and TimeGenerated < ago(7d)
    | distinct ActionType
    | join kind=rightanti (
        CloudAppEvents
        | where TimeGenerated > ago(7d)
        )
        on ActionType
    | summarize ['First Time Seen']=min(TimeGenerated), Count=count() by Activity=ActionType, Application, Source="Defender for Cloud Apps Events";
let newsecurityalerts=
    SecurityAlert
    | where TimeGenerated > ago(180d) and TimeGenerated < ago(7d)
    | where ProviderName != "ASI Scheduled Alerts"
    | distinct AlertName
    | join kind=rightanti (
        SecurityAlert
        | where TimeGenerated > ago(7d)
        | where ProviderName != "ASI Scheduled Alerts"
        )
        on AlertName
    | summarize ['First Time Seen']=min(TimeGenerated), Count=count() by Activity=AlertName, Application=ProviderName, Source="Security Alert Events";
union
    newauditevents,
    newdeviceevents,
    newnewofficeactivity,
    newcloudappevents,
    newsecurityalerts
| sort by Count desc

// Save as a function in your workspace then invoke via its name, i.e if you save as PrivilegeChanges then call via the same name
// This function is designed to unify group or privilege changes across on-premises AD and Azure AD
// It will find changes to privileged AD groups, changes to privileged AAD groups, changes to privileged AAD roles (both directly and via PIM)
// Default groups and roles have been added, but add your own groups that you wish to monitor unique to your environment
// By default it will look back one day

let privADgroups=dynamic(["Domain Admins", "Enterprise Admins", "Schema Admins", "Account Operators", "DnsAdmins", "Backup Operators"]);
let privAADgroups=dynamic(["azure.Privileged Group", "azure.Privileged App", "az.ConditionalAccessBypassGroup"]);
let privAADroles=dynamic(["Global Administrator", "Application Administrator", "Privileged Authentication Administrator", "Privileged Role Administrator", "Security Administrator" "Identity Governance Administrator"]);
let timeframe=1d;
let adchanges=
    SecurityEvent
    | where TimeGenerated > ago (timeframe)
    | project TimeGenerated, Account, MemberName, TargetAccount, TargetUserName, Activity, EventID
    | where EventID in (4728, 4729, 4732, 4733, 4756, 4757) and TargetUserName in~ (privADgroups)
    | project
        TimeGenerated,
        Activity,
        ['Group or Role Name']=TargetUserName,
        Actor=Account,
        Target=MemberName,
        Environment="Active Directory";
let aaduseradded=
    AuditLogs
    | where TimeGenerated > ago (timeframe)
    | where OperationName == "Add member to group"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend Target = tostring(TargetResources[0].userPrincipalName)
    | extend ['Group or Role Name']= tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
    | where isnotempty(Actor) and isnotempty(Target)
    | where ['Group or Role Name'] in~ (privAADgroups)
    | project
        TimeGenerated,
        Activity="A member was added to a privleged Azure AD Group",
        ['Group or Role Name'],
        Actor,
        Target,
        Environment="Azure Active Directory";
let aaduserremoved=
    AuditLogs
    | where TimeGenerated > ago (timeframe)
    | where OperationName == "Remove member from group"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend ['Group or Role Name'] = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].oldValue)))
    | extend Target = tostring(TargetResources[0].userPrincipalName)
    | where isnotempty(Actor) and isnotempty(Target)
    | where ['Group or Role Name'] in~ (privAADgroups)
    | extend Environment = strcat("Azure Active Directory")
    | project
        TimeGenerated,
        Activity="A member was removed from a privleged Azure AD Group",
        ['Group or Role Name'],
        Actor,
        Target,
        Environment="Azure Active Directory";
let aadroleadded=
    AuditLogs
    | where TimeGenerated > ago (timeframe)
    | where OperationName == "Add member to role"
    //Exclude PIM activations
    | where Identity != "MS-PIM"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend Target = tostring(TargetResources[0].userPrincipalName)
    | extend ['Group or Role Name'] = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
    | where isnotempty(Actor) and isnotempty(Target)
    | where ['Group or Role Name'] in~ (privAADroles)
    | project
        TimeGenerated,
        Activity="A member was added to a privleged Azure AD Role",
        ['Group or Role Name'],
        Actor,
        Target,
        Environment="Azure Active Directory";
let aadroleremoved=
    AuditLogs
    | where TimeGenerated > ago (timeframe)
    | where OperationName == "Remove member from role"
   //Exclude PIM activations
    | where Identity != "MS-PIM"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend Target = tostring(TargetResources[0].userPrincipalName)
    | extend ['Group or Role Name'] = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].oldValue)))
    | where isnotempty(Actor) and isnotempty(Target)
    | where ['Group or Role Name'] in~ (privAADroles)
    | project
        TimeGenerated,
        Activity="A member was removed from a privleged Azure AD Role",
        ['Group or Role Name'],
        Actor,
        Target,
        Environment="Azure Active Directory";
let addpim=
    AuditLogs
    | where TimeGenerated > ago (timeframe)
    | where OperationName in ("Add member to role in PIM completed (permanent)", "Add member to role in PIM completed (timebound)", "Add eligible member to role in PIM completed (timebound)", "Add eligible member to role in PIM completed (permanent)")
    | where TargetResources[2].type == "User"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend Target = tostring(TargetResources[2].userPrincipalName)
    | extend ['Group or Role Name'] = tostring(TargetResources[0].displayName)
    | where isnotempty(Actor) and isnotempty(Target)
    | where ['Group or Role Name'] in~ (privAADroles)
    | extend Activity = case(OperationName == "Add member to role in PIM completed (permanent)", strcat="A member was assigned to a permanent active Azure AD PIM Role",
        OperationName == "Add member to role in PIM completed (timebound)", strcat="A member was assigned to a timebound active Azure AD PIM Role",
        OperationName == "Add eligible member to role in PIM completed (permanent)", strcat="A member was assigned to a permanent eligible Azure AD PIM Role",
        OperationName == "Add eligible member to role in PIM completed (timebound)", strcat="A member was assigned to a timebound eligible Azure AD PIM Role",
        "unknown")
    | project
        TimeGenerated,
        Activity,
        ['Group or Role Name'],
        Actor,
        Target,
        Environment="Azure Active Directory";
let removepim=
    AuditLogs
    | where TimeGenerated > ago (timeframe)
    | where OperationName in ("Remove member from role in PIM completed (permanent)", "Remove member from role in PIM completed (timebound)", "Remove eligible member from role in PIM completed (permanent)", "Remove eligible member from role in PIM completed (timebound)")
    | where TargetResources[2].type == "User"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend Target = tostring(TargetResources[2].userPrincipalName)
    | extend ['Group or Role Name'] = tostring(TargetResources[0].displayName)
    | where isnotempty(Actor) and isnotempty(Target)
    | where ['Group or Role Name'] in~ (privAADroles)
    | extend Activity = case(OperationName == "Remove member from role in PIM completed (permanent)", strcat="A member was removed from a permanent active Azure AD PIM Role",
        OperationName == "Remove member from role in PIM completed (timebound)", strcat="A member was removed from a timebound active Azure AD PIM Role",
        OperationName == "Remove eligible member from role in PIM completed (permanent)", strcat="A member was removed from a permanent eligible Azure AD PIM Role",
        OperationName == "Remove eligible member from role in PIM completed (timebound)", strcat="A member was removed from a timebound eligible Azure AD PIM Role",
        "unknown")
    | project
        TimeGenerated,
        Activity,
        ['Group or Role Name'],
        Actor,
        Target,
        Environment="Azure Active Directory";
union adchanges, aaduseradded, aaduserremoved, aadroleadded, aadroleremoved, addpim, removepim
| project-reorder TimeGenerated, Activity, ['Group or Role Name'], Actor, Target, Environment
| sort by TimeGenerated desc

//Query several tables to retireve all your DCs - such as kerberos, DNS, replication events
let SamrDC=
IdentityQueryEvents
| where TimeGenerated > ago (30d)
| where ActionType == "SAMR query"
| distinct DestinationDeviceName;
let DnsDC=
DnsEvents
| where TimeGenerated > ago (30d)
| where Name startswith "_kerberos."
| distinct Computer
| extend DestinationDeviceName = tolower(Computer);
let SrvDC=
IdentityQueryEvents
| where TimeGenerated > ago (30d)
| where QueryType == "Srv"
| where QueryTarget startswith "_kerberos."
| distinct DestinationDeviceName;
let directoryeventsDC=
IdentityDirectoryEvents
| where TimeGenerated > ago (30d)
| where ActionType in ("Directory Services replication")
// Exclude Azure AD Connect
| where isnotempty( AccountName) and isnotempty( DestinationDeviceName)
| distinct DestinationDeviceName;
union isfuzzy= true SamrDC, DnsDC, SrvDC, directoryeventsDC 
| distinct DestinationDeviceName

// Save as a function in your workspace then invoke via its name, i.e if you save as TeamsAccess
// TeamsAccess | where TeamName == "Secret Project"  // will all events, user adds, removes and access changes for the "Secret Project" team
// TeamsAccess | where Actor == "[email protected]" // will find all events, user adds, removes and changes performed by [email protected]
// TeamsAccess | where TimeGenerated > ago(1d) and Activity == "User added as guest" // will find all guests being added in the last day
// This function makes the data structure for the different events consistent so you can query them quickly
let memberadded=
    OfficeActivity
    | where Operation == "MemberAdded"
    | where CommunicationType == "Team"
    | mv-expand Members
    | extend User = tostring(Members.UPN)
    | extend x = tostring(Members.Role)
    | extend Activity = case(x == "1", strcat("User added as member"),
        x == "2", strcat("User added as owner"), 
        x == "3", strcat("User added as guest"),
        "unknown")
    | extend Action = "Add"
    | project
        TimeGenerated,
        Action,
        Activity,
        Actor=UserId,
        User,
        TeamName,
        TeamGuid,
        ActorType=UserType;
let memberremoved=
    OfficeActivity
    | where Operation == "MemberRemoved"
    | where CommunicationType == "Team"
    | mv-expand Members
    | extend User = tostring(Members.UPN)
    | extend Activity = "User removed from Team"
    | extend ActorType = "User"
    | extend Action = "Remove"
    | project
        TimeGenerated,
        Action,
        Activity,
        Actor=UserId,
        User,
        TeamName,
        TeamGuid,
        ActorType=UserType;
let memberaccesschanged=
    OfficeActivity
    | where Operation == "MemberRoleChanged"
    | mv-expand Members
    | extend User = tostring(Members.UPN)
    | extend x = tostring(Members.Role)
    | extend Activity = case(x == "1", strcat("User changed to member"),
        x == "2", strcat("User changed to owner"), "unknown")
    | extend Action = "Change"
    | project
        TimeGenerated,
        Action,
        Activity,
        Actor=UserId,
        User,
        TeamName,
        TeamGuid,
        ActorType=UserType;
union memberadded, memberremoved, memberaccesschanged
| project-reorder TimeGenerated, Action, Activity, User, Actor, ActorType, TeamName, TeamGuid

//Single function to investigate potential suspicious activity across several data sources. The function will find the following information.
//Azure AD Sign in Logs - legacy auth attempts, conditional access failures, new user agents or locations found in the last day
//Azure AD Audit Logs - add service principals, consent to permissions, add credentials to service principals, any MFA configuration changes
//Azure AD risk events - any non automatically dismissed risk events
//Defender for Cloud Apps - mailbox rule changes
//Security Alert - any alerts from the various products such as Azure AD Identity Protection, Defender for Office 365 etc
//Save as a function in your workspace then invoke via its name, ie UserInvestigation("[email protected]"). Your function requires a parameter as per https://github.com/reprise99/Sentinel-Queries/tree/main/Functions
let legacyauth=
SigninLogs
| where TimeGenerated > ago (30d)
| where UserPrincipalName =~ user
| where ClientAppUsed !in ("Mobile Apps and Desktop clients", "Browser") and isnotempty( ClientAppUsed)
| extend Indicator = "Legacy auth attempts detected"
| extend ['Event Source'] = "Azure AD Signin Logs"
| project TimeGenerated, UserPrincipalName, AppDisplayName, ClientAppUsed, IPAddress, Location, UserAgent, ResultType, ResultDescription, Indicator, ['Event Source'], TableName=Type, CorrelationId;
let cafailures=
SigninLogs
| where TimeGenerated > ago (30d)
| where UserPrincipalName =~ user
| where ResultType == "53003"
| extend Indicator = "Conditional Access failures detected"
| extend ['Event Source'] = "Azure AD Signin Logs"
| project TimeGenerated, UserPrincipalName, AppDisplayName, ClientAppUsed, IPAddress, tostring(Location), UserAgent, ResultType, ResultDescription, Indicator, ['Event Source'], TableName=Type, CorrelationId;
let newuseragents=
SigninLogs
| where TimeGenerated > ago(30d) and TimeGenerated < ago(1d)
| where UserPrincipalName =~ user
| distinct UserAgent
| join kind=rightanti (
SigninLogs
| where TimeGenerated > ago (1d)
| where UserPrincipalName =~ user
) on UserAgent
| extend Indicator = "New user agent detected in last day"
| extend ['Event Source'] = "Azure AD Signin Logs"
| project TimeGenerated, UserPrincipalName, AppDisplayName, ClientAppUsed, IPAddress, tostring(Location), UserAgent, ResultType, ResultDescription, Indicator, ['Event Source'], TableName=Type, CorrelationId;
let newlocations=
SigninLogs
| where TimeGenerated > ago(30d) and TimeGenerated < ago(1d)
| where UserPrincipalName =~ user
| where TimeGenerated > ago(30d) and TimeGenerated < ago(1d)
| distinct Location
| join kind=rightanti (
SigninLogs
| where TimeGenerated > ago (1d)
| where UserPrincipalName =~ user
) on Location
| extend Indicator = "New location detected in last day"
| extend ['Event Source'] = "Azure AD Signin Logs"
| project TimeGenerated, UserPrincipalName, AppDisplayName, ClientAppUsed, IPAddress, tostring(Location), UserAgent, ResultType, ResultDescription, Indicator, ['Event Source'], TableName=Type, CorrelationId;
let riskevents=
AADUserRiskEvents
| where TimeGenerated > ago (30d)
| where UserPrincipalName =~ (user)
| where RiskDetail <> "aiConfirmedSigninSafe"
| extend Indicator = "Azure AD risk event detected"
| extend ['Event Source'] = "Azure AD Risky Signin Logs"
| project TimeGenerated, UserPrincipalName, IPAddress=IpAddress, tostring(Location), RiskState, RiskLevel, RiskEventType, RiskDetail, Indicator, ['Event Source'], TableName=Type, CorrelationId;
let audit=
AuditLogs
| where TimeGenerated > ago (30d)
| where OperationName in~ ("Add service principal","Consent to application","Update application – Certificates and secrets management ","User registered security info", "User changed default security info", "User deleted security info")
| extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where UserPrincipalName =~ user
| extend Indicator = case (
    OperationName == "Add service principal", strcat("Azure AD service principal added"),
    OperationName == "Consent to application", strcat("Azure AD service principal permissions consented to"),
    OperationName == "Update application – Certificates and secrets management ", strcat("Azure AD service principal credentials added"),
    OperationName == "User registered security info", strcat("MFA method registered"),
    OperationName == "User changed default security info", strcat("MFA default method changed"),
    OperationName == "User deleted security info", strcat("MFA method deleted"),
    "unknown"
    )
| extend ['Event Source'] = "Azure AD Audit Logs"
| extend IPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| project TimeGenerated, IPAddress, Indicator, ['Event Source'], TableName=Type, CorrelationId;
let cloudapp=
CloudAppEvents
| where TimeGenerated > ago (30d)
| where ActionType in ("New-InboxRule","Remove-InboxRule","Set-InboxRule")
| extend UserPrincipalName = tostring(RawEventData.UserId)
| where UserPrincipalName =~ user
| extend CorrelationId = tostring(RawEventData.Id)
| extend ['Event Source'] = "Defender for Cloud Apps Logs"
| extend Indicator = case (
    ActionType == "New-InboxRule", strcat("Exchange Online inbox rule created"),
    ActionType == "Remove-InboxRule", strcat("Exchange Online inbox rule deleted"),
    ActionType == "Set-InboxRule", strcat("Exchange Online inbox rule changed"),
    "unknown"
    )
| project TimeGenerated, UserPrincipalName, IPAddress, Indicator, TableName=Type, ['Event Source'];
let alerts=
SecurityAlert
| where TimeGenerated > ago (30d)
| where CompromisedEntity =~ user
| project TimeGenerated, UserPrincipalName=CompromisedEntity, Indicator=AlertName, TableName=Type, ['Event Source']=ProductName;
union legacyauth, cafailures, newuseragents, newlocations, riskevents, audit, cloudapp, alerts
| project-reorder TimeGenerated, UserPrincipalName, Indicator, ['Event Source'], TableName, IPAddress
| sort by Indicator asc, TimeGenerated desc

// Save as a function in your workspace then invoke via its name, i.e if you save as UserLogins
// UserLogins | where UserPrincipalName == "[email protected]" or AccountName == "user2" // will find information for Azure AD user [email protected] or AD user user2
// UserLogins | where Department has "Human Resources" // will find information for all Human Resources staff
// UserLogins | where AccountDisplayName has "Matt" and ['Last Azure AD Sign In'] > ago(30m) // will find information for anyone with "Matt" in their Azure AD displayname and has signed into Azure AD in the last 30 minutes
// This will parse the last 30 days of the IdentityInfo, SigninLogs and SecurityEvent tables for logon information
let idinfo=
    IdentityInfo
    | where TimeGenerated > ago(21d)
    | summarize arg_max (TimeGenerated, *) by AccountUPN
    | project
        UserPrincipalName=AccountUPN,
        AccountName,
        AccountDisplayName,
        JobTitle,
        Country,
        City,
        Department;
let aad=
    SigninLogs
    | where TimeGenerated > ago(30d)
    | where ResultType == 0
    | summarize arg_max(TimeGenerated, *) by UserPrincipalName
    | project ['Last Azure AD Sign In']=TimeGenerated, UserPrincipalName;
let ad=
    SecurityEvent
    | where TimeGenerated > ago(30d)
    | project TimeGenerated, Computer, EventID, TargetUserName
    | where EventID == "4624"
    | summarize arg_max(TimeGenerated, TargetUserName) by AccountName=TargetUserName
    | project ['Last AD Sign In']=TimeGenerated, AccountName;
idinfo
| lookup aad on UserPrincipalName
| lookup ad on AccountName
| project-reorder UserPrincipalName, AccountName, ['Last AD Sign In'], ['Last Azure AD Sign In']

// Save as a function in your workspace then invoke via its name, i.e if you save as UserLookup
// UserLookup | where UserPrincipalName == "[email protected]" // will find information for [email protected]
// UserLookup | where Countries has "AU" and ['Authentication Methods'] has "Windows Hello for Business" // will find all users who have signed in from Australia and have used WHFB
// UserLookup | where JobTitle == "Chief Astronaut" and IPAddresses has "10.10.10.10" // will find all Chief Astronauts who have signed on from 10.10.10.10
// This will parse the last 14 days of the IdentityInfo, SigninLogs and SecurityAlerts tables for information
let identity=
    IdentityInfo
    | where TimeGenerated > ago (14d)
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    | project
        UserPrincipalName=AccountUPN,
        AccountName,
        AccountDisplayName,
        JobTitle,
        City,
        Country;
let signininfo=
    SigninLogs
    | where TimeGenerated > ago(14d)
    | where ResultType == 0
    | extend City = tostring(LocationDetails.city)
    | extend Country = tostring(LocationDetails.countryOrRegion)
    | extend DeviceName = tostring(DeviceDetail.displayName)
    | summarize
        Applications=make_set(AppDisplayName),
        IPAddresses=make_set(IPAddress),
        Countries=make_set_if(Country, isnotempty(Country)),
        Cities=make_set_if(City, isnotempty(City)),
        Devices=make_set_if(DeviceName, isnotempty(DeviceName))
        by UserPrincipalName;
let authmethods=
    SigninLogs
    | where TimeGenerated > ago(14d)
    | where ResultType == 0
    | mv-expand todynamic(AuthenticationDetails)
    | extend AuthMethod = tostring(AuthenticationDetails.authenticationMethod)
    | where AuthMethod !in ("Previously satisfied", "Password", "Other")
    | summarize ['Authentication Methods']=make_set(AuthMethod) by UserPrincipalName;
let alerts=
    SecurityAlert
    | where TimeGenerated > ago(14d)
    | extend Alert = strcat(AlertName, " - ", ProductName)
    | summarize Alerts=make_set(Alert) by UserPrincipalName=CompromisedEntity;
identity
| lookup signininfo on UserPrincipalName
| lookup authmethods on UserPrincipalName
| lookup alerts on UserPrincipalName

let timeframe = 1d;
// Adjust for a longer timeframe for identifying ADFS Servers
let lookback = 6d;
// Identify ADFS Servers
let ADFS_Servers = ( union isfuzzy=true
( Event
| where TimeGenerated > ago(timeframe+lookback)
| where Source == "Microsoft-Windows-Sysmon"
| where EventID == 1
| extend EventData = parse_xml(EventData).DataItem.EventData.Data
| mv-expand bagexpansion=array EventData
| evaluate bag_unpack(EventData)
| extend Key=tostring(['@Name']), Value=['#text']
| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, UserName, RenderedDescription, MG, ManagementGroupName, Type, _ResourceId)
| extend process = split(Image, '\\', -1)[-1]
| where process =~ "Microsoft.IdentityServer.ServiceHost.exe"
| distinct Computer
),
( SecurityEvent
| where TimeGenerated > ago(timeframe+lookback)
| where EventID == 4688 and SubjectLogonId != "0x3e4"
| where ProcessName has "Microsoft.IdentityServer.ServiceHost.exe"
| distinct Computer
),
(WindowsEvent
| where TimeGenerated > ago(timeframe+lookback)
| where EventID == 4688 and EventData has "0x3e4" and EventData has "Microsoft.IdentityServer.ServiceHost.exe"
| extend SubjectLogonId  = tostring(EventData.SubjectLogonId)
| where SubjectLogonId != "0x3e4"
| extend ProcessName  = tostring(EventData.ProcessName)
| where ProcessName has "Microsoft.IdentityServer.ServiceHost.exe"
| distinct Computer
)
| distinct Computer);
(union isfuzzy=true
(
SecurityEvent
| where EventID == 4688
| where TimeGenerated > ago(timeframe)
| where Computer in~ (ADFS_Servers)
| where ParentProcessName has 'wmiprvse.exe'
// Looking for rundll32.exe is based on intel from the blog linked in the description
// This can be commented out or altered to filter out known internal uses
| where CommandLine has_any ('rundll32') 
| project TimeGenerated, TargetAccount, CommandLine, Computer, Account, TargetLogonId
| extend timestamp = TimeGenerated
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend AccountName = tostring(split(Account, "\\")[0]), AccountNTDomain = tostring(split(Account, "\\")[1])
// Search for recent logons to identify lateral movement
| join kind= inner
(SecurityEvent
| where TimeGenerated > ago(timeframe)
| where EventID == 4624 and LogonType == 3
| where Account !endswith "$"
| project TargetLogonId
) on TargetLogonId
),
(
WindowsEvent
| where EventID == 4688
| where TimeGenerated > ago(timeframe)
| where Computer in~ (ADFS_Servers)
| where EventData has 'wmiprvse.exe' and EventData has_any ('rundll32') 
| extend ParentProcessName = tostring(EventData.ParentProcessName)
| where ParentProcessName has 'wmiprvse.exe'
// Looking for rundll32.exe is based on intel from the blog linked in the description
// This can be commented out or altered to filter out known internal uses
| extend CommandLine = tostring(EventData.CommandLine)
| where CommandLine has_any ('rundll32') 
| extend TargetAccount = strcat(EventData.TargetDomainName,"\\", EventData.TargetUserName)
| extend Account = strcat(EventData.TargetDomainName,"\\", EventData.TargetUserName)
| extend TargetLogonId = tostring(EventData.TargetLogonId)
| project TimeGenerated, TargetAccount, CommandLine, Computer, Account, TargetLogonId
| extend timestamp = TimeGenerated
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend AccountName = tostring(split(Account, "\\")[0]), AccountNTDomain = tostring(split(Account, "\\")[1])
// Search for recent logons to identify lateral movement
| join kind= inner
(WindowsEvent
| where TimeGenerated > ago(timeframe)
| where EventID == 4624 
| extend  LogonType = tostring(EventData.LogonType)
| where LogonType == 3
| extend Account = strcat(EventData.TargetDomainName,"\\", EventData.TargetUserName)
| where Account !endswith "$"
| extend TargetLogonId = tostring(EventData.TargetLogonId)
| project TargetLogonId
) on TargetLogonId
),
(
Event
| where TimeGenerated > ago(timeframe)
| where Source == "Microsoft-Windows-Sysmon"
// Check for WMI Events
| where Computer in~ (ADFS_Servers) and EventID in (19, 20, 21)
| extend EventData = parse_xml(EventData).DataItem.EventData.Data
| mv-expand bagexpansion=array EventData
| evaluate bag_unpack(EventData)
| extend Key=tostring(['@Name']), Value=['#text']
| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, UserName, RenderedDescription, MG, ManagementGroupName, Type, _ResourceId)
| project TimeGenerated, EventType, Image, Computer, UserName
| extend timestamp = TimeGenerated
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend AccountName = tostring(split(UserName, "\\")[0]), AccountNTDomain = tostring(split(UserName, "\\")[1])
)
)

let WellKnownLocalSID = "S-1-5-32-5[0-9][0-9]$";
let WellKnownGroupSID = "S-1-5-21-[0-9]*-[0-9]*-[0-9]*-5[0-9][0-9]$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-1102$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-1103$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-498$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-1000$";
let GroupAddition = (union isfuzzy=true   
(SecurityEvent 
// 4728 - A member was added to a security-enabled global group
// 4732 - A member was added to a security-enabled local group
// 4756 - A member was added to a security-enabled universal group  
| where EventID in ("4728", "4732", "4756")
| where AccountType =~ "User"
// Exclude Remote Desktop Users group: S-1-5-32-555
| where TargetSid !in ("S-1-5-32-555")
| where TargetSid matches regex WellKnownLocalSID or TargetSid matches regex WellKnownGroupSID
| project GroupAddTime = TimeGenerated, GroupAddEventID = EventID, GroupAddActivity = Activity, GroupAddComputer = Computer, 
GroupAddTargetAccount = TargetAccount, GroupAddTargetUserName = TargetUserName, GroupAddTargetDomainName = TargetDomainName, GroupAddTargetSid = TargetSid, 
GroupAddSubjectAccount = SubjectAccount, GroupAddSubjectUserName = SubjectUserName, GroupAddSubjectDomainName = SubjectDomainName, GroupAddSubjectUserSid = SubjectUserSid, 
GroupSid = MemberSid
),
(
WindowsEvent 
// 4728 - A member was added to a security-enabled global group
// 4732 - A member was added to a security-enabled local group
// 4756 - A member was added to a security-enabled universal group  
| where EventID in ("4728", "4732", "4756")  and not(EventData has "S-1-5-32-555")
| extend SubjectUserSid = tostring(EventData.SubjectUserSid)
| extend Account = strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend AccountType=case(Account endswith "$" or SubjectUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(SubjectUserSid), "", "User")
| extend MemberName = tostring(EventData.MemberName)
| where AccountType =~ "User"
// Exclude Remote Desktop Users group: S-1-5-32-555
| extend TargetSid = tostring(EventData.TargetSid)
| where TargetSid !in ("S-1-5-32-555")
| where TargetSid matches regex WellKnownLocalSID or TargetSid matches regex WellKnownGroupSID
| extend TargetAccount = strcat(EventData.TargetDomainName,"\\", EventData.TargetUserName)
| extend MemberSid = tostring(EventData.MemberSid)
| extend Activity= "GroupAddActivity"
| project GroupAddTime = TimeGenerated, GroupAddEventID = EventID, GroupAddActivity = Activity, GroupAddComputer = Computer, 
GroupAddTargetAccount = TargetAccount, GroupAddTargetUserName = tostring(EventData.TargetUserName), GroupAddTargetDomainName = tostring(EventData.TargetDomainName), GroupAddTargetSid = TargetSid, 
GroupAddSubjectAccount = Account, GroupAddSubjectUserName = tostring(EventData.SubjectUserName), GroupAddSubjectDomainName = tostring(EventData.SubjectDomainName), GroupAddSubjectUserSid = SubjectUserSid, 
GroupSid = MemberSid
));
let GroupCreated = (union isfuzzy=true  
(SecurityEvent
// 4727 - A security-enabled global group was created
// 4731 - A security-enabled local group was created
// 4754 - A security-enabled universal group was created
| where EventID in ("4727", "4731", "4754")
| where AccountType =~ "User"
| project GroupCreateTime = TimeGenerated, GroupCreateEventID = EventID, GroupCreateActivity = Activity, GroupCreateComputer = Computer, 
GroupCreateTargetAccount = TargetAccount, GroupCreateTargetUserName = TargetUserName, GroupCreateTargetDomainName = TargetDomainName,
GroupCreateSubjectAccount = SubjectAccount, GroupCreateSubjectUserName = SubjectUserName, GroupCreateSubjectDomainName = SubjectDomainName, GroupCreateSubjectUserSid = SubjectUserSid, 
GroupSid = TargetSid
),
(WindowsEvent
// 4727 - A security-enabled global group was created
// 4731 - A security-enabled local group was created
// 4754 - A security-enabled universal group was created
| where EventID in ("4727", "4731", "4754")
| extend SubjectUserSid = tostring(EventData.SubjectUserSid)
| extend Account = strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend AccountType=case(Account endswith "$", "Machine", iff(SubjectUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", iff(isempty(SubjectUserSid), "", "User")))
| where AccountType =~ "User"
| extend TargetAccount = strcat(EventData.TargetDomainName,"\\", EventData.TargetUserName)
| extend SubjectAccount = strcat(EventData.SubjectDomainName,"\\", EventData.SubjectUserName)  
| extend TargetSid = tostring(EventData.TargetSid) 
| extend Activity= "GroupAddActivity"
| project GroupCreateTime = TimeGenerated, GroupCreateEventID = EventID, GroupCreateActivity = Activity, GroupCreateComputer = Computer, 
GroupCreateTargetAccount = TargetAccount, GroupCreateTargetUserName = tostring(EventData.TargetUserName), GroupCreateTargetDomainName = tostring(EventData.TargetDomainName), 
GroupCreateSubjectAccount = SubjectAccount, GroupCreateSubjectUserName = tostring(EventData.SubjectUserName), GroupCreateSubjectDomainName = tostring(EventData.SubjectDomainName),GroupCreateSubjectUserSid = SubjectUserSid, 
GroupSid = TargetSid
));
GroupCreated
| join (
GroupAddition
) on GroupSid
| extend GroupCreateHostName = tostring(split(GroupCreateComputer , ".")[0]), DomainIndex = toint(indexof(GroupCreateComputer , '.'))
| extend GroupCreateHostNameDomain = iff(DomainIndex != -1, substring(GroupCreateComputer , DomainIndex + 1), GroupCreateComputer)
| extend GroupAddHostName = tostring(split(GroupAddComputer , ".")[0]), DomainIndex = toint(indexof(GroupAddComputer , '.'))
| extend GroupAddHostNameDomain = iff(DomainIndex != -1, substring(GroupAddComputer , DomainIndex + 1), GroupAddComputer)
| project-away DomainIndex

let inviting_users = (AuditLogs
  | where TimeGenerated between(ago(14d)..ago(1d))
  | where OperationName =~ "Invite external user"
  | where Result =~ "success"
  | extend InitiatingUserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
  | where isnotempty(InitiatingUserPrincipalName)
  | summarize by InitiatingUserPrincipalName);
  AuditLogs
  | where TimeGenerated > ago(1d)
  | where OperationName =~ "Invite external user"
  | where Result =~ "success"
  | extend InitiatingUserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
  | where isnotempty(InitiatingUserPrincipalName) and InitiatingUserPrincipalName !in (inviting_users)
  | extend TargetUserPrincipalName = tostring(TargetResources[0].userPrincipalName)
  | extend TargetAadUserId = tostring(TargetResources[0].id)
  | extend invitingUser = InitiatingUserPrincipalName, invitedUserPrincipalName = TargetUserPrincipalName
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | extend TargetAccountName = tostring(split(TargetUserPrincipalName, "@")[0]), TargetAccountUPNSuffix = tostring(split(TargetUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, OperationName, Result, TargetUserPrincipalName, TargetAadUserId, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIPAddress

//Finds computers that haven't sent a heartbeat in the last 30 days

//Data connector required for this query - Heartbeat (created automatically when you onboard machines to Sentinel)

Heartbeat
| where TimeGenerated > ago(365d)
| summarize arg_max(TimeGenerated, *) by Computer
| project
    Computer,
    ['Last Heartbeat']=TimeGenerated,
    ['Days Since Last Heartbeat']=datetime_diff("day", now(), TimeGenerated)
| where ['Days Since Last Heartbeat'] > 30
| sort by ['Days Since Last Heartbeat'] desc

//Visualize distinct computers per month sending data

//Data connector required for this query - Heartbeat (created automatically when you onboard machines to Sentinel)

Heartbeat
| where TimeGenerated > ago(365d)
| summarize Count=dcount(Computer)by Month=startofmonth(TimeGenerated)
| render columnchart with (title="Distinct monthly computers sending data to Microsoft Sentinel")

let timeBin = 10m;
let portThreshold = 30;
W3CIISLog
| extend scStatusFull = strcat(scStatus, ".",scSubStatus)
// Map common IIS codes
| extend scStatusFull_Friendly = case(
scStatusFull == "401.0", "Access denied.",
scStatusFull == "401.1", "Logon failed.",
scStatusFull == "401.2", "Logon failed due to server configuration.",
scStatusFull == "401.3", "Unauthorized due to ACL on resource.",
scStatusFull == "401.4", "Authorization failed by filter.",
scStatusFull == "401.5", "Authorization failed by ISAPI/CGI application.",
scStatusFull == "403.0", "Forbidden.",
scStatusFull == "403.4", "SSL required.",
"See - https://support.microsoft.com/help/943891/the-http-status-code-in-iis-7-0-iis-7-5-and-iis-8-0")
// Mapping to Hex so can be mapped using website in comments above
| extend scWin32Status_Hex = tohex(tolong(scWin32Status))
// Map common win32 codes
| extend scWin32Status_Friendly = case(
scWin32Status_Hex =~ "775", "The referenced account is currently locked out and cannot be logged on to.",
scWin32Status_Hex =~ "52e", "Logon failure: Unknown user name or bad password.",
scWin32Status_Hex =~ "532", "Logon failure: The specified account password has expired.",
scWin32Status_Hex =~ "533", "Logon failure: Account currently disabled.",
scWin32Status_Hex =~ "2ee2", "The request has timed out.",
scWin32Status_Hex =~ "0", "The operation completed successfully.",
scWin32Status_Hex =~ "1", "Incorrect function.",
scWin32Status_Hex =~ "2", "The system cannot find the file specified.",
scWin32Status_Hex =~ "3", "The system cannot find the path specified.",
scWin32Status_Hex =~ "4", "The system cannot open the file.",
scWin32Status_Hex =~ "5", "Access is denied.",
scWin32Status_Hex =~ "8009030e", "SEC_E_NO_CREDENTIALS",
scWin32Status_Hex =~ "8009030C", "SEC_E_LOGON_DENIED",
"See - https://msdn.microsoft.com/library/cc231199.aspx")
// decode URI when available
| extend decodedUriQuery = url_decode(csUriQuery)
// Count of attempts by client IP on many ports
| summarize makeset(sPort), makeset(decodedUriQuery), makeset(csUserName), makeset(sSiteName), makeset(sPort), makeset(csUserAgent), makeset(csMethod), makeset(csUriQuery), makeset(scStatusFull), makeset(scStatusFull_Friendly), makeset(scWin32Status_Hex), makeset(scWin32Status_Friendly), ConnectionsCount = count() by bin(TimeGenerated, timeBin), cIP, Computer, sIP
| extend portCount = arraylength(set_sPort)
| where portCount >= portThreshold
| project TimeGenerated, cIP, set_sPort, set_csUserName, set_decodedUriQuery, Computer, set_sSiteName, sIP, set_csUserAgent, set_csMethod, set_scStatusFull, set_scStatusFull_Friendly, set_scWin32Status_Hex, set_scWin32Status_Friendly, ConnectionsCount, portCount
| order by portCount

let timeBin = 1m;
let failedThreshold = 20;
W3CIISLog
| where scStatus in ("401","403")
| where csUserName != "-"
| extend scStatusFull = strcat(scStatus, ".",scSubStatus)
// Map common IIS codes
| extend scStatusFull_Friendly = case(
scStatusFull == "401.0", "Access denied.",
scStatusFull == "401.1", "Logon failed.",
scStatusFull == "401.2", "Logon failed due to server configuration.",
scStatusFull == "401.3", "Unauthorized due to ACL on resource.",
scStatusFull == "401.4", "Authorization failed by filter.",
scStatusFull == "401.5", "Authorization failed by ISAPI/CGI application.",
scStatusFull == "403.0", "Forbidden.",
scStatusFull == "403.4", "SSL required.",
"See - https://support.microsoft.com/help/943891/the-http-status-code-in-iis-7-0-iis-7-5-and-iis-8-0")
// Mapping to Hex so can be mapped using website in comments above
| extend scWin32Status_Hex = tohex(tolong(scWin32Status))
// Map common win32 codes
| extend scWin32Status_Friendly = case(
scWin32Status_Hex =~ "775", "The referenced account is currently locked out and cannot be logged on to.",
scWin32Status_Hex =~ "52e", "Logon failure: Unknown user name or bad password.",
scWin32Status_Hex =~ "532", "Logon failure: The specified account password has expired.",
scWin32Status_Hex =~ "533", "Logon failure: Account currently disabled.",
scWin32Status_Hex =~ "2ee2", "The request has timed out.",
scWin32Status_Hex =~ "0", "The operation completed successfully.",
scWin32Status_Hex =~ "1", "Incorrect function.",
scWin32Status_Hex =~ "2", "The system cannot find the file specified.",
scWin32Status_Hex =~ "3", "The system cannot find the path specified.",
scWin32Status_Hex =~ "4", "The system cannot open the file.",
scWin32Status_Hex =~ "5", "Access is denied.",
scWin32Status_Hex =~ "8009030e", "SEC_E_NO_CREDENTIALS",
scWin32Status_Hex =~ "8009030C", "SEC_E_LOGON_DENIED",
"See - https://msdn.microsoft.com/library/cc231199.aspx")
// decode URI when available
| extend decodedUriQuery = url_decode(csUriQuery)
// Count of failed attempts from same client IP
| summarize makeset(decodedUriQuery), makeset(csUserName), makeset(sSiteName), makeset(sPort), makeset(csUserAgent), makeset(csMethod), makeset(csUriQuery), makeset(scStatusFull), makeset(scStatusFull_Friendly), makeset(scWin32Status_Hex), makeset(scWin32Status_Friendly), FailedConnectionsCount = count() by bin(TimeGenerated, timeBin), cIP, Computer, sIP
| where FailedConnectionsCount >= failedThreshold
| project TimeGenerated, cIP, set_csUserName, set_decodedUriQuery, Computer, set_sSiteName, sIP, set_sPort, set_csUserAgent, set_csMethod, set_scStatusFull, set_scStatusFull_Friendly, set_scWin32Status_Hex, set_scWin32Status_Friendly, FailedConnectionsCount
| order by FailedConnectionsCount
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)

let timeBin = 10m;
let failedThreshold = 100;
W3CIISLog
| where scStatus in ("401","403")
| where csUserName != "-"
// Handling Exchange specific items in IIS logs to remove the unique log identifier in the URI
| extend csUriQuery = iff(csUriQuery startswith "MailboxId=", tostring(split(csUriQuery, "&")[0]) , csUriQuery )
| extend csUriQuery = iff(csUriQuery startswith "X-ARR-CACHE-HIT=", strcat(tostring(split(csUriQuery, "&")[0]),tostring(split(csUriQuery, "&")[1])) , csUriQuery )
| extend scStatusFull = strcat(scStatus, ".",scSubStatus)
// Map common IIS codes
| extend scStatusFull_Friendly = case(
scStatusFull == "401.0", "Access denied.",
scStatusFull == "401.1", "Logon failed.",
scStatusFull == "401.2", "Logon failed due to server configuration.",
scStatusFull == "401.3", "Unauthorized due to ACL on resource.",
scStatusFull == "401.4", "Authorization failed by filter.",
scStatusFull == "401.5", "Authorization failed by ISAPI/CGI application.",
scStatusFull == "403.0", "Forbidden.",
scStatusFull == "403.4", "SSL required.",
"See - https://support.microsoft.com/help/943891/the-http-status-code-in-iis-7-0-iis-7-5-and-iis-8-0")
// Mapping to Hex so can be mapped using website in comments above
| extend scWin32Status_Hex = tohex(tolong(scWin32Status))
// Map common win32 codes
| extend scWin32Status_Friendly = case(
scWin32Status_Hex =~ "775", "The referenced account is currently locked out and cannot be logged on to.",
scWin32Status_Hex =~ "52e", "Logon failure: Unknown user name or bad password.",
scWin32Status_Hex =~ "532", "Logon failure: The specified account password has expired.",
scWin32Status_Hex =~ "533", "Logon failure: Account currently disabled.",
scWin32Status_Hex =~ "2ee2", "The request has timed out.",
scWin32Status_Hex =~ "0", "The operation completed successfully.",
scWin32Status_Hex =~ "1", "Incorrect function.",
scWin32Status_Hex =~ "2", "The system cannot find the file specified.",
scWin32Status_Hex =~ "3", "The system cannot find the path specified.",
scWin32Status_Hex =~ "4", "The system cannot open the file.",
scWin32Status_Hex =~ "5", "Access is denied.",
scWin32Status_Hex =~ "8009030e", "SEC_E_NO_CREDENTIALS",
scWin32Status_Hex =~ "8009030C", "SEC_E_LOGON_DENIED",
"See - https://msdn.microsoft.com/library/cc231199.aspx")
// decode URI when available
| extend decodedUriQuery = url_decode(csUriQuery)
// Count of failed logons by a user
| summarize makeset(decodedUriQuery), makeset(cIP), makeset(sSiteName), makeset(sPort), makeset(csUserAgent), makeset(csMethod), makeset(csUriQuery), makeset(scStatusFull), makeset(scStatusFull_Friendly), makeset(scWin32Status_Hex), makeset(scWin32Status_Friendly), FailedConnectionsCount = count() by bin(TimeGenerated, timeBin), csUserName, Computer, sIP
| where FailedConnectionsCount >= failedThreshold
| project TimeGenerated, csUserName, set_decodedUriQuery, Computer, set_sSiteName, sIP, set_cIP, set_sPort, set_csUserAgent, set_csMethod, set_scStatusFull, set_scStatusFull_Friendly, set_scWin32Status_Hex, set_scWin32Status_Friendly, FailedConnectionsCount
| order by FailedConnectionsCount
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend AccountName = tostring(split(csUserName, "@")[0]), AccountUPNSuffix = tostring(split(csUserName, "@")[1])

let threshold = 100;
let timeRange = ago(7d);
let timeBuffer = 1;
SigninLogs 
| where TimeGenerated > timeRange
| where ResultType == "50057" 
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), disabledAccountLoginAttempts = count(), 
disabledAccountsTargeted = dcount(UserPrincipalName), applicationsTargeted = dcount(AppDisplayName), disabledAccountSet = make_set(UserPrincipalName), 
applicationSet = make_set(AppDisplayName) by IPAddress, AppId
| order by disabledAccountLoginAttempts desc
| join kind=inner (
    // IPs are considered suspicious - and any related successful sign-ins are detected
    SigninLogs
    | where TimeGenerated > timeRange
    | where ResultType == 0
    | summarize successSigninStart = min(TimeGenerated), successSigninEnd = max(TimeGenerated), successfulAccountSigninCount = dcount(UserPrincipalName), successfulAccountSigninSet = make_set(UserPrincipalName, 15) by IPAddress
    // Assume IPs associated with sign-ins from 100+ distinct user accounts are safe
    | where successfulAccountSigninCount < threshold
) on IPAddress  
// IPs where attempts to authenticate as disabled user accounts originated, and had a non-zero success rate for some other account
| where successfulAccountSigninCount != 0
// Successful Account Signins occur within the same lookback period as the failed 
| extend SuccessBeforeFailure = iff(successSigninStart >= StartTime and successSigninEnd <= EndTime, true, false)  
| project StartTime, EndTime, IPAddress, disabledAccountLoginAttempts, disabledAccountsTargeted, disabledAccountSet, applicationSet, 
successfulAccountSigninCount, successfulAccountSigninSet, successSigninStart, successSigninEnd, AppId
| order by disabledAccountLoginAttempts
// Break up the string of Succesfully signed into accounts into individual events
| mvexpand successfulAccountSigninSet
| extend JoinedOnIp = IPAddress
| join kind = inner (
      OfficeActivity
    | where TimeGenerated > timeRange
    | where Operation in~ ( "Add-MailboxPermission", "Add-MailboxFolderPermission", "Set-Mailbox", "New-ManagementRoleAssignment", "New-InboxRule", "Set-InboxRule", "Set-TransportRule") and not(UserId has_any ('NT AUTHORITY\\SYSTEM (Microsoft.Exchange.ServiceHost)', 'NT AUTHORITY\\SYSTEM (w3wp)', 'devilfish-applicationaccount'))
    // Remove port from the end of the IP and/or square brackets around IP, if they exist 
    | extend JoinedOnIp = case(
      ClientIP matches regex @'\[((25[0-5]2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]2[0-4][0-9]|[01]?[0-9][0-9]?)\]-\d{1,5}', tostring(extract('\\[([0-9]+\\.[0-9]+\\.[0-9]+)\\]-[0-9]+', 1, ClientIP)),
      ClientIP matches regex @'\[((25[0-5]2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]2[0-4][0-9]|[01]?[0-9][0-9]?)\]', tostring(extract('\\[([0-9]+\\.[0-9]+\\.[0-9]+)\\]', 1, ClientIP)),  
      ClientIP matches regex @'(((25[0-5]2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]2[0-4][0-9]|[01]?[0-9][0-9]?))-\d{1,5}', tostring(extract('([0-9]+\\.[0-9]+\\.[0-9]+)-[0-9]+', 1, ClientIP)),
      ClientIP matches regex @'((25[0-5]2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]2[0-4][0-9]|[01]?[0-9][0-9]?)', ClientIP,       
      ClientIP matches regex @'\[((?:[0-9a-fA-F]{1,4}::?){1,8}[0-9a-fA-F]{1,4}|\d{1,3}(?:\.\d{1,3}){3})\]-\d{1,5}', tostring(extract('\\[((?:[0-9a-fA-F]{1,4}::?){1,8}[0-9a-fA-F]{1,4}|\\d{1,3}(?:\\.\\d{1,3}){3})\\]-[0-9]+', 1, ClientIP)),
      ClientIP matches regex @'\[((?:[0-9a-fA-F]{1,4}::?){1,8}[0-9a-fA-F]{1,4}|\d{1,3}(?:\.\d{1,3}){3})\]', tostring(extract('\\[((?:[0-9a-fA-F]{1,4}::?){1,8}[0-9a-fA-F]{1,4}|\\d{1,3}(?:\\.\\d{1,3}){3})\\]', 1, ClientIP)),  
      ClientIP matches regex @'((?:[0-9a-fA-F]{1,4}::?){1,8}[0-9a-fA-F]{1,4}|\d{1,3}(?:\.\d{1,3}){3})-\d{1,5}', tostring(extract('((?:[0-9a-fA-F]{1,4}::?){1,8}[0-9a-fA-F]{1,4}|\\d{1,3}(?:\\.\\d{1,3}){3})-[0-9]+', 1, ClientIP)),
      ClientIP matches regex @'((?:[0-9a-fA-F]{1,4}::?){1,8}[0-9a-fA-F]{1,4}|\d{1,3}(?:\.\d{1,3}){3})', ClientIP,
      "")
    | where isnotempty(JoinedOnIp)
    | extend OfficeTimeStamp = ElevationTime, UserPrincipalName = UserId
) on JoinedOnIp
// Rare and risky operations only happen within a certain time range of the successful sign-in
| where OfficeTimeStamp >= successSigninStart and datetime_diff('day', OfficeTimeStamp, successSigninEnd) <= timeBuffer
| extend AccountName = tostring(split(UserPrincipalName, "@")[0]), AccountUPNSuffix = tostring(split(UserPrincipalName, "@")[1])

// Extracts plaintext IPv4 addresses
let ipv4_plaintext_extraction_regex = @"((?:(?:[0-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])(?:\.)){3}(?:[0-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5]){1,3})";
// Identified base64 encoded IPv4 addresses
let ipv4_encoded_identification_regex = @"\=([a-zA-Z0-9\/\+]*(?:(?:MC|Au|wL|MS|Eu|xL|Mi|Iu|yL|My|Mu|zL|NC|Qu|0L|NS|Uu|1L|Ni|Yu|2L|Ny|cu|3L|OC|gu|4L|OS|ku|5L){1}[a-zA-Z0-9\/\+]{2,4}){3}[a-zA-Z0-9\/\+\=]*)";
// Extractes IPv4 addresses as hex values
let ipv4_decoded_hex_extract = @"((?:(?:61|62|63|64|65|66|67|68|69|6a|6b|6c|6d|6e|6f|70|71|72|73|74|75|76|77|78|79|7a|41|42|43|44|45|46|47|48|49|4a|4b|4c|4d|4e|4f|50|51|52|53|54|55|56|57|58|59|5a|2f|2b|3d),){7,15})";
CommonSecurityLog
| where isnotempty(RequestURL)
// Identify requests with encoded IPv4 addresses
| where RequestURL matches regex ipv4_encoded_identification_regex
| project TimeGenerated, RequestURL
// Extract IP candidates in their base64 encoded format, significantly reducing the dataset
| extend extracted_encoded_ip_candidate = extract_all(ipv4_encoded_identification_regex, RequestURL)
// We could have more than one candidate, expand them out
| mv-expand extracted_encoded_ip_candidate to typeof(string)
| summarize Start=min(TimeGenerated), End=max(TimeGenerated), make_set(RequestURL) by extracted_encoded_ip_candidate
// Pad if we need to
| extend extracted_encoded_ip_candidate = iff(strlen(extracted_encoded_ip_candidate) % 2 == 0, extracted_encoded_ip_candidate, strcat(extracted_encoded_ip_candidate, "="))
// Now decode the candidate to a long array, we cannot go straight to string as it cannot handle non-UTF8, we need to strip that first
| extend extracted_encoded_ip_candidate = tostring(base64_decode_toarray(extracted_encoded_ip_candidate))
// Extract the IP candidates from the array
| extend hex_extracted = extract_all(ipv4_decoded_hex_extract, extracted_encoded_ip_candidate)
// Expand, it's still possible that we might have more than 1 IP
| mv-expand hex_extracted
// Now we should have a clean string. We need to put it back into a dynamic array to convert back to a string.
| extend hex_extracted = trim_end(",", tostring(hex_extracted))
| extend hex_extracted = strcat("[",hex_extracted,"]")
| extend hex_extracted = todynamic(hex_extracted)
| extend extracted_encoded_ip_candidate = todynamic(extracted_encoded_ip_candidate)
// Convert the array back into a string
| extend decoded_ip_candidate = make_string(hex_extracted)
| summarize by decoded_ip_candidate, tostring(set_RequestURL), Start, End
// Now the IP candidates will be in plaintext, extract the IPs using a regex
| extend ipmatch = extract_all(ipv4_plaintext_extraction_regex, decoded_ip_candidate)
// If it's not an IP, throw it out
| where isnotnull(ipmatch)
| mv-expand ipmatch to typeof(string)
// Join with DeviceNetworkEvents to find instances where an IP of a machine in our MDE estate sent it's IP in a base64 encoded string
| join (
    DeviceNetworkEvents
    | summarize make_set(DeviceId), make_set(DeviceName) by RemoteIP
) on $left.ipmatch == $right.RemoteIP
| project Start, End, IPmatch=ipmatch, RequestURL=set_RequestURL, DeviceNames=set_DeviceName, DeviceIds=set_DeviceId, RemoteIP

//Set a threshold of failed AAD signins from an IP address within 1 day above which we want to deem those logins suspicious.
let signin_threshold = 5; 
//Make a list of IPs with AAD signin failures above our threshold.
let aadFunc = (tableName:string){
let suspicious_signins = 
    table(tableName)
    //Looking for logon failure results
    | where ResultType !in ("0", "50125", "50140")
    //Exclude localhost addresses to reduce the chance of FPs
    | where IPAddress !in ("127.0.0.1", "::1")
    | summarize count() by IPAddress
    | where count_ >  signin_threshold
    | summarize make_set(IPAddress);
    suspicious_signins
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
let suspicious_signins = 
union isfuzzy=true aadSignin, aadNonInt
| summarize make_set(set_IPAddress);
//See if any of those IPs have sucessfully logged into PA VPNs during the same timeperiod
CommonSecurityLog
    //Select only PA VPN sucessful logons
    | where DeviceVendor == "Palo Alto Networks" and DeviceEventClassID == "globalprotect"
    | where Message has "GlobalProtect gateway user authentication succeeded"
    //Parse out the logon source IP from the Message field to match on
    | extend SourceIP = extract("Login from: ([^,]+)", 1, Message) 
    | where SourceIP in (suspicious_signins)
    | extend Reason = "Multiple failed AAD logins from SourceIP"
    //Parse out other useful information from Message field
    | extend User = extract('User name: ([^,]+)', 1, Message) 
    | extend ClientOS = extract('Client OS version: ([^,\"]+)', 1, Message)
    | extend Location = extract('Source region: ([^,]{2})',1, Message)
    | project TimeGenerated, Reason, SourceIP, User, ClientOS, Location, Message, DeviceName, ReceiptTime, DeviceVendor, DeviceEventClassID, Computer, FileName
    | extend timestamp = TimeGenerated

// Detects when a user downgrades a label on a file and that file is then copied to USB

//Data connector required for this query - Azure Information Protection
//Data connector required for this query - M365 Defender - Device* tables

// Timeframe = the time between the label downgrade and file copy event
let timeframe=4h;
InformationProtectionEvents
| where Time > ago(1d)
| where Activity == "DowngradeLabel"
| project LabelChangeTime=Time, User, FileDowngraded=ItemName
| join kind=inner (
    DeviceEvents
    | where TimeGenerated > ago(1d)
    | where ActionType == "UsbDriveMounted"
    | extend DriveLetter = tostring(todynamic(AdditionalFields).DriveLetter)
    | join kind=inner (DeviceFileEvents
        | where TimeGenerated > ago(1d)
        | project TimeGenerated, ActionType, FileName, FolderPath, DeviceId, DeviceName
        | extend FileCopyTime = TimeGenerated
        | where ActionType == "FileCreated"
        | extend FileCopyName = FileName
        | parse FolderPath with DriveLetter '\\' *
        | extend DriveLetter = tostring(DriveLetter)
        )
        on DeviceId, DriveLetter) 
    on $left.FileDowngraded == $right.FileCopyName
| project LabelChangeTime, FileCopyTime, FileDowngraded, DeviceName, AccountName
| where (FileCopyTime - LabelChangeTime) between (0min .. timeframe)

// Detects when a user downgrades a label on a file and that file is then emailed outbound 

//Data connector required for this query - Azure Information Protection
//Data connector required for this query - M365 Defender - Email* tables

// Starttime = the amount of data to look back on
// Timeframe = the time between the label downgrade and email event
let starttime=7d;
let timeframe=4h;
InformationProtectionEvents
| where Time > ago(starttime)
| where Activity == "DowngradeLabel"
| project LabelChangeTime=Time, User, FileName=ItemName
| join kind=inner(
    EmailEvents
    | where EmailDirection == "Outbound"
    | project
        TimeGenerated,
        SenderMailFromAddress,
        RecipientEmailAddress,
        EmailDirection,
        NetworkMessageId
    | join kind=inner (EmailAttachmentInfo) on NetworkMessageId
    | project
        TimeGenerated,
        SenderMailFromAddress,
        RecipientEmailAddress,
        EmailDirection,
        FileName
    )
    on FileName
| project
    LabelChangeTime,
    EmailSendTime=TimeGenerated,
    SenderMailFromAddress,
    RecipientEmailAddress,
    EmailDirection,
    FileName
| where (EmailSendTime - LabelChangeTime) between (0min .. timeframe)

(union isfuzzy=true
(SecurityEvent
| where EventID == 4688
| where Process has_any ("powershell.exe","powershell_ise.exe","pwsh.exe") and CommandLine has_cs "-exec bypass -w 1 -enc"
| where CommandLine  contains_cs "UwB0AGEAcgB0AC0ASgBvAGIAIAAtAFMAYwByAGkAcAB0AEIAbABvAGMAawAgAHsAKABzAGEAcABzACAAKAAiAHAA"
| extend DvcHostname = Computer, ProcessId = tostring(ProcessId), ActorUsername = Account
),
(DeviceProcessEvents
| where FileName =~ "powershell.exe" and ProcessCommandLine has_cs "-exec bypass -w 1 -enc"
| where ProcessCommandLine contains_cs "UwB0AGEAcgB0AC0ASgBvAGIAIAAtAFMAYwByAGkAcAB0AEIAbABvAGMAawAgAHsAKABzAGEAcABzACAAKAAiAHAA"
| extend DvcHostname = DeviceName, ProcessId = tostring(InitiatingProcessId), ActorUsername = strcat(AccountDomain, @"\", AccountName)
),
(imProcessCreate
| where Process has_any ("powershell.exe","powershell_ise.exe","pwsh.exe") and CommandLine has_cs "-exec bypass -w 1 -enc"
| where CommandLine  contains_cs "UwB0AGEAcgB0AC0ASgBvAGIAIAAtAFMAYwByAGkAcAB0AEIAbABvAGMAawAgAHsAKABzAGEAcABzACAAKAAiAHAA"
| extend ProcessId = tostring(TargetProcessId)
)
)
| extend AccountName = tostring(split(ActorUsername, "\\")[0]), AccountNTDomain = tostring(split(ActorUsername, "\\")[1])
| extend HostName = tostring(split(DvcHostname, ".")[0]), DomainIndex = toint(indexof(DvcHostname, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(DvcHostname, DomainIndex + 1), DvcHostname)

//This query will hunt for real time risk events flagged as medium or high that aren't confirmed safe by Microsoft and then enrich that data with information from the IdentityInfo table

//Data connector required for this query - Azure Active Directory - AAD User Risk Events
//Data connector required for this query - Azure Active Directory - Signin Logs
//Data connector required for this query - Microsoft Sentinel UEBA

let id=
    IdentityInfo
    | summarize arg_max(TimeGenerated, *) by AccountUPN;
let signin=
    SigninLogs
    | where TimeGenerated > ago (14d)
    | where RiskLevelDuringSignIn in ('high', 'medium')
    | join kind=inner id on $left.UserPrincipalName == $right.AccountUPN
    | extend SigninTime = TimeGenerated
    | where RiskEventTypes_V2 != "[]";
AADUserRiskEvents
| where TimeGenerated > ago (14d)
| extend RiskTime = TimeGenerated
| where DetectionTimingType == "realtime"
| where RiskDetail !has "aiConfirmedSigninSafe"
| join kind=inner signin on CorrelationId
| extend TimeDelta = abs(SigninTime - RiskTime)
| project
    SigninTime,
    UserPrincipalName,
    RiskTime,
    TimeDelta,
    RiskEventTypes,
    RiskLevelDuringSignIn,
    City,
    Country,
    EmployeeId,
    AssignedRoles

//Detects when an admin changes the authentication phone details for another user

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

AuditLogs
| where OperationName == "Admin updated security info"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend Target = tostring(TargetResources[0].userPrincipalName)
| extend ['New Phone Number'] = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[2].newValue)))
| extend ['Old Phone Number'] = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[2].oldValue)))
| project TimeGenerated, Actor, Target, ['New Phone Number'], ['Old Phone Number']

//Alert when Azure AD guest accounts are denied access (either by Conditional Access or because they aren't granted specific access) to multiple applications in a short time period
//This query uses 3 or more applications within an hour

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

//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago (7d)
| where UserType == "Guest"
| where ResultType in ("53003", "50105")
| summarize
    ['Application Count']=dcount(AppDisplayName),
    ['Application List']=make_set(AppDisplayName)
    by UserPrincipalName, bin(TimeGenerated, 1h)
| where ['Application Count'] >= 3

//Advanced Hunting query

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

AADSignInEventsBeta
| where Timestamp > ago (7d)
| where IsGuestUser == 1
| where ErrorCode  in ("53003", "50105")
| summarize
    ['Application Count']=dcount(Application),
    ['Application List']=make_set(Application)
    by AccountUpn, bin(Timestamp, 1h)
| where ['Application Count'] >= 3

//Query to find security alerts for users who have privileged Azure AD roles

//Data connector required for this query - Microsoft Sentinel UEBA
//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)

let PrivRoles = dynamic(["Global Administrator", "Security Administrator", "Teams Administrator"]);
let identityinfo=
    IdentityInfo
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    | where AssignedRoles has_any (PrivRoles)
    | extend TargetUserName = AccountName
    | extend UserPrincipalName = AccountUPN
    | project TargetUserName, UserPrincipalName, AssignedRoles;
SecurityAlert
| where TimeGenerated >= ago(5d)
| extend AlertTime = TimeGenerated
| extend UserPrincipalName = CompromisedEntity
| join kind=inner identityinfo on UserPrincipalName
| project AlertTime, TargetUserName, UserPrincipalName, AlertName, AssignedRoles

//Detect anomalies in the amount of conditional access failures by users in your tenant, then visualize those conditional access failures

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

//Starttime and endtime = which period of data to look at, i.e from 21 days ago until today.
let startdate=21d;
let enddate=1d;
//Timeframe = time period to break the data up into, i.e 1 hour blocks.
let timeframe=1h;
//Sensitivity = the lower the number the more sensitive the anomaly detection is, i.e it will find more anomalies, default is 1.5
let sensitivity=2;
//Threshold = set this to tune out low count anomalies, i.e when total failures for a user doubles from 1 to 2
let threshold=5;
let outlierusers=
SigninLogs
| where TimeGenerated between (startofday(ago(startdate))..startofday(ago(enddate)))
| where ResultType == "53003"
| project TimeGenerated, ResultType, UserPrincipalName
| make-series CAFailureCount=count() on TimeGenerated from startofday(ago(startdate)) to startofday(ago(enddate)) step timeframe by UserPrincipalName 
| extend outliers=series_decompose_anomalies(CAFailureCount, sensitivity)
| mv-expand TimeGenerated, CAFailureCount, outliers
| where outliers == 1 and CAFailureCount > threshold
| distinct UserPrincipalName;
//Optionally visualize the anomalies
SigninLogs
| where TimeGenerated between (startofday(ago(startdate))..startofday(ago(enddate)))
| where ResultType == "53003"
| project TimeGenerated, ResultType, UserPrincipalName
| where UserPrincipalName in (outlierusers)
| summarize CAFailures=count()by UserPrincipalName, bin(TimeGenerated, timeframe)
| render timechart with (ytitle="Failure Count",title="Anomalous Conditional Access Failures")

//Creates a list of your applications and summarizes successful signins by members vs guests separated to total and distinct signins

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

SigninLogs
| where TimeGenerated > ago(30d)
| project TimeGenerated, UserType, ResultType, AppDisplayName, UserPrincipalName
| where ResultType == 0
| summarize
    ['Total Member Signins']=countif(UserType == "Member"),
    ['Distinct Member Signins']=dcountif(UserPrincipalName, UserType == "Member"),
    ['Total Guest Signins']=countif(UserType == "Guest"),
    ['Distinct Guest Signins']=dcountif(UserPrincipalName, UserType == "Guest")
    by AppDisplayName
| sort by AppDisplayName asc

//Query to find users who have access to an application but haven't signed in for 90 days

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

let signins=
SigninLogs
| where TimeGenerated > ago (90d)
| where AppDisplayName has "Application Name"
| project TimeGenerated, UserPrincipalName, AppDisplayName;
IdentityInfo
| where TimeGenerated > ago (21d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| extend UserPrincipalName = AccountUPN
| where GroupMembership contains "Group that gives access to Application"
| join kind=leftanti signins on UserPrincipalName
| project UserPrincipalName

//Find Azure AD applications that have more guests than members accessing them

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

//Microsoft Sentinel Query
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == "0"
| summarize Guests=dcountif(UserPrincipalName,UserType == "Guest"), Members=dcountif(UserPrincipalName,UserType == "Member") by AppDisplayName
| where Guests > Members
| sort by Guests desc 

//Advanced Hunting query

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

AADSignInEventsBeta
| where Timestamp > ago(30d)
| where LogonType == @"[""interactiveUser""]"
| where ErrorCode == "0"
| summarize Guests=dcountif(AccountUpn,IsGuestUser == "true"), Members=dcountif(AccountUpn,IsGuestUser == "false") by Application
| where Guests > Members
| sort by Guests desc

//Find the applications that have the most privileged users accessing them using only single factor authentication

//Data connector required for this query - Azure Active Directory - Signin 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;
SigninLogs
| where TimeGenerated > ago(30d)
| where UserPrincipalName in (privusers)
| where ResultType == 0
| where AuthenticationRequirement == "singleFactorAuthentication"
| summarize
    ['List of Users']=make_set(UserPrincipalName),
    ['Count of Users']=dcount(UserPrincipalName)
    by AppDisplayName
| sort by ['Count of Users'] desc

// Calculate the percentage of sign ins requiring authentication strengths, MFA and single factor auth to all of your applications

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

SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| summarize
    ['Total Signin Count']=count(),
    ['Total Authentication Strength Count']=countif(AuthenticationRequirementPolicies has 'Authentication Strength(s)' and AuthenticationRequirement == "multiFactorAuthentication"),
    ['Total MFA Count']=countif(AuthenticationRequirementPolicies !has 'Authentication Strength(s)' and AuthenticationRequirement == "multiFactorAuthentication"),
    ['Total non MFA Count']=countif(AuthenticationRequirement == "singleFactorAuthentication")
    by AppDisplayName
| project
    AppDisplayName,
    ['Total Signin Count'],
    ['Total Authentication Strength Count'],
    ['Authentication Strength Percentage']=(todouble(['Total Authentication Strength Count']) * 100 / todouble(['Total Signin Count'])),
    ['Total MFA Count'],
    ['MFA Percentage']=(todouble(['Total MFA Count']) * 100 / todouble(['Total Signin Count'])),
    ['Total non MFA Count'],
    ['Non MFA Percentage']=(todouble(['Total non MFA Count']) * 100 / todouble(['Total Signin Count']))
| sort by ['Total Signin Count'] desc

//Find any CA policies that are not actively in use (no success or failure events)

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

//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(180d)
| where UserType == "Member"
| mv-expand todynamic(ConditionalAccessPolicies)
| extend CAResult=tostring(ConditionalAccessPolicies.result), CAName=tostring(ConditionalAccessPolicies.displayName)
| summarize TotalCount=count(),ResultSet=make_set(CAResult) by CAName
| where not(ResultSet has_any ("success","failure"))
| sort by CAName asc

//Create a table of stats for your CA policies, showing which were successful, failed, not in use or applied and which are in report only mode

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

SigninLogs
| project ConditionalAccessPolicies
| mv-expand ConditionalAccessPolicies
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend ['Policy Name'] = tostring(ConditionalAccessPolicies.displayName)
| project CAResult, ['Policy Name']
| summarize
    TotalCount=count(),
    ['Total Success Count']=countif(CAResult == "success"),
    ['Total Failure Count']=countif(CAResult == "failure"),
    ['Total Not Enabled Count']=countif(CAResult == "notEnabled"),
    ['Total Not Applied Count']=countif(CAResult == "notApplied"),
    ['Total Report Only Count']=countif(CAResult startswith "reportOnly")
    by ['Policy Name']
| extend
    ['Failure Percentage'] = round(todouble(['Total Failure Count']) * 100 / todouble(TotalCount), 2),
    ['Success Percentage'] = round(todouble(['Total Success Count']) * 100 / todouble(TotalCount), 2),
    ['Not Enabled Percentage']=round(todouble(['Total Not Enabled Count']) * 100 / todouble(TotalCount), 2),
    ['Not Applied Percentage']=round(todouble(['Total Not Applied Count']) * 100 / todouble(TotalCount), 2),
    ['Report Only Percentage']=round(todouble(['Total Report Only Count']) * 100 / todouble(TotalCount), 2)
| project-reorder
    ['Policy Name'],
    TotalCount,
    ['Total Success Count'],
    ['Success Percentage'],
    ['Total Failure Count'],
    ['Failure Percentage'],
    ['Total Not Applied Count'],
    ['Not Applied Percentage'],
    ['Total Not Enabled Count'],
    ['Not Enabled Percentage'],
    ['Total Report Only Count'],
    ['Report Only Percentage']

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

AADSignInEventsBeta
| where LogonType == @"[""interactiveUser""]"
| project ConditionalAccessPolicies
| mv-expand ConditionalAccessPolicies=todynamic(ConditionalAccessPolicies)
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend ['Policy Name'] = tostring(ConditionalAccessPolicies.displayName)
| project CAResult, ['Policy Name']
| summarize
    TotalCount=count(),
    ['Total Success Count']=countif(CAResult == "success"),
    ['Total Failure Count']=countif(CAResult == "failure"),
    ['Total Not Enabled Count']=countif(CAResult == "notEnabled"),
    ['Total Not Applied Count']=countif(CAResult == "notApplied"),
    ['Total Report Only Count']=countif(CAResult startswith "reportOnly")
    by ['Policy Name']
| extend
    ['Failure Percentage'] = round(todouble(['Total Failure Count']) * 100 / todouble(TotalCount), 2),
    ['Success Percentage'] = round(todouble(['Total Success Count']) * 100 / todouble(TotalCount), 2),
    ['Not Enabled Percentage']=round(todouble(['Total Not Enabled Count']) * 100 / todouble(TotalCount), 2),
    ['Not Applied Percentage']=round(todouble(['Total Not Applied Count']) * 100 / todouble(TotalCount), 2),
    ['Report Only Percentage']=round(todouble(['Total Report Only Count']) * 100 / todouble(TotalCount), 2)
| project-reorder
    ['Policy Name'],
    TotalCount,
    ['Total Success Count'],
    ['Success Percentage'],
    ['Total Failure Count'],
    ['Failure Percentage'],
    ['Total Not Applied Count'],
    ['Not Applied Percentage'],
    ['Total Not Enabled Count'],
    ['Not Enabled Percentage'],
    ['Total Report Only Count'],
    ['Report Only Percentage']

//Calculate the percentage of signins to all your Azure AD apps considered risky. Those requiring single factor authentication, coming from an unknown location and from an unknown device

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

SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
| extend DeviceTrustType = tostring(DeviceDetail.trustType)
| summarize
    ['Total Signins']=count(),
    ['At Risk Signins']=countif(NetworkLocationDetails == '[]' and isempty(DeviceTrustType) and AuthenticationRequirement == "singleFactorAuthentication")
    by AppDisplayName
| extend ['At Risk Percentage']=(todouble(['At Risk Signins']) * 100 / todouble(['Total Signins']))