Kusto rules: 04

id: 1c7ff502-2ad4-4970-9d29-9210c6753138
name: App Gateway WAF - XSS Detection
description: | 
  'Identifies a match for an XSS attack in the App Gateway WAF logs. The threshold value in the query can be changed as per your infrastructure's requirements.
   References: https://owasp.org/www-community/attacks/xss/'
severity: High
status: Available
requiredDataConnectors: 
  - connectorId: WAF 
    dataTypes: 
      - AzureDiagnostics
queryFrequency: 6h 
queryPeriod: 6h 
triggerOperator: gt 
triggerThreshold: 0 
tactics: 
  - InitialAccess
  - Execution
relevantTechniques:
  - T1189
  - T1203
  - T0853
tags:
  - Cross Site Scripting
query:  |  
 let Threshold = 3;  
 AGWFirewallLogs
 | where Action == "Matched"
 | where Message has "XSS"
 | project TransactionId, Hostname, RequestUri, TimeGenerated, ClientIp, Message
 | join kind = inner(
 AGWFirewallLogs
 | where Action == "Blocked"
 | extend transactionId_g = tostring(TransactionId)) on TransactionId
 | extend Uri = strcat(Hostname,RequestUri)
 | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), TransactionID = make_set(transactionId_g,100), Message = make_set(Message,100), Total_TransactionId = dcount(transactionId_g) by ClientIp, Uri, Action
 | where Total_TransactionId >= Threshold
# The Threshold value above can be changed as per your infrastructure's requirement 
entityMappings:
  - entityType: URL
    fieldMappings:
      - identifier: Url
        columnName: Uri
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: ClientIp
version: 1.0.2
kind: Scheduled

id: 97ad74c4-fdd9-4a3f-b6bf-5e28f4f71e06
name: Brute Force Attack against GitHub Account
description: |
  'Attackers who are trying to guess your users' passwords or use brute-force methods to get in. If your organization is using SSO with Microsoft Entra ID, authentication logs to GitHub.com will be generated. Using the following query can help you identify a sudden increase in failed logon attempt of users.'
severity: Medium
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
      - SigninLogs
  - connectorId: AzureActiveDirectory
    dataTypes:
      - AADNonInteractiveUserSignInLogs
queryFrequency: 1h
queryPeriod: 7d
triggerOperator: gt
triggerThreshold: 0
status: Available
tactics:
  - CredentialAccess
relevantTechniques:
  - T1110
query: |
  let LearningPeriod = 7d;
  let BinTime = 1h;
  let RunTime = 1h;
  let StartTime = 1h;  
  let sensitivity = 2.5;
  let EndRunTime = StartTime - RunTime;
  let EndLearningTime = StartTime + LearningPeriod;
  let aadFunc = (tableName:string){
  table(tableName)  
  | where TimeGenerated between (ago(EndLearningTime) .. ago(EndRunTime))
  | where AppDisplayName =~ "GitHub.com"
  | where ResultType != 0
  | make-series FailedLogins = count() on TimeGenerated from ago(LearningPeriod) to ago(EndRunTime) step BinTime by UserPrincipalName, Type
  | extend (Anomalies, Score, Baseline) = series_decompose_anomalies(FailedLogins, sensitivity, -1, 'linefit')
  | mv-expand FailedLogins to typeof(double), TimeGenerated to typeof(datetime), Anomalies to typeof(double), Score to typeof(double), Baseline to typeof(long)  
  | where TimeGenerated >= ago(RunTime)
  | where Anomalies > 0 and Baseline > 0
  | join kind=inner (
            table(tableName)  
            | where TimeGenerated between (ago(StartTime) .. ago(EndRunTime))
            | where AppDisplayName =~ "GitHub.com"
            | where ResultType != 0
            | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), IPAddresses = make_set(IPAddress,100), Locations = make_set(LocationDetails,20), Devices = make_set(DeviceDetail,20) by UserPrincipalName, UserId, AppDisplayName
        ) on UserPrincipalName
  | project-away UserPrincipalName1
  | extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])
  | extend IPAddressFirst = tostring(IPAddresses[0])
  };
  let aadSignin = aadFunc("SigninLogs");
  let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
  union isfuzzy=true aadSignin, aadNonInt
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: UserPrincipalName
      - identifier: Name
        columnName: Name
      - identifier: UPNSuffix
        columnName: UPNSuffix
  - entityType: Account
    fieldMappings:
      - identifier: AadUserId
        columnName: UserId
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IPAddressFirst
version: 2.0.2
kind: Scheduled

