Splunk ESCU

//Parse the IP information from Security Alerts and find other users who have successfully signed on from the same IP addresses

//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)
//Data connector required for this query - Azure Active Directory - Signin Logs

SecurityAlert
| where ProviderName in ("MCAS", "IPC")
| extend x = todynamic(Entities)
| mv-expand x
| parse-where x with * '"Address":"' MaliciousIP '"' *
//Exclude any corporate or trusted IP addresses
| where MaliciousIP != "10.10.10.10"
| project AlertTime=TimeGenerated, MaliciousIP, CompromisedEntity
| join kind=inner
    (
    SigninLogs
    | where ResultType in ("0","53003","50158")
    )
    on $left.MaliciousIP == $right.IPAddress
| where CompromisedEntity != UserPrincipalName
| distinct UserPrincipalName, AppDisplayName, IPAddress, UserAgent, ResultType, ResultDescription

//Forecast the count of Azure AD Identity Protection Events events for the next 14 days based on the previous 30 days

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

SecurityAlert
| where ProviderName == "IPC"
| make-series ["Azure AD Identity Protection Events"]=count() on TimeGenerated from ago(30d) to now() + 14d step 1d
| extend ["Azure AD Identity Protection Events Forecast"] = series_decompose_forecast(['Azure AD Identity Protection Events'], toint(14d / 1d))
| render timechart

//When Defender for Endpoint detects malware in an ISO file retrieve the ISO file name, which directory it was found in and associated file hashes

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

SecurityAlert
| where ProviderName == "MDATP"
| where AlertName == "Malware was detected in an iso disc image file"
| mv-expand todynamic(Entities)
| extend Hashes = Entities.FileHashes
| mv-expand Hashes
| extend ['ISO File Name'] = tostring(Entities.Name)
| extend Directory = tostring(Entities.Directory)
| extend ['Hash Type'] = tostring(Hashes.Algorithm)
| extend Hash = tostring(Hashes.Value)
| where isnotempty(['ISO File Name'])
| project
    TimeGenerated,
    CompromisedEntity,
    ['ISO File Name'],
    Directory,
    ['Hash Type'],
    Hash

//Detect when a user or device triggers 3 or more unique alerts within a short time frame. This example uses a period of 4 hours

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

SecurityAlert
| where TimeGenerated > ago(1d)
| where isnotempty(CompromisedEntity) and CompromisedEntity != "CompromisedEntity"
| project TimeGenerated, ProviderName, AlertName, CompromisedEntity
| summarize
    ['Alert Names']=make_set(AlertName),
    ['Count of Unique Alerts']=dcount(AlertName)
    by CompromisedEntity, bin(TimeGenerated, 4h)
| where ['Count of Unique Alerts'] >= 3

//Detect when the same user or device triggers 3 or more low severity alerts in the space of a day

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

SecurityAlert
| where TimeGenerated > ago (7d)
| where AlertSeverity == "Low"
| summarize
    ['Count of low severity alerts']=dcount(AlertName),
    ['List of low severity alerts']=make_set(AlertName)
    by CompromisedEntity, bin(TimeGenerated, 1d)
| where ['Count of low severity alerts'] >= 3

//When Defender for Office 365 removes a malicious file from an email track down all device events with the same file hash

//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)
//Data connector required for this query - M365 Defender - Email* tables

let filehashes=
SecurityAlert
| where TimeGenerated > ago (7d)
| where ProviderName == "OATP"
| where AlertName == "Email messages containing malicious file removed after delivery​"
| mv-expand todynamic(Entities)
| extend Files = Entities.Files
| project Files
| mv-expand Files
| extend FileHashes = Files.FileHashes
| mv-expand FileHashes
| extend FileHash = tolower(tostring(FileHashes.Value))
| where isnotempty( FileHash)
| distinct FileHash;
DeviceFileEvents
    | where TimeGenerated > ago(7d)
    | project
        TimeGenerated,
        ActionType,
        FileName,
        DeviceName,
        SHA256,
        InitiatingProcessAccountUpn 
| where SHA256 in (filehashes)

//Calculate the percentage of alerts that are high or critical per product

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

SecurityAlert
| where TimeGenerated > ago(30d)
| summarize
    ['Total Alert Count']=count(),
    ['Total High or Critical Count']=countif(AlertSeverity in ("Critical", "High"))
    by ProductName
| extend Percentage=(todouble(['Total High or Critical Count']) * 100 / todouble(['Total Alert Count']))
| project-reorder ProductName, ['Total Alert Count'], ['Total High or Critical Count'], Percentage
| sort by Percentage desc

//When Defender for Cloud detects possible data transfer via DNS tunnel, use DNS logs to find any other devices that have queried the potentially malicious domain

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

let maliciousdomain=
    SecurityAlert
    | where AlertName contains "via DNS tunnel"
    | mv-expand todynamic(Entities)
    | project Entities
    | extend MaliciousDomain = tostring(Entities.DomainName)
    | where isnotempty(MaliciousDomain)
    | distinct MaliciousDomain;
DnsEvents
| where QueryType == "A"
| project Name, ClientIP
| where Name in~ (maliciousdomain)
| summarize ['List of Device IPs']=make_set(ClientIP) by Name

//When Defender for Cloud detects communication with a possible phishing domain, use Defedner logs to find any network connections to that same domain

//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)
//Data connector required for this query - M365 Defender - Device* tables

let domain=
    SecurityAlert
    | where TimeGenerated > ago (7d)
    | where AlertName startswith "Communication with possible phishing domain"
    | mv-expand todynamic(Entities)
    | extend DomainName = tostring(Entities.DomainName)
    | where isnotempty(DomainName)
    | distinct DomainName;
DeviceNetworkEvents
| where TimeGenerated > ago (7d)
| where RemoteUrl in~ (domain)
| project
    TimeGenerated,
    ActionType,
    DeviceName,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemoteUrl,
    RemotePort

