Server.Enrichment.ScanMalware

name: Server.Enrichment.ScanMalware
author: Jonas Lejon, ScanMalware
description: |
  Look up a domain, hostname, IPv4 address or URL in the public archive of
  ScanMalware, a free sandbox that renders URLs in a browser and keeps its
  public scans searchable. Returns the verdict and risk score of the newest
  completed scan of that host or URL, with a link to the full report. No
  account, API key or secret is needed, and nothing is submitted: the
  artifact only reads scans that already exist.

  Phishing pages often redirect to the brand they imitate, so the archive
  lists a lure hosted on `pages.dev` under `facebook.com` when that is where
  the lure ended. On shared platforms such as `github.io`, a subdomain is
  usually an unrelated tenant. A verdict is therefore attributed to the
  indicator only when the scan was submitted for that exact host or its
  `www` name. Scans that only redirected to the host are counted in
  RedirectedHere and scans of subdomains in SubdomainScans; neither lends
  its verdict.

  For a URL, a scan of that exact URL (ignoring a trailing slash) is
  preferred and Match is `url`. Otherwise the newest scan of the same host
  is returned and Match is `host`, because a scan of a home page is not a
  verdict on a login page of the same site. When no scan of the host exists
  the row has an empty Verdict: unknown is not the same as clean.

  Run from a server notebook, for example:

      SELECT * FROM Artifact.Server.Enrichment.ScanMalware(
        Indicator="example.com")

  To enrich hunt results, deduplicate first and pace the lookups:

      SELECT * FROM foreach(
        row={SELECT Domain FROM source() GROUP BY Domain},
        query={SELECT * FROM Artifact.Server.Enrichment.ScanMalware(
          Indicator=Domain) WHERE sleep(ms=300)})

  Each indicator costs one request for a host or two for a URL, plus one
  for the verdict when a scan is found. The anonymous API allows 600
  requests per minute per IP address. An HTTP 429 is reported in Error
  rather than retried. Requests carry the User-Agent
  `scanmalware-velociraptor/1.0`.

reference:
  - https://scanmalware.com/api-docs

type: SERVER

parameters:
  - name: Indicator
    type: string
    description: Domain, hostname, IPv4 address or http(s) URL to look up.
  - name: ScanWindow
    type: int
    default: 100
    description: How many of the newest scans of the host to consider, 1 to 100.

sources:
  - query: |
      LET Base <= "https://scanmalware.com"
      LET UA <= "scanmalware-velociraptor/1.0"

      LET Value <= strip(string=Indicator)
      LET IsURL <= Value =~ "^(?i)https?://"
      LET Host <= lowcase(string=strip(suffix=".",
          string=if(condition=IsURL, then=url(parse=Value).Hostname, else=Value)))
      LET ValidHost <= Host =~ "^[a-z0-9_-]+(\\.[a-z0-9_-]+)+$"

      // The url filter matches the submitted and the final URL as substrings,
      // so only a row submitted as this exact URL is a scan of it.
      LET ExactResponse <= SELECT Response, parse_json(data=Content) AS Body
        FROM if(condition=IsURL AND ValidHost, then={
          SELECT * FROM http_client(
            url=Base + "/api/v1/search/smql",
            params=dict(q="url:" + Value, `limit`=100),
            user_agent=UA)
        })

      LET ExactScans <= SELECT scan_id, url, final_url, completed_at
        FROM foreach(row=ExactResponse[0].Body.results)
        WHERE status = "completed"
          AND strip(string=url, suffix="/") = strip(string=Value, suffix="/")
        LIMIT 1

      // Lists scans submitted for the host, scans of its subdomains, and scans
      // of other sites that redirected here, newest first.
      LET HostResponse <= SELECT Response, parse_json_array(data=Content) AS Body
        FROM if(condition=ValidHost, then={
          SELECT * FROM http_client(
            url=Base + "/api/v1/domains/" + Host + "/scans",
            params=dict(`limit`=ScanWindow, status="completed"),
            user_agent=UA)
        })

      LET HostRows <= SELECT scan_id, url, final_url, completed_at,
          "url" in matched_on AS Submitted,
          lowcase(string=url(parse=url).Hostname) AS ScanHost
        FROM foreach(row=HostResponse[0].Body)

      LET OwnScans <= SELECT * FROM HostRows
        WHERE Submitted AND ScanHost in (Host, "www." + Host)

      LET SubdomainRows <= SELECT * FROM HostRows
        WHERE Submitted AND NOT ScanHost in (Host, "www." + Host)

      LET RedirectRows <= SELECT * FROM HostRows WHERE NOT Submitted

      LET Chosen <= if(condition=ExactScans, then=ExactScans[0],
                       else=OwnScans[0])

      LET SummaryResponse <= SELECT Response, parse_json(data=Content) AS Body
        FROM if(condition=Chosen.scan_id =~ "^[0-9a-f-]{36}$", then={
          SELECT * FROM http_client(
            url=Base + "/api/v1/scan/" + Chosen.scan_id + "/summary",
            user_agent=UA)
        })

      LET Summary <= SummaryResponse[0].Body

      LET Failed <= SELECT Response FROM chain(
          a=ExactResponse, b=HostResponse, c=SummaryResponse)
        WHERE Response != 200
        LIMIT 1

      // An empty list parses to [], an unreadable body to NULL.
      LET Unreadable <= SELECT Response FROM chain(
          a=ExactResponse, b=HostResponse, c=SummaryResponse)
        WHERE Response = 200 AND Body = NULL
        LIMIT 1

      SELECT Indicator,
          if(condition=ExactScans, then="url",
             else=if(condition=OwnScans, then="host")) AS Match,
          Summary.security_verdict.verdict AS Verdict,
          Summary.risk_score AS RiskScore,
          Summary.security_verdict.risk_level AS RiskLevel,
          Summary.security_verdict.confidence AS Confidence,
          Summary.security_verdict.risk_factors AS RiskFactors,
          Chosen.url AS ScannedURL,
          Chosen.final_url AS FinalURL,
          Chosen.completed_at AS ScannedAt,
          if(condition=Chosen.scan_id,
             then=Base + "/result/" + Chosen.scan_id) AS ReportURL,
          len(list=OwnScans) AS HostScans,
          len(list=SubdomainRows) AS SubdomainScans,
          len(list=RedirectRows) AS RedirectedHere,
          if(condition=NOT ValidHost,
             then="Indicator is not a hostname, IPv4 address or http(s) URL.",
             else=if(condition=Failed[0].Response = 429,
               then="ScanMalware rate limit reached (600 requests per minute); try again later.",
               else=if(condition=Failed,
                 then=format(format="ScanMalware returned HTTP %v.",
                             args=Failed[0].Response),
                 else=if(condition=Unreadable,
                   then="ScanMalware returned an unreadable response.",
                   else=if(condition=Chosen.scan_id AND NOT Summary.security_verdict.verdict,
                     then="The newest scan has no verdict yet; run again in a few minutes.")))))
            AS Error,
          Summary AS _Summary
      FROM scope()
      WHERE Value