id: 8e9d1f70-d529-4598-9d3e-5dd5164d1d02
name: Cisco WSA - Unexpected file type
description: |
  'Detects unexpected file type.'
severity: Medium
status: Available
requiredDataConnectors:
  - connectorId: SyslogAma
    datatypes:
      - Syslog
queryFrequency: 1h
queryPeriod: 1h
triggerOperator: gt
triggerThreshold: 0
tactics:
  - InitialAccess
relevantTechniques:
  - T1189
query: |
  CiscoWSAEvent
  | where isnotempty(AmpFileName)
  | where isempty(AmpThreatName)
  | where ResponseBodyMimeType =~ 'application/octet-stream'
  | where AmpFileName !endswith '.exe'
  | extend IPCustomEntity = SrcIpAddr, AccountCustomEntity = SrcUserName
entityMappings:
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IPCustomEntity
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: AccountCustomEntity
version: 1.0.2
kind: Scheduled

id: 9281b7cc-8f05-45a9-bf10-17fb29492a84
name: CyberArkEPM - Renamed Windows binary
description: |
  'Detects renamed windows binaries.'
severity: High
requiredDataConnectors:
  - connectorId: CyberArkEPM
    dataTypes:
      - CyberArkEPM
queryFrequency: 1h
queryPeriod: 1h
triggerOperator: gt
triggerThreshold: 0
tactics:
  - Execution
  - DefenseEvasion
relevantTechniques:
  - T1204
  - T1036
query: | 
  CyberArkEPM
  | where EventSubType != 'AttackAttempt'
  | where ActingProcessName has @'\'
  | where ActingProcessName !has ActingProcessFileInternalName
  | project EventEndTime, EventMessage, ActorUsername, ActingProcessFileInternalName
  | extend AccountCustomEntity = ActorUsername
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: AccountCustomEntity
version: 1.0.0
kind: Scheduled

id: 34a5d79b-8f9a-420c-aa64-7f4d262ac29a
kind: Scheduled
name: Dataverse - New user agent type that was not used before
description: Identifies users accessing Dataverse from a User Agent that has not been
  seen in any Dataverse instance in the last 14 days.
severity: Low
status: Available
requiredDataConnectors:
  - connectorId: Dataverse
    dataTypes:
      - DataverseActivity
queryFrequency: 1h
queryPeriod: 14d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - InitialAccess
  - DefenseEvasion
relevantTechniques:
  - T1078
  - T0866
  - T0819
  - T1036
query: |
  let query_lookback = 14d;
  let query_frequency = 1h;
  let known_useragents = dynamic([
      // Enter known user agents to exclude.
      // example:
      // "Agent1", "Agent2", "Agent3"
      ]);
  DataverseActivity
  | where TimeGenerated between(ago(query_lookback) .. ago(query_frequency))
  | where isnotempty(UserAgent)
  | summarize by UserAgent
  | join kind = rightanti (DataverseActivity
      | where TimeGenerated > ago(query_frequency)
      | where not (UserId has_any ("@onmicrosoft.com", "@microsoft.com", "Unknown"))
      | where isnotempty(UserAgent)
      | where UserAgent !in~ (known_useragents)
      | where UserAgent !hasprefix "azure-logic-apps" and UserAgent !hasprefix "PowerApps")
      on UserAgent
  // Exclude user agents with a render agent to reduce noise.
  | join kind = leftanti(
      DataverseActivity
      | where TimeGenerated > ago(query_frequency)
      | where UserAgent has_any ("Gecko", "WebKit", "Presto", "Trident", "EdgeHTML", "Blink"))
      on UserAgent
  | summarize
      FirstSeen = min(TimeGenerated),
      LatestIP = arg_max(ClientIp, TimeGenerated)
      by UserAgent, UserId, InstanceUrl
  | extend
      AccountName = tostring(split(UserId, '@')[0]),
      UPNSuffix = tostring(split(UserId, '@')[1]),
      CloudAppId = int(32780)
  | project
      FirstSeen,
      UserId,
      UserAgent,
      LatestIP,
      InstanceUrl,
      CloudAppId,
      AccountName,
      UPNSuffix