//When a user is flagged for suspicious email sending patterns retrieve all the email they have sent around the time of the Alert

//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)
//Data connector required for this query - M365 Defender - Email* tables

SecurityAlert
| where TimeGenerated > ago (7d)
| where ProviderName == "OATP"
| where AlertName == "Suspicious email sending patterns detected"
| mv-expand todynamic(Entities)
| extend SenderFromAddress = tolower(tostring(Entities.MailboxPrimaryAddress))
| project AlertTime=TimeGenerated, SenderFromAddress
| join kind=inner (
    EmailEvents
    )
    on SenderFromAddress
| where EmailDirection == "Outbound"
| where TimeGenerated between ((AlertTime - timespan(1h)) .. (AlertTime + timespan(1h)))
| project TimeGenerated, Subject, AttachmentCount, RecipientEmailAddress

//When Defender for Cloud Apps detects a suspicious mailbox rule created, take that IP address and summarize sign in events for that user and IP address for the last 30 days.

//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)
//Data connector required for this query - Azure Active Directory - Signin Logs

//If they have only signed in a few times from that IP it may a sign the account has been compromised and a threat actor has added mailbox rule
let failureCodes = dynamic([50053, 50126, 50055]);
let successCodes = dynamic([0, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
SecurityAlert
| where TimeGenerated > ago(1d)
| where AlertName == "Suspicious inbox manipulation rule"
| extend IPAddress = tostring(parse_json(ExtendedProperties).["IP Addresses"])
| project ['Alert Time']=TimeGenerated, Description, IPAddress, UserPrincipalName=CompromisedEntity
| join kind=inner(
    SigninLogs
    | where TimeGenerated > ago (30d)
    )
    on UserPrincipalName, IPAddress
| project
    TimeGenerated,
    ['Alert Time'],
    Description,
    ResultType,
    UserPrincipalName,
    IPAddress,
    NetworkLocationDetails
| summarize
    ['Count of successful sign ins from MFA IP Address'] = countif(ResultType in(successCodes)),
    ['Count of failed sign ins from MFA IP Address'] = countif(ResultType in(failureCodes))
    by UserPrincipalName, Description, IPAddress, NetworkLocationDetails, ['Alert Time']

//When Defender for Identity detects suspected golden ticket usage, parse the relevant user accounts and host names

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

SecurityAlert
| where AlertName startswith "Suspected Golden Ticket usage"
| mv-expand todynamic(Entities)
| extend AccountName = tostring(Entities.Name)
| extend HostName = tostring(Entities.HostName)
| summarize
    Accounts=make_list_if(AccountName, isnotempty(AccountName)),
    Hosts=make_list_if(HostName, isnotempty(HostName))
    by VendorOriginalId

//Find the top 20 of a collection of varied data sets, no real detections in here just interesting data that is captured

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

//Top 20 alerts triggered
SecurityAlert
| where TimeGenerated > ago (30d)
| where ProviderName != "ASI Scheduled Alerts"
| summarize Count=count() by AlertName
| top 20 by Count

//Top 20 alerts high severity triggered
SecurityAlert
| where TimeGenerated > ago (30d)
| where ProviderName != "ASI Scheduled Alerts" and AlertSeverity == "High"
| summarize Count=count() by AlertName
| top 20 by Count

//Top 20 users generating identity alerts
SecurityAlert
| where TimeGenerated > ago (30d)
| where ProviderName in ("OATP","IPC","Azure Advanced Threat Protection","MCAS")
| summarize Count=count() by CompromisedEntity
| where CompromisedEntity != "CompromisedEntity" and isnotempty( CompromisedEntity)
| top 20 by Count

//Top 20 devices triggering Defender alerts
SecurityAlert
| where TimeGenerated > ago (30d)
| where ProviderName == "MDATP"
| summarize Count=count() by CompromisedEntity
| where CompromisedEntity != "CompromisedEntity" and isnotempty( CompromisedEntity)
| top 20 by Count

//Top 20 accounts by distinct alerts
SecurityAlert
| where TimeGenerated > ago (30d)
| where ProviderName in ("OATP","IPC","Azure Advanced Threat Protection","MCAS")
| summarize Count=dcount(AlertName) by CompromisedEntity
| where CompromisedEntity != "CompromisedEntity" and isnotempty( CompromisedEntity)
| top 20 by Count

//Top 20 devices by distinct alerts
SecurityAlert
| where TimeGenerated > ago (30d)
| where ProviderName == "MDATP"
| summarize Count=dcount(AlertName) by CompromisedEntity
| where CompromisedEntity != "CompromisedEntity" and isnotempty( CompromisedEntity)
| top 20 by Count

//Top 20 users generating high severity identity alerts
SecurityAlert
| where TimeGenerated > ago (30d)
| where ProviderName in ("OATP","IPC","Azure Advanced Threat Protection","MCAS") and AlertSeverity == "High"
| summarize Count=count() by CompromisedEntity
| where CompromisedEntity != "CompromisedEntity" and isnotempty( CompromisedEntity)
| top 20 by Count

//Top 20 devices triggering high severity Defender alerts
SecurityAlert
| where TimeGenerated > ago (30d)
| where ProviderName == "MDATP" and AlertSeverity == "High"
| summarize Count=count() by CompromisedEntity
| where CompromisedEntity != "CompromisedEntity" and isnotempty( CompromisedEntity)
| top 20 by Count

//Visualize your security alerts by MITRE ATT&CK tactic

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

SecurityAlert
| where TimeGenerated > ago(30d)
//Exclude Sentinel generated alerts if you want to. This may stop you double counting alerts, i.e generated by Azure AD Identity Protection and then again in Sentinel.
| where ProviderName != "ASI Scheduled Alerts"
//
| where isnotempty(Tactics) and Tactics != "Unknown"
| summarize arg_max(TimeGenerated, *) by VendorOriginalId
| summarize Count=count()by Tactics
| sort by Count desc
| render barchart with (title="Security alerts by MITRE ATT&CK tactic")

//Visualize the number of alerts generated per day by each Defender product

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

SecurityAlert
| where TimeGenerated > ago(14d)
| summarize Count=count() by ProductName, bin(TimeGenerated, 1d)
| where ProductName != "Azure Sentinel"
| render columnchart with (kind=unstacked, title="Alerts by Defender product per day")

//Visualize the severity of your MDE alerts (Informational, Low, Medium, High) per day

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

SecurityAlert
| where TimeGenerated > ago (14d)
| where ProviderName == "MDATP"
| summarize Count=count()by AlertSeverity, startofday(TimeGenerated)
| render columnchart with (kind=unstacked, ytitle="Alert Count", xtitle="Day", title="Defender for Endpoint alert severity per day")

//Visualize the most popular weaponized domains in the phishing emails your users receive

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

SecurityAlert
| where TimeGenerated > ago(365d)
| where ProviderName == "OATP"
| where AlertName in ("Email messages containing malicious URL removed after delivery​","Email messages containing phish URLs removed after delivery")
| mv-expand todynamic(Entities)
| extend MaliciousURL = tostring(Entities.Url)
| project MaliciousURL
| parse-where MaliciousURL with * "//" ['Malicious Domain'] "/" *
| summarize Count=count() by ['Malicious Domain']
| sort by Count desc 
| render barchart

//Visualize your total alerts vs distinct entity alerts per week

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

SecurityAlert
| where TimeGenerated > ago(180d)
//Exclude alerts generated by Microsoft Sentinel itself if you don't wish to double count them
| where ProductName != "ASI Scheduled Alerts"
| where Status == "New"
| summarize
    ['Total Security Alerts']=count(),
    ['Unique Entity Alerts']=dcountif(CompromisedEntity, isnotempty(CompromisedEntity))
    by bin(TimeGenerated, 7d)
| render timechart with (title="Total Security Alerts vs Unique Entity Alerts")

//Find which data sources are being used the most, by calculating which analytics rules are querying which tables when alerts are generated

//This will not account for the use of functions and may double handle table names occasionally, so it can be used as just a rough guide

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

let tablenames = search *
   | summarize make_set($table);
SecurityAlert
| where TimeGenerated > ago (30d)
| where ProviderName == "ASI Scheduled Alerts"
| summarize arg_max(TimeGenerated, *) by SystemAlertId
| extend Query = tostring(parse_json(ExtendedProperties).Query)
| mv-apply table=toscalar(tablenames) to typeof(string) on (where Query contains ['table'])
| summarize QueryCount = count()by ['table'], AlertName
| order by QueryCount

//Detect when Kerberos preauthentication is enabled or disabled for a user

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent

SecurityEvent
| where EventID == 4738
| where AccountType == "User"
| where UserAccountControl has_any ("2064", "2096")
| extend Action = case(UserAccountControl has "2096", strcat("Kerberos preauthentication disabled"),
    UserAccountControl has "2064", strcat("Kerberos preauthentication enabled"),
    "unknown")
| project TimeGenerated, Actor=SubjectAccount, User=TargetAccount, Action

//Detect when the 'account is sensitive and cannot be delegated' flag on an account is changed

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent

SecurityEvent
| project TimeGenerated, EventID, TargetAccount, SubjectAccount, UserAccountControl
| where EventID == "4738"
| where UserAccountControl has_any("2094", "2062")
| extend Activity = case
    (UserAccountControl contains "2094", strcat("Account Sensitivity Enabled"),
     UserAccountControl contains "2062", strcat("Account Sensitivity Disabled"),
    "Unknown")
| project TimeGenerated, Target=TargetAccount, Actor=SubjectAccount, Activity

//Alert when an Active Directory account is set to password not required

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent


SecurityEvent
| project TimeGenerated, EventID, TargetAccount, SubjectAccount, UserAccountControl
| where EventID == "4738"
| where UserAccountControl has ("2082")
| extend Activity = strcat("Account set to password not required")
| project TimeGenerated, Target=TargetAccount, Actor=SubjectAccount, Activity

//Use series_decompose_anomalies to detect potentially anomalous IPC$ recon events. Configure start time as your anomaly learning period and timeframe as your detection period.
// Detection threshold determines the sensitivity, the higher the threshold value the higher the anomaly required to detect

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent


let starttime = 7d;
let timeframe = 30m;
let detectionthreshold = 2;
let outliers = 
SecurityEvent
| project TimeGenerated, Account, Computer, EventID, ShareName
| where TimeGenerated > ago(starttime)
// Exclude known Accounts that often connect to various machines, such as Defender for ID or vulnerability management software
| where Account !in ("DOMAIN\\Account1")
| where EventID == "5140"
| where ShareName == "\\\\*\\IPC$"
| order by TimeGenerated
| summarize Events=count()by Account, bin(TimeGenerated, timeframe)
| summarize EventCount=make_list(Events),TimeGenerated=make_list(TimeGenerated) by Account
| extend outliers=series_decompose_anomalies(EventCount, detectionthreshold)
| mv-expand TimeGenerated, EventCount, outliers
| where outliers == 1
| distinct Account;
SecurityEvent
| project TimeGenerated, Account, Computer, EventID, ShareName, IpAddress
| where TimeGenerated > ago(timeframe)
| where EventID == "5140"
| where ShareName == "\\\\*\\IPC$"
// Exclude computer objects connecting to themselves by parsing DOMAIN\Computer$ objects and Computer.DOMAIN.COM objects and excluding matches
| parse Account with * "\\" AccountParse "$"
| parse Computer with ComputerParse "." * 
| where AccountParse != ComputerParse
// Find remaining outliers and make a set
| where Account in (outliers)
| summarize AccountActivity=make_set(Computer) by Account

//Create a daily report of users being added to on premise Active Directory groups, summarized by group name

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent

SecurityEvent
| where TimeGenerated > ago (7d)
| where AccountType == "User"
| where EventID in (4728, 4732, 4756, 4761, 4746, 4751)
| project TimeGenerated, MemberName, ['Group Name']=TargetUserName, EventID
| parse MemberName with * 'CN=' UserAdded ',' *
| summarize UsersAdded=make_set(UserAdded) by ['Group Name'], startofday(TimeGenerated)
| sort by ['Group Name'] asc, TimeGenerated desc

//Detects when a user with a privileged Azure AD role has had their on premises Active Directory password changed by someone other than themselves.

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent
//Data connector required for this query - Microsoft Sentinel UEBA

let timeframe=7d;
//First find any users that hold privileged Azure AD roles
IdentityInfo
| where TimeGenerated > ago(21d)
| where isnotempty(AssignedRoles)
| where AssignedRoles != "[]"
| summarize arg_max(TimeGenerated, *) by AccountUPN
| project AccountUPN, AccountName, AccountSID
//Join those users based on AccountSID to on premises Active Directory password reset events
| join kind=inner (
    SecurityEvent
    | where TimeGenerated > ago(timeframe)
    | where EventID == "4724"
    | project
        TimeGenerated,
        Activity,
        SubjectAccount,
        TargetAccount,
        TargetSid,
        SubjectUserSid
    )
    on $left.AccountSID == $right.TargetSid
| where SubjectUserSid != TargetSid
//Summarize event data to make it easy to read
| project ['Time of Password Reset']=TimeGenerated, Activity, Actor=SubjectAccount, ['Target UserPrincipalName']=AccountUPN,['Target AccountName']=TargetAccount

//Detect when group policy inheritance is either allowed or blocked

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent

SecurityEvent
| project TimeGenerated, EventID, EventData, SubjectAccount
| where EventID == "5136"
| parse EventData with * 'ObjectDN">' OU '</Data' *
| parse EventData with * 'AttributeLDAPDisplayName">' LDAPAttribute '</Data' *
| parse EventData with * 'AttributeValue">' AttributeValue '</Data' *
| parse EventData with * 'OperationType">%%' OperationType '</Data' *
| project
    TimeGenerated,
    Actor=SubjectAccount,
    OU,
    LDAPAttribute,
    AttributeValue,
    OperationType
| where LDAPAttribute == "gPOptions"
| where AttributeValue == "1"
| extend Activity = case
(OperationType == "14674" and AttributeValue == "1", strcat("Group Policy Inheritance Blocked"),
 OperationType == "14675" and AttributeValue == "1", strcat("Group Policy Inheritance Allowed"),
 "Unknown")
| project TimeGenerated, Actor, OU, Activity

//Alert when the 'Log on to' device list is changed for a user

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent

SecurityEvent
| where EventID == 4738
| where AccountType == "User"
//Include domain accounts only (excluding local accounts)
| where TargetDomainName == SubjectDomainName
| extend ['Allowed Devices'] = case(isnotempty(UserWorkstations) and UserWorkstations != "-" and UserWorkstations != "%%1793", split(UserWorkstations, ","),
    (isnotempty(UserWorkstations) and UserWorkstations == "%%1793"), strcat("User can log onto all devices"),
    "unknown")
//Exclude other 4738 events where the device list isn't changed
| where ['Allowed Devices'] != "unknown"
| project TimeGenerated, Actor=SubjectAccount, User=TargetAccount, ['Allowed Devices']

//Create a summary of your computers and the accounts that have logged on with special privileges over the last 30 days

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent

SecurityEvent
| where TimeGenerated > ago (30d)
| project TimeGenerated, EventID, Account, AccountType, PrivilegeList, Computer
| where EventID == "4672"
| where Account != "NT AUTHORITY\\SYSTEM" and Account !has "Window Manager"
| where AccountType == "User"
//The privilege list is stored in a string of text that we need to split
| extend Privs=extract_all(@"Se(.*?)Privilege", PrivilegeList)
//Once we retrieve the privileges from the string of text we can recreate the proper naming
| mv-expand Privs
| extend Privilege=strcat('Se', Privs, 'Privilege')
| project TimeGenerated, Account, Computer, Privilege
| summarize ['List of Privileges']=make_set(Privilege) by Computer, Account
| sort by Computer asc

//Creates a list of computers that your users have connected to via RDP and the total count of distinct computers each user has connected to

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent

SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == "4624"
| where LogonType == 10
//Extend new column that drops Account to lower case so users are correctly summarized, i.e User123 and user123 are combined
| extend AccountName=tolower(Account)
| summarize
    ['Count of Computers']=dcount(Computer),
    ['List of Computers']=make_set(Computer)
    by AccountName
| sort by ['Count of Computers'] desc

//Creates a parser for all user account control changes changing the code into a readable message

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent

SecurityEvent
| where isnotempty(UserAccountControl) and UserAccountControl != "-"
| where AccountType == "User"
| extend x = extract_all(@"([0-9]{4})", UserAccountControl)
| mv-expand x
| extend ['User Account Flag Description'] = case
    (
    x == "2048", strcat("Account Enabled"),
    x == "2049", strcat("Home Directory Required - Disabled"),
    x == "2050", strcat("Password Not Required - Disabled"),
    x == "2051", strcat("Temp Duplicate Account - Disabled"),
    x == "2052", strcat("Normal Account - Disabled"),
    x == "2053", strcat("MNS Logon Account - Disabled"),
    x == "2054", strcat("Interdomain Trust Account - Disabled"),
    x == "2055", strcat("Workstation Trust Account - Disabled"),
    x == "2056", strcat("Server Trust Account - Disabled"),
    x == "2057", strcat("Don't Expire Password - Disabled"),
    x == "2058", strcat("Account Unlocked"),
    x == "2059", strcat("Encrypted Text Password Allowed - Disabled"),
    x == "2060", strcat("Smartcard Required - Disabled"),
    x == "2061", strcat("Trusted For Delegation - Disabled"),
    x == "2062", strcat("Not Delegated - Disabled"),
    x == "2063", strcat("Use DES Key Only - Disabled"),
    x == "2064", strcat("Don't Require Preauth - Disabled"),
    x == "2065", strcat("Password Expired - Disabled"),
    x == "2066", strcat("Trusted To Authenticate For Delegation - Disabled"),
    x == "2067", strcat("Exclude Authorization Information - Disabled"),
    x == "2068", strcat("Undefined UserAccountControl Bit 20 - Disabled"),
    x == "2069", strcat("Protect Kerberos Service Tickets with AES Keys - Disabled"),
    x == "2070", strcat("Undefined UserAccountControl Bit 22 - Disabled"),
    x == "2071", strcat("Undefined UserAccountControl Bit 23 - Disabled"),
    x == "2072", strcat("Undefined UserAccountControl Bit 24 - Disabled"),
    x == "2073", strcat("Undefined UserAccountControl Bit 25 - Disabled"),
    x == "2074", strcat("Undefined UserAccountControl Bit 26 - Disabled"),
    x == "2075", strcat("Undefined UserAccountControl Bit 27 - Disabled"),
    x == "2076", strcat("Undefined UserAccountControl Bit 28 - Disabled"),
    x == "2077", strcat("Undefined UserAccountControl Bit 29 - Disabled"),
    x == "2078", strcat("Undefined UserAccountControl Bit 30 - Disabled"),
    x == "2079", strcat("Undefined UserAccountControl Bit 31 - Disabled"),
    x == "2080", strcat("Account Disabled"),
    x == "2081", strcat("Home Directory Required - Enabled"),
    x == "2082", strcat("Password Not Required - Enabled"),
    x == "2083", strcat("Temp Duplicate Account - Enabled"),
    x == "2084", strcat("Normal Account - Enabled"),
    x == "2085", strcat("MNS Logon Account - Enabled"),
    x == "2086", strcat("Interdomain Trust Account - Enabled"),
    x == "2087", strcat("Workstation Trust Account - Enabled"),
    x == "2088", strcat("Server Trust Account - Enabled"),
    x == "2089", strcat("Don't Expire Password - Enabled"),
    x == "2090", strcat("Account Locked"),
    x == "2091", strcat("Encrypted Text Password Allowed - Enabled"),
    x == "2092", strcat("Smartcard Required - Enabled"),
    x == "2093", strcat("Trusted For Delegation - Enabled"),
    x == "2094", strcat("Not Delegated - Enabled"),
    x == "2095", strcat("Use DES Key Only - Enabled"),
    x == "2096", strcat("Don't Require Preauth - Enabled"),
    x == "2097", strcat("Password Expired - Enabled"),
    x == "2098", strcat("Trusted To Authenticate For Delegation - Enabled"),
    x == "2099", strcat("Exclude Authorization Information - Enabled"),
    x == "2100", strcat("Undefined UserAccountControl Bit 20 - Enabled"),
    x == "2101", strcat("Protect Kerberos Service Tickets with AES Keys - Enabled"),
    x == "2102", strcat("Undefined UserAccountControl Bit 22 - Enabled"),
    x == "2103", strcat("Undefined UserAccountControl Bit 23 - Enabled"),
    x == "2104", strcat("Undefined UserAccountControl Bit 24 - Enabled"),
    x == "2105", strcat("Undefined UserAccountControl Bit 25 - Enabled"),
    x == "2106", strcat("Undefined UserAccountControl Bit 26 - Enabled"),
    x == "2107", strcat("Undefined UserAccountControl Bit 27 - Enabled"),
    x == "2108", strcat("Undefined UserAccountControl Bit 28 - Enabled"),
    x == "2109", strcat("Undefined UserAccountControl Bit 29 - Enabled"),
    x == "2110", strcat("Undefined UserAccountControl Bit 30 - Enabled"),
    x == "2111", strcat("Undefined UserAccountControl Bit 31 - Enabled"),
    "Unknown")
| project
    TimeGenerated,
    TargetAccount,
    Actor=SubjectAccount,
    UserAccountControl=x,
    ['User Account Flag Description']

//Detects when unconstrained kerberos delegation is enabled on a computer object

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent

SecurityEvent
| where EventID == "4742"
| parse EventData with * 'NewUacValue">' NewUacValue '</Data>' *
| parse EventData with * 'TargetUserName">' ComputerName '</Data>' *
| parse EventData with * 'SubjectUserName">' Actor '</Data>' *
| where NewUacValue == "0x2080"
| project TimeGenerated, Activity, ComputerName, Actor

//Detects when unconstrained kerberos delegation is enabled on a user object

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent

SecurityEvent
| where EventID == "4738"
| parse EventData with * 'NewUacValue">' NewUacValue '</Data>' *
| parse EventData with * 'TargetUserName">' UserName '</Data>' *
| parse EventData with * 'SubjectUserName">' Actor '</Data>' *
| where NewUacValue == "0x2010"
| project TimeGenerated, Activity, UserName, Actor

//Visualize Active Directory accounts created, disabled and deleted per day

//Data connector required for this query - Windows Security Events via AMA or Security Events via Legacy Agent

SecurityEvent
| where TimeGenerated > ago(30d)
| where AccountType == "User"
| project TimeGenerated, Account, EventID, TargetAccount
| where EventID in ("4720", "4725", "4726")
| where TargetAccount !endswith "$"
| summarize
    ['Accounts Created']=countif(EventID == "4720"),
    ['Accounts Deleted']=countif(EventID == "4726"),
    ['Accounts Disabled']=countif(EventID == "4725")
    by startofday(TimeGenerated)
| render columnchart
    with (
    kind=unstacked,
    xtitle="Day",
    ytitle="Count",
    title="Active Directory User Accounts Created, Disabled and Deleted per day")

//Calculate how many days since each analytic rule last triggered, useful to determine if rules are still valid

//Data connector required for this query - Microsoft Sentinel Incidents (generated automatically if you create incidents in Sentinel)

SecurityIncident
| where TimeGenerated > ago(180d)
| where Status == "New" and ModifiedBy == "Incident created from alert"
| summarize arg_max(TimeGenerated, *) by Title
| extend ['Days Since Last Incident'] = datetime_diff("day", now(), TimeGenerated)
| project Title, ['Days Since Last Incident']
| sort by ['Days Since Last Incident'] desc

//Visualize which playbooks are interacting with security incidents

//Data connector required for this query - Microsoft Sentinel Incidents (generated automatically if you create incidents in Sentinel)

let timeframe=45d;
SecurityIncident
| where TimeGenerated > ago (timeframe)
| where ModifiedBy startswith "Playbook"
| summarize Count=count() by ModifiedBy
| sort by Count desc 
| render barchart
    with (
    title="Count of playbooks interacting with Microsoft Sentinel incidents",
    ytitle="Playbook Name")

//Visualize the severity of your Microsoft Sentinel incidents per month

//Data connector required for this query - Microsoft Sentinel Incidents (generated automatically if you create incidents in Sentinel)

SecurityIncident
| where TimeGenerated > ago(365d)
| summarize Count=dcount(IncidentNumber)by Severity, startofmonth(TimeGenerated)
| render columnchart with (kind=unstacked, title="Microsoft Sentinel Incident Severity", xtitle="Month")

//Create a visualization showing the total Sentinel incidents and the trend of incidents over time

//Data connector required for this query - Microsoft Sentinel Incidents (generated automatically if you create incidents in Sentinel)

SecurityIncident
| summarize arg_min(TimeGenerated, *) by IncidentNumber
| make-series TotalIncidents=count() default=0 on TimeGenerated in range(ago(90d), now(), 1d)
| extend (RSquare, SplitIdx, Variance, RVariance, TrendLine)=series_fit_2lines(TotalIncidents)
| project TimeGenerated, TotalIncidents, TrendLine
| render timechart with (title="Microsoft Sentinel incidents over time with trend")

//Visualize the incidents generated in Microsoft Sentinel by MITRE ATT&CK tactics

//Data connector required for this query - Microsoft Sentinel Incidents (generated automatically if you create incidents in Sentinel)

SecurityIncident
| where TimeGenerated > ago(30d)
| summarize arg_min(TimeGenerated, *) by IncidentNumber
| extend Tactics = tostring(AdditionalData.tactics)
| where Tactics != "[]"
| mv-expand todynamic(Tactics)
| summarize Count=count()by tostring(Tactics)
| sort by Count
| render barchart with (title="Microsoft Sentinel incidents by MITRE ATT&CK tactic")

//Alerts on users being added to roles on your Azure Sentinel log analytics workspace.

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

//Uses a lookup to a GitHub gist to match Azure role ids to friendly role names and the IdentityInfo to retrieve identity details

let workspaceid="your Sentinel workspace id";
let timeframe=1d;
let AZRoles = externaldata(Name: string, Id: string) [@"https://gist.githubusercontent.com/reprise99/363eee70938c9a3d662e3f6da4610fe4/raw/b25b2d7a626396684ab578363888a0e360e7b287/.csv"] with(ignoreFirstRecord=true, format="csv");
let accesschange =AzureActivity
    | where TimeGenerated > ago(timeframe)
    | where OperationName == "Create role assignment"
    | where TenantId == workspaceid
    | extend TargetAADUserId = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).Properties)).PrincipalId)
    | extend RoleDefinitionId = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).Properties)).RoleDefinitionId)
    | parse RoleDefinitionId with * '/roleDefinitions/' AzureRoleId
    | where ActivityStatus == "Started"
    | project
        AccessChangeTime=TimeGenerated,
        Actor=Caller,
        ActorIPAddress=CallerIpAddress,
        ResourceGroup,
        WorkspaceId=TenantId,
        AzureRoleId,
        TargetAADUserId
    | join kind=inner (AZRoles 
        )
        on $left.AzureRoleId == $right.Id
    | project-away Id;
