Hunt pack: INC Ransom

let threatCategory="Hacking Tool";
let knownUserAgentsIndicators = materialize(externaldata(UserAgent:string, Category:string)
    [ @"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/UnusualUserAgents.csv"] 
        with(format="csv", ignoreFirstRecord=True));
let knownUserAgents=toscalar(knownUserAgentsIndicators | where Category==threatCategory | where isnotempty(UserAgent) | summarize make_list(UserAgent));
let customUserAgents=toscalar(_GetWatchlist("UnusualUserAgents") | where SearchKey==threatCategory | extend UserAgent=column_ifexists("UserAgent","") | where isnotempty(UserAgent) | summarize make_list(UserAgent));
let fullUAList = array_concat(knownUserAgents,customUserAgents);
_Im_WebSession(httpuseragent_has_any=fullUAList)
| project SrcIpAddr, Url, TimeGenerated, HttpUserAgent, SrcUsername
| extend AccountName = tostring(split(SrcUsername, "@")[0]), AccountUPNSuffix = tostring(split(SrcUsername, "@")[1])

(union isfuzzy=true 
(SecurityEvent 
| where EventID == 4662 // You need to create a SACL on the ADFS Policy Store DKM group for this event to be created. 
| where ObjectServer == 'DS'
| where OperationType == 'Object Access'
//| where ObjectName contains '<GUID of ADFS Policy Store DKM Group object' This is unique to the domain. Check description for more details.
| where ObjectType contains '5cb41ed0-0e4c-11d0-a286-00aa003049e2' // Contact Class
| where Properties contains '8d3bca50-1d7e-11d0-a081-00aa006c33ed' // Picture Attribute - Ldap-Display-Name: thumbnailPhoto
| extend AccountName = SubjectUserName, AccountDomain = SubjectDomainName
| extend timestamp = TimeGenerated, DeviceName = Computer
),
( WindowsEvent 
| where EventID == 4662 // You need to create a SACL on the ADFS Policy Store DKM group for this event to be created. 
| where EventData has_all('Object Access', '5cb41ed0-0e4c-11d0-a286-00aa003049e2','8d3bca50-1d7e-11d0-a081-00aa006c33ed') 
| extend ObjectServer = tostring(EventData.ObjectServer)
| where ObjectServer == 'DS'
| extend OperationType = tostring(EventData.OperationType)
| where OperationType == 'Object Access'
//| where ObjectName contains '<GUID of ADFS Policy Store DKM Group object' This is unique to the domain. Check description for more details.
| extend ObjectType = tostring(EventData.ObjectType)
| where ObjectType contains '5cb41ed0-0e4c-11d0-a286-00aa003049e2' // Contact Class
| extend Properties = tostring(EventData.Properties)
| where Properties contains '8d3bca50-1d7e-11d0-a081-00aa006c33ed' // Picture Attribute - Ldap-Display-Name: thumbnailPhoto
| extend AccountName = tostring(EventData.SubjectUserName), AccountDomain = tostring(EventData.SubjectDomainName)
| extend timestamp = TimeGenerated, DeviceName = Computer
),
(DeviceEvents
| where ActionType =~ "LdapSearch"
| where AdditionalFields.AttributeList contains "thumbnailPhoto"
| where AdditionalFields.DistinguishedName contains "CN=ADFS,CN=Microsoft,CN=Program Data" // Filter results to show only hits related to the ADFS AD container
| extend timestamp = TimeGenerated, AccountName = InitiatingProcessAccountName, AccountDomain = InitiatingProcessAccountDomain
)
)
| extend Account = strcat(AccountDomain, "\\", AccountName)

let core_domains = (SigninLogs
  | where TimeGenerated > ago(7d)
  | where ResultType == 0
  | extend domain = tolower(split(UserPrincipalName, "@")[1])
  | summarize by tostring(domain));
  let alternative_domains = (SigninLogs
  | where TimeGenerated > ago(7d)
  | where isnotempty(AlternateSignInName)
  | where ResultType == 0
  | extend domain = tolower(split(AlternateSignInName, "@")[1])
  | summarize by tostring(domain));
  AuditLogs
  | where TimeGenerated > ago(1d)
  | where OperationName =~ "Add User"
  | 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(iff(isnotempty(InitiatedBy.user.ipAddress), InitiatedBy.user.ipAddress, InitiatedBy.app.ipAddress))
  | extend UserAdded = tostring(TargetResources[0].userPrincipalName)
  | extend UserAddedDomain = case(
  UserAdded has "#EXT#", tostring(split(tostring(split(UserAdded, "#EXT#")[0]), "_")[1]),
  UserAdded !has "#EXT#", tostring(split(UserAdded, "@")[1]),
  UserAdded)
  | where UserAddedDomain !in (core_domains) and UserAddedDomain !in (alternative_domains)
  | extend AddedByName = case(
  InitiatingUserPrincipalName has "#EXT#", tostring(split(tostring(split(InitiatingUserPrincipalName, "#EXT#")[0]), "_")[0]),
  InitiatingUserPrincipalName !has "#EXT#", tostring(split(InitiatingUserPrincipalName, "@")[0]),
  InitiatingUserPrincipalName)
  | extend AddedByUPNSuffix = case(
  InitiatingUserPrincipalName has "#EXT#", tostring(split(tostring(split(InitiatingUserPrincipalName, "#EXT#")[0]), "_")[1]),
  InitiatingUserPrincipalName !has "#EXT#", tostring(split(InitiatingUserPrincipalName, "@")[1]),
  InitiatingUserPrincipalName)
  | extend UserAddedName = case(
  UserAdded has "#EXT#", tostring(split(tostring(split(UserAdded, "#EXT#")[0]), "_")[0]),
  UserAdded !has "#EXT#", tostring(split(UserAdded, "@")[0]),
  UserAdded)

let admin_users = (IdentityInfo
  | summarize arg_max(TimeGenerated, *) by AccountUPN
  | where AssignedRoles contains "admin"
  | summarize by tolower(AccountUPN));
  AuditLogs
  | where OperationName =~ "Admin registered security info"
  | where ResultReason =~ "Admin registered temporary access pass method for user"
  | extend TargetUserPrincipalName = tostring(TargetResources[0].userPrincipalName)
  | where tolower(TargetUserPrincipalName) in (admin_users)
  | extend TargetAadUserId = tostring(TargetResources[0].id)
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
  | extend TargetAccountName = tostring(split(TargetUserPrincipalName, "@")[0]), TargetAccountUPNSuffix = tostring(split(TargetUserPrincipalName, "@")[1])
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])

