Kusto rules: 34

id: d0953d50-3dc1-4fa3-80fa-4d3e973a0959
name: AWSCloudTrail - Privilege escalation via CRUD Lambda policy
description: |
  Detects inline IAM policy updates that grant broad AWS Lambda lifecycle permissions and related destructive
  capabilities. This behavior can establish execution control paths and may indicate privilege escalation through
  unauthorized policy expansion.
severity: Medium
status: Available
requiredDataConnectors:
  - connectorId: AWS
    dataTypes:
      - AWSCloudTrail
queryFrequency: 1d
queryPeriod: 1d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - PrivilegeEscalation
relevantTechniques:
  - T1098.003
query: |
  AWSCloudTrail
  | where EventName in ("PutUserPolicy","PutRolePolicy","PutGroupPolicy") and isempty(ErrorCode) and isempty(ErrorMessage)
  | extend PolicyName = tostring(parse_json(RequestParameters).policyName)
  | extend Statement = parse_json(tostring((parse_json(RequestParameters).policyDocument))).Statement
  | mvexpand Statement
  | extend Action = parse_json(Statement).Action , Effect = tostring(parse_json(Statement).Effect), Resource = tostring(parse_json(Statement).Resource), Condition = tostring(parse_json(Statement).Condition)
  | extend Action = tostring(Action)
  | where Effect =~ "Allow" and (Action contains "lambda:Create" and Action contains "lambda:Get" and Action contains "lambda:Update" and Action contains "kms:Delete") and Resource == "*" and Condition == ""
  | 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]), "")
  | distinct TimeGenerated, EventName, PolicyName, SourceIpAddress, RecipientAccountId, AccountName, AccountUPNSuffix, UserIdentityArn, UserIdentityUserName
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: AccountName
      - identifier: UPNSuffix
        columnName: AccountUPNSuffix
      - identifier: CloudAppAccountId
        columnName: RecipientAccountId
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: SourceIpAddress
customDetails:
  PolicyName: PolicyName
  EventName: EventName
  UserIdentityArn: UserIdentityArn
  RecipientAccountId: RecipientAccountId
alertDetailsOverride:
  alertDisplayNameFormat: 'AWS Lambda privilege escalation policy update by {{AccountName}}'
  alertDescriptionFormat: 'Detected {{EventName}} Event, updating inline Lambda escalation policy {{PolicyName}} in account {{RecipientAccountId}}.'
version: 1.0.2
kind: Scheduled

id: ef877d68-755f-4cf1-ac1d-f336e395667c
name: Cloudflare - Multiple error requests from single source
description: |
  'Detects multiple failure requests from single source in short timeframe.'
severity: Low
status: Available
requiredDataConnectors:
  - connectorId: CloudflareDataConnector
    dataTypes:
      - Cloudflare
queryFrequency: 1h
queryPeriod: 1h
triggerOperator: gt
triggerThreshold: 0
tactics:
  - InitialAccess
relevantTechniques:
  - T1190
  - T1133
query: |
  let threshold = 100;
  Cloudflare
  | where HttpRequestMethod =~ 'GET'
  | summarize err_cnt = count() by SrcIpAddr, bin(TimeGenerated, 5m)
  | where err_cnt > threshold
entityMappings:
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: SrcIpAddr
version: 1.0.1
kind: Scheduled

id: 2ce7f00d-3b3c-41b9-ae9a-b79c19d2394e
name: Conditional Access - A Conditional Access user/group/role exclusion has changed
version: 1.0.1
kind: Scheduled
description: A Conditional Access user/group/role exclusion has changed in Azure AD.
severity: High
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
      - AuditLogs