IdentityInfo
| where TimeGenerated > ago(21d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| join kind=inner accesschange on $left.AccountObjectId == $right.TargetAADUserId
| project
    AccessChangeTime=TimeGenerated,
    Actor,
    ActorIPAddress,
    ResourceGroup,
    WorkspaceId=TenantId,
    AzureRoleId,
    AzureRoleName=Name,
    TargetAADUserId,
    AccountUPN

// Add other permissions to this list as needed
let permissions = dynamic([".All", "ReadWrite", "Mail.", "offline_access", "Files.Read", "Notes.Read", "ChannelMessage.Read", "Chat.Read", "TeamsActivity.Read",
"Group.Read", "EWS.AccessAsUser.All", "EAS.AccessAsUser.All"]);
let auditList = 
AuditLogs
| where OperationName =~ "Add app role assignment to service principal"
| mv-expand TargetResources[0].modifiedProperties
| extend TargetResources_0_modifiedProperties = column_ifexists("TargetResources_0_modifiedProperties", '')
| where isnotempty(TargetResources_0_modifiedProperties)
;
let detailsList = auditList
| where TargetResources_0_modifiedProperties.displayName =~ "AppRole.Value" or TargetResources_0_modifiedProperties.displayName =~ "DelegatedPermissionGrant.Scope"
| extend Permissions = split((parse_json(tostring(TargetResources_0_modifiedProperties.newValue))), " ")
| where Permissions has_any (permissions)
| summarize AddedPermissions=make_set(Permissions,200) by CorrelationId
| join kind=inner auditList on CorrelationId
| extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
| extend InitiatingAppServicePrincipalId = tostring(InitiatedBy.app.servicePrincipalId)
| extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
| extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
| extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
| extend InitiatedBy = tostring(iff(isnotempty(InitiatingUserPrincipalName),InitiatingUserPrincipalName, InitiatingAppName))
| extend displayName = tostring(TargetResources_0_modifiedProperties.displayName), newValue = tostring(parse_json(tostring(TargetResources_0_modifiedProperties.newValue)))
| where displayName == "ServicePrincipal.ObjectID" or displayName == "ServicePrincipal.DisplayName"
| extend displayName = case(displayName == "ServicePrincipal.ObjectID", "ServicePrincipalObjectID", displayName == "ServicePrincipal.DisplayName", "ServicePrincipalDisplayName", displayName)
| project TimeGenerated, CorrelationId, Id, AddedPermissions = tostring(AddedPermissions), InitiatingAadUserId, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingIPAddress, InitiatingUserPrincipalName, InitiatedBy, displayName, newValue
;
detailsList | project Id, displayName, newValue
| evaluate pivot(displayName, make_set(newValue))
| join kind=inner detailsList on Id
| extend ServicePrincipalObjectID = todynamic(column_ifexists("ServicePrincipalObjectID", "")), ServicePrincipalDisplayName = todynamic(column_ifexists("ServicePrincipalDisplayName", ""))
| mv-expand ServicePrincipalObjectID, ServicePrincipalDisplayName
| project-away Id1, displayName, newValue
| extend ServicePrincipalObjectID = tostring(ServicePrincipalObjectID), ServicePrincipalDisplayName = tostring(ServicePrincipalDisplayName)
| summarize FirstSeen = min(TimeGenerated), LastSeen = max(TimeGenerated), EventIds = make_set(Id,200) by CorrelationId, AddedPermissions, InitiatingAadUserId, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingIPAddress, InitiatingUserPrincipalName, InitiatedBy, ServicePrincipalDisplayName, ServicePrincipalObjectID
| extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])