eventGroupingSettings:
  aggregationKind: AlertPerResult
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: AccountName
      - identifier: UPNSuffix
        columnName: UPNSuffix
  - entityType: CloudApplication
    fieldMappings:
      - identifier: AppId
        columnName: CloudAppId
      - identifier: InstanceName
        columnName: InstanceUrl
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: LatestIP
alertDetailsOverride:
  alertDisplayNameFormat: 'Dataverse - new user agent detected in {{{InstanceUrl}} '
  alertDescriptionFormat: |
    {{UserId}} with new agent not seen previously in the Dataverse activity log.
    Agent: {{UserAgent}}
    Latest IP: {{LatestIP}}
version: 3.2.0

id:  8ee967a2-a645-4832-85f4-72b635bcb3a6
name: Failed AzureAD logons but success logon to host
description: |
  'Identifies a list of IP addresses with a minimum number (default of 5) of failed logon attempts to Microsoft Entra ID.
  Uses that list to identify any successful remote logons to hosts from these IPs within the same timeframe.'
severity: Medium
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
     - SigninLogs
  - connectorId: AzureActiveDirectory
    dataTypes:
      - AADNonInteractiveUserSignInLogs
  - connectorId: SecurityEvents
    dataTypes:
      - SecurityEvent
  - connectorId: Syslog
    dataTypes:
      - Syslog
  - connectorId: WindowsSecurityEvents
    dataTypes:
      - SecurityEvents
  - connectorId: WindowsForwardedEvents
    dataTypes:
      - WindowsEvent
queryFrequency: 1d
queryPeriod: 1d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - InitialAccess
  - CredentialAccess
relevantTechniques:
  - T1078
  - T1110
query: |
  //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
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: Account
      - identifier: Name
        columnName: TargetUserName
      - identifier: NTDomain
        columnName: TargetDomainName
  - entityType: Host
    fieldMappings:
      - identifier: FullName
        columnName: Computer
      - identifier: HostName
        columnName: HostName
      - identifier: NTDomain
        columnName: HostNameDomain
  - entityType: Host
    fieldMappings:
      - identifier: AzureID
        columnName: _ResourceId
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IpAddress
version: 1.3.2
kind: Scheduled
metadata:
    source:
        kind: Community
    author:
        name: Microsoft Security Research
    support:
        tier: Community
    categories:
        domains: [ "Security - Others", "Identity" ]

id: "d9e1646c-dc17-4150-ac85-581f5c9cb41f"
name: Google Threat Intelligence - Threat Hunting Domain
description: |
  'Google Threat Intelligence domain correlation.'
severity: Medium
requiredDataConnectors:
  - connectorId: ThreatIntelligence
    dataTypes:
      - ThreatIntelIndicators
queryFrequency: 30m
queryPeriod: 1d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - CommandAndControl
relevantTechniques:
  - T1071
incidentConfiguration:
  createIncident: true
  groupingConfiguration:
    enabled: true
    reopenClosedIncident: false
    lookbackDuration: 1h
    matchingMethod: AllEntities
eventGroupingSettings:
  aggregationKind: AlertPerResult
alertDetailsOverride:
  alertDisplayNameFormat: 'Google Threat Intelligence Match'
  alertDescriptionFormat: 'Correlation found from the {{Type}} table.'
query: |
  let ioc_lookBack = 1d;
  _Im_Dns
  | where isnotempty(DnsQuery)
  | extend lowerDomain=tolower(DnsQuery)
  | join kind=inner (
  ThreatIntelIndicators
  | where ObservableKey == 'domain-name:value'
  | where isnotempty(ObservableValue)
  | where SourceSystem == "Google Threat Intelligence"
  | where TimeGenerated >= ago(ioc_lookBack)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id
  | where IsActive == true and (isnull(ValidUntil) or ValidUntil > now())
  | extend lowerDomain=tolower(ObservableValue)
  ) on lowerDomain
  | project Domain=ObservableValue, Description=Data.description, Type, TimeGenerated
entityMappings:
  - entityType: DNS
    fieldMappings:
      - identifier: DomainName
        columnName: Domain
version: 1.0.0
kind: Scheduled

id: e65938c4-1379-4f7a-bd22-78f670a239c3
name: Guardian- Language Detection Policy Violation Detection
description: |
  'This alert creates an incident when Language Detection Policy Violation detected from the Guardian.'
severity: Informational
status: Available
requiredDataConnectors:
  - connectorId: BoschAIShield
    dataTypes:
      - Guardian
