Cayosoft Guardian - Cloud Application Security Threats

Original query, unchanged.

// ATTENTION: ruleFrequency must match the "queryFrequency" value in the rule's scheduling settings!
// ATTENTION: lookbackPeriod must match the "queryPeriod" value in the rule's scheduling settings!
// If you change one, change both, in every location, or you will reintroduce duplicate alerts.
// Pattern reference: https://learn.microsoft.com/en-us/azure/sentinel/ingestion-delay
let ruleFrequency = 1h;
let lookbackPeriod = 1d;
let window_end = bin(now(), ruleFrequency);      
let window_start = window_end - ruleFrequency;   
CayosoftThreatAlerts_CL
| where TimeGenerated >= ago(lookbackPeriod)
| where ingestion_time() > window_start and ingestion_time() <= window_end
| extend ParsedCategories = todynamic(Categories)
| extend TargetObjectTypeNormalized = tostring(TargetObjectType)
| extend TargetLocationNormalized = tostring(TargetLocation)
| mv-apply Cat = ParsedCategories on (
    summarize Values = make_list(tostring(Cat.value)) by Key = iff(tostring(Cat.name) startswith 'MITRE ATT', 'MITRE ATT&CK', tostring(Cat.name))
| summarize CleanCategories = make_bag(pack(Key, strcat_array(Values, ', ')))
    )
| extend Tactics = iff(isnotempty(CleanCategories['Attack tactics']), split(replace_string(tostring(CleanCategories['Attack tactics']), ' ', ''), ','), dynamic([]))
| extend Techniques = iff(isnotempty(CleanCategories['MITRE ATT&CK']), extract_all(@"(T\d{4})", tostring(CleanCategories['MITRE ATT&CK'])), dynamic([]))
| extend ExtractedDomain = tostring(split(TargetLocationNormalized, '/')[0])
| extend IsEntra = TargetObjectTypeNormalized startswith 'microsoft.graph'
| extend IsAD = TargetObjectTypeNormalized startswith 'microsoft.ad' or TargetObjectTypeNormalized has 'ServiceAccount'
| extend IsGMSA = TargetObjectTypeNormalized contains 'ManagedServiceAccount'
| extend ParsedUser = case(
    TargetObjectTypeNormalized has 'user' or TargetObjectTypeNormalized has 'ServiceAccount' or TargetObjectTypeNormalized has 'inetOrgPerson', TargetObjectName,
    extract("member\\s+'([^']+)'", 1, Subject)
    )
| extend TargetAccountAadUserId = iff(IsEntra and TargetObjectTypeNormalized has 'user', TargetObjectId, dynamic(null))
| extend TargetAccountName = case(
    IsEntra and TargetObjectTypeNormalized has 'user', iff(ParsedUser contains '@', tostring(split(ParsedUser, '@')[0]), ParsedUser),
    IsAD and (TargetObjectTypeNormalized has 'user' or TargetObjectTypeNormalized has 'foreignSecurityPrincipal' or IsGMSA), TargetObjectName,
    IsAD and TargetObjectTypeNormalized has 'directoryObject', TargetObjectName,
    isnotempty(ParsedUser), iff(ParsedUser contains '@', tostring(split(ParsedUser, '@')[0]), ParsedUser),
    dynamic(null)
    )
| extend TargetAccountObjectGuid = iff(IsAD and (TargetObjectTypeNormalized has 'user' or TargetObjectTypeNormalized has 'foreignSecurityPrincipal' or IsGMSA), TargetObjectId, dynamic(null))
| extend TargetHostName = iff(TargetObjectTypeNormalized has 'computer' or TargetObjectTypeNormalized has 'device', TargetObjectName, dynamic(null))
| extend TargetHostDomain = iff(IsAD and TargetObjectTypeNormalized has 'computer', ExtractedDomain, dynamic(null))
| extend TargetHostAzureID = iff(IsEntra and TargetObjectTypeNormalized has 'device', TargetObjectId, dynamic(null))
| extend RawPath = iff(TargetLocationNormalized contains '/', substring(TargetLocationNormalized, indexof(TargetLocationNormalized, '/') + 1), '')
// Convert domain
| extend DomainDN = strcat('DC=', replace_string(ExtractedDomain, '.', ',DC='))
// Smart path processing
| extend PathDN = case(
    isempty(RawPath), '',
    RawPath has_any ('Builtin', 'Users', 'Computers', 'ForeignSecurityPrincipals', 'Managed Service Accounts'), strcat('CN=', replace_string(RawPath, '/', ',CN=')),
    strcat('OU=', replace_string(RawPath, '/', ',OU='))
)
// Assemble the final DistinguishedName and Name for the group
| extend TargetGroupName = iff(TargetObjectTypeNormalized has 'group', TargetObjectName, dynamic(null))
| extend TargetGroupDistinguishedName = iff(
    TargetObjectTypeNormalized has 'group', 
    strcat('CN=', TargetObjectName, iff(isnotempty(PathDN), strcat(',', PathDN), ''), ',', DomainDN), 
    dynamic(null)
)
| extend TargetGroupObjectGuid = iff(TargetObjectTypeNormalized has 'group', TargetObjectId, dynamic(null))
| extend TargetDNSName = case(
    IsAD and TargetObjectTypeNormalized has 'trustedDomain', TargetObjectName,
    isnotempty(TargetLocationNormalized), ExtractedDomain,
    dynamic(null)
    )
| extend ThreatDocUrl = tolower( strcat('https://www.cayosoft.com/threat-directory/', ThreatId, '/'))
| extend AlertSeverity = case(
    Severity == 'high', 'High',
    Severity == 'medium', 'Medium',
    Severity == 'low', 'Low',
    Severity == 'informational', 'Informational',
    'Informational'
    )
| project-away Categories, ParsedCategories
| project-rename Categories = CleanCategories 
| where IsEntra and (TargetObjectType has 'application' or TargetObjectType has 'servicePrincipal')
| extend TargetCloudAppName = TargetObjectName
| extend TargetCloudAppId = TargetObjectId