AuditLogs
  | where OperationName has_all ("member to role", "add")
  | where Result =~ "Success"
  | extend type_ = tostring(TargetResources[0].type)
  | where type_ =~ "ServicePrincipal"
  | where isnotempty(TargetResources)
  | extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
  | extend InitiatingAppServicePrincipalId = tostring(InitiatedBy.app.servicePrincipalId)
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
  | extend InitiatedBy = tostring(iff(isnotempty(InitiatingUserPrincipalName),InitiatingUserPrincipalName, InitiatingAppName))
  | extend ServicePrincipalName = tostring(TargetResources[0].displayName)
  | extend ServicePrincipalId = tostring(TargetResources[0].id)
  | mv-expand TargetResources[0].modifiedProperties
  | extend TargetResources_0_modifiedProperties = columnifexists("TargetResources_0_modifiedProperties", '')
  | where isnotempty(TargetResources_0_modifiedProperties)
  | extend displayName = tostring(TargetResources_0_modifiedProperties.displayName), newValue = tostring(parse_json(tostring(TargetResources_0_modifiedProperties.newValue)))
  | where displayName == "Role.DisplayName" and newValue contains "admin"
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | extend TargetRole = newValue
  | project-reorder TimeGenerated, ServicePrincipalName, ServicePrincipalId, InitiatedBy, TargetRole, InitiatingIPAddress