queryFrequency: 1h
queryPeriod: 1h
triggerOperator: gt
triggerThreshold: 0
tactics: []
relevantTechniques: []
query: |
  Guardian
  | where PolicyViolatedControlFeature =~ 'Language Detection'
  | where Severity =~ 'Informational'
entityMappings:
  - entityType: Host
    fieldMappings:
      - identifier: HostName
        columnName: HostName
      - identifier: NTDomain
        columnName: NTDomain
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: SourceIP
eventGroupingSettings:
  aggregationKind: SingleAlert
alertDetailsOverride:
  alertDisplayNameFormat: Guardian- Language Detection Policy Violation detection
  alertDescriptionFormat: |
    This query detects Language Detection Policy Violation detected from the Guardian generated at {{TimeGenerated}}.\n\nPlease check the source for more information and investigate further.
  alertTacticsColumnName: null
  alertSeverityColumnName: Severity
version: 1.0.0
kind: Scheduled

id: ce74dc9a-cb3c-4081-8c2f-7d39f6b7bae1
name: Identify Mango Sandstorm powershell commands
description: |
  'The query below identifies powershell commands used by the threat actor Mango Sandstorm.
  Reference:  https://www.microsoft.com/security/blog/2022/08/25/mercury-leveraging-log4j-2-vulnerabilities-in-unpatched-systems-to-target-israeli-organizations/'
severity: High
requiredDataConnectors:
  - connectorId: SecurityEvents
    dataTypes:
      - SecurityEvent
  - connectorId: MicrosoftThreatProtection
    dataTypes:
      - DeviceProcessEvents
queryFrequency: 1d
queryPeriod: 1d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - LateralMovement
relevantTechniques:
  - T1570
tags:
  - Mango Sandstorm
  - Schema: ASIMFileEvent
    SchemaVersion: 0.1.0