queryFrequency: 5m
queryPeriod: 5m
triggerOperator: gt
triggerThreshold: 0
tactics:
- Persistence
- DefenseEvasion
- CredentialAccess
relevantTechniques:
- T1098
- T1078
query: |+
  // A Conditional Access user/group/role exclusion has changed.
  AuditLogs
  | where OperationName in ("Update conditional access policy")
  | extend excludeUsersOld = extractjson("$.conditions.users.excludeUsers", tostring(TargetResources[0].modifiedProperties[0].oldValue))
  | extend excludeGroupsOld = extractjson("$.conditions.users.excludeGroups", tostring(TargetResources[0].modifiedProperties[0].oldValue))
  | extend excludeRolesOld = extractjson("$.conditions.users.excludeRoles", tostring(TargetResources[0].modifiedProperties[0].oldValue))
  | extend excludeUsersNew = extractjson("$.conditions.users.excludeUsers", tostring(TargetResources[0].modifiedProperties[0].newValue))
  | extend excludeGroupsNew = extractjson("$.conditions.users.excludeGroups", tostring(TargetResources[0].modifiedProperties[0].newValue))
  | extend excludeRolesNew = extractjson("$.conditions.users.excludeRoles", tostring(TargetResources[0].modifiedProperties[0].newValue))
  | where excludeUsersOld != excludeUsersNew or excludeGroupsOld != excludeGroupsNew or excludeRolesOld != excludeRolesNew
  | extend modifiedBy = tostring(InitiatedBy.user.userPrincipalName)
  | extend accountName = tostring(split(modifiedBy, "@")[0])
  | extend upnSuffix = tostring(split(modifiedBy, "@")[1])
  | project TimeGenerated, OperationName, policy = TargetResources[0].displayName, modifiedBy, accountName, upnSuffix, result = Result,
            excludeUsersOld, excludeUsersNew, excludeGroupsOld, excludeGroupsNew, excludeRolesOld, excludeRolesNew
  | order by TimeGenerated desc
entityMappings:
- entityType: Account
  fieldMappings:
  - identifier: Name
    columnName: accountName
  - identifier: UPNSuffix
    columnName: upnSuffix
suppressionEnabled: false
suppressionDuration: 5h
eventGroupingSettings:
  aggregationKind: AlertPerResult
incidentConfiguration:
  createIncident: true
  groupingConfiguration:
    enabled: false
    reopenClosedIncident: false
    lookbackDuration: PT1H
    matchingMethod: AllEntities
    groupByEntities: []
    groupByAlertDetails: []
    groupByCustomDetails: []

id: 5e644219-6067-4849-85df-7458aa575aea
name: CTE - Device License Capacity High (95%)
description: |
  Device license capacity has reached 95%. Emitted once when usage newly crosses this threshold by the daily tenant health-check job (api/src/functions/tenantHealthChecks.js); resets and can re-fire if usage later drops back below 95% and crosses again.
severity: Medium
requiredDataConnectors:
  - connectorId: cybereinforce_cte
    dataTypes:
      - CybereinforceCTE_CL
queryFrequency: 24h
queryPeriod: 2d
triggerOperator: gt
triggerThreshold: 0
tactics:
  []
relevantTechniques: []
query: |
  CybereinforceCTE_CL
  | where EventType == "license.capacity_high"
  | extend d = parse_json(Details)
  | extend AppName = "Cybereinforce", ActiveDevices = toint(d.activeDevices), LicensedDevices = toint(d.licensedDevices), UsedPct = todouble(d.usedPct)
  | project TimeGenerated, TenantId, AppName, ActiveDevices, LicensedDevices, UsedPct, Severity, Category, Source
entityMappings:
  - entityType: CloudApplication
    fieldMappings:
      - identifier: Name
        columnName: AppName
      - identifier: InstanceName
        columnName: TenantId
version: 1.0.0
kind: Scheduled

id: 5365f294-0c67-432a-bacf-b1282a3b6c46
name: CTERA Mass Deletions Detection Analytic
description: 'This analytic rule detects and alerts when large amount of deletion operations generated by the CTERA Edge Filer'
kind: Scheduled
severity: High
status: Available
requiredDataConnectors:
  - connectorId: CTERA
    dataTypes:
      - Syslog
queryFrequency: 5m
queryPeriod: 5m
triggerOperator: GreaterThan
triggerThreshold: 5000
tactics:
  - Impact
relevantTechniques:
  - T1485