let known_locations = (
  AADServicePrincipalSignInLogs
  | where TimeGenerated between(ago(14d)..ago(1d))
  | where ResultType == 0
  | summarize by Location);
  AADServicePrincipalSignInLogs
  | where TimeGenerated > ago(1d)
  | where ResultType != 50126
  | where Location !in (known_locations)
  | extend City = tostring(parse_json(LocationDetails).city)
  | extend State = tostring(parse_json(LocationDetails).state)
  | extend Place = strcat(City, " - ", State)
  | extend Result = strcat(tostring(ResultType), " - ", ResultDescription)
  | summarize FirstSeen=min(TimeGenerated), LastSeen=max(TimeGenerated), make_set(Result), make_set(IPAddress), make_set(Place) by ServicePrincipalName, Location

SecurityEvent
| where EventID == 5136 
| parse EventData with * 'AttributeLDAPDisplayName">' AttributeLDAPDisplayName "<" *
| parse EventData with * 'ObjectClass">' ObjectClass "<" *
| where AttributeLDAPDisplayName == "servicePrincipalName" and  ObjectClass == "user"
| parse EventData with * 'ObjectDN">' ObjectDN "<" *
| parse EventData with * 'AttributeValue">' AttributeValue "<" *
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by Computer, SubjectAccount, ObjectDN, AttributeValue, SubjectUserName, SubjectDomainName, SubjectUserSid
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| project-away DomainIndex

