Splunk ESCU

//Visualize downloads from your Office 365 tenant by trust type (trusted/known by Azure Active Directory vs Unknown)

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

//Query Azure AD logs to get a listing of each username, IPAddress and trust type
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where UserType == "Member"
| extend DeviceTrustType = tostring(DeviceDetail.trustType)
| distinct UserPrincipalName, IPAddress, DeviceTrustType
//Join to Office Activity download on username and IP and find download events
| join kind=inner(
    OfficeActivity
    | where TimeGenerated > ago(30d)
    | where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
    )
    on $left.UserPrincipalName == $right.UserId, $left.IPAddress == $right.ClientIP
//Summarize download events by whether the device is known or not
| summarize
    ['Trusted Devices']=countif(isnotempty(DeviceTrustType)),
    ['Untrusted Devices']=countif(isempty(DeviceTrustType))
    by bin(TimeGenerated, 1d)
| render timechart with (title="Downloads from Office 365 by device trust type")

//Visualize uploads vs downloads in your Office 365 tenant per day

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago(30d)
| project TimeGenerated, Operation
| where Operation in ("FileSyncDownloadedFull", "FileSyncUploadedFull", "FileDownloaded", "FileUploaded")
| summarize
    ['Files Downloaded']=countif(Operation in ("FileDownloaded", "FileSyncDownloadedFull")),
    ['Files Uploaded']=countif(Operation in ("FileSyncUploadedFull", "FileUploaded"))
    by startofday(TimeGenerated)
| render columnchart
    with (
    kind=unstacked,
    title="Downloads vs Uploads in Office 365",
    ytitle="Count",
    xtitle="Day")

//Visualize the guest domains that have had the most files shares to them from your Office 365 tenant

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in~ ("AddedToSecureLink", "SecureLinkCreated", "SecureLinkUpdated")
| where TargetUserOrGroupType == "Guest" and TargetUserOrGroupName contains "#ext#"
| extend ['Guest UserPrincipalName'] = tostring(split(TargetUserOrGroupName, "#")[0])
| extend ['Guest Domain'] = tostring(split(['Guest UserPrincipalName'], "_")[-1])
| summarize Count=count() by ['Guest Domain']
| top 20 by Count
| render barchart with (title="Top guest domains with files shared to")

//Visualize the files shared to guests from Office 365 over time

//Data connector required for this query - Office 365

let timerange=90d;
OfficeActivity
| where TimeGenerated > ago(timerange)
| where Operation in ("SecureLinkCreated", "AddedToSecureLink")
| where TargetUserOrGroupType == "Guest"
| summarize Count=count()by bin(TimeGenerated, 1d)
| render timechart with (ytitle="File Count", title="Files shared with guests over time")

//Visualize how many files are being downloaded from your Office 365 tenant by guest accounts with trend

//Data connector required for this query - Office 365