query: |
    Syslog
    | where ProcessName == 'gw-audit'
    | extend
        TenantName = extract("\"vportal\":\"([^\"]*)\"", 1, SyslogMessage),
        UserName = extract("user=([^|]*)", 1, SyslogMessage),
        Permission = extract("op=([^|]*)", 1, SyslogMessage),
        EdgeFiler = extract("\"client\":\"([^\"]*)\"", 1, SyslogMessage),
        RootPath = extract("rootPath=([^|]*)", 1, SyslogMessage),
        Share = extract("share=([^|]*)", 1, SyslogMessage),
        LocalPath = extract("path=([^|]*)", 1, SyslogMessage),
        Timestamp = todatetime(extract("\"@timestamp\":\"([^\"]*)\"", 1, SyslogMessage))
    | where Permission == 'delete'
    | summarize Count = count() by UserName, bin(Timestamp, 5m)
    | where Count > 5000
incidentConfiguration:
    createIncident: true
    groupingConfiguration:
        enabled: false
        reopenClosedIncident: false
        lookbackDuration: PT5H
        matchingMethod: AllEntities
eventGroupingSettings:
    aggregationKind: SingleAlert
alertDetailsOverride:
    alertnameFormat: 'CTERA Batch Access Denied Detection'
    alertDescriptionFormat: >
      Detected {{Count}} denied access attempts by user {{UserName}} on Edge Filer 
      {{EdgeFiler}} within 5 minutes. Please investigate unauthorized access attempts or misconfigurations.
customDetails:
    TenantName: TenantName
    UserName: UserName
    EdgeFiler: EdgeFiler
    RootPath: RootPath
    Share: Share
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: UserName
  - entityType: File
    fieldMappings:
      - identifier: Name
        columnName: EdgeFiler
suppressionDuration: PT5H
suppressionEnabled: false
version: 1.0.0

id: e0d937ca-4582-4c3b-b95b-82fff33ed610
name: Detaching Backups Started
description: Detects when a backup file is detached from a backup job.
severity: Informational
status: Available
requiredDataConnectors:
- connectorId: Syslog
  dataTypes:
  - Syslog
- connectorId: SyslogAma
  dataTypes:
  - Syslog
queryFrequency: 1d
queryPeriod: 1d
triggerOperator: gt
triggerThreshold: 0
eventGroupingSettings:
  aggregationKind: AlertPerResult
tactics: []
relevantTechniques: []
query: "Veeam_GetSecurityEvents\n| where instanceId == 41200\n| project\n    Date\
  \ = format_datetime(TimeGenerated, 'dd.MM.yyyy HH:mm'),\n    DataSource = original_host,\n\
  \    EventId = instanceId,\n    [\"User Name\"] = user,\n  MessageDetails = Description,\n\
  \   Severity = SeverityDescription"
version: 1.0.1
kind: Scheduled
customDetails:
  Date: Date
  VbrHostName: DataSource
  EventId: EventId
  MessageDetails: MessageDetails
  Severity: Severity

id: 1f3b4dfd-21ff-4ed3-8e27-afc219e05c50
name: Detect PIM Alert Disabling activity
description: |
  'Privileged Identity Management (PIM) generates alerts when there is suspicious or unsafe activity in Microsoft Entra ID (Azure AD) organization. 
  This query will help detect attackers attempts to disable in product PIM alerts which are associated with Azure MFA requirements and could indicate activation of privileged access'
severity: Medium
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
      - AuditLogs
queryFrequency: 1d
queryPeriod: 1d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - Persistence
  - PrivilegeEscalation
relevantTechniques:
  - T1098
  - T1078