imAuthentication
| where EventResult =='Failure'
| where EventResultDetails == 'User disabled'
| summarize StartTime=min(EventStartTime), EndTime=max(EventEndTime), disabledAccountLoginAttempts = count()
      , disabledAccountsTargeted = dcount(TargetUsername), disabledAccountSet = make_set(TargetUsername)
      , applicationsTargeted = dcount(TargetAppName)
      , applicationSet = make_set(TargetAppName) 
      by SrcDvcIpAddr, Type
| order by disabledAccountLoginAttempts desc
| join kind=leftouter 
    (
    // Consider these IPs suspicious - and alert any related  successful sign-ins
    imAuthentication
    | where EventResult=='Success'
    | summarize successfulAccountSigninCount = dcount(TargetUsername), successfulAccountSigninSet = makeset(TargetUsername, 15) by SrcDvcIpAddr, Type
    // Assume IPs associated with sign-ins from 100+ distinct user accounts are safe
    | where successfulAccountSigninCount < 100
    )
    on SrcDvcIpAddr
| where isnotempty(successfulAccountSigninCount)
| project StartTime, EndTime, SrcDvcIpAddr, disabledAccountLoginAttempts, disabledAccountsTargeted, disabledAccountSet, applicationSet, 
successfulAccountSigninCount, successfulAccountSigninSet, Type
| order by disabledAccountLoginAttempts