SecurityEvent
| where EventID == 5136 and EventData contains "<Data Name=\"ObjectDN\">CN=AdminSDHolder,CN=System"
| parse EventData with * 'ObjectDN">' ObjectDN "<" *
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by Computer, SubjectAccount, SubjectUserSid, SubjectLogonId, ObjectDN
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend Name = tostring(split(SubjectAccount, "\\")[1]), NTDomain = tostring(split(SubjectAccount, "\\")[0])

let known_locations = (SigninLogs
  | where TimeGenerated between(ago(7d)..ago(1d))
  | where ResultType == 0
  | extend LocationDetail = strcat(Location, "-", LocationDetails.state)
  | summarize by LocationDetail);
let known_asn = (SigninLogs
  | where TimeGenerated between(ago(7d)..ago(1d))
  | where ResultType == 0
  | summarize by AutonomousSystemNumber);
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0
| where isempty(DeviceDetail.deviceId)
| where AuthenticationRequirement == "singleFactorAuthentication"
| extend LocationParsed = parse_json(LocationDetails), DeviceParsed = parse_json(DeviceDetail)
| extend City = tostring(LocationParsed.city), State = tostring(LocationParsed.state)
| extend LocationDetail = strcat(Location, "-", State)
| extend DeviceId = tostring(DeviceParsed.deviceId), DeviceName=tostring(DeviceParsed.displayName), OS=tostring(DeviceParsed.operatingSystem), Browser=tostring(DeviceParsed.browser)
| where AutonomousSystemNumber !in (known_asn) and LocationDetail !in (known_locations)
| project TimeGenerated, Type, UserId, UserDisplayName, UserPrincipalName, IPAddress, Location, State, City, ResultType, ResultDescription, AppId, AppDisplayName, AuthenticationRequirement, ConditionalAccessStatus, ResourceDisplayName, ClientAppUsed, Identity, HomeTenantId, ResourceTenantId, Status, UserAgent, DeviceId, DeviceName, OS, Browser, MfaDetail
| extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])

let short_uaLength = 5;
let long_uaLength = 1000;
let c_threshold = 100;
W3CIISLog
// Exclude local IPs as these create noise
| where cIP !startswith "192.168." and cIP != "::1"
| where isnotempty(csUserAgent) and csUserAgent !in~ ("-", "MSRPC") and (string_size(csUserAgent) <= short_uaLength or string_size(csUserAgent) >= long_uaLength)
| extend csUserAgent_size = string_size(csUserAgent)
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), ConnectionCount = count() by Computer, sSiteName, sPort, csUserAgent, csUserAgent_size, csUserName , csMethod, csUriStem, sIP, cIP, scStatus, scSubStatus, scWin32Status
| where ConnectionCount < c_threshold
| 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 LookBack = 1h;
let Data = (
SigninLogs
| where TimeGenerated >= ago(LookBack)
| where parse_json(NetworkLocationDetails)[0].networkType != "trustedNamedLocation" // Excludes known tagged networks
// Counts the number of sign in events in the last hour every 15 minutes by IP
| make-series EventCounts = count() on TimeGenerated from ago(LookBack) to now() step 15m by IPAddress 
);
let AnomalyAlert = (
Data
| extend (Anomalies, Score, Baseline) = series_decompose_anomalies(EventCounts,1.5,-1,'linefit')
| mv-expand EventCounts,TimeGenerated,Anomalies to typeof(double),Baseline to typeof(long),Score to typeof(double)
| where Anomalies > 0
);
AnomalyAlert
| join kind = inner (SigninLogs
| where TimeGenerated between (ago(LookBack) .. now())
| where parse_json(NetworkLocationDetails)[0].networkType != "trustedNamedLocation"
| extend PasswordResult = tostring(parse_json(AuthenticationDetails).authenticationStepResultDetail)
| summarize UserCount = dcount(UserPrincipalName), UserList = make_set(UserPrincipalName), AppName = make_set(AppDisplayName), PasswordResult = make_list(PasswordResult) by IPAddress) on IPAddress
| where PasswordResult has "Correct Password"
| where UserCount > 1 // looks for events targeting more than one user.

AuditLogs
  | where Category == "ApplicationManagement"
  | where OperationName has_any ("Update Application", "Update Service principal")
  | where TargetResources has "AppIdentifierUri"
  | 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 mod_props = TargetResources[0].modifiedProperties
  | extend TargetAppName = tostring(TargetResources[0].displayName)
  | mv-expand mod_props
  | where mod_props.displayName has "AppIdentifierUri"
  | extend OldURI = tostring(mod_props.oldValue)
  | extend NewURI = tostring(mod_props.newValue)
  | extend UpdatedBy = iif(isnotempty(InitiatingAppName), InitiatingAppName, InitiatingUserPrincipalName)
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingAadUserId, InitiatingUserPrincipalName, InitiatingIPAddress

AuditLogs
  | where Category =~ "ApplicationManagement"
  | where Result =~ "success"
  | where OperationName =~ 'Update Application'
  | where TargetResources has "AppAddress"
  | mv-expand TargetResources
  | mv-expand TargetResources.modifiedProperties
  | where TargetResources_modifiedProperties.displayName =~ "AppAddress"
  | extend Key = tostring(TargetResources_modifiedProperties.displayName)
  | extend NewValue = TargetResources_modifiedProperties.newValue
  | extend OldValue = TargetResources_modifiedProperties.oldValue
  | where isnotempty(Key) and isnotempty(NewValue)
  | project-reorder Key, NewValue, OldValue
  | extend NewUrls = extract_all('"Address":([^,]*)', tostring(NewValue))
  | extend OldUrls = extract_all('"Address":([^,]*)', tostring(OldValue))
  | extend AddedUrls = set_difference(NewUrls, OldUrls)
  | where array_length(AddedUrls) > 0
  | extend UserAgent = iif(tostring(AdditionalDetails[0].key) == "User-Agent", tostring(AdditionalDetails[0].value), "")
  | 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 AddedBy = iif(isnotempty(InitiatingUserPrincipalName), InitiatingUserPrincipalName, InitiatingAppName)
  | extend TargetAppName = tostring(TargetResources.displayName)
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, TargetAppName, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIPAddress, AddedUrls, AddedBy, UserAgent