query: |
  AuditLogs
  | where LoggedByService =~ "PIM"
  | where Category =~ "RoleManagement"
  | where ActivityDisplayName has "Disable PIM Alert"
  | extend IpAddress = case(
    isnotempty(tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)) and tostring(parse_json(tostring(InitiatedBy.user)).ipAddress) != 'null', tostring(parse_json(tostring(InitiatedBy.user)).ipAddress), 
    isnotempty(tostring(parse_json(tostring(InitiatedBy.app)).ipAddress)) and tostring(parse_json(tostring(InitiatedBy.app)).ipAddress) != 'null', tostring(parse_json(tostring(InitiatedBy.app)).ipAddress),
    'Not Available')
  | extend InitiatedBy = iff(isnotempty(tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)), 
    tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName), tostring(parse_json(tostring(InitiatedBy.app)).displayName)), UserRoles = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
  | project InitiatedBy, ActivityDateTime, ActivityDisplayName, IpAddress, AADOperationType, AADTenantId, ResourceId, CorrelationId, Identity
  | extend AccountName = tostring(split(InitiatedBy, "@")[0]), AccountUPNSuffix = tostring(split(InitiatedBy, "@")[1])
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: InitiatedBy
      - identifier: Name
        columnName: AccountName
      - identifier: UPNSuffix
        columnName: AccountUPNSuffix
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IpAddress
  - entityType: AzureResource
    fieldMappings:
      - identifier: ResourceId
        columnName: ResourceId
version: 1.0.4
kind: Scheduled
metadata:
    source:
        kind: Community
    author:
        name: Microsoft Security Research
    support:
        tier: Community
    categories:
        domains: [ "Security - Others", "Identity" ]

id: 4c9f0a9e-44d7-4c9b-b7f0-f6a6e0d8f8fa
name: GSA - Detect Connections Outside Operational Hours
description: |
  This query identifies connections that occur outside of the defined operational hours. It helps in monitoring and flagging any unusual activity that may occur during non-business hours, indicating potential security concerns or policy violations.
severity: High
status: Available
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
      - NetworkAccessTrafficLogs
queryFrequency: 1h
queryPeriod: 24h
triggerOperator: gt
triggerThreshold: 0
tactics:
  - InitialAccess
relevantTechniques:
  - T1078
  - T1133
query: |
  let starttime = todatetime('{{StartTimeISO}}');
  let endtime = todatetime('{{EndTimeISO}}');
  let operational_start_hour = 8; // Start of operational hours (8 AM)
  let operational_end_hour = 18; // End of operational hours (6 PM)
  NetworkAccessTraffic
  | where TimeGenerated between (starttime .. endtime)
  | extend HourOfDay = datetime_part('hour', TimeGenerated)
  | where HourOfDay < operational_start_hour or HourOfDay >= operational_end_hour
  | project TimeGenerated, UserPrincipalName, SourceIp, DestinationIp, DestinationPort, Action, DeviceId, DeviceOperatingSystem, ConnectionId
  | extend IPCustomEntity = SourceIp, AccountCustomEntity = UserPrincipalName
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: AccountCustomEntity
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IPCustomEntity
version: 1.0.2
kind: Scheduled

id: 2ff35ed4-b26a-4cad-93a6-f67adb00e919
name: Imperva - Malicious Client
description: |
  'Detects connections from known malicious clients.'
severity: High
status: Available
requiredDataConnectors:
  - connectorId: ImpervaWAFCloudAPI
    dataTypes:
      - ImpervaWAFCloud
queryFrequency: 10m
queryPeriod: 10m
triggerOperator: gt
triggerThreshold: 0
tactics:
  - InitialAccess
relevantTechniques:
  - T1190
  - T1133
query: |
  ImpervaWAFCloud
  | where ClientApp in~ ('VulnerabilityScanner', 'DDoSBot', 'ClickBot','CommentSpamBot','HackingTool', 'SpamBot', 'Worm')
  | where DvcAction !startswith 'REQ_BLOCKED' or DvcAction !startswith 'REQ_BAD_'
  | extend IPCustomEntity = SrcIpAddr, UrlCustomEntity = QueryString
entityMappings:
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IPCustomEntity
  - entityType: URL
    fieldMappings:
      - identifier: Url
        columnName: UrlCustomEntity
version: 1.0.1
kind: Scheduled

id: 72592618-fa57-45e1-9f01-ca8706a5e3f5
name: Jira - Permission scheme updated
description: |
  'Detects when permission scheme was updated.'
severity: Medium
status: Available
requiredDataConnectors:
  - connectorId: JiraAuditAPI
    dataTypes:
      - JiraAudit