let lookback = 14d;
let timeframe = 1d;
(union isfuzzy=true
(SecurityEvent
| where TimeGenerated > ago(lookback) and TimeGenerated < ago(timeframe)
| where EventID == 4688
| where ParentProcessName has_any ("umworkerprocess.exe", "UMService.exe")
| join kind=rightanti (
SecurityEvent
| where TimeGenerated > ago(timeframe)
| where ParentProcessName has_any ("umworkerprocess.exe", "UMService.exe")
| where EventID == 4688) on NewProcessName
),
(WindowsEvent
| where TimeGenerated > ago(lookback) and TimeGenerated < ago(timeframe)
| where EventID == 4688 and EventData has_any ("umworkerprocess.exe", "UMService.exe")
| extend ParentProcessName = tostring(EventData.ParentProcessName)
| where ParentProcessName has_any ("umworkerprocess.exe", "UMService.exe")
| extend NewProcessName = tostring(EventData.NewProcessName)
| extend Account = strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend IpAddress = tostring(EventData.IpAddress)
| join kind=rightanti (
WindowsEvent
| where TimeGenerated > ago(timeframe)
| where EventID == 4688  and EventData has_any ("umworkerprocess.exe", "UMService.exe")
| extend ParentProcessName = tostring(EventData.ParentProcessName)
| where ParentProcessName has_any ("umworkerprocess.exe", "UMService.exe")
| extend NewProcessName = tostring(EventData.NewProcessName)
| extend Account = strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend IpAddress = tostring(EventData.IpAddress)) on NewProcessName
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend SubjectUserName = tostring(EventData.SubjectUserName), SubjectDomainName = tostring(EventData.SubjectDomainName)
| project-away DomainIndex
))  

