Detect web requests to potentially harmful files ASIM Web Session

let lookback = 5m;
let RiskyFileExtensions = materialize(externaldata(Extensions: string)
    [@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/RiskyFileExtensionsInUrl.csv"]
    with(format="csv", ignoreFirstRecord=True));
let CustomRiskyFileExtensions = (_ASIM_GetWatchlistRaw("Web_RiskyFileExtensions") // Create new Watchlist and add your custom indicators(Optional)
    | extend
        Extensions = tostring(WatchlistItem["Extensions"])
    | project Extensions
    | where isnotempty(Extensions));
let CombinedRiskyFileExtensions = union RiskyFileExtensions, CustomRiskyFileExtensions;
let knownRiskyFileExtensions=toscalar(CombinedRiskyFileExtensions
    | where isnotempty(Extensions)
    | summarize make_set(Extensions, 1000));
let Whitelisted_Domains = materialize(externaldata(WhiteListedDomains: string)
    [@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/WhiteListedDomainsForWebSessionUseCases.csv"]
    with(format="csv", ignoreFirstRecord=True));
let CustomWhiteListedDomains = (_ASIM_GetWatchlistRaw("Web_WhiteListedDomains") // Create new Watchlist and add your white listed domains(Optional)
    | extend
        WhiteListedDomains = tostring(WatchlistItem["WhiteListedDomains"])
    | project WhiteListedDomains
    | where isnotempty(WhiteListedDomains));
let CombinedWhitelisted_Domains = union Whitelisted_Domains, CustomWhiteListedDomains;
let knownWhitelisted_Domains=toscalar(CombinedWhitelisted_Domains
    | where isnotempty(WhiteListedDomains)
    | summarize make_set(WhiteListedDomains, 1000));
_Im_WebSession (starttime=ago(lookback), url_has_any=knownRiskyFileExtensions, eventresult='Success')
| project
    Url,
    SrcIpAddr,
    TimeGenerated,
    SrcUsername,
    SrcHostname,
    DstIpAddr,
    DstPortNumber
| extend requestedFileName=tostring(split(tostring(parse_url(Url)["Path"]), '/')[-1])
| extend requestedFileExt=extract(@'(\.\w+)$', 1, requestedFileName, typeof(string))
| where requestedFileExt in~ (knownRiskyFileExtensions)
| summarize
    EventCount=count(),
    EventStartTime=min(TimeGenerated),
    EventEndTime=max(TimeGenerated)
    by
    SrcUsername,
    SrcIpAddr,
    SrcHostname,
    DstIpAddr,
    DstPortNumber,
    Url,
    requestedFileName
| extend FQDN = split(parse_url(Url)["Host"], '.')
| extend Domain = iif(array_length(FQDN) > 1, strcat(FQDN[-2], '.', FQDN[-1]), FQDN)
| where Domain !in~ (knownWhitelisted_Domains)
| project-away FQDN
| extend Name = iif(SrcUsername contains "@", tostring(split(SrcUsername,'@',0)[0]),SrcUsername), UPNSuffix = iif(SrcUsername contains "@",tostring(split(SrcUsername,'@',1)[0]),"")

version: 1.0.0
id: c6608467-3678-45fe-b038-b590ce6d00fb
query: |
  let lookback = 5m;
  let RiskyFileExtensions = materialize(externaldata(Extensions: string)
      [@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/RiskyFileExtensionsInUrl.csv"]
      with(format="csv", ignoreFirstRecord=True));
  let CustomRiskyFileExtensions = (_ASIM_GetWatchlistRaw("Web_RiskyFileExtensions") // Create new Watchlist and add your custom indicators(Optional)
      | extend
          Extensions = tostring(WatchlistItem["Extensions"])
      | project Extensions
      | where isnotempty(Extensions));
  let CombinedRiskyFileExtensions = union RiskyFileExtensions, CustomRiskyFileExtensions;
  let knownRiskyFileExtensions=toscalar(CombinedRiskyFileExtensions
      | where isnotempty(Extensions)
      | summarize make_set(Extensions, 1000));
  let Whitelisted_Domains = materialize(externaldata(WhiteListedDomains: string)
      [@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/WhiteListedDomainsForWebSessionUseCases.csv"]
      with(format="csv", ignoreFirstRecord=True));
  let CustomWhiteListedDomains = (_ASIM_GetWatchlistRaw("Web_WhiteListedDomains") // Create new Watchlist and add your white listed domains(Optional)
      | extend
          WhiteListedDomains = tostring(WatchlistItem["WhiteListedDomains"])
      | project WhiteListedDomains
      | where isnotempty(WhiteListedDomains));
  let CombinedWhitelisted_Domains = union Whitelisted_Domains, CustomWhiteListedDomains;
  let knownWhitelisted_Domains=toscalar(CombinedWhitelisted_Domains
      | where isnotempty(WhiteListedDomains)
      | summarize make_set(WhiteListedDomains, 1000));
  _Im_WebSession (starttime=ago(lookback), url_has_any=knownRiskyFileExtensions, eventresult='Success')
  | project
      Url,
      SrcIpAddr,
      TimeGenerated,
      SrcUsername,
      SrcHostname,
      DstIpAddr,
      DstPortNumber
  | extend requestedFileName=tostring(split(tostring(parse_url(Url)["Path"]), '/')[-1])
  | extend requestedFileExt=extract(@'(\.\w+)$', 1, requestedFileName, typeof(string))
  | where requestedFileExt in~ (knownRiskyFileExtensions)
  | summarize
      EventCount=count(),
      EventStartTime=min(TimeGenerated),
      EventEndTime=max(TimeGenerated)
      by
      SrcUsername,
      SrcIpAddr,
      SrcHostname,
      DstIpAddr,
      DstPortNumber,
      Url,
      requestedFileName
  | extend FQDN = split(parse_url(Url)["Host"], '.')
  | extend Domain = iif(array_length(FQDN) > 1, strcat(FQDN[-2], '.', FQDN[-1]), FQDN)
  | where Domain !in~ (knownWhitelisted_Domains)
  | project-away FQDN
  | extend Name = iif(SrcUsername contains "@", tostring(split(SrcUsername,'@',0)[0]),SrcUsername), UPNSuffix = iif(SrcUsername contains "@",tostring(split(SrcUsername,'@',1)[0]),"")  
queryPeriod: 5m
relevantTechniques:
- T1133
- T1203
- T1566
alertDetailsOverride:
  alertDisplayNameFormat: User '{{SrcUsername}}' with IP address '{{SrcIpAddr}}' accessed a potentially harmful URL
  alertDescriptionFormat: User accessed URL - '{{Url}}' that contains a file - '{{requestedFileName}}' with risky extension. Downloading this file could pose a potential risk
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Web Session Essentials/Analytic Rules/RequestToPotentiallyHarmfulFileTypes.yaml
triggerOperator: gt
triggerThreshold: 0
entityMappings:
- entityType: IP
  fieldMappings:
  - identifier: Address
    columnName: SrcIpAddr
- entityType: URL
  fieldMappings:
  - identifier: Url
    columnName: Url
- entityType: File
  fieldMappings:
  - identifier: Name
    columnName: requestedFileName
- entityType: Host
  fieldMappings:
  - identifier: HostName
    columnName: SrcHostname
- entityType: Account
  fieldMappings:
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
name: Detect web requests to potentially harmful files (ASIM Web Session)
tactics:
- InitialAccess
- Persistence
- Execution
description: |
    'This rule detects web requests made to URLs containing file types such as .ps1, .bat, .vbs,.scr etc. which have the potential to be harmful if downloaded. This rule uses the [Advanced Security Information Model (ASIM)](https://aka.ms/AboutASIM) and supports any web session source that complies with ASIM.'
tags:
- SchemaVersion: 0.2.6
  Schema: WebSession
queryFrequency: 5m
severity: Medium
status: Available
eventGroupingSettings:
  aggregationKind: AlertPerResult
customDetails:
  EventEndTime: EventEndTime
  DstIpAddr: DstIpAddr
  EventCount: EventCount
  EventStartTime: EventStartTime
requiredDataConnectors: []
kind: Scheduled