let timeframe = 1d;
let AccountAllowList = dynamic(['SYSTEM']);
let SubCategoryList = dynamic(["Logoff", "Account Lockout", "User Account Management", "Authorization Policy Change"]); // Add any Category in the list to be allowed or disallowed
let tokens = dynamic(["clear", "remove", "success:disable","failure:disable"]); 
(union isfuzzy=true
(
SecurityEvent
| where TimeGenerated >= ago(timeframe)
//| where Process =~ "auditpol.exe" 
| where CommandLine has_any (tokens)
| where AccountType !~ "Machine" and Account !in~ (AccountAllowList)
| parse CommandLine with * "/subcategory:" subcategorytoken
| extend SubCategory = tostring(split(subcategorytoken, "\"")[1]) , Toggle =  tostring(split(subcategorytoken, "\"")[2])
| where SubCategory in~ (SubCategoryList) //use in~ for inclusion or !in~ for exclusion
| where Toggle !in~ ("/failure:disable", " /success:enable /failure:disable") // use this filter if required to exclude certain toggles
| project TimeGenerated, Computer, Account, SubjectDomainName, SubjectUserName, Process, ParentProcessName, CommandLine, SubCategory, Toggle
| extend timestamp = TimeGenerated, AccountName = SubjectUserName, AccountDomain = SubjectDomainName, DeviceName = Computer
),
(
DeviceProcessEvents
| where TimeGenerated >= ago(timeframe)
// | where InitiatingProcessFileName =~ "auditpol.exe" 
| where InitiatingProcessCommandLine has_any (tokens)
| where AccountName !in~ (AccountAllowList)
| parse InitiatingProcessCommandLine with * "/subcategory:" subcategorytoken
| extend SubCategory = tostring(split(subcategorytoken, "\"")[1]) , Toggle =  tostring(split(subcategorytoken, "\"")[2])
| where SubCategory in~ (SubCategoryList) //use in~ for inclusion or !in~ for exclusion
| where Toggle !in~ ("/failure:disable", " /success:enable /failure:disable") // use this filter if required to exclude certain toggles
| project TimeGenerated, DeviceName, AccountName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessFileName, InitiatingProcessParentFileName,  InitiatingProcessCommandLine, SubCategory, Toggle
| extend timestamp = TimeGenerated, AccountName = InitiatingProcessAccountName, AccountDomain = InitiatingProcessAccountDomain
),
(
Event
| where TimeGenerated > ago(timeframe)
| where Source == "Microsoft-Windows-Sysmon"
| where EventID == 1
| extend EventData = parse_xml(EventData).DataItem.EventData.Data
| mv-expand bagexpansion=array EventData
| evaluate bag_unpack(EventData)
| extend Key=tostring(['@Name']), Value=['#text']
| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, RenderedDescription, MG, ManagementGroupName, Type, _ResourceId)
// | where OriginalFileName =~ "auditpol.exe"
| where CommandLine has_any (tokens)
| where User !in~ (AccountAllowList)
| parse CommandLine with * "/subcategory:" subcategorytoken
| extend SubCategory = tostring(split(subcategorytoken, "\"")[1]) , Toggle =  tostring(split(subcategorytoken, "\"")[2])
| where SubCategory in~ (SubCategoryList) //use in~ for inclusion or !in~ for exclusion
| where Toggle !in~ ("/failure:disable", " /success:enable /failure:disable") // use this filter if required to exclude certain toggles
| project TimeGenerated, Computer, User, Process, ParentImage, CommandLine, SubCategory, Toggle
| extend timestamp = TimeGenerated, AccountName = tostring(split(User, @'\')[1]), AccountUPNSuffix = tostring(split(User, @'\')[0]), DeviceName = Computer
)
)
| extend Account = strcat(AccountDomain, "\\", AccountName)

let CombinedSignInLogs = union isfuzzy=True AADNonInteractiveUserSignInLogs, SigninLogs;
  // Combine AADNonInteractiveUserSignInLogs and SigninLogs into a single table
  // Fetch Azure IP address ranges data from a JSON file hosted on GitHub
  let AzureRanges = externaldata(changeNumber: string, cloud: string, values: dynamic)
  ["https://raw.githubusercontent.com/microsoft/mstic/master/PublicFeeds/MSFTIPRanges/ServiceTags_Public.json"] with(format='multijson')
  // Load Azure IP address ranges from the JSON file hosted on GitHub
  | mv-expand values
  // Expand the values column into separate rows
  | extend Name = values.name, AddressPrefixes = tostring(values.properties.addressPrefixes);
  // Create additional columns for the name and address prefixes
  // Identify known locations to be excluded from analysis
  let ExcludedKnownLocations = CombinedSignInLogs
  // Filter the combined logs based on the specified time range
  | where TimeGenerated between (ago(14d)..ago(1d))
  // Filter by specific ResultType
  | where ResultType == 0
  // Summarize the logs by location
  | summarize by Location;
  // Find sign-in locations matching specific criteria
  let MatchedLocations = materialize(CombinedSignInLogs
  // Filter the combined logs based on the specified time range
  | where TimeGenerated > ago(1d)
  // Exclude specific ResultTypes
  | where ResultType !in (50126, 50053, 50074, 70044)
  // Exclude known locations
  | where Location !in (ExcludedKnownLocations));
  // Match IP addresses of matched locations with Azure IP address ranges
  let MatchedIPs = MatchedLocations
  // Use the 'ipv4_lookup' function to match IP addresses with Azure IP address ranges
  | evaluate ipv4_lookup(AzureRanges, IPAddress, AddressPrefixes)
  // Project only the IPAddress column
  | project IPAddress;
  // Exclude IP addresses that are already matched with Azure IP address ranges
  let MaxSetSize = 5; // Set the maximum size limit for make_set
  let ExcludedIPs = MatchedLocations
  // Filter out IP addresses that are already matched
  | where not (IPAddress in (MatchedIPs))
  // Exclude empty or null Location values
  | where isnotempty(Location)
  // Handle dynamic and string column values for LocationDetails and DeviceDetail
  | extend LocationDetails_dynamic = column_ifexists("LocationDetails_dynamic", "")
  | extend DeviceDetail_dynamic = column_ifexists("DeviceDetail_dynamic", "")
  | extend LocationDetails = iif(isnotempty(LocationDetails_dynamic), LocationDetails_dynamic, parse_json(LocationDetails_string))
  | extend DeviceDetail = iif(isnotempty(DeviceDetail_dynamic), DeviceDetail_dynamic, parse_json(DeviceDetail_string))
  // Extract location details (city and state)
  | extend City = tostring(LocationDetails.city)
  | extend State = tostring(LocationDetails.state)
  | extend Place = strcat(City, " - ", State)
  | extend DeviceId = tostring(DeviceDetail.deviceId)
  | extend Result = strcat(tostring(ResultType), " - ", ResultDescription)
  // Summarize the data based on UserPrincipalName, Location, and Category
  | summarize FirstSeen=min(TimeGenerated), LastSeen=max(TimeGenerated),
  make_set(Result, MaxSetSize), make_set(IPAddress, MaxSetSize),
  make_set(UserAgent, MaxSetSize), make_set(Place, MaxSetSize),
  make_set(DeviceId, MaxSetSize) by UserPrincipalName, Location, Category
  // Extract the username prefix and suffix from UserPrincipalName
  | extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0]);
  ExcludedIPs // Output the final result set
  | extend IP = set_IPAddress[0]