let exchange_servers = (
W3CIISLog
| where TimeGenerated > ago(14d)
| where sSiteName =~ "Exchange Back End"
| summarize by Computer);
W3CIISLog
| where TimeGenerated > ago(1d)
| where Computer in (exchange_servers)
| where csUriQuery startswith "t="
| project-reorder TimeGenerated, Computer, csUriStem, csUriQuery, csUserName, csUserAgent, cIP
| 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 scriptExtensions = dynamic([".php", ".jsp", ".js", ".aspx", ".asmx", ".asax", ".cfm", ".shtml"]);
http_proxy_oab_CL
| where RawData contains "Download failed and temporary file"
| extend File = extract("([^\\\\]*)(\\\\[^']*)",2,RawData)
| extend Extension = strcat(".",split(File, ".")[-1])
| extend InteractiveFile = iif(Extension in (scriptExtensions), "Yes", "No")
// Uncomment the following line to alert only on interactive file download type
//| where InteractiveFile =~ "Yes"
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)

Event
| where EventLog =~ "Application"
| where Source startswith "MSExchange"
| where EventLevelName =~ "error"
| where (RenderedDescription startswith "Watson report" and RenderedDescription contains "umworkerprocess" and RenderedDescription contains "TextFormattingRunProperties") or RenderedDescription startswith "An unhandled exception occurred in a UM worker process" or RenderedDescription startswith "The Microsoft Exchange Unified Messaging service" or RenderedDescription contains "MSExchange Unified Messaging"
| where RenderedDescription !contains "System.OutOfMemoryException"
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| project-away DomainIndex

DeviceInfo
| extend DeviceName = tolower(DeviceName)
| join (SecurityAlert
| where ProviderName =~ "MDATP"
| extend ThreatName = tostring(parse_json(ExtendedProperties).ThreatName)
| where ThreatName has "Solorigate"
) on $left.DeviceName == $right.CompromisedEntity
| project TimeGenerated, DisplayName, ThreatName, CompromisedEntity, PublicIP, MachineGroup, AlertSeverity, Description, LoggedOnUsers, DeviceId, TenantId
| extend HostName = tostring(split(CompromisedEntity, ".")[0]), DomainIndex = toint(indexof(CompromisedEntity, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(CompromisedEntity, DomainIndex + 1), CompromisedEntity)
| project-away DomainIndex

(union isfuzzy=true
(Event
| where Source == "Microsoft-Windows-Sysmon"
| where EventID in (17,18)
| where EventData has '583da945-62af-10e8-4902-a8f205c72b2e'
| extend EventData = parse_xml(EventData).DataItem.EventData.Data
| mv-expand bagexpansion=array EventData
| evaluate bag_unpack(EventData)
| extend Key = tostring(column_ifexists('@Name', "")), Value = column_ifexists('#text', "")
| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, MG, ManagementGroupName, _ResourceId)
| extend PipeName = column_ifexists("PipeName", "")
| extend Account = User
| extend AccountName = tostring(split(User, @"\")[1]), AccountNTDomain = tostring(split(User, @"\")[0])
),
(
SecurityEvent
| where EventID == '5145'
// %%4418 looks for presence of CreatePipeInstance value
| where AccessList has '%%4418'
| where RelativeTargetName has '583da945-62af-10e8-4902-a8f205c72b2e'
| extend AccountName = SubjectUserName, AccountNTDomain = SubjectDomainName
),
(
WindowsEvent
| where EventID == '5145' and EventData has '%%4418'  and EventData has '583da945-62af-10e8-4902-a8f205c72b2e'
// %%4418 looks for presence of CreatePipeInstance value
| extend AccessList= tostring(EventData.AccessList)
| where AccessList has '%%4418'
| extend RelativeTargetName= tostring(EventData.RelativeTargetName)
| where RelativeTargetName has '583da945-62af-10e8-4902-a8f205c72b2e'
| extend Account =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend AccountName = tostring(EventData.SubjectUserName), AccountNTDomain = tostring(EventData.SubjectDomainName)
)
)
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| project-away DomainIndex