let StartDate = now(-90d);
let EndDate = now();
OfficeActivity
| where TimeGenerated > ago(90d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId contains "#ext#"
| make-series TotalDownloads=count() on TimeGenerated in range(StartDate, EndDate, 1d)
| extend (RSquare, SplitIdx, Variance, RVariance, TrendLine)=series_fit_2lines(TotalDownloads)
| project TimeGenerated, TotalDownloads, TrendLine
| render timechart with (title="Guest downloads from Office 365 per day over time with trend")

//Visualize guests added vs removed from Teams per day over the last 30 days

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago(30d)
| where UserType == "Regular"
| where CommunicationType == "Team"
| where OfficeWorkload == "MicrosoftTeams" 
| where Operation in ("MemberAdded", "MemberRemoved")
| mv-expand Members
| extend User = tostring(Members.UPN)
| where User contains "#EXT#"
| project TimeGenerated, Operation, User
| summarize
    ['Guests Added']=countif(Operation == "MemberAdded"),
    ['Guests Removed']=countif(Operation == "MemberRemoved")
    by startofday(TimeGenerated)
| render columnchart
    with (
    kind=unstacked,
    xtitle="Count",
    ytitle="Day",
    title="Guests Added vs Removed from Teams")

//Visualize total guests redeemed in Azure AD vs guests that have been added to a Team

//Data connector required for this query - Office 365

let guestsredeemed=
    AuditLogs
    | where TimeGenerated > ago (90d)
    | where OperationName == "Redeem external user invite"
    | extend GuestRedeemed = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | extend Activity = strcat("Guest Invite Redeemed")
    | project TimeGenerated, GuestRedeemed, Activity;
let guestsaddedtoteams=
    OfficeActivity
    | where TimeGenerated > ago(90d)
    | where Operation == "MemberAdded"
    | mv-expand Members
    | extend GuestAdded = tostring(Members.UPN)
    | where GuestAdded contains "#EXT#"
    | extend Activity = strcat("Guest Added to Team")
    | project TimeGenerated, GuestAdded, Activity;
union guestsredeemed, guestsaddedtoteams
| summarize ['Total Count']=count() by Activity, bin(TimeGenerated, 1d)
| render columnchart with (kind=unstacked, title="Total Guests Redeemed vs Guests Added to Teams")

//Visualize the top 20 files downloaded by Azure AD guests over the last month

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago (30d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId contains "#ext#"
| summarize Count=count()by FileName=SourceFileName
| sort by Count desc
| take 20
| render barchart with (title="Top files downloaded by guests over the last month")

DeviceFileEvents
  | where ActionType =~ "FileCreated"
  | where FolderPath has "C:\\Windows\\System32\\spool\\drivers\\color\\" 
  | where FileName endswith ".exe" or FileName endswith ".dll"

// Find users who are assigned a privileged role in Azure AD but haven't activated a role in the last 45 days

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

IdentityInfo
| where TimeGenerated > ago(21d)
| where isnotempty(AssignedRoles)
| where AssignedRoles != "[]"
| summarize arg_max(TimeGenerated, *) by AccountUPN
| join kind=leftanti  (
    AuditLogs
    | where TimeGenerated > ago(45d)
    | where OperationName == "Add member to role completed (PIM activation)"
    | extend AccountUPN = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | summarize arg_max(TimeGenerated, *) by AccountUPN)
    on AccountUPN

//Finding MDO Security alerts and extracting the Entities user, Domain, Ip, and URL.
let Alert_List= dynamic([
"Phishing link click observed in Network Traffic",
"Phish delivered due to an IP allow policy",
"A potentially malicious URL click was detected",
"High Risk Sign-in Observed in Network Traffic",
"A user clicked through to a potentially malicious URL",
"Suspicious network connection to AitM phishing site",
"Messages containing malicious entity not removed after delivery",
"Email messages containing malicious URL removed after delivery",
"Email reported by user as malware or phish",
"Phish delivered due to an ETR override",
"Phish not zapped because ZAP is disabled"]);
SecurityAlert
|where ProviderName in~ ("Office 365 Advanced Threat Protection", "OATP")
| where AlertName in~ (Alert_List)
//extracting Alert Entities
 | extend Entities = parse_json(Entities)
| mv-apply Entity = Entities on
(
where Entity.Type == 'account'
| extend EntityUPN = iff(isempty(Entity.UserPrincipalName), tostring(strcat(Entity.Name, "@", tostring (Entity.UPNSuffix))), tostring(Entity.UserPrincipalName))
)
| mv-apply Entity = Entities on
(
where Entity.Type == 'url'
| extend EntityUrl = tostring(Entity.Url)
)
| summarize AccountUpn=tolower(tostring(take_any(EntityUPN))),Url=tostring(tolower(take_any(EntityUrl))),AlertTime= min(TimeGenerated)by SystemAlertId, ProductName
// filtering 3pnetwork devices
| join kind= inner (CommonSecurityLog
| where DeviceVendor has_any  ("Palo Alto Networks", "Fortinet", "Check Point", "Zscaler")
| where DeviceAction != "Block"
| where DeviceProduct startswith "FortiGate" or DeviceProduct startswith  "PAN" or DeviceProduct startswith  "VPN" or DeviceProduct startswith "FireWall" or DeviceProduct startswith  "NSSWeblog" or DeviceProduct startswith "URL"
| where isnotempty(RequestURL)
| where isnotempty(SourceUserName)
| extend SourceUserName = tolower(SourceUserName)
| project
3plogTime=TimeGenerated,
DeviceVendor,
DeviceProduct,
Activity,
DestinationHostName,
DestinationIP,
RequestURL=tostring(tolower(RequestURL)),
MaliciousIP,
Name = tostring(split(SourceUserName,"@")[0]),
UPNSuffix =tostring(split(SourceUserName,"@")[1]),
SourceUserName,
IndicatorThreatType,
ThreatSeverity,AdditionalExtensions,
ThreatConfidence)on $left.Url == $right.RequestURL and $left.AccountUpn == $right.SourceUserName
// Applied the condition where alert trigger 1st and then the 3p Network activity execution
| where AlertTime between ((3plogTime - 1h) .. (3plogTime + 1h))

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

let triThreshold = 500;
let startTime = 6h;
let dgaLengthThreshold = 8;
// fetch the alexa top 1M domains
let top1M =  (externaldata (Position:int, Domain:string)   [@"http://s3-us-west-1.amazonaws.com/umbrella-static/top-1m.csv.zip"]  with (format="csv", zipPattern="*.csv"));
// extract tri grams that are above our threshold - i.e. are common
let triBaseline =   top1M
| extend Domain = tolower(extract("([^.]*).{0,7}$", 1, Domain))
| extend AllTriGrams = array_concat(extract_all("(...)", Domain), extract_all("(...)", substring(Domain, 1)), extract_all("(...)", substring(Domain, 2)))
| mvexpand Trigram=AllTriGrams
| summarize triCount=count() by tostring(Trigram)
| sort by triCount desc
| where triCount > triThreshold
| distinct Trigram;
// collect domain information from common security log, filter and extract the DGA candidate and its trigrams
let allDataSummarized =   CommonSecurityLog
| where TimeGenerated > ago(startTime)
| where isnotempty(DestinationHostName)
| extend Name = tolower(DestinationHostName)
| distinct Name
| where Name has "."
| where Name !endswith ".home" and Name !endswith ".lan"
// extract DGA candidate
| extend DGADomain = extract("([^.]*).{0,7}$", 1, Name)
| where strlen(DGADomain) > dgaLengthThreshold
// throw out domains with number in them
| where DGADomain matches regex "^[A-Za-z]{0,}$"
// extract the tri grams from summarized data
| extend AllTriGrams = array_concat(extract_all("(...)", DGADomain), extract_all("(...)", substring(DGADomain, 1)), extract_all("(...)", substring(DGADomain, 2)));
// throw out domains that have repeating tri's and/or >=3 repeating letters
let nonRepeatingTris =  allDataSummarized
| join kind=leftanti
(
    allDataSummarized
    | mvexpand AllTriGrams
    | summarize count() by tostring(AllTriGrams), DGADomain
    | where count_ > 1
    | distinct DGADomain
)
on DGADomain;
// find domains that do not have a common tri in the baseline
let dataWithRareTris =  nonRepeatingTris
| join kind=leftanti
(
    nonRepeatingTris
    | mvexpand AllTriGrams
    | extend Trigram = tostring(AllTriGrams)
    | distinct Trigram, DGADomain
    | join kind=inner
    (
        triBaseline
    )
    on Trigram
    | distinct DGADomain
)
on DGADomain;
dataWithRareTris
// join DGAs back on connection data
| join kind=inner
(
    CommonSecurityLog
    | where TimeGenerated > ago(startTime)
    | where isnotempty(DestinationHostName)
    | extend DestinationHostName = tolower(DestinationHostName)
    | project-rename Name=DestinationHostName, DataSource=DeviceVendor
    | summarize StartTime=min(TimeGenerated), EndTime=max(TimeGenerated) by Name, SourceIP, DestinationIP, DataSource
)
on Name
| project StartTime, EndTime, Name, DGADomain, SourceIP, DestinationIP, DataSource

// How far back to look for events from
let timeframe = 1d;
// How close together build events and file modifications should occur to alert (make this smaller to reduce FPs)
let time_window = 5m;
// Edit this to include build processes used
let build_processes = dynamic(["MSBuild.exe", "dotnet.exe", "VBCSCompiler.exe"]);
// Include any processes that you want to allow to edit files during/around the build process
let allow_list = dynamic([""]);
(union isfuzzy=true
(SecurityEvent
| where TimeGenerated > ago(timeframe)
// Look for build process starts
| where EventID == 4688
| where Process has_any (build_processes)
| summarize by BuildParentProcess=ParentProcessName, BuildProcess=Process, BuildAccount = Account, Computer, BuildCommand=CommandLine, timekey= bin(TimeGenerated, time_window), BuildProcessTime=TimeGenerated
| join kind=inner(
SecurityEvent
| where TimeGenerated > ago(timeframe)
// Look for file modifications to code file
| where EventID == 4663
| where Process !in (allow_list)
// Look for code files, edit this to include file extensions used in build.
| where ObjectName endswith ".cs" or ObjectName endswith ".cpp"
// 0x6 and 0x4 for file append, 0x100 for file replacements
| where AccessMask == "0x6"  or AccessMask == "0x4" or AccessMask == "0X100"
| summarize by FileEditParentProcess=ParentProcessName, FileEditAccount = Account, Computer, FileEdited=ObjectName, FileEditProcess=ProcessName, timekey= bin(TimeGenerated, time_window), FileEditTime=TimeGenerated)
// join where build processes and file modifications seen at same time on same host
on timekey, Computer
// Limit to only where the file edit happens after the build process starts
| where BuildProcessTime <= FileEditTime
| summarize make_set(FileEdited), make_set(FileEditProcess), make_set(FileEditAccount) by timekey, Computer, BuildParentProcess, BuildProcess
),
(WindowsEvent
| where TimeGenerated > ago(timeframe)
// Look for build process starts
| where EventID == 4688 and EventData has_any (build_processes)
| extend NewProcessName = tostring(EventData.NewProcessName)
| extend Process=tostring(split(NewProcessName, '\\')[-1])
| where Process has_any (build_processes)
| extend ParentProcessName = tostring(EventData.ParentProcessName)
| extend Account =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend CommandLine = tostring(EventData.CommandLine) 
| summarize by BuildParentProcess=ParentProcessName, BuildProcess=Process, BuildAccount = Account, Computer, BuildCommand=CommandLine, timekey= bin(TimeGenerated, time_window), BuildProcessTime=TimeGenerated
| join kind=inner(
WindowsEvent
| where TimeGenerated > ago(timeframe)
// Look for file modifications to code file
| where EventID == 4663 and EventData has_any ("0x6", "0x4", "0X100") and EventData has_any (".cs", ".cpp")
| extend NewProcessName = tostring(EventData.NewProcessName)
| extend Process=tostring(split(NewProcessName, '\\')[-1])
| where Process !in (allow_list)
// Look for code files, edit this to include file extensions used in build.
| extend ObjectName = tostring(EventData.ObjectName)
| where ObjectName endswith ".cs" or ObjectName endswith ".cpp"
// 0x6 and 0x4 for file append, 0x100 for file replacements
| extend AccessMask = tostring(EventData.AccessMask)  
| where AccessMask == "0x6"  or AccessMask == "0x4" or AccessMask == "0X100"
| extend ParentProcessName = tostring(EventData.ParentProcessName)
| extend Account =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend ProcessName = tostring(EventData.ProcessName)
| summarize by FileEditParentProcess=ParentProcessName, FileEditAccount = Account, Computer, FileEdited=ObjectName, FileEditProcess=ProcessName, timekey= bin(TimeGenerated, time_window), FileEditTime=TimeGenerated)
// join where build processes and file modifications seen at same time on same host
on timekey, Computer
// Limit to only where the file edit happens after the build process starts
| where BuildProcessTime <= FileEditTime
| summarize make_set(FileEdited), make_set(FileEditProcess), make_set(FileEditAccount) by timekey, Computer, BuildParentProcess, BuildProcess
))
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| project-away DomainIndex

let referencestarttime = 10d;
let referenceendtime = 1d;
let threshold = 100;
let nxDomainDnsEvents = (stime:datetime, etime:datetime) 
  {_Im_Dns(responsecodename='NXDOMAIN', starttime=stime, endtime=etime)
  | where DnsQueryTypeName in ("A", "AAAA")
  | where ipv4_is_match("127.0.0.1", SrcIpAddr) == False
  | where DnsQuery !contains "/" and  DnsQuery contains "."};
nxDomainDnsEvents (stime=ago(referenceendtime) ,etime=now())
  | extend sld = tostring(split(DnsQuery, ".")[-2])
  | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), dcount(sld) by SrcIpAddr
  | where dcount_sld > threshold
  // Filter out previously seen IPs
  | join kind=leftanti (nxDomainDnsEvents (stime=ago(referencestarttime), etime=ago(referenceendtime))
    | extend sld = tostring(split(DnsQuery, ".")[-2])
    | summarize dcount(sld) by SrcIpAddr
    | where dcount_sld > threshold ) on SrcIpAddr
// Pull out sample NXDomain responses for those remaining potentially infected IPs
| join kind = inner (nxDomainDnsEvents (stime=ago(referencestarttime), etime=now()) | summarize by DnsQuery, SrcIpAddr) on SrcIpAddr
| summarize StartTime = min(StartTime), EndTime = max(EndTime), sampleNXDomainList=make_list(DnsQuery, 100)  by SrcIpAddr, dcount_sld

imRegistry
  | where EventType in ("RegistryValueSet", "RegistryKeyCreated")
  | where RegistryKey has "Software\\Classes\\ms-settings\\shell\\open\\command"
  | extend TimeKey = bin(TimeGenerated, 1h)
  | join (imProcess
  | where Process endswith "fodhelper.exe"
  | where ParentProcessName endswith "cmd.exe" or ParentProcessName endswith "powershell.exe" or ParentProcessName endswith "powershell_ise.exe"
  | extend TimeKey = bin(TimeGenerated, 1h)) on TimeKey, Dvc

let starttime = 1d;
let endtime = 1h;
let prev23hThreshold = 4;
let prev1hThreshold = 15;
let Kerbevent = (union isfuzzy=true
(SecurityEvent
| where TimeGenerated >= ago(starttime)
| where EventID == 4769
| parse EventData with * 'TicketEncryptionType">' TicketEncryptionType "<" *
| where TicketEncryptionType == '0x17'
| parse EventData with * 'TicketOptions">' TicketOptions "<" *
| where TicketOptions == '0x40810000'
| parse EventData with * 'Status">' Status "<" *
| where Status == '0x0'
| parse EventData with * 'ServiceName">' ServiceName "<" *
| where ServiceName !contains "$" and ServiceName !contains "krbtgt"
| parse EventData with * 'TargetUserName">' TargetUserName "<" *
| where TargetUserName !contains "$@" and TargetUserName !contains ServiceName
| parse EventData with * 'IpAddress">::ffff:' ClientIPAddress "<" *
),
(
WindowsEvent
| where TimeGenerated >= ago(starttime)
| where EventID == 4769 and EventData has '0x17' and EventData has '0x40810000' and EventData has 'krbtgt'
| extend TicketEncryptionType = tostring(EventData.TicketEncryptionType)
| where TicketEncryptionType == '0x17'
| extend TicketOptions = tostring(EventData.TicketOptions)
| where TicketOptions == '0x40810000'
| extend Status = tostring(EventData.Status)
| where Status == '0x0'
| extend ServiceName = tostring(EventData.ServiceName)
| where ServiceName !contains "$" and ServiceName !contains "krbtgt"
| extend TargetUserName = tostring(EventData.TargetUserName)
| where TargetUserName !contains "$@" and TargetUserName !contains ServiceName
| extend ClientIPAddress = tostring(EventData.IpAddress)
));
let Kerbevent23h = Kerbevent
| where TimeGenerated >= ago(starttime) and TimeGenerated < ago(endtime)
| summarize ServiceNameCountPrev23h = dcount(ServiceName), ServiceNameSet23h = makeset(ServiceName)
by Computer, TargetUserName,TargetDomainName, ClientIPAddress, TicketOptions, TicketEncryptionType, Status
| where ServiceNameCountPrev23h < prev23hThreshold;
let Kerbevent1h =
Kerbevent
| where TimeGenerated >= ago(endtime)
| summarize min(TimeGenerated), max(TimeGenerated), ServiceNameCountPrev1h = dcount(ServiceName), ServiceNameSet1h = makeset(ServiceName)
by Computer, TargetUserName, TargetDomainName, ClientIPAddress, TicketOptions, TicketEncryptionType, Status;
Kerbevent1h
| join kind=leftanti
(
Kerbevent23h
) on TargetUserName, TargetDomainName
// Threshold value set above is based on testing, this value may need to be changed for your environment.
| where ServiceNameCountPrev1h > prev1hThreshold
| project StartTime = min_TimeGenerated, EndTime = max_TimeGenerated, TargetUserName, Computer, ClientIPAddress, TicketOptions,
TicketEncryptionType, Status, ServiceNameCountPrev1h, ServiceNameSet1h, TargetDomainName
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend TargetAccount = strcat(TargetDomainName,  "\\", TargetUserName)
| project-away DomainIndex

let FailureThreshold = 15;
imAuthentication
| where EventType== 'Logon' and  EventResult== 'Failure'
// reason: creds 
| where EventResultDetails in ('No such user or password', 'Incorrect password')
| summarize UserCount=dcount(TargetUserId), Vendors=make_set(EventVendor), Products=make_set(EventVendor)
  , Users = make_set(TargetUserId,100) 
    by SrcDvcIpAddr, SrcGeoCountry, bin(TimeGenerated, 5m)
| where UserCount > FailureThreshold

let triThreshold = 500;
let querystarttime = 6h;
let dgaLengthThreshold = 8;
// fetch the cisco umbrella top 1M domains
let top1M =  (externaldata (Position:int, Domain:string)   [@"http://s3-us-west-1.amazonaws.com/umbrella-static/top-1m.csv.zip"]  with (format="csv", zipPattern="*.csv"));
// extract tri grams that are above our threshold - i.e. are common
let triBaseline =   top1M
  | extend Domain = tolower(extract("([^.]*).{0,7}$", 1, Domain))
  | extend AllTriGrams = array_concat(extract_all("(...)", Domain), extract_all("(...)", substring(Domain, 1)), extract_all("(...)", substring(Domain, 2)))
  | mvexpand Trigram=AllTriGrams to typeof(string)
  | summarize triCount=count() by Trigram
  | sort by triCount desc
  | where triCount > triThreshold
  | distinct Trigram;
// collect domain information from common security log, filter and extract the DGA candidate and its trigrams
let allDataSummarized =  _Im_WebSession
| where isnotempty(Url)
| extend Name = tolower(tostring(parse_url(Url)["Host"]))
| summarize NameCount=count() by Name
| where Name has "."
| where Name !endswith ".home" and Name !endswith ".lan"
// extract DGA candidate
| extend DGADomain = extract("([^.]*).{0,7}$", 1, Name)
| where strlen(DGADomain) > dgaLengthThreshold
// throw out domains with number in them
| where DGADomain matches regex "^[A-Za-z]{0,}$"
// extract the tri grams from summarized data
| extend AllTriGrams = array_concat(extract_all("(...)", DGADomain), extract_all("(...)", substring(DGADomain, 1)), extract_all("(...)", substring(DGADomain, 2)));
// throw out domains that have repeating tri's and/or >=3 repeating letters
let nonRepeatingTris =  allDataSummarized
| join kind=leftanti
(
    allDataSummarized
    | mvexpand AllTriGrams
    | summarize count() by tostring(AllTriGrams), DGADomain
    | where count_ > 1
    | distinct DGADomain
)
on DGADomain;
// find domains that do not have a common tri in the baseline
let dataWithRareTris =  nonRepeatingTris
| join kind=leftanti
(
    nonRepeatingTris
    | mvexpand AllTriGrams
    | extend Trigram = tostring(AllTriGrams)
    | distinct Trigram, DGADomain
    | join kind=inner
    (
        triBaseline
    )
    on Trigram
    | distinct DGADomain
)
on DGADomain;
dataWithRareTris
// join DGAs back on connection data
| join kind=inner
(
    _Im_WebSession
    | where isnotempty(Url)
    | extend Url = tolower(Url)
    | summarize arg_max(TimeGenerated, EventVendor,  SrcIpAddr) by Url
    | extend Name=tostring(parse_url(Url)["Host"])
    | summarize StartTime=min(TimeGenerated), EndTime=max(TimeGenerated) by Name, SrcIpAddr, Url
)
on Name
| project StartTime, EndTime, Name, DGADomain, SrcIpAddr, Url, NameCount

imProcess
| where CommandLine has_all ("accepteula", "-s", "-r", "-q")
| where Process !endswith "sdelete.exe"
| where CommandLine !has "sdelete"
| extend AccountName = tostring(split(ActorUsername, @'\')[1]), AccountNTDomain = tostring(split(ActorUsername, @'\')[0])

let sha256Hashes = dynamic(["5dd1ca0d471dee41eb3ea0b6ea117810f228354fc3b7b47400a812573d40d91d", "5fc44c7342b84f50f24758e39c8848b2f0991e8817ef5465844f5f2ff6085a57", "6cff0bbd62efe99f381e5cc0c4182b0fb7a9a34e4be9ce68ee6b0d0ea3eee39c"]);
let signames = dynamic(["Ransom:Win32/Prestige"]);
(union isfuzzy=true
(CommonSecurityLog
| where FileHash in (sha256Hashes)
| project TimeGenerated, Message, SourceUserID, FileHash, Type
| extend timestamp = TimeGenerated, Algorithm = "SHA256", AccountNTName = SourceUserID
),
(imFileEvent
| where TargetFileSHA256 has_any (sha256Hashes)
| extend AccountNT = ActorUsername, Computer = DvcHostname, IPAddress = SrcIpAddr, CommandLine = ActingProcessCommandLine, FileHash = TargetFileSHA256
| project Type, TimeGenerated, Computer, AccountNT, IPAddress, CommandLine, FileHash, Algorithm = "SHA256"
),
(Event
| where Source =~ "Microsoft-Windows-Sysmon"
| where EventID == 1
| extend EvData = parse_xml(EventData)
| extend EventDetail = EvData.DataItem.EventData.Data
| extend ProcessId = tolong(EventDetail.[3].["#text"]), Image = tostring(EventDetail.[4].["#text"]), CommandLine = tostring(EventDetail.[10].["#text"]), Hashes = tostring(EventDetail.[17].["#text"])
| extend Hashes = extract_all(@"(?P<key>\w+)=(?P<value>[a-zA-Z0-9]+)", dynamic(["key","value"]), Hashes)
| extend Hashes = column_ifexists("Hashes", dynamic(["", ""])), CommandLine = column_ifexists("CommandLine", "")
| mv-expand Hashes
| where Hashes[0] =~ "SHA256" and Hashes[1] has_any (sha256Hashes)  
| project TimeGenerated, EventDetail, UserName, Computer, Type, Source, ProcessId, Hashes, CommandLine, Image
| extend Type = strcat(Type, ": ", Source)
| extend AccountNT = UserName, InitiatingProcessId = ProcessId
| extend Process = tostring(split(Image, '\\', -1)[-1]), Algorithm = "SHA256", FileHash = tostring(Hashes[1]) 
),
(DeviceEvents
| where InitiatingProcessSHA256 has_any (sha256Hashes) or SHA256 has_any (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, DeviceName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, InitiatingProcessFolderPath, InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, InitiatingProcessSHA256, Type
| extend AccountNT = InitiatingProcessAccountName, Computer = DeviceName
| extend Algorithm = "SHA256", FileHash = tostring(InitiatingProcessSHA256), CommandLine = InitiatingProcessCommandLine, Image = InitiatingProcessFolderPath
),
(DeviceFileEvents
| where SHA256 has_any (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, DeviceName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, InitiatingProcessFolderPath, InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, InitiatingProcessSHA256, Type
| extend AccountNT = InitiatingProcessAccountName, Computer = DeviceName
| extend Algorithm = "SHA256", FileHash = tostring(InitiatingProcessSHA256), CommandLine = InitiatingProcessCommandLine, Image = InitiatingProcessFolderPath
),
(DeviceImageLoadEvents
| where SHA256 has_any (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, DeviceName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, InitiatingProcessFolderPath, InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, InitiatingProcessSHA256, Type
| extend AccountNT = InitiatingProcessAccountName, Computer = DeviceName
| extend Algorithm = "SHA256", FileHash = tostring(InitiatingProcessSHA256), CommandLine = InitiatingProcessCommandLine, Image = InitiatingProcessFolderPath
),
(SecurityAlert
| where ProductName == "Microsoft Defender Advanced Threat Protection"
| extend ThreatName = tostring(parse_json(ExtendedProperties).ThreatName)
| where isnotempty(ThreatName)
| where ThreatName has_any (signames)
| extend Computer = tostring(parse_json(Entities)[0].HostName)
)
)
| extend AccountNTName = tostring(split(AccountNT, "\\")[0]), AccountNTDomain = tostring(split(AccountNT, "\\")[1])
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| project-away DomainIndex

let admins=(IdentityInfo
  | where AssignedRoles contains "admin" or GroupMembership has "Admin"
  | summarize by tolower(AccountUPN));
  let known_asns = (
  SigninLogs
  | where TimeGenerated between(ago(14d)..ago(1d))
  | where ResultType == 0
  | summarize by AutonomousSystemNumber);
  SigninLogs
  | where TimeGenerated > ago(1d)
  | where ResultType == 0
  | where tolower(UserPrincipalName) in (admins)
  | where AutonomousSystemNumber !in (known_asns)
  | project-reorder TimeGenerated, UserPrincipalName, UserAgent, IPAddress, AutonomousSystemNumber
  | extend AccountName = tostring(split(UserPrincipalName, "@")[0]), AccountUPNSuffix = tostring(split(UserPrincipalName, "@")[1])

let args = dynamic(["objectcategory","domainlist","dcmodes","adinfo","trustdmp","computers_pwdnotreqd","Domain Admins", "objectcategory=person", "objectcategory=computer", "objectcategory=*","dclist"]);
let parentProcesses = dynamic(["pwsh.exe","powershell.exe","cmd.exe"]);
imProcessCreate
//looks for execution from a shell
| where ActingProcessName has_any (parentProcesses)
| extend ActingProcessFileName = tostring(split(ActingProcessName, '\\')[-1])
| where ActingProcessFileName in~ (parentProcesses)
// main filter
| where Process hassuffix "AdFind.exe" or TargetProcessSHA256 == "c92c158d7c37fea795114fa6491fe5f145ad2f8c08776b18ae79db811e8e36a3"
// AdFind common Flags to check for from various threat actor TTPs
or CommandLine has_any (args)
| extend AlgorithmType = "SHA256"
| extend AccountName = tostring(split(User, @'\')[1]), AccountNTDomain = tostring(split(User, @'\')[0])
| extend HostName = tostring(split(Dvc, ".")[0]), DomainIndex = toint(indexof(Dvc, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Dvc, DomainIndex + 1), Dvc)
| project-away DomainIndex

let threshold = 3;
PulseConnectSecure
| where Messages contains "Unauthenticated request url /dana-na/"
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), count() by Source_IP
| where count_ > threshold

let endtime = 1d;
// Function to resolve hostname to IP address using DNS logs or a lookup table (example syntax)
let rdpConnections =
(union isfuzzy=true
(
SecurityEvent
| where TimeGenerated >= ago(endtime)
| where EventID == 4624 and LogonType == 10
// Labeling the first RDP connection time, computer and ip
| extend
FirstHop = bin(TimeGenerated, 1m),
FirstComputer = toupper(Computer),
FirstRemoteIPAddress = IpAddress,
Account = tolower(Account)
),
(
WindowsEvent
| where TimeGenerated >= ago(endtime)
| where EventID == 4624 and EventData has ("10")
| extend LogonType = tostring(EventData.LogonType)
| where LogonType == 10 // Labeling the first RDP connection time, computer and ip
| extend Account = strcat(tostring(EventData.TargetDomainName), "", tostring(EventData.TargetUserName))
| extend IpAddress = tostring(EventData.IpAddress)
| extend
FirstHop = bin(TimeGenerated, 1m),
FirstComputer = toupper(Computer),
FirstRemoteIPAddress = IpAddress,
Account = tolower(Account)
))
| join kind=inner (
(union isfuzzy=true
(
SecurityEvent
| where TimeGenerated >= ago(endtime)
| where EventID == 4624 and LogonType == 10
// Labeling the second RDP connection time, computer and ip
| extend
SecondHop = bin(TimeGenerated, 1m),
SecondComputer = toupper(Computer),
SecondRemoteIPAddress = IpAddress,
Account = tolower(Account)
),
(
WindowsEvent
| where TimeGenerated >= ago(endtime)
| where EventID == 4624 and EventData has ("10")
| extend LogonType = toint(EventData.LogonType)
| where LogonType == 10 // Labeling the second RDP connection time, computer and ip
| extend Account = strcat(tostring(EventData.TargetDomainName), "", tostring(EventData.TargetUserName))
| extend IpAddress = tostring(EventData.IpAddress)
| extend
SecondHop = bin(TimeGenerated, 1m),
SecondComputer = toupper(Computer),
SecondRemoteIPAddress = IpAddress,
Account = tolower(Account)
))
)
on Account
| distinct
Account,
FirstHop,
FirstComputer,
FirstRemoteIPAddress,
SecondHop,
SecondComputer,
SecondRemoteIPAddress,
AccountType,
Activity,
LogonTypeName,
ProcessName;
// Resolve hostnames to IP addresses device network Ip's
let listOfFirstComputer = rdpConnections | distinct FirstComputer;
let listOfSecondComputer = rdpConnections | distinct SecondComputer;
let resolvedIPs =
DeviceNetworkInfo
| where TimeGenerated >= ago(endtime)
| where isnotempty(ConnectedNetworks) and NetworkAdapterStatus == "Up"
| extend ClientIP = tostring(parse_json(IPAddresses[0]).IPAddress)
| where isnotempty(ClientIP)
| where DeviceName in~ (listOfFirstComputer) or DeviceName in~ (listOfSecondComputer)
| summarize arg_max(TimeGenerated, ClientIP) by Computer= DeviceName
| project Computer=toupper(Computer), ResolvedIP = ClientIP;
// Join resolved IPs with the RDP connections
rdpConnections
| join kind=inner (resolvedIPs) on $left.FirstComputer == $right.Computer
| join kind=inner (resolvedIPs) on $left.SecondComputer == $right.Computer
// | where ResolvedIP != ResolvedIP1
| distinct
Account,
FirstHop,
FirstComputer,
FirstComputerIP = ResolvedIP,
FirstRemoteIPAddress,
SecondHop,
SecondComputer,
SecondComputerIP = ResolvedIP1,
SecondRemoteIPAddress,
AccountType,
Activity,
LogonTypeName,
ProcessName
// Ensure the first connection is before the second connection
// Identify only RDP to another computer from within the first RDP connection by only choosing matches where the Computer names do not match
// Ensure the IPAddresses do not match by excluding connections from the same computers with first hop RDP connections to multiple computers
| where FirstComputer != SecondComputer
and FirstRemoteIPAddress != SecondRemoteIPAddress
and SecondHop > FirstHop
// Ensure the second hop occurs within 30 minutes of the first hop
| where SecondHop <= FirstHop + 30m
| where SecondRemoteIPAddress == FirstComputerIP
| summarize FirstHopFirstSeen = min(FirstHop), FirstHopLastSeen = max(FirstHop)
by
Account,
FirstComputer,
FirstComputerIP,
FirstRemoteIPAddress,
SecondHop,
SecondComputer,
SecondComputerIP,
SecondRemoteIPAddress,
AccountType,
Activity,
LogonTypeName,
ProcessName
| extend
AccountName = tostring(split(Account, @"")[1]),
AccountNTDomain = tostring(split(Account, @"")[0])
| extend
HostName1 = tostring(split(FirstComputer, ".")[0]),
DomainIndex = toint(indexof(FirstComputer, '.'))
| extend HostNameDomain1 = iff(DomainIndex != -1, substring(FirstComputer, DomainIndex + 1), FirstComputer)
| extend
HostName2 = tostring(split(SecondComputer, ".")[0]),
DomainIndex = toint(indexof(SecondComputer, '.'))
| extend HostNameDomain2 = iff(DomainIndex != -1, substring(SecondComputer, DomainIndex + 1), SecondComputer)
| project-away DomainIndex

let starttime = 14d;
let endtime = 1d;
(union isfuzzy=true
(SecurityEvent
| where TimeGenerated >= ago(endtime)
| where EventID == 4624 and LogonType == 10
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), ConnectionCount = count()
by Account = tolower(Account), Computer = toupper(Computer), IpAddress, AccountType, Activity, LogonTypeName, ProcessName
// use left anti to exclude anything from the previous 14 days that is not rare
),
(WindowsEvent
| where TimeGenerated >= ago(endtime)
| where EventID == 4624 and EventData has ("10")
| extend LogonType = tostring(EventData.LogonType)
| where  LogonType == 10
| extend Account = strcat(tostring(EventData.TargetDomainName),"\\", tostring(EventData.TargetUserName))
| extend ProcessName = tostring(EventData.ProcessName)
| extend IpAddress = tostring(EventData.IpAddress)
| extend TargetUserSid = tostring(EventData.TargetUserSid)
| 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 Activity="4624 - An account was successfully logged on."
| extend LogonTypeName="10 - RemoteInteractive"
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), ConnectionCount = count()
by Account = tolower(Account), Computer = toupper(Computer), IpAddress, AccountType, Activity, LogonTypeName, ProcessName
))
| join kind=leftanti (
(union isfuzzy=true
(SecurityEvent
| where TimeGenerated between (ago(starttime) .. ago(endtime))
| where EventID == 4624
| summarize by Computer = toupper(Computer), IpAddress, Account = tolower(Account)
),
( WindowsEvent
| where TimeGenerated between (ago(starttime) .. ago(endtime))
| where EventID == 4624
| extend IpAddress = tostring(EventData.IpAddress)
| extend Account = strcat(tostring(EventData.TargetDomainName),"\\", tostring(EventData.TargetUserName))
| summarize by Computer = toupper(Computer), IpAddress, Account = tolower(Account)
))
) on Account, Computer
| summarize StartTime = min(StartTime), EndTime = max(EndTime), ConnectionCount = sum(ConnectionCount)
by Account, Computer, IpAddress, AccountType, Activity, LogonTypeName, ProcessName
| 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, @"\")[1]), AccountNTDomain = tostring(split(Account, @"\")[0])
| project-away DomainIndex

SigninLogs
//Find risky Signin
| where RiskState == "atRisk" and ResultType == 0
| extend Signin_Time = TimeGenerated
| summarize
    AppDisplayName=make_set(AppDisplayName),
    ClientAppUsed=make_set(ClientAppUsed),
    UserAgent=make_set(UserAgent),
    CorrelationId=make_set(CorrelationId),
    Signin_Time= min(Signin_Time),
    RiskEventTypes=make_set(RiskEventTypes)
    by
    ConditionalAccessStatus,
    IPAddress,
    IsRisky,
    ResourceDisplayName,
    RiskDetail,
    ResultType,
    RiskLevelAggregated,
    RiskLevelDuringSignIn,
    RiskState,
    UserPrincipalName=tostring(tolower(UserPrincipalName)),
    SourceSystem
| join kind=inner (
    CommonSecurityLog
    | where DeviceVendor has_any  ("Palo Alto Networks", "Fortinet", "Check Point", "Zscaler")
    | where DeviceProduct startswith "FortiGate" or DeviceProduct startswith  "PAN" or DeviceProduct startswith  "VPN" or DeviceProduct startswith "FireWall" or DeviceProduct startswith  "NSSWeblog" or DeviceProduct startswith "URL"
    | where DeviceAction != "Block"
    | where isnotempty(RequestURL)
    | where isnotempty(SourceUserName)
    | extend SourceUserName = tolower(SourceUserName)
    | summarize
        min(TimeGenerated),
        max(TimeGenerated),
        Activity=make_set(Activity)
        by DestinationHostName, DestinationIP, RequestURL, SourceUserName=tostring(tolower(SourceUserName)),DeviceVendor,DeviceProduct
    | extend 3p_observed_Time= min_TimeGenerated,Name = tostring(split(SourceUserName,"@")[0]),UPNSuffix =tostring(split(SourceUserName,"@")[1]))
    on $left.IPAddress == $right.DestinationIP and $left.UserPrincipalName == $right.SourceUserName
| extend Timediff = datetime_diff('day', 3p_observed_Time, Signin_Time)
| where Timediff <= 1 and Timediff >= 0

let runningRAT_parameters = dynamic(['/ui/chk', 'mactok=', 'UsRnMe=', 'IlocalP=', 'kMnD=']);
CommonSecurityLog
| where RequestMethod == "GET"
| project TimeGenerated, DeviceVendor, DeviceProduct, DeviceAction, DestinationDnsDomain, DestinationIP, RequestURL, SourceIP, SourceHostName, RequestClientApplication
| where RequestURL has_any (runningRAT_parameters)

// Detects when a self service password reset has been initiated via MS Graph and is successful

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

AuditLogs
| where OperationName == "POST UserAuthMethod.ResetPasswordOnPasswordMethods"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| project TimeGenerated, OperationName, Actor, CorrelationId
| join kind=inner
    (AuditLogs
    | where OperationName == "Reset password (by admin)"
    | extend Target = tostring(TargetResources[0].userPrincipalName)
    | where Result == "success"
    )
    on CorrelationId
| project GraphPostTime=TimeGenerated, PasswordResetTime=TimeGenerated1, Actor, Target

let SunburstMD5=dynamic(["b91ce2fa41029f6955bff20079468448","02af7cec58b9a5da1c542b5a32151ba1","2c4a910a1299cdae2a4e55988a2f102e","846e27a652a5e1bfbd0ddd38a16dc865","4f2eb62fa529c0283b28d05ddd311fae"]);
let SupernovaMD5="56ceb6d0011d87b6e4d7023d7ef85676";
imFileEvent
| where TargetFileMD5 in (SunburstMD5) or TargetFileMD5 in (SupernovaMD5)
| extend AccountName = tostring(split(User, @'\')[1]), AccountNTDomain = tostring(split(User, @'\')[0])
| extend AlgorithmType = "MD5"

let excludeProcs = dynamic([@"\SolarWinds\Orion\APM\APMServiceControl.exe", @"\SolarWinds\Orion\ExportToPDFCmd.Exe", @"\SolarWinds.Credentials\SolarWinds.Credentials.Orion.WebApi.exe", @"\SolarWinds\Orion\Topology\SolarWinds.Orion.Topology.Calculator.exe", @"\SolarWinds\Orion\Database-Maint.exe", @"\SolarWinds.Orion.ApiPoller.Service\SolarWinds.Orion.ApiPoller.Service.exe", @"\Windows\SysWOW64\WerFault.exe"]);
DeviceProcessEvents
| where InitiatingProcessFileName =~ "solarwinds.businesslayerhost.exe"
| where not(FolderPath has_any (excludeProcs))
| extend
    timestamp = TimeGenerated,
    InitiatingProcessAccountUPNSuffix = tostring(split(InitiatingProcessAccountUpn, "@")[1]),
    Algorithm = "MD5"

let excludeProcs = dynamic([@"\SolarWinds\Orion\APM\APMServiceControl.exe", @"\SolarWinds\Orion\ExportToPDFCmd.Exe", @"\SolarWinds.Credentials\SolarWinds.Credentials.Orion.WebApi.exe", @"\SolarWinds\Orion\Topology\SolarWinds.Orion.Topology.Calculator.exe", @"\SolarWinds\Orion\Database-Maint.exe", @"\SolarWinds.Orion.ApiPoller.Service\SolarWinds.Orion.ApiPoller.Service.exe", @"\Windows\SysWOW64\WerFault.exe"]);
imProcessCreate
| where Process hassuffix 'solarwinds.businesslayerhost.exe'
| where not(Process has_any (excludeProcs))
| extend AccountName = tostring(split(ActorUsername, @'\')[1]), AccountNTDomain = tostring(split(ActorUsername, @'\')[0])
| extend HostName = tostring(split(Dvc, ".")[0]), DomainIndex = toint(indexof(Dvc, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Dvc, DomainIndex + 1), Dvc)
| project-away DomainIndex

_Im_ProcessEvent
| where EventType =~ "ProcessCreated"
| where Process endswith "svchost.exe"
| where CommandLine has "-k GPSvcGroup" or CommandLine has "-s gpsvc"
| extend timekey = bin(TimeGenerated, 1m)
| project timekey, ActingProcessId, Dvc
| join kind=inner (
  _Im_ProcessEvent
  | where EventType =~ "ProcessCreated"
  | where Process =~ "sdelete.exe" or CommandLine has "sdelete"
  | where ActingProcessName endswith "svchost.exe"
  | where CommandLine has_all ("-s", "-r")
  | extend timekey = bin(TimeGenerated, 1m)
  ) 
  on $left.ActingProcessId == $right.ParentProcessId, timekey, Dvc
| extend AccountName = tostring(split(ActorUsername, @'\')[1]), AccountNTDomain = tostring(split(ActorUsername, @'\')[0])
| extend HostName = tostring(split(Dvc, ".")[0]), DomainIndex = toint(indexof(Dvc, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Dvc, DomainIndex + 1), Dvc)
| project-away DomainIndex

//Find computers that have not sent any security events for over an hour

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

SecurityEvent
| where TimeGenerated > ago (1d)
| summarize ['Last Record Received']  = datetime_diff("minute", now(), max(TimeGenerated)) by Computer
| project Computer, ['Last Record Received']
| where ['Last Record Received'] >= 60
| order by ['Last Record Received'] desc

//Use your Windows security log to find the users most at risk for lateral movement by finding those that have connected remotely to the most devices

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

SecurityEvent
| where TimeGenerated > ago (30d)
| where EventID == "4624"
| where LogonType == 10
| where SubjectDomainName == TargetDomainName
//Summarize total logins, distinct devices and then list all the devices each account has logged onto
//Account is dropped to lower case to make sure each account is only listed once, i.e Reprise99 and reprise99 are combined
| summarize
    ['Total logon count']=count(),
    ['Distinct device logon count']=dcount(Computer),
    ['List of devices']=make_set(Computer)
    by tolower(Account)
| sort by ['Distinct device logon count'] desc

//Detect when a user connects to 3 or more unique devices via RDP over a 30 minute period

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

SecurityEvent
| where TimeGenerated > ago (1d)
| where EventID == "4624"
| where LogonType == 10
| where SubjectDomainName == TargetDomainName
//Account is dropped to lower case to make sure each account is only listed once, i.e Reprise99 and reprise99 are combined
| summarize
    ['Distinct device logon count']=dcount(Computer),
    ['List of devices']=make_set(Computer)
    by tolower(Account), bin(TimeGenerated, 30m)
//Find accounts that have logged on to 3 or more unique devices in less than 30 minutes
| where ['Distinct device logon count'] >= 3

//Create a summary of interactive and remote interactive (RDP) logons to your Windows devices using the security event logs

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

SecurityEvent
| where TimeGenerated > ago (30d)
| where EventID == "4624"
| where LogonType in ("2", "10")
//Search for just domain logon events but matching subject and target domain name fields
| where SubjectDomainName == TargetDomainName
| summarize
    ['Interactive logon count']=countif(LogonType == 2),
    ['Interactive distinct logon count']=dcountif(Account, LogonType == 2),
    ['List of interactive logons']=make_set_if(Account, LogonType == 2),
    ['Remote interactive logon count']=countif(LogonType == 10),
    ['Remote interactive distinct logon count']=dcountif(Account, LogonType == 10),
    ['List of remote interactive logons']=make_set_if(Account, LogonType == 10)
    by Computer
| sort by Computer asc

let servicelist = dynamic(['Services\\HealthService', 'Services\\Sense', 'Services\\WinDefend', 'Services\\MsSecFlt', 'Services\\DiagTrack', 'Services\\SgrmBroker', 'Services\\SgrmAgent', 'Services\\AATPSensorUpdater' , 'Services\\AATPSensor', 'Services\\mpssvc']);
let filename = dynamic(["subinacl.exe",'SetACL.exe']);
let parameters = dynamic (['/deny=SYSTEM', '/deny=S-1-5-18', '/grant=SYSTEM=r', '/grant=S-1-5-18=r', 'n:SYSTEM;p:READ', 'n1:SYSTEM;ta:remtrst;w:dacl']);
let FullAccess = dynamic(['A;CI;KA;;;SY', 'A;ID;KA;;;SY', 'A;CIID;KA;;;SY']);
let ReadAccess = dynamic(['A;CI;KR;;;SY', 'A;ID;KR;;;SY', 'A;CIID;KR;;;SY']);
let DenyAccess = dynamic(['D;CI;KR;;;SY', 'D;ID;KR;;;SY', 'D;CIID;KR;;;SY']);
let timeframe = 1d;
(union isfuzzy=true
(
SecurityEvent
| where TimeGenerated >= ago(timeframe)
| where EventID == 4670
| where ObjectType == 'Key'
| where ObjectName has_any (servicelist)
| parse EventData with * 'OldSd">' OldSd "<" *
| parse EventData with * 'NewSd">' NewSd "<" *
| extend Reason = case( (OldSd has ';;;SY' and NewSd !has ';;;SY'), 'System Account is removed', (OldSd has_any (FullAccess) and NewSd has_any (ReadAccess)) , 'System permission has been changed to read from full access', (OldSd has_any (FullAccess) and NewSd has_any (DenyAccess)), 'System account has been given denied permission', 'None')
| project TimeGenerated, Computer, Account,  ProcessName, ProcessId, ObjectName, EventData, Activity, HandleId, SubjectLogonId, OldSd, NewSd , Reason
),
(
SecurityEvent
| where TimeGenerated >= ago(timeframe)
| where EventID == 4688
| extend ProcessName = tostring(split(NewProcessName, '\\')[-1])
| where ProcessName in~ (filename)
| where CommandLine has_any (servicelist) and CommandLine has_any (parameters)
| project TimeGenerated, Computer, Account, AccountDomain, ProcessName, ProcessNameFullPath = NewProcessName, EventID, Activity, CommandLine, EventSourceName, Type
),
(
WindowsEvent
| where TimeGenerated >= ago(timeframe)
| where EventID == 4670 and EventData has_any (servicelist) and EventData has 'Key'
| extend ObjectType = tostring(EventData.ObjectType)
| where ObjectType == 'Key'
| extend ObjectName = tostring(EventData.ObjectName)
| where ObjectName has_any (servicelist)
| extend OldSd = tostring(EventData.OldSd)
| extend NewSd = tostring(EventData.NewSd)
| extend Reason = case( (OldSd has ';;;SY' and NewSd !has ';;;SY'), 'System Account is removed', (OldSd has_any (FullAccess) and NewSd has_any (ReadAccess)) , 'System permission has been changed to read from full access', (OldSd has_any (FullAccess) and NewSd has_any (DenyAccess)), 'System account has been given denied permission', 'None')
| extend Account =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend ProcessName = tostring(EventData.ProcessName)
| extend ProcessId = tostring(EventData.ProcessId)
| extend Activity= "4670 - Permissions on an object were changed."
| extend HandleId = tostring(EventData.HandleId)
| extend  SubjectLogonId = tostring(EventData.SubjectLogonId)
| project TimeGenerated, Computer, Account,  ProcessName, ProcessId, ObjectName, EventData, Activity, HandleId, SubjectLogonId, OldSd, NewSd , Reason
),
(
WindowsEvent
| where TimeGenerated >= ago(timeframe)
| where EventID == 4688 and EventData has_any (filename) and EventData has_any (servicelist) and EventData has_any (parameters)
| extend NewProcessName = tostring(EventData.NewProcessName)
| extend ProcessName = tostring(split(NewProcessName, '\\')[-1])
| where ProcessName in~ (filename)
| extend CommandLine = tostring(EventData.CommandLine)
| where CommandLine has_any (servicelist) and CommandLine has_any (parameters)
| extend  Account =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend  AccountDomain = tostring(EventData.AccountDomain)
| extend  Activity="4688 - A new process has been created."
| extend  EventSourceName=Provider
| project TimeGenerated, Computer, Account, AccountDomain, ProcessName, ProcessNameFullPath = NewProcessName, EventID, Activity, CommandLine, EventSourceName, Type
),
(
DeviceProcessEvents
| where TimeGenerated >= ago(timeframe)
| where InitiatingProcessFileName in~ (filename)
| where InitiatingProcessCommandLine has_any(servicelist) and InitiatingProcessCommandLine has_any (parameters)
| extend Account = iff(isnotempty(InitiatingProcessAccountUpn), InitiatingProcessAccountUpn, InitiatingProcessAccountName), Computer = DeviceName
| project TimeGenerated, Computer, Account, AccountDomain, ProcessName = InitiatingProcessFileName, ProcessNameFullPath = FolderPath, Activity = ActionType, CommandLine = InitiatingProcessCommandLine, Type, InitiatingProcessParentFileName
)
)
| extend AccountName = tostring(split(Account, "\\")[0]), AccountNTDomain = tostring(split(Account, "\\")[1])
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)

//This query looks at all your tables and then finds them in the SecurtiyAlert table and LAQueryLogs tables to try to determine if data sources are being actively hunted on and alerted on
//This may not be perfect due to the use of functions in alerts and queries but is a good starting point for optimization. Also if you use Azure dashboards they will 'run' the queries so may be artifically inflating the query count, though looking at a dashboard is still valuable!

//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)
////Data connector required for this query - LAQueryLogs (tracks query history on a Log Analytics workspace)

let tablenames = search *
   | summarize make_set($table);
let alerts=
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 AlertCount = count()by ['table']
| order by AlertCount;
LAQueryLogs
| where TimeGenerated > ago (30d)
| mv-apply table=toscalar(tablenames) to typeof(string) on (where QueryText contains ['table'])
| summarize QueryCount = count()by ['table']
| order by QueryCount
| join kind=fullouter (alerts) on table
| project-away table1

//When Defender for Identity alerts on user and group reconnaissance, parse the relevant accounts, hosts and groups affected

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

SecurityAlert
| where AlertName == "User and group membership reconnaissance (SAMR)"
| extend x = todynamic(Entities)
| mv-expand x
| parse x with * 'HostName":"' HostName '","Id' *
| parse x with * 'FriendlyName":"' GroupName '","Type":"security-group"' *
| parse x with * '"Name":"' AccountName '","Sid"' *
| summarize
    Accounts=make_list_if(AccountName, isnotempty(AccountName)),
    Hosts=make_list_if(HostName, isnotempty(HostName)),
    Groups=make_list_if(GroupName, isnotempty(GroupName))
    by VendorOriginalId

//Parse all the relevant entities - hosts, accounts, IP addresses, files, groups, resources and times from Defender for Identity alerts

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

SecurityAlert
| where ProviderName == "Azure Advanced Threat Protection"
| summarize arg_max(TimeGenerated, *) by VendorOriginalId
| mv-expand todynamic(Entities)
| extend x = parse_json(Entities)
| extend Host = x.HostName
| extend Account = x.Name
| extend IP = x.Address
| extend File = x.File
| extend Group = x.Group
| extend ResourceId = x.ResourceName
| extend Time= x.Time
| summarize
    HostNames=make_set(Host),
    AccountNames=make_set(Account),
    IPAddresses=make_set(IP),
    Files=make_set(File),
    SecurityGroups=make_set(Group),
    Resources=make_set(ResourceId),
    TimeAccessed=make_set(Time)
    by TimeGenerated, SystemAlertId, AlertName, Description

//List any new alert types found by the Defender product suite in the last week compared to the previous year

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

//First find all the existing alerts from the last year excluding the last week
let existingalerts=
    SecurityAlert
    | where TimeGenerated > ago(365d) and TimeGenerated < ago(7d)
    // Exclude alerts from Sentinel itself
    | where ProviderName != "ASI Scheduled Alerts"
    | distinct AlertName;
//Find new alerts triggered in the last week
SecurityAlert
| where TimeGenerated > ago(7d)
// Exclude alerts from Sentinel itself
| where ProviderName != "ASI Scheduled Alerts"
| where AlertName !in (existingalerts)
| distinct AlertName, ProviderName, ProductName

//Detect when a device triggers a Defender for Endpoint alert where Defender for Identity has also detected a lateral movement path

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

SecurityAlert
| where ProviderName == "MDATP"
| project TimeGenerated, AlertName, CompromisedEntity
| join kind=inner (
    IdentityDirectoryEvents
    | where ActionType == "Potential lateral movement path identified")
    on $left.CompromisedEntity == $right.DeviceName
| distinct DeviceName, AlertName, AccountName, ReportId

//Detect when Defender for Endpoint alerts on suspicious PowerShell usage. If command is encoded it will be decoded.

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

SecurityAlert
| where AlertName == "Suspicious PowerShell command line"
| mv-expand todynamic(Entities)
| extend CommandLine = tostring(Entities.CommandLine)
//This particular query looks for only encoded Powershell commands, if you want all Powershell commands just remove the lines below
| extend EncodedCommand = extract(@'\s+([A-Za-z0-9+/]{20}\S+$)', 1, CommandLine)
| where EncodedCommand != ""
| extend DecodedCommand = base64_decode_tostring(EncodedCommand)
| where DecodedCommand != ""
//
| project TimeGenerated, CompromisedEntity, AlertName, CommandLine, DecodedCommand

//Advanced Hunting query - depending on the content of the decoded string AH can struggle to render the command occasionally

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

let alertid=
AlertInfo
| where Title == @"Suspicious PowerShell command line"
| distinct AlertId;
AlertEvidence
| where AlertId in (alertid)
| where EntityType == "Process"
| extend EncodedCommand = extract(@'\s+([A-Za-z0-9+/]{20}\S+$)', 1, ProcessCommandLine)
| where EncodedCommand != ""
| extend DecodedCommand = base64_decode_tostring(EncodedCommand)
| where DecodedCommand != ""
| project Timestamp, AlertId, ProcessCommandLine, DecodedCommand

//When Defender for Cloud Apps detects activity from an infrequent country, summarize the impact to your users

//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

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]);
//Microsoft Sentinel query
//Using the Security Alert table find any locations detected. This query looks back 7 days to find alerts as this can be an offline detection, but you can adjust.
let suspiciouslocation=
    SecurityAlert
    | where TimeGenerated > ago(7d)
    | where AlertName == "Activity from infrequent country"
    | mv-expand todynamic(Entities)
    | project Entities
    | extend Location = tostring(parse_json(tostring(Entities.Location)).CountryCode)
    | where isnotempty(Location)
    | distinct Location;
//Take that location and send back through the sign in logs to find the blast radius
SigninLogs
| where TimeGenerated > ago(7d)
| where Location in (suspiciouslocation)
| summarize
    ['Count of distinct successful sign ins'] = dcountif(UserPrincipalName, (ResultType in(successCodes))),
    ['List of successful users']=make_set_if(UserPrincipalName, (ResultType in(successCodes))),
    ['Successful result codes'] = make_set_if(ResultType, (ResultType in(successCodes))),
    ['Count of distinct failed sign ins'] = dcountif(UserPrincipalName, (ResultType in(failureCodes))),
    ['List of failed users'] = make_set_if(UserPrincipalName, (ResultType in(failureCodes))),
    ['Failed result codes'] = make_set_if(ResultType, (ResultType in(failureCodes)))
    by Location

//Advanced Hunting query

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

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]);
let alertid=
AlertInfo
| where Timestamp > ago(7d)
| where Title == @"Activity from infrequent country"
| distinct AlertId;
let suspiciouslocation=
AlertEvidence
| where AlertId in (alertid)
| extend AF=parse_json(AdditionalFields)
| extend Location=tostring(AF.Location.CountryCode)
| where isnotempty(Location)
| distinct Location;
AADSignInEventsBeta
| where Timestamp > ago(7d)
| where Country in (suspiciouslocation)
| summarize
    ['Count of distinct successful sign ins'] = dcountif(AccountUpn, (ErrorCode in(successCodes))),
    ['List of successful users']=make_set_if(AccountUpn, (ErrorCode in(successCodes))),
    ['Successful result codes'] = make_set_if(ErrorCode, (ErrorCode in(successCodes))),
    ['Count of distinct failed sign ins'] = dcountif(AccountUpn, (ErrorCode in(failureCodes))),
    ['List of failed users'] = make_set_if(AccountUpn, (ErrorCode in(failureCodes))),
    ['Failed result codes'] = make_set_if(ErrorCode, (ErrorCode in(failureCodes)))
    by Country

//When Defender for Cloud Apps detects password spray activity, summarize the impact to your users

//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

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]);
//Using the Security Alert table find any IP addresses associated with password spray activity. This query looks back 7 days to find alerts as this can be an offline detection, but you can adjust.
//Microsoft Sentinel query
let maliciousip=
    SecurityAlert
    | where TimeGenerated > ago (7d)
    | where AlertName == "Activity from a password-spray associated IP address"
    | mv-expand todynamic(Entities)
    | project TimeGenerated, Entities
    | extend IPAddress = tostring(Entities.Address)
    | where isnotempty(IPAddress)
    | distinct IPAddress;
//Look back in your signin logs the last 30 days and summarize activity from that address
SigninLogs
| where TimeGenerated > ago(30d)
| where IPAddress in (maliciousip)
| summarize
    ['Count of distinct successful sign ins'] = dcountif(UserPrincipalName, (ResultType in(successCodes))),
    ['List of successful users']=make_set_if(UserPrincipalName, (ResultType in(successCodes))),
    ['Successful result codes'] = make_set_if(ResultType, (ResultType in(successCodes))),
    ['Count of distinct failed sign ins'] = dcountif(UserPrincipalName, (ResultType in(failureCodes))),
    ['List of failed users'] = make_set_if(UserPrincipalName, (ResultType in(failureCodes))),
    ['Failed result codes'] = make_set_if(ResultType, (ResultType in(failureCodes)))
    by IPAddress

//Advanced hunting query

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

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]);
let alertid=
AlertInfo
| where Timestamp > ago(7d)
| where Title == @"Activity from a password-spray associated IP address"
| distinct AlertId;
let maliciousip=
AlertEvidence
| where AlertId in (alertid)
| where EntityType == @"Ip"
| extend AF = parse_json(AdditionalFields)
| extend IPAddress = tostring(AF.Address)
| distinct IPAddress;
AADSignInEventsBeta
| where Timestamp > ago(30d)
| where IPAddress in (maliciousip)
| summarize
    ['Count of distinct successful sign ins'] = dcountif(AccountUpn, (ErrorCode in(successCodes))),
    ['List of successful users']=make_set_if(AccountUpn, (ErrorCode in(successCodes))),
    ['Successful result codes'] = make_set_if(ErrorCode, (ErrorCode in(successCodes))),
    ['Count of distinct failed sign ins'] = dcountif(AccountUpn, (ErrorCode in(failureCodes))),
    ['List of failed users'] = make_set_if(AccountUpn, (ErrorCode in(failureCodes))),
    ['Failed result codes'] = make_set_if(ErrorCode, (ErrorCode in(failureCodes)))
    by IPAddress

//Find the most phished users from the last 365 days

//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)
| project Entities
| extend User = tostring(Entities.MailboxPrimaryAddress)
| where isnotempty(User)
| summarize ['Count of Phishing Attempts']=count()by User
| order by ['Count of Phishing Attempts']

//When Defender for Cloud detects communication with a DNS sinkhole, find the devices and processes initiating the connection

//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 (1d)
    | where AlertName startswith "Attempted communication with suspicious sinkholed 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 reports an email as potential phishing find all other users who received that email

//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(1d)
| where ProviderName == "OATP"
| where AlertName has "Email reported by user as malware or phish"
| mv-expand todynamic(Entities)
| project Entities
| extend SenderMailFromAddress = tostring(Entities.P1Sender)
| extend Subject = tostring(Entities.Subject)
| where isnotempty(SenderMailFromAddress) and isnotempty(Subject)
| distinct SenderMailFromAddress, Subject
| join kind=inner(
    EmailEvents
    | where TimeGenerated > ago(2d)
    | project RecipientEmailAddress, SenderMailFromAddress, Subject
    )
    on SenderMailFromAddress, Subject
| summarize Recipients=make_set(RecipientEmailAddress) by Subject, SenderMailFromAddress

//When an anomalous token alert is flagged, find the specific risk events that flagged 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 - Azure Active Directory - AAD User Risk Events

let alerts=
    SecurityAlert
    | where TimeGenerated > ago(1d)
    | where AlertName == "Anomalous Token"
    | mv-expand todynamic(Entities)
    | project Entities
    | extend RequestId = tostring(Entities.SessionId)
    | distinct RequestId;
//Detections can be offline so retrieve a weeks worth of risk data
AADUserRiskEvents
| where TimeGenerated > ago(7d)
| where RequestId in (alerts)
| project TimeGenerated, UserPrincipalName, RiskEventType, RiskLevel, DetectionTimingType, IpAddress, Location