queryFrequency: 1h
queryPeriod: 1h
triggerOperator: gt
triggerThreshold: 0
tactics:
  - Impact
relevantTechniques:
  - T1531
query: |
  JiraAudit
  | where EventMessage =~ 'Permission scheme updated'
  | project EventCreationTime, ObjectItemName, UserName, SrcIpAddr, ChangedValues
  | extend AccountCustomEntity = UserName, IPCustomEntity = SrcIpAddr
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: AccountCustomEntity
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IPCustomEntity
version: 1.0.0
kind: Scheduled

id: 983a6922-894d-413c-9f04-d7add0ecc307
name: Potential DGA detected (ASIM DNS Schema)
description: |
  'Identifies clients with a high NXDomain count which could be indicative of a DGA (cycling through possible C2 domains where most C2s are not live). Alert is generated when a new IP address is seen (based on not being seen associated with 
  NXDomain records in prior 10-day baseline period).
  This analytic rule uses [ASIM](https://aka.ms/AboutASIM) and supports any built-in or custom source that supports the ASIM DNS schema'
severity: Medium
requiredDataConnectors:
  - connectorId: DNS
    dataTypes:
      - DnsEvents
  - connectorId: AzureFirewall
    dataTypes: 
      - AzureDiagnostics
  - connectorId: Zscaler
    dataTypes:
      - CommonSecurityLog
  - connectorId: InfobloxNIOS
    dataTypes: 
      - Syslog
  - connectorId: GCPDNSDataConnector
    dataTypes: 
      - GCP_DNS_CL
  - connectorId: NXLogDnsLogs
    dataTypes: 
      - NXLog_DNS_Server_CL
  - connectorId: CiscoUmbrellaDataConnector
    dataTypes: 
      - Cisco_Umbrella_dns_CL
  - connectorId: Corelight
    dataTypes: 
      - Corelight_CL

queryFrequency: 1d
queryPeriod: 10d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - CommandAndControl
relevantTechniques:
  - T1568
  - T1008
tags:
  - ParentAlert: https://github.com/Azure/Azure-Sentinel/blob/master/Detections/DnsEvents/DNS_HighNXDomainCount_detection.yaml
    version: 1.0.0
  - Schema: ASIMDns
    SchemaVersion: 0.1.1
query: |
  let referencestarttime = 10d;
  let referenceendtime = 1d;
  let threshold = 100;
  let nxDomainDnsEvents = (stime:datetime, etime:datetime) 
    {_Im_Dns(responsecodename='NXDOMAIN', starttime=stime, endtime=etime)
    | where DnsQueryTypeName in ("A", "AAAA")
    | where ipv4_is_match("127.0.0.1", SrcIpAddr) == False
    | where DnsQuery !contains "/" and  DnsQuery contains "."};
  nxDomainDnsEvents (stime=ago(referenceendtime) ,etime=now())
    | extend sld = tostring(split(DnsQuery, ".")[-2])
    | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), dcount(sld) by SrcIpAddr
    | where dcount_sld > threshold
    // Filter out previously seen IPs
    | join kind=leftanti (nxDomainDnsEvents (stime=ago(referencestarttime), etime=ago(referenceendtime))
      | extend sld = tostring(split(DnsQuery, ".")[-2])
      | summarize dcount(sld) by SrcIpAddr
      | where dcount_sld > threshold ) on SrcIpAddr
  // Pull out sample NXDomain responses for those remaining potentially infected IPs
  | join kind = inner (nxDomainDnsEvents (stime=ago(referencestarttime), etime=now()) | summarize by DnsQuery, SrcIpAddr) on SrcIpAddr
  | summarize StartTime = min(StartTime), EndTime = max(EndTime), sampleNXDomainList=make_list(DnsQuery, 100)  by SrcIpAddr, dcount_sld
entityMappings:
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: SrcIpAddr
version: 1.3.4
kind: Scheduled
metadata:
    source:
        kind: Community
    author:
        name: Yaron
    support:
        tier: Community
    categories:
        domains: [ "Security - Network" ]