query: |
  (union isfuzzy=true
  (SecurityEvent
  | where EventID == 4688
  | where Process has_any ("powershell.exe","powershell_ise.exe","pwsh.exe") and CommandLine has_cs "-exec bypass -w 1 -enc"
  | where CommandLine  contains_cs "UwB0AGEAcgB0AC0ASgBvAGIAIAAtAFMAYwByAGkAcAB0AEIAbABvAGMAawAgAHsAKABzAGEAcABzACAAKAAiAHAA"
  | extend DvcHostname = Computer, ProcessId = tostring(ProcessId), ActorUsername = Account
  ),
  (DeviceProcessEvents
  | where FileName =~ "powershell.exe" and ProcessCommandLine has_cs "-exec bypass -w 1 -enc"
  | where ProcessCommandLine contains_cs "UwB0AGEAcgB0AC0ASgBvAGIAIAAtAFMAYwByAGkAcAB0AEIAbABvAGMAawAgAHsAKABzAGEAcABzACAAKAAiAHAA"
  | extend DvcHostname = DeviceName, ProcessId = tostring(InitiatingProcessId), ActorUsername = strcat(AccountDomain, @"\", AccountName)
  ),
  (imProcessCreate
  | where Process has_any ("powershell.exe","powershell_ise.exe","pwsh.exe") and CommandLine has_cs "-exec bypass -w 1 -enc"
  | where CommandLine  contains_cs "UwB0AGEAcgB0AC0ASgBvAGIAIAAtAFMAYwByAGkAcAB0AEIAbABvAGMAawAgAHsAKABzAGEAcABzACAAKAAiAHAA"
  | extend ProcessId = tostring(TargetProcessId)
  )
  )
  | extend AccountName = tostring(split(ActorUsername, "\\")[0]), AccountNTDomain = tostring(split(ActorUsername, "\\")[1])
  | extend HostName = tostring(split(DvcHostname, ".")[0]), DomainIndex = toint(indexof(DvcHostname, '.'))
  | extend HostNameDomain = iff(DomainIndex != -1, substring(DvcHostname, DomainIndex + 1), DvcHostname)
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: ActorUsername
      - identifier: Name
        columnName: AccountName
      - identifier: NTDomain
        columnName: AccountNTDomain
  - entityType: Host
    fieldMappings:
      - identifier: FullName
        columnName: DvcHostname
      - identifier: HostName
        columnName: HostName
      - identifier: NTDomain
        columnName: HostNameDomain
  - entityType: Process
    fieldMappings:
      - identifier: ProcessId
        columnName: ProcessId
version: 1.0.5
kind: Scheduled
metadata:
    source:
        kind: Community
    author:
        name: Microsoft Security Research
    support:
        tier: Community
    categories:
        domains: [ "Security - Threat Intelligence" ]

id: 6e575295-a7e6-464c-8192-3e1d8fd6a990
name: Log4j vulnerability exploit aka Log4Shell IP IOC
description: |
  'Identifies a match across various data feeds for IP IOCs related to the Log4j vulnerability exploit aka Log4Shell described in CVE-2021-44228.
   References: https://cve.mitre.org/cgi-bin/cvename.cgi?name=2021-44228'
severity: High
status: Available
tags:
  - Log4j
  - Log4Shell
  - CVE-2021-44228
  - Schema: ASIMDns
    SchemaVersion: 0.1.1
  - Schema: ASIMNetworkSession
    SchemaVersion: 0.2.0
requiredDataConnectors:
  - connectorId: Office365
    dataTypes:
     - OfficeActivity
  - connectorId: DNS
    dataTypes:
      - DnsEvents
  - connectorId: AzureMonitor(VMInsights)
    dataTypes:
      - VMConnection
  - connectorId: CiscoASA
    dataTypes:
      - CommonSecurityLog
  - connectorId: CiscoAsaAma
    dataTypes:
      - CommonSecurityLog
  - connectorId: PaloAltoNetworks
    dataTypes:
      - CommonSecurityLog
  - connectorId: SecurityEvents
    dataTypes:
      - SecurityEvent
  - connectorId: AzureActiveDirectory
    dataTypes:
      - SigninLogs
  - connectorId: AzureActiveDirectory
    dataTypes:
      - AADNonInteractiveUserSignInLogs
  - connectorId: AzureMonitor(WireData)
    dataTypes:
      - WireData
  - connectorId: AzureMonitor(IIS)
    dataTypes:
      - W3CIISLog
  - connectorId: AzureActivity
    dataTypes:
      - AzureActivity
  - connectorId: AWS
    dataTypes:
      - AWSCloudTrail
  - connectorId: MicrosoftThreatProtection
    dataTypes:
      - DeviceNetworkEvents
  - connectorId: AzureFirewall
    dataTypes:
      - AzureDiagnostics
queryFrequency: 1h
queryPeriod: 1h
triggerOperator: gt
triggerThreshold: 0
tactics:
  - CommandAndControl
relevantTechniques:
  - T1071
query:  |
  let IPList = externaldata(IPAddress:string)[@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/Log4j_IOC_List.csv"] with (format="csv", ignoreFirstRecord=True);
  let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
  //Network logs
  let CSlogSourceIP = CommonSecurityLog | summarize by IPAddress = SourceIP, Type;
  let CSlogDestIP = CommonSecurityLog | summarize by IPAddress = DestinationIP, Type;
  let CSlogMsgIP = CommonSecurityLog | extend MessageIP = extract(IPRegex, 0, Message) | summarize by IPAddress = MessageIP, Type;
  let DnsIP = DnsEvents | summarize by IPAddress = IPAddresses, Type;
  // If you have enabled the _Im_Dns and/or imNetworkSession normalization in your workspace, you can uncomment one or both below.  Reference - https://docs.microsoft.com/azure/sentinel/normalization
  //let imDnsIP = _Im_Dns (response_has_any_prefix=IPList) | summarize by IPAddress = ResponseName, Type;
  //let imNetSessIP = imNetworkSession (dstipaddr_has_any_prefix=IPList) | summarize by IPAddress = DstIpAddr, Type;
  //Cloud service logs
  let officeIP = OfficeActivity | summarize by IPAddress = ClientIP, Type;
  let signinIP = SigninLogs | summarize by IPAddress, Type;
  let nonintSigninIP = AADNonInteractiveUserSignInLogs | summarize by IPAddress, Type;
  let azureActIP = AzureActivity | summarize by IPAddress = CallerIpAddress, Type;
  let awsCtIP = AWSCloudTrail | summarize by IPAddress = SourceIpAddress, Type;
  //Device logs
  let vmConnSourceIP = VMConnection | summarize by IPAddress = SourceIp, Type;
  let vmConnDestIP = VMConnection | summarize by IPAddress = DestinationIp, Type;
  let iisLogIP = W3CIISLog | summarize by IPAddress = cIP, Type;
  let devNetIP = DeviceNetworkEvents | summarize by IPAddress = RemoteIP, Type;
  //need to parse to get IP
  let azureDiagIP = AzureDiagnostics | where ResourceType == "AZUREFIREWALLS" | where Category in ("AzureFirewallApplicationRule", "AzureFirewallNetworkRule")
  | where msg_s has_any (IPList) | parse msg_s with Protocol 'request from ' SourceHost ':' SourcePort 'to ' DestinationHost ':' DestinationPort '. Action:' Action | summarize by IPAddress = DestinationHost, Type;
  let sysEvtIP = Event | where Source == "Microsoft-Windows-Sysmon" | where EventID == 3 | where EventData has_any (IPList) | extend EvData = parse_xml(EventData)
  | extend EventDetail = EvData.DataItem.EventData.Data
  | extend SourceIP = tostring(EventDetail.[9].["#text"]), DestinationIP = tostring(EventDetail.[14].["#text"])
  | where SourceIP in (IPList) or DestinationIP in (IPList) | extend IPAddress = iff(SourceIP in (IPList), SourceIP, DestinationIP) | summarize by IPAddress, Type;
  // If you have enabled the _Im_DNS and/or imNetworkSession normalization in your workdspace, you can uncomment below and include. Reference - https://docs.microsoft.com/azure/sentinel/normalization
  //let ipsort = union isfuzzy=true CSlogDestIP, CSlogMsgIP, CSlogSourceIP, DnsIP, officeIP, signinIP, nonintSigninIP, azureActIP, awsCtIP, vmConnDestIP, vmConnSourceIP, azureDiagIP, sysEvtIP, imDnsIP, imNetSessIP
  // If you uncomment above, then comment out the line below
  let ipsort = union isfuzzy=true CSlogDestIP, CSlogMsgIP, CSlogSourceIP, DnsIP, officeIP, signinIP, nonintSigninIP, azureActIP, awsCtIP, vmConnDestIP, vmConnSourceIP, azureDiagIP, sysEvtIP
  | summarize by IPAddress
  | where isnotempty(IPAddress) | where not(ipv4_is_private(IPAddress)) and IPAddress !in ('0.0.0.0','127.0.0.1');
  let ipMatch = ipsort | where IPAddress in (IPList);
  (union isfuzzy=true
  (CommonSecurityLog
  | where SourceIP in (ipMatch) or DestinationIP in (ipMatch) or Message has_any (ipMatch)
  | project TimeGenerated, SourceIP, DestinationIP, Message, SourceUserID, RequestURL, Type
  | extend MessageIP = extract(IPRegex, 0, Message)
  | extend IPMatch = case(SourceIP in (ipMatch), "SourceIP", DestinationIP in (ipMatch), "DestinationIP", MessageIP in (ipMatch), "Message", "No Match")
  | extend timestamp = TimeGenerated, IPEntity = case(IPMatch == "SourceIP", SourceIP, IPMatch == "DestinationIP", DestinationIP, IPMatch == "Message", MessageIP, "No Match")
  ),
  (OfficeActivity
  | where  ClientIP in (ipMatch)
  | project TimeGenerated, UserAgent, Operation, RecordType, UserId, ClientIP, Type
  | extend SourceIPAddress = ClientIP, Account = UserId
  | extend timestamp = TimeGenerated , IPEntity = SourceIPAddress , AccountEntity = Account
  ),
  (DnsEvents
  | where  IPAddresses has_any (ipMatch)
  | project TimeGenerated, Computer, IPAddresses, Name, ClientIP, Type
  | extend DestinationIPAddress = IPAddresses,  Host = Computer
  | extend timestamp = TimeGenerated, IPEntity = DestinationIPAddress, HostEntity = Host
  ),
  (VMConnection
  | where SourceIp in (ipMatch) or DestinationIp in (ipMatch)
  | project TimeGenerated, Computer, SourceIp, DestinationIp, Type
  | extend IPMatch = case( SourceIp in (ipMatch), "SourceIP", DestinationIp in (ipMatch), "DestinationIP", "None")
  | extend timestamp = TimeGenerated , IPEntity = case(IPMatch == "SourceIP", SourceIp, IPMatch == "DestinationIP", DestinationIp, "None"), Host = Computer
  ),
  (Event
  | where Source =~ "Microsoft-Windows-Sysmon"
  | where EventID == 3
  | where EventData has_any (ipMatch)
  | project TimeGenerated, EventData, UserName, Computer, Type
  | extend EvData = parse_xml(EventData)
  | extend EventDetail = EvData.DataItem.EventData.Data
  | extend SourceIP = tostring(EventDetail.[9].["#text"]), DestinationIP = tostring(EventDetail.[14].["#text"])
  | where SourceIP in (ipMatch) or DestinationIP in (ipMatch)
  | extend IPMatch = case( SourceIP in (ipMatch), "SourceIP", DestinationIP in (ipMatch), "DestinationIP", "None")
  | extend timestamp = TimeGenerated, AccountEntity = UserName, HostEntity = Computer , IPEntity = case(IPMatch == "SourceIP", SourceIP, IPMatch == "DestinationIP", DestinationIP, "None")
  ),
  (SigninLogs
  | where IPAddress in (ipMatch)
  | project TimeGenerated, UserPrincipalName, IPAddress, Type
  | extend timestamp = TimeGenerated, AccountEntity = UserPrincipalName, IPEntity = IPAddress
  ),
  (AADNonInteractiveUserSignInLogs
  | where IPAddress in (ipMatch)
  | project TimeGenerated, UserPrincipalName, IPAddress, Type
  | extend timestamp = TimeGenerated, AccountEntity = UserPrincipalName, IPEntity = IPAddress
  ),
  (W3CIISLog
  | where cIP in (ipMatch)
  | project TimeGenerated, Computer, cIP, csUserName, Type
  | extend timestamp = TimeGenerated, IPEntity = cIP, HostEntity = Computer, AccountEntity = csUserName
  ),
  (AzureActivity
  | where CallerIpAddress in (ipMatch)
  | project TimeGenerated, CallerIpAddress, Caller, Type
  | extend timestamp = TimeGenerated, IPEntity = CallerIpAddress, AccountEntity = Caller
  ),
  (
  AWSCloudTrail
  | where SourceIpAddress in (ipMatch)
  | project TimeGenerated, SourceIpAddress, UserIdentityUserName, Type
  | extend timestamp = TimeGenerated, IPEntity = SourceIpAddress, AccountEntity = UserIdentityUserName
  ),
  (
  DeviceNetworkEvents
  | where RemoteIP in (ipMatch)
  | where ActionType =~ "InboundConnectionAccepted"
  | project TimeGenerated, RemoteIP, DeviceName, Type
  | extend timestamp = TimeGenerated, IPEntity = RemoteIP, HostEntity = DeviceName
  ),
  (
  AzureDiagnostics
  | where ResourceType =~ "AZUREFIREWALLS"
  | where Category in ("AzureFirewallApplicationRule", "AzureFirewallNetworkRule")
  | where msg_s has_any (ipMatch)
  | project TimeGenerated, msg_s, Type
  | parse msg_s with Protocol 'request from ' SourceIP ':' SourcePort 'to ' DestinationIP ':' DestinationPort '. Action:' Action
  | where DestinationIP has_any (ipMatch)
  | extend timestamp = TimeGenerated, IPEntity = DestinationIP
  )
  // If you have enabled the _Im_Dns and/or imNetworkSession normalization in your workdspace, you can uncomment below and include. Reference - https://docs.microsoft.com/azure/sentinel/normalization
  //,
  //(_Im_Dns (response_has_any_prefix=IPList)
  //| project TimeGenerated, ResponseName, SrcIpAddr, Type
  //| extend DestinationIPAddress = ResponseName,  Host = SrcIpAddr
  //| extend timestamp = TimeGenerated, IPEntity = DestinationIPAddress, HostEntity = Host
  //),
  //(imNetworkSession (dstipaddr_has_any_prefix=IPList)
  //| project TimeGenerated, DstIpAddr, SrcIpAddr, Type
  //| extend timestamp = TimeGenerated, IPEntity = DstIpAddr, HostEntity = SrcIpAddr
  //)
  )
  | extend Name = tostring(split(AccountEntity, '@', 0)[0]), UPNSuffix = tostring(split(AccountEntity, '@', 1)[0])
  | extend HostName = tostring(split(HostEntity, '.', 0)[0]), DnsDomain = tostring(strcat_array(array_slice(split(HostEntity, '.'), 1, -1), '.'))
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: Name
      - identifier: UPNSuffix
        columnName: UPNSuffix
  - entityType: Host
    fieldMappings:
      - identifier: HostName
        columnName: HostName
      - identifier: DnsDomain
        columnName: DnsDomain
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IPEntity
version: 2.0.6
kind: Scheduled