let admin_users = (IdentityInfo
  | summarize arg_max(TimeGenerated, *) by AccountUPN
  | where AssignedRoles contains "admin"
  | summarize by tolower(AccountUPN));
  let admin_asn = (SigninLogs
  | where TimeGenerated between (ago(7d)..ago(1d))
  | where tolower(UserPrincipalName) in (admin_users)
  | summarize by AutonomousSystemNumber);
  let admin_locations = (SigninLogs
  | where TimeGenerated between (ago(7d)..ago(1d))
  | where tolower(UserPrincipalName) in (admin_users)
  | summarize by Location);
  let admin_devices = (SigninLogs
  | where TimeGenerated between (ago(7d)..ago(1d))
  | where tolower(UserPrincipalName) in (admin_users)
  | extend deviceId = tostring(DeviceDetail.deviceId)
  | where isnotempty(deviceId)
  | summarize by deviceId);
  SigninLogs
  | where TimeGenerated > ago(1d)
  | where ResultType == 0
  | where tolower(UserPrincipalName) in (admin_users)
  | extend deviceId = tostring(DeviceDetail.deviceId)
  | where AutonomousSystemNumber !in (admin_asn) and deviceId !in (admin_devices) and Location !in (admin_locations)

imProcessCreate
| where CommandLine contains "TVqQAAMAAAAEAAA"
| where isnotempty(Process)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), count() by Dvc, ActorUsername, Process, CommandLine, ActingProcessName, EventVendor, EventProduct
| 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

AuditLogs
  | where Category =~ "ApplicationManagement"
  | where OperationName has_any ("Update Application", "Update Service principal")
  | 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 TargetAppName = tostring(TargetResources[0].displayName)
  | extend mod_props = TargetResources[0].modifiedProperties
  | mv-expand mod_props
  | extend Action = tostring(mod_props.displayName)
  | where Action contains "Url"
  | extend UpdatedBy = iif(isnotempty(InitiatingAppName), InitiatingAppName, InitiatingUserPrincipalName)
  | extend OldURL = tostring(mod_props.oldValue)
  | extend NewURL = tostring(mod_props.newValue)
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingAadUserId, InitiatingUserPrincipalName, InitiatingIPAddress, UpdatedBy