id: 74b243a6-3046-48aa-8b03-e43b3c529cc1
kind: Scheduled
name: SAP BTP - Failed access attempts across multiple BAS subaccounts
description: Identifies failed Business Application Studio access attempts over a
  predefined number of subaccounts.
severity: Medium
status: Available
requiredDataConnectors:
  - connectorId: SAPBTPAuditEvents
    dataTypes:
      - SAPBTPAuditLog_CL
queryFrequency: 1h
queryPeriod: 1h
triggerOperator: gt
triggerThreshold: 0
tactics:
  - Reconnaissance
  - Discovery
relevantTechniques:
  - T1595
  - T1526
query: |
  let subaccount_detection_threshold = 3;
  SAPBTPAuditLog_CL
  | where Category == "audit.security-events" and Message has "Unauthorized access attempt"
  | summarize Start=min(UpdatedOn), End=max(UpdatedOn), Tenants = make_set(Tenant, 100) by UserName
  | where array_length(Tenants) > subaccount_detection_threshold
  | project Start, End, UserName, Tenants, CloudApp = "SAP BTP"
  | extend AccountName = split(UserName, "@")[0], UPNSuffix = split(UserName, "@")[1]
eventGroupingSettings:
  aggregationKind: SingleAlert
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: AccountName
      - identifier: UPNSuffix
        columnName: UPNSuffix
  - entityType: CloudApplication
    fieldMappings:
      - identifier: Name
        columnName: CloudApp
alertDetailsOverride:
  alertDisplayNameFormat: SAP BTP - Unauthorized access attempt to multiple tenants
  alertDescriptionFormat: '{{UserName}} attempted, and failed, to log into multiple
    Business Application Studio dev spaces. Tenants accessed: {{Tenants}}'
version: 3.0.6

id: c6111e06-11e2-45eb-86ef-28313a06db35
kind: Scheduled
name: SAP ETD - Execution of Sensitive Function Module
description: |
  Identifies execution of a sensitive ABAP Function Module using the watchlists provided by the Microsoft Sentinel Solution for SAP
  
  Source Action: Execute a sensitive function module directly using SE37.
  
  *Data Sources: SAP Enterprise Thread Detection Solution -  Alerts*
severity: Medium
status: Available
requiredDataConnectors:
  - connectorId: SAPETDAlerts
    dataTypes:
      - SAPETDAlerts_CL
queryFrequency: 5m
queryPeriod: 60m
triggerOperator: gt
triggerThreshold: 0
tactics: 
  - Discovery
relevantTechniques: []
query: |
  let AuditTimeAgo = 60m;
  let minThreshold= 1;
  let minScore= 50;
  let SenseModules = _GetWatchlist('SAP - Sensitive Function Modules')
  | project trimmedFunctionModule = trim(@"\s+", FunctionModule);
  SAPETDAlerts_CL
  | where TimeGenerated > ago(AuditTimeAgo)
  | where Threshold >= minThreshold and Score >= minScore
  | where PatternName == "Critical Function module call in Test framework calls"
  | mv-expand NormalizedTriggeringEvents
  | extend sapOriginalEvent = tostring(NormalizedTriggeringEvents.OriginalEvent)
  | extend Id_ = NormalizedTriggeringEvents.Id
  | extend extracted_user_ip = tostring(NormalizedTriggeringEvents.NetworkIPAddressInitiator)
  | extend extracted_sap_user = tostring(NormalizedTriggeringEvents.UserAccountActing)
  | extend extracted_sid = tostring(NormalizedTriggeringEvents.SystemIdActor)
  | extend extracted_instance_name = tostring(NormalizedTriggeringEvents.NetworkHostnameActor)
  | extend extracted_instance_host = tostring(NormalizedTriggeringEvents.ServiceInstanceName)
  | extend extracted_function_module = trim(@"\s+", tostring(NormalizedTriggeringEvents.ServiceFunctionName))
  | extend extracted_system_role = tostring(NormalizedTriggeringEvents.SystemRoleReporter)
  | where extracted_function_module in (SenseModules)
  | extend AlertName = strcat("SAP ETD - Sensitive Function Module ", extracted_function_module," was executed by user ", extracted_sap_user, 
  " in a ", tolower(extracted_system_role), " system"), Dummy = " "
  | mv-expand Users
  | extend
    UserAccountName = tostring(Users.UserAccountName),
    UserEmail = tostring(Users.EmailAddresses[0])
eventGroupingSettings:
  aggregationKind: SingleAlert
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: UserAccountName
  - entityType: Mailbox
    fieldMappings:
      - identifier: MailboxPrimaryAddress
        columnName: UserEmail
  - entityType: CloudApplication
    fieldMappings:
      - identifier: AppId
        columnName: extracted_sid
      - identifier: InstanceName
        columnName: extracted_instance_name
  - entityType: Host
    fieldMappings:
      - identifier: FullName
        columnName: extracted_instance_host
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: extracted_user_ip
alertDetailsOverride:
  alertDisplayNameFormat: 'SAP ETD - {{PatternName}} '
  alertDescriptionFormat: |
    {{PatternDescription}}

    Source: SAP ETD
customDetails:
  SAP_User: extracted_sap_user
  SAP_UserEmail: UserEmail
  ETD_AlertNumber: AlertId
version: 1.0.1

id: c2f93727-e4b0-4cb9-8f80-f52ebbd96ece
name: Snowflake - Possible data destraction
description: |
  'Detects possible data destruction.'
severity: Medium
status: Available
requiredDataConnectors:
  - connectorId: Snowflake
    dataTypes:
      - Snowflake
queryFrequency: 1h
queryPeriod: 1h
triggerOperator: gt
triggerThreshold: 0
tactics:
  - Impact
relevantTechniques:
  - T1485
query: |
  let threshold = 10;
  Snowflake
  | where QueryType =~ 'DROP'
  | where QueryExecutionStatus =~ 'SUCCESS'
  | summarize count() by TargetUsername, bin(TimeGenerated, 5m)
  | where count_ > threshold
  | extend AccountCustomEntity = TargetUsername
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: AccountCustomEntity
version: 1.0.1
kind: Scheduled

// create a whitelist that consists of your AD domain names. ex: ("contoso.com","contoso.local","contoso.dmz")
let _whitelist_ServiceName = dynamic (["contoso.com","contoso.local","contoso.dmz"]);
let _fromDate = ago(8d);
let _thruDate = now();
let _min_dcount_threshold = 6;
let _period =2h; // this value should also be used as the rule frequency.
// If an account requested more than <_min_dcount_threshold> service tickets during the last <_period> hour, 
// we need to check if it is suspicious or not by looking at historical data for the same user. 
// STEP 1: Create a list of accounts to be checked based on their requests during the last <_period> hour.
// This approach also increases the rule performance as only the suspicious accounts are checked historically.
let _accounts_to_be_checked =
    SecurityEvent
    | where TimeGenerated > ago(_period)
    | where EventID == 4769
    | parse EventData with * 'Status">' Status "<" *
    | where Status == '0x0'
    | parse EventData with * 'ServiceName">' ServiceName "<" *
    | where ServiceName !contains "$" and ServiceName !contains "krbtgt"
    | where ServiceName !in~ (_whitelist_ServiceName )
    | parse EventData with * 'TargetUserName">' TargetUserName "<" *
    | where TargetUserName !contains ServiceName
    | parse EventData with * 'TicketEncryptionType">' TicketEncryptionType "<" *
    | where TicketEncryptionType in~ ('0x17','0x18') //these are the weak encryption types.
    | parse EventData with * 'TicketOptions">' TicketOptions "<" *
    | parse EventData with * 'IpAddress">::ffff:' ClientIPAddress "<" *
    | summarize dcount(ServiceName) by TargetUserName
    | where dcount_ServiceName > _min_dcount_threshold
    | summarize make_list(TargetUserName);