AuditLogs
  | where Category =~ "ApplicationManagement"
  | where OperationName =~ "Add owner to application"
  | 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 TargetUserPrincipalName = TargetResources[0].userPrincipalName
  | extend TargetAadUserId = tostring(TargetResources[0].id)
  | extend mod_props = TargetResources[0].modifiedProperties
  | mv-expand mod_props
  | where mod_props.displayName =~ "Application.DisplayName"
  | extend TargetAppName = tostring(parse_json(tostring(mod_props.newValue)))
  | extend AddedUser = TargetUserPrincipalName
  | extend UpdatedBy = iif(isnotempty(InitiatingAppName), InitiatingAppName, InitiatingUserPrincipalName)
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | extend TargetAccountName = tostring(split(TargetUserPrincipalName, "@")[0]), TargetAccountUPNSuffix = tostring(split(TargetUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingAadUserId, InitiatingUserPrincipalName, InitiatingIPAddress, TargetAppName, AddedUser, UpdatedBy

AuditLogs
  | where Category =~ "RoleManagement"
  | where OperationName =~ "Update role setting in PIM"
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, OperationName, ResultReason, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIPAddress, InitiatingAccountName, InitiatingAccountUPNSuffix

let aadFunc = (tableName:string){
CommonSecurityLog
| where DeviceVendor =~ "Cisco"
| where DeviceAction =~ "denied"
| where ipv4_is_private(SourceIP) == false
| summarize count() by SourceIP
| join (
    // Successful signins from IPs blocked by the firewall solution are suspect
    // Include fully successful sign-ins, but also ones that failed only at MFA stage
    // as that supposes the password was sucessfully guessed.
  table(tableName)
  | where ResultType in ("0", "50074", "50076")
) on $left.SourceIP == $right.IPAddress
| extend AccountName = tostring(split(Account, "@")[0]), AccountUPNSuffix = tostring(split(Account, "@")[1])
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
union isfuzzy=true aadSignin, aadNonInt

let known_users = (AuditLogs
  | where TimeGenerated between(ago(14d)..ago(1d))
  | where OperationName has "conditional access policy"
  | where Result =~ "success"
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | summarize by InitiatingUserPrincipalName);
  AuditLogs
  | where TimeGenerated > ago(1d)
  | where OperationName has "conditional access policy"
  | where Result =~ "success"
  | extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
  | extend InitiatingAppId = tostring(InitiatedBy.app.appId)
  | 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 CAPolicyName = tostring(TargetResources[0].displayName)
  | where InitiatingUserPrincipalName !in (known_users)
  | extend NewPolicyValues = TargetResources[0].modifiedProperties[0].newValue
  | extend OldPolicyValues = TargetResources[0].modifiedProperties[0].oldValue
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, OperationName, CAPolicyName, InitiatingAppId, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIPAddress, NewPolicyValues, OldPolicyValues

let torProxies=dynamic(["tor2web.org", "tor2web.com", "torlink.co", "onion.to", "onion.ink", "onion.cab", "onion.nu", "onion.link", 
"onion.it", "onion.city", "onion.direct", "onion.top", "onion.casa", "onion.plus", "onion.rip", "onion.dog", "tor2web.fi", 
"tor2web.blutmagie.de", "onion.sh", "onion.lu", "onion.pet", "t2w.pw", "tor2web.ae.org", "tor2web.io", "tor2web.xyz", "onion.lt", 
"s1.tor-gateways.de", "s2.tor-gateways.de", "s3.tor-gateways.de", "s4.tor-gateways.de", "s5.tor-gateways.de", "hiddenservice.net"]);
_Im_Dns(domain_has_any=torProxies)
| extend HostName = tostring(split(Dvc, ".")[0]), DomainIndex = toint(indexof(Dvc, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Dvc, DomainIndex + 1), Dvc)
| project-away DomainIndex

SecurityAlert 
| where AlertName == "Impossible travel activity"
| extend Extprop = parsejson(Entities)
| mv-expand Extprop
| extend Extprop = parsejson(Extprop)
| extend CmdLine = iff(Extprop['Type']=="process", Extprop['CommandLine'], '')
| extend File = iff(Extprop['Type']=="file", Extprop['Name'], '')
| extend Account = Extprop['Name']
| extend Domain = Extprop['UPNSuffix']
| extend Account = iif(isnotempty(Domain) and Extprop['Type']=="account", tolower(strcat(Account, "@", Domain)), iif(Extprop['Type']=="account", tolower(Account), ""))
| extend IpAddress = iff(Extprop["Type"] == "ip",Extprop['Address'], '')
| extend Process = iff(isnotempty(CmdLine), CmdLine, File)
| project TimeGenerated,Account,IpAddress,CompromisedEntity,Description,ProviderName,ResourceId
| join kind=inner
(
OfficeActivity
| where Operation =~ "Add-MailboxPermission"
| extend value = tostring(parse_json(Parameters)[3].Value)
| where value contains "FullAccess"
| where ResultStatus == "True"
| project Parameters,TimeGenerated,value,RecordType,Operation,OrganizationId,UserType,UserKey,OfficeWorkload,ResultStatus,OfficeObjectId,UserId,ClientIP,ExternalAccess,OriginatingServer,OrganizationName,TenantId,ElevationTime,SourceSystem,OfficeId,OfficeTenantId,Type,SourceRecordId
) on $left.Account == $right.UserId
| join kind=inner
(
AuditLogs
| where ActivityDisplayName =~ "Add eligible member to role in PIM requested (timebound)"
| where AADOperationType =~ "CreateRequestEligibleRole"
| where TargetResources has_any ("-PRIV", "Administrator", "Security")
| extend BuiltinRole = tostring(parse_json(TargetResources[0].displayName))
| extend CustomGroup = tostring(parse_json(TargetResources[3].displayName))
| extend TargetAccount = tostring(parse_json(TargetResources[2].displayName))
| extend Initiatedby = Identity
| project TimeGenerated, ActivityDisplayName, AADOperationType, Initiatedby, TargetAccount, BuiltinRole, CustomGroup, LoggedByService, Result, ResourceId, Id
| sort by TimeGenerated desc
) on $left.UserId == $right.Initiatedby
| extend AccountName = tostring(split(Initiatedby, "@")[0]), AccountUPNSuffix = tostring(split(Initiatedby, "@")[1])
| project AADOperationType, ActivityDisplayName,AccountName, AccountUPNSuffix, Id,ResourceId,IpAddress

let discord=dynamic(["cdn.discordapp.com", "media.discordapp.com"]);
  _Im_WebSession(url_has_any=discord, eventresult='Success')
  | where Url has "attachments"
  | extend DiscordServerId = extract(@"\/attachments\/([0-9]+)\/", 1, Url)
  | summarize dcount(Url), make_set(SrcUsername), make_set(SrcIpAddr), make_set(Url), min(TimeGenerated), max(TimeGenerated), make_set(EventResult) by DiscordServerId
  | mv-expand set_SrcUsername to typeof(string), set_Url to typeof(string), set_EventResult to typeof(string), set_SrcIpAddr to typeof(string)
  | summarize by DiscordServerId, dcount_Url, set_SrcUsername, min_TimeGenerated, max_TimeGenerated, set_EventResult, set_SrcIpAddr, set_Url
  | project StartTime=min_TimeGenerated, EndTime=max_TimeGenerated, Result=set_EventResult, SourceUser=set_SrcUsername, SourceIP=set_SrcIpAddr, RequestURL=set_Url
  | where RequestURL has_any (".bin",".exe",".dll",".bin",".msi")
  | extend AccountName = tostring(split(SourceUser, "@")[0]), AccountUPNSuffix = tostring(split(SourceUser, "@")[1])

let timeframe = 1d;
let cmdList = dynamic(["Set-CASMailbox","ActiveSyncAllowedDeviceIDs","add"]);
(union isfuzzy=true
(
SecurityEvent
| where TimeGenerated >= ago(timeframe)
| where EventID == 4688
| where CommandLine has_all (cmdList)
| project Type, TimeGenerated, Computer, Account, SubjectDomainName, SubjectUserName, Process, ParentProcessName, CommandLine
| extend timestamp = TimeGenerated, AccountEntity = Account, HostEntity = Computer
),
( WindowsEvent
| where TimeGenerated >= ago(timeframe)
| where EventID == 4688
| where EventData has_all (cmdList)
| extend CommandLine = tostring(EventData.CommandLine) 
| where CommandLine has_all (cmdList)
| extend Account =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend SubjectUserName = tostring(EventData.SubjectUserName)
| extend SubjectDomainName = tostring(EventData.SubjectDomainName)
| extend NewProcessName = tostring(EventData.NewProcessName)
| extend Process=tostring(split(NewProcessName, '\\')[-1])
| extend ParentProcessName = tostring(EventData.ParentProcessName)
| project Type, TimeGenerated, Computer, Account, SubjectDomainName, SubjectUserName, Process, ParentProcessName, CommandLine
| extend timestamp = TimeGenerated, AccountEntity = Account, HostEntity = Computer
),
(
DeviceProcessEvents
| where TimeGenerated >= ago(timeframe)
| where InitiatingProcessCommandLine has_all (cmdList)
| project Type, TimeGenerated, DeviceName, AccountName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessFileName, InitiatingProcessParentFileName,  InitiatingProcessCommandLine
| extend timestamp = TimeGenerated, AccountDomain = InitiatingProcessAccountDomain, AccountName = InitiatingProcessAccountName, HostEntity = DeviceName
),
(
Event
| where TimeGenerated > ago(timeframe)
| where Source == "Microsoft-Windows-Sysmon"
| where EventID == 1
| extend EventData = parse_xml(EventData).DataItem.EventData.Data
| mv-expand bagexpansion=array EventData
| evaluate bag_unpack(EventData)
| extend Key=tostring(['@Name']), Value=['#text']
| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, RenderedDescription, MG, ManagementGroupName, Type, _ResourceId)
| where TimeGenerated >= ago(timeframe)
| where CommandLine has_all (cmdList)
| extend Type = strcat(Type, ": ", Source)
| project Type, TimeGenerated, Computer, User, Process, ParentImage, CommandLine
| extend timestamp = TimeGenerated, AccountEntity = User, HostEntity = Computer
)
)
| extend HostName = tostring(split(HostEntity, ".")[0]), DomainIndex = toint(indexof(HostEntity, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(HostEntity, DomainIndex + 1), HostEntity)
| extend AccountName = tostring(split(AccountEntity, @'\')[1]), AccountDomain = tostring(split(AccountEntity, @'\')[0])

AuditLogs
  | where OperationName has "Consent to application"
  | where Result =~ "failure"
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
  | extend userAgent = iif(AdditionalDetails[0].key == "User-Agent", tostring(AdditionalDetails[0].value), tostring(AdditionalDetails[1].value))
  | where isnotempty(TargetResources)
  | extend TargetAppName = tostring(TargetResources[0].displayName)
  | extend TargetAppId = tostring(TargetResources[0].id)
  | mv-expand TargetResources[0].modifiedProperties
  | extend TargetResources_0_modifiedProperties = columnifexists("TargetResources_0_modifiedProperties", '')
  | where isnotempty(TargetResources_0_modifiedProperties)
  | where TargetResources_0_modifiedProperties.displayName =~ "MethodExecutionResult."
  | extend TargetPropertyDisplayName = tostring(TargetResources_0_modifiedProperties.displayName)
  | extend FailureReason = tostring(parse_json(tostring(TargetResources_0_modifiedProperties.newValue)))
  | where FailureReason contains "Risky"
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, OperationName, Result, TargetAppName, TargetAppId, FailureReason, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIPAddress, userAgent

let iocs = externaldata(DateAdded:string,IoC:string,Type:string,TLP:string) [@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/Europium_September2022.csv"] with (format="csv", ignoreFirstRecord=True);
let sha256Hashes = (iocs | where Type =~ "sha256" | project IoC);
let IPList = (iocs | where Type =~ "ip"| project IoC);
let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
(union isfuzzy=true
(CommonSecurityLog
| where SourceIP in (IPList) or DestinationIP in (IPList) or Message has_any (IPList)
| parse Message with * '(' DNSName ')' * 
| project TimeGenerated, SourceIP, DestinationIP, Message, SourceUserID, RequestURL, DNSName, Type
| extend MessageIP = extract(IPRegex, 0, Message), RequestIP = extract(IPRegex, 0, RequestURL)
| extend IPMatch = case(SourceIP in (IPList), "SourceIP", DestinationIP in (IPList), "DestinationIP", MessageIP in (IPList), "Message",  "NoMatch")
| extend timestamp = TimeGenerated, IPEntity = case(IPMatch == "SourceIP", SourceIP, IPMatch == "DestinationIP", DestinationIP, IPMatch == "Message", MessageIP, "NoMatch")
),
(DnsEvents
| where IPAddresses in (IPList)  
| project TimeGenerated, Computer, IPAddresses, Name, ClientIP, Type
| extend DestinationIPAddress = IPAddresses, DNSName = Name, Computer 
| extend timestamp = TimeGenerated, IPEntity = DestinationIPAddress, HostEntity = Computer
),
(VMConnection
| where SourceIp in (IPList) or DestinationIp in (IPList)
| parse RemoteDnsCanonicalNames with * '["' DNSName '"]' *
| project TimeGenerated, Computer, Direction, ProcessName, SourceIp, DestinationIp, DestinationPort, RemoteDnsQuestions, DNSName,BytesSent, BytesReceived, RemoteCountry, Type
| extend IPMatch = case( SourceIp in (IPList), "SourceIP", DestinationIp in (IPList), "DestinationIP", "None") 
| extend timestamp = TimeGenerated, IPEntity = case(IPMatch == "SourceIP", SourceIp, IPMatch == "DestinationIP", DestinationIp, "NoMatch"), File = ProcessName, HostEntity = Computer
),
(Event
| where Source == "Microsoft-Windows-Sysmon"
| where EventID == 3
| extend EvData = parse_xml(EventData)
| extend EventDetail = EvData.DataItem.EventData.Data
| extend SourceIP = tostring(EventDetail.[9].["#text"]), DestinationIP = tostring(EventDetail.[14].["#text"]), Image = tostring(EventDetail.[4].["#text"])
| where SourceIP in (IPList) or DestinationIP in (IPList)
| project TimeGenerated, SourceIP, DestinationIP, Image, UserName, Computer, Type
| extend IPMatch = case( SourceIP in (IPList), "SourceIP", DestinationIP in (IPList), "DestinationIP", "None")
| extend timestamp = TimeGenerated, File = tostring(split(Image, '\\', -1)[-1]), IPEntity = case(IPMatch == "SourceIP", SourceIP, IPMatch == "DestinationIP", DestinationIP, "None"), 
HostEntity = Computer, AccountName = tostring(split(UserName, @'\')[1]), AccountDomain = tostring(split(UserName, @'\')[0])
| extend InitiatingProcessAccount = UserName
), 
(OfficeActivity
| where ClientIP in (IPList) 
| project TimeGenerated, UserAgent, Operation, RecordType, UserId, ClientIP, Type
| extend timestamp = TimeGenerated, IPEntity = ClientIP, AccountName = tostring(split(UserId, "@")[0]), AccountDomain = tostring(split(UserId, "@")[1])
| extend InitiatingProcessAccount = UserId
),
(DeviceNetworkEvents
| where RemoteIP in (IPList) or InitiatingProcessSHA256 in (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, Computer = DeviceName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, 
InitiatingProcessFolderPath, InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, RemoteIP, RemoteUrl, RemotePort, LocalIP, Type
| extend timestamp = TimeGenerated, IPEntity = RemoteIP, HostEntity = Computer, AccountName = InitiatingProcessAccountName, AccountDomain = InitiatingProcessAccountDomain
| extend InitiatingProcessAccount = strcat(AccountDomain, "\\", AccountName)
),
(WindowsFirewall
| where SourceIP in (IPList) or DestinationIP in (IPList) 
| project TimeGenerated, Computer, CommunicationDirection, SourceIP, DestinationIP, SourcePort, DestinationPort, Type
| extend IPMatch = case( SourceIP in (IPList), "SourceIP", DestinationIP in (IPList), "DestinationIP", "None")
| extend timestamp = TimeGenerated, HostEntity = Computer, IPEntity = case(IPMatch == "SourceIP", SourceIP, IPMatch == "DestinationIP", DestinationIP, "None")
), 
(imFileEvent
| where TargetFileSHA256 has_any (sha256Hashes)
| extend Account = ActorUsername, Computer = DvcHostname, IPAddress = SrcIpAddr, CommandLine = ActingProcessCommandLine, FileHash = TargetFileSHA256
| project Type, TimeGenerated, Computer, Account, IPAddress, CommandLine, FileHash
| extend timestamp = TimeGenerated, IPEntity = IPAddress,  HostEntity = Computer, Algorithm = "SHA256", FileHash = tostring(FileHash)
| extend AccountName = tostring(split(Account, @'\')[1]), AccountDomain = tostring(split(Account, @'\')[0])
| extend InitiatingProcessAccount = Account
),
(DeviceFileEvents
| where SHA256 has_any (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, DeviceName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, InitiatingProcessFolderPath, 
InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, InitiatingProcessSHA256, Type
| extend timestamp = TimeGenerated, HostEntity = DeviceName, AccountName = InitiatingProcessAccountName, AccountDomain = InitiatingProcessAccountDomain, 
Algorithm = "SHA256", FileHash = tostring(InitiatingProcessSHA256), CommandLine = InitiatingProcessCommandLine,Image = InitiatingProcessFolderPath
| extend InitiatingProcessAccount = strcat(AccountDomain, "\\", AccountName)
),
(DeviceImageLoadEvents
| where SHA256 has_any (sha256Hashes)
| project TimeGenerated, ActionType, DeviceId, DeviceName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessCommandLine, InitiatingProcessFolderPath, 
InitiatingProcessId, InitiatingProcessParentFileName, InitiatingProcessFileName, InitiatingProcessSHA256, Type
| extend timestamp = TimeGenerated, HostEntity = DeviceName, AccountName = InitiatingProcessAccountName, AccountDomain = InitiatingProcessAccountDomain, 
Algorithm = "SHA256", FileHash = tostring(InitiatingProcessSHA256),  CommandLine = InitiatingProcessCommandLine,Image = InitiatingProcessFolderPath
| extend InitiatingProcessAccount = strcat(AccountDomain, "\\", AccountName)
),
(Event
| where Source =~ "Microsoft-Windows-Sysmon"
| where EventID == 1
| extend EvData = parse_xml(EventData)
| extend EventDetail = EvData.DataItem.EventData.Data
| extend Image = EventDetail.[4].["#text"],  CommandLine = EventDetail.[10].["#text"], Hashes = tostring(EventDetail.[17].["#text"])
| extend Hashes = extract_all(@"(?P<key>\w+)=(?P<value>[a-zA-Z0-9]+)", dynamic(["key","value"]), Hashes)
| extend Hashes = column_ifexists("Hashes", dynamic(["", ""])), CommandLine = column_ifexists("CommandLine", "")
| mv-expand Hashes
| where Hashes[0] =~ "SHA256" and Hashes[1] has_any (sha256Hashes)  
| project TimeGenerated, EventDetail, UserName, Computer, Type, Source, Hashes, CommandLine, Image
| extend Type = strcat(Type, ": ", Source)
| extend timestamp = TimeGenerated, HostEntity = Computer, AccountName = tostring(split(UserName, @'\')[1]), AccountUPNSuffix = tostring(split(UserName, @'\')[0]), FileHash = tostring(Hashes[1])
| extend InitiatingProcessAccount = UserName
)
)
| extend HostName = tostring(split(HostEntity, ".")[0]), DomainIndex = toint(indexof(HostEntity, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(HostEntity, DomainIndex + 1), HostEntity)

let successCodes = dynamic([200, 302, 401]);
W3CIISLog
| where scStatus has_any (successCodes)
| where ipv4_is_private(cIP) == False
| where csUriStem hasprefix "/autodiscover/autodiscover.json"
| project TimeGenerated, cIP, sIP, sSiteName, csUriStem, csUriQuery, Computer, csUserName, _ResourceId, FileUri
| where (csUriQuery !has "Protocol" and isnotempty(csUriQuery))
or (csUriQuery has_any("/mapi/", "powershell"))
or (csUriQuery contains "@" and csUriQuery matches regex @"\.[a-zA-Z]{2,4}?(?:[a-zA-Z]{2,4}\/)")
or (csUriQuery contains ":" and csUriQuery matches regex @"\:[0-9]{2,4}\/")
| 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)

let suspiciousCmdLineKeywords = dynamic(["http://", "https://"]);
// Identify exchange servers based on known paths
// Summarize these to get a list of exchange server hostnames
let exchangeServers = W3CIISLog
| where csUriStem has_any("/owa/","/ews/","/ecp/","/autodiscover/")
// Only where successful, rule out failed scanning
| where scStatus startswith "2"
| summarize by Computer;
DeviceProcessEvents
| where DeviceName in~ (exchangeServers)
// Where the IIS worker process initiated CMD or PowerShell
| where InitiatingProcessParentFileName == "w3wp.exe"
| where InitiatingProcessFileName has_any("cmd.exe", "powershell.exe")
// Where CMD or PowerShell command line included parameters associated with CVE-2022-41040/CVE-2022-41082 exploitation
| where ProcessCommandLine has_any(suspiciousCmdLineKeywords)
| project TimeGenerated, DeviceId, DeviceName, InitiatingProcessFileName, ProcessCommandLine, AccountDomain = InitiatingProcessAccountDomain, AccountName = InitiatingProcessAccountName
| extend Account = strcat(AccountDomain, "\\", AccountName)
| extend HostName = tostring(split(DeviceName, ".")[0]), DomainIndex = toint(indexof(DeviceName, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(DeviceName, DomainIndex + 1), DeviceName)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

let selfServicePasswordReset = dynamic(["Self-service password reset flow activity progress", "Change password (self-service)", "Reset password (self-service)"]); 
//Self-service password reset flow activity progress is typically caused by a password policy which requires users to rotate passwords. This operation already implies the user has signed in successfully and therefore the password reset is non-malicious.
let PerUserThreshold = 5;
let TotalThreshold = 100;
let action = dynamic(["change", "changed", "reset"]);
let pWord = dynamic(["password", "credentials"]);
let PasswordResetMultiDataSource =
(union isfuzzy=true
(//Password reset events
//4723: An attempt was made to change an account's password
//4724: An attempt was made to reset an accounts password
SecurityEvent
| where EventID in ("4723","4724")
| project TimeGenerated, Computer, AccountType, Account, Type, TargetUserName),
(//Password reset events
//4723: An attempt was made to change an account's password
//4724: An attempt was made to reset an accounts password
WindowsEvent
| where EventID in ("4723","4724")
| extend SubjectUserSid = tostring(EventData.SubjectUserSid)
| extend TargetUserName = tostring(EventData.TargetUserName)
| extend Account = strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend AccountType=case(Account endswith "$" or SubjectUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(SubjectUserSid), "", "User")
| project TimeGenerated, Computer, AccountType, Account, Type, TargetUserName),
(//Azure Active Directory Password reset events
AuditLogs
| where OperationName has_any (pWord) and OperationName has_any (action) and Result =~ "success"
| where OperationName !in (selfServicePasswordReset)
| mv-apply TargetResource = TargetResources on 
  (
      where TargetResource.type =~ "User"
      | extend AccountType = tostring(TargetResource.type),
               Account = tostring(InitiatedBy.user.userPrincipalName),
               TargetUserName = tolower(tostring(TargetResource.userPrincipalName))
  )
| project TimeGenerated, AccountType, Account, TargetUserName, Computer = "", Type),
(//OfficeActive ActiveDirectory Password reset events
OfficeActivity
| where OfficeWorkload == "AzureActiveDirectory"
| where (ExtendedProperties has_any (pWord) or ModifiedProperties has_any (pWord)) and (ExtendedProperties has_any (action) or ModifiedProperties has_any (action))
| extend AccountType = UserType, Account = OfficeObjectId
| project TimeGenerated, AccountType, Account, Type, Computer = ""),
(// Unix syslog password reset events
Syslog
| where Facility in ("auth","authpriv")
| where SyslogMessage has_any (pWord) and SyslogMessage has_any (action)
| extend AccountType = iif(SyslogMessage contains "root", "Root", "Non-Root")
| where SyslogMessage matches regex ".*password changed for.*"
| parse SyslogMessage with * "password changed for" Account
| project TimeGenerated, AccountType, Account, Computer = HostName, Type)
);
let pwrmd = PasswordResetMultiDataSource
| project TimeGenerated, Computer, AccountType, Account, Type, TargetUserName;
(union isfuzzy=true
(pwrmd
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), Computerlist = make_set(Computer, 25), AccountType = make_set(AccountType, 25), Computer = arg_max(Computer , TimeGenerated), TargetUserList = make_set(TargetUserName, 25), TargetUserName = arg_max(TargetUserName, TimeGenerated), Total=count() by Account, Type
| where Total > PerUserThreshold
| extend ResetPivot = "PerUserReset"),
(pwrmd
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), ComputerList = make_set(Computer, 25), AccountList = make_set(Account, 25), AccountType = make_set(AccountType, 25), Computer = arg_max(Computer , TimeGenerated), TargetUserList = make_set(TargetUserName, 25), TargetUserName = arg_max(TargetUserName, TimeGenerated), Total=count() by Type
| where Total > TotalThreshold
| extend ResetPivot = "TotalUserReset")
)
| extend timestamp = StartTimeUtc, HostName = tostring(split(Computer, '.', 0)[0]), DnsDomain = tostring(strcat_array(array_slice(split(Computer, '.'), 1, -1), '.')), Name = tostring(split(Account, '@', 0)[0]), UPNSuffix = tostring(split(Account, '@', 1)[0]), TargetName = tostring(split(TargetUserName,'@',0)[0]), TargetUPNSuffix = tostring(split(TargetUserName,'@',1)[0])

let endtime = 1d;
let starttime = 8d;
let threshold = 2.0;
(union isfuzzy=true
(SecurityEvent
| where TimeGenerated >= ago(endtime)
| where EventID == 4624 and LogonType == 10
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), ComputerCountToday = dcount(Computer), ComputerSet = makeset(Computer), ProcessSet = makeset(ProcessName)
by Account = tolower(Account), IpAddress, AccountType, Activity, LogonTypeName),
(WindowsEvent
| where TimeGenerated >= ago(endtime)
| where EventID == 4624
| extend LogonType = tostring(EventData.LogonType)
| where  LogonType == 10
| extend ProcessName = tostring(EventData.ProcessName)
| extend Account = strcat(tostring(EventData.TargetDomainName),"\\", tostring(EventData.TargetUserName))
| 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), ComputerCountToday = dcount(Computer), ComputerSet = makeset(Computer), ProcessSet = makeset(ProcessName)
by Account = tolower(Account), IpAddress, AccountType, Activity, LogonTypeName)
)
| join kind=inner (
(union isfuzzy=true
(SecurityEvent
| where TimeGenerated >= ago(starttime) and TimeGenerated < ago(endtime)
| where EventID == 4624 and LogonType == 10
| summarize ComputerCountPrev7Days = dcount(Computer) by Account = tolower(Account), IpAddress
),
( WindowsEvent
| where TimeGenerated >= ago(starttime) and TimeGenerated < ago(endtime)
| where EventID == 4624  and EventData has ("10")
| extend LogonType = toint(EventData.LogonType)
| where  LogonType == 10
| extend Account = strcat(tostring(EventData.TargetDomainName),"\\", tostring(EventData.TargetUserName))
| extend IpAddress = tostring(EventData.IpAddress)
| summarize ComputerCountPrev7Days = dcount(Computer) by Account = tolower(Account), IpAddress)
)
) on Account, IpAddress
| extend Ratio = iff(isempty(ComputerCountPrev7Days), toreal(ComputerCountToday), ComputerCountToday / (ComputerCountPrev7Days * 1.0))
// Where the ratio of today to previous 7 days is more than double.
| where Ratio > threshold
| project StartTime, EndTime, Account, IpAddress, ComputerSet, ComputerCountToday, ComputerCountPrev7Days, Ratio, AccountType, Activity, LogonTypeName, ProcessSet
| extend AccountName = tostring(split(Account, @"\")[1]), AccountNTDomain = tostring(split(Account, @"\")[0])

// Creating a list of successful sign-in by users in the last 7 days.
let KnownUserCountry = (
SigninLogs
| where TimeGenerated between (ago(7d) .. ago(1d) ) 
| where ResultType == 0
| summarize KnownCountry = make_set(Location,1048576) by UserPrincipalName
);
// Identify sign-ins that are no successful but have the auth details indicating a correct password.
SigninLogs
| where TimeGenerated >= ago(1d)
| where ResultType != 0
| extend ParseAuth = parse_json(AuthenticationDetails)
| extend AuthMethod = tostring(ParseAuth.[0].authenticationMethod),
    PasswordResult = tostring(ParseAuth.[0].authenticationStepResultDetail),
    AuthSucceeded = tostring(ParseAuth.[0].succeeded)
| where PasswordResult == "Correct Password" or AuthSucceeded == "true"
| where AuthMethod == "Password"
| extend failureReason = tostring(Status.failureReason)
| summarize NewCountry = make_set(Location,1048576), LastObservedTime = max(TimeGenerated), AppName = make_set(AppDisplayName,1048576) by UserPrincipalName, PasswordResult, AuthSucceeded, failureReason
// Combining both tables by user
| join kind=inner KnownUserCountry on UserPrincipalName
// Compare both arrays and identify if the country has been observed in the past.
| extend CountryDiff = set_difference(NewCountry,KnownCountry)
| extend CountryDiffCount = array_length(CountryDiff)
// Count the new column to only alert if there is a difference between both arrays
| where CountryDiffCount != 0
| extend NewCountryEvent = CountryDiff
// Getting UserName and Domain
| extend Name = split(UserPrincipalName,"@",0),
    Domain = split(UserPrincipalName,"@",1)
| mv-expand Name,Domain

let OMIVulnerabilityPatchVersion = "OMIVulnerabilityPatchVersion:1.13.40-0";
Heartbeat
| where Category == "Direct Agent"
| summarize arg_max(TimeGenerated,*) by Computer
| parse strcat("Version:" , Version) with * "Version:" Major:long "."
Minor:long "." Patch:long "-" *
| parse OMIVulnerabilityPatchVersion with * "OMIVulnerabilityPatchVersion:"
OMIVersionMajor:long "." OMIVersionMinor:long "." OMIVersionPatch:long "-" *
| where Major <OMIVersionMajor or (Major==OMIVersionMajor and Minor
<OMIVersionMinor) or (Major==OMIVersionMajor and Minor==OMIVersionMinor and
Patch<OMIVersionPatch) 
| project Version, Major,Minor,Patch,
Computer,ComputerIP,OSType,OSName,ResourceId