// STEP 2: Create a time-series statistics per day for each account in the list, for the last 8 days.
let baseData = materialize(
    SecurityEvent
    | where TimeGenerated > _fromDate
    | where EventID == 4769
    | parse EventData with * 'Status">' Status "<" *
    | where Status == '0x0'
    | parse EventData with * 'ServiceName">' ServiceName "<" *
    | where ServiceName !contains "$" and ServiceName !contains "krbtgt"
    | where ServiceName !in~ (_whitelist_ServiceName )
    | parse EventData with * 'TargetUserName">' TargetUserName "<" *
    | where TargetUserName in~ (_accounts_to_be_checked) // only the accounts in the list will be analyzed.
    | parse EventData with * 'TicketEncryptionType">' TicketEncryptionType "<" *
    | where TicketEncryptionType in~ ('0x17','0x18')
    | parse EventData with * 'TicketOptions">' TicketOptions "<" *
    | parse EventData with * 'IpAddress">::ffff:' ClientIPAddress "<" *
    //creating the time-series stats
    | make-series dcount(ServiceName) default=0  on TimeGenerated in range(_fromDate, _thruDate, 1d)  by TargetUserName, ClientIPAddress 
    | extend avg = todouble(series_stats_dynamic(dcount_ServiceName).avg )
    );
// STEP 3: Find outliers(anomalies) in the time-series data and make a list of suspicious accounts.
let AnomTargetUserNames = (
    baseData
    | extend outliers = series_outliers(dcount_ServiceName)
    | mvexpand dcount_ServiceName, TimeGenerated, outliers to typeof(double)
    // dcount must be at least greater than the threshold. otherwise small numbers can also be an outlier.
	// ex: 1,1,1,1,5 -> 5 is an outlier but probably not suspicious in big environment
    | where dcount_ServiceName > _min_dcount_threshold
    // time-series function rounds the time information and displays the time info as the startofday like '2020-08-23T00:00:00.0000000Z'. 
	// check if the anomlay happened on the current day.
    | where TimeGenerated >= startofday(now())
    // we are looking for positive outliers, meaning increase in the volume.
    // this threshold can be adjusted based on the number of false positives or false negatives
    | where outliers > 1.5
    // we are looking for big outliers. Top 25 outliers should be more than enough. 
    | top 25 by outliers desc
    | summarize make_list(TargetUserName)
    );
// STEP 4: Anomaly is found but it's not clear when it happened exactly and there is not enough information for incident response.
//         Get last <_period> of data and dcount of service names, set of service names that have been requested for each account in the anomalous account list.
//         Join with the baseData as it has both avg values and other details of the anomaly. 
//         Avg value will also be used for preventing the rule from triggering the same alert again.  
//         We already found the anomalous users but we need to check if the anomaly happened in the last <_period> to prevent duplicate incidents.
SecurityEvent
| where TimeGenerated >= ago(_period)
| where EventID == 4769
| parse EventData with * 'Status">' Status "<" *
| where Status == '0x0'
| parse EventData with * 'ServiceName">' ServiceName "<" *
| where ServiceName !contains "$" and ServiceName !contains "krbtgt"
| where ServiceName !in~ (_whitelist_ServiceName )
| parse EventData with * 'TargetUserName">' TargetUserName "<" *
| where TargetUserName in (AnomTargetUserNames) // checking if the user is in the list of anomolous users
| parse EventData with * 'TicketEncryptionType">' TicketEncryptionType "<" *
| where TicketEncryptionType in~ ('0x17','0x18')
| parse EventData with * 'TicketOptions">' TicketOptions "<" *
| parse EventData with * 'IpAddress">::ffff:' ClientIPAddress "<" *
// Calculate the dcount of service tickets, create the set of service tickets requested
| summarize start_Time=min(TimeGenerated), end_Time=max(TimeGenerated), ServiceNameCount_LastPeriod = dcount(ServiceName), ServiceNameSet_LastPeriod = makeset(ServiceName) by TargetUserName
// Now join this with the baseData to display the details of the anomaly.
| join kind=leftouter baseData on TargetUserName
| where ServiceNameCount_LastPeriod > avg + 2 // this is a simple check to see if the anomaly happened in the last period. 
// Display the columns we need
| project start_Time, end_Time, ClientIPAddress, TargetUserName, ServiceNameCount_Avg = avg, ServiceNameCount_LastPeriod, dcount_ServiceName, ServiceNameSet_LastPeriod