> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oasm.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Composing queries

> Combine batches, related rows, lists, filters and counts in Attack Surface Query to answer real attack surface questions

The building blocks — `select`, `include`, `where`, windows and aggregates — combine freely. This page starts from questions people actually ask and shows the pattern behind each answer, then a cookbook of complete batches you can paste into the API.

## Pattern 1: batch the questions

Put every part of a question into one batch. Queries run in parallel, and one failing query does not stop the others.

```json theme={null}
{
  "window": { "since": "now-7d" },
  "queries": {
    "newHosts": { "from": "hosts", "select": ["value", "kind", "target.value"] },
    "newPorts": { "from": "services", "select": ["host.value", "port", "http.title"] },
    "newFindings": { "from": "vulnerabilities", "select": ["name", "severity", "host.value"],
                     "where": { "logic": "and", "items": [{ "field": "isOpen", "op": "eq", "value": "true" }] } },
    "allTimePorts": { "from": "services", "groupBy": ["port"], "aggregate": { "n": { "fn": "count" } }, "window": null }
  }
}
```

The batch `window` is the default; `allTimePorts` opts out with `"window": null`.

## Pattern 2: widen a row with related fields

Start from the resource the question is about and pull the context through dot paths. *"Which TLS certificates expire this month, on which host and port, and what does the site say?"*

```json theme={null}
{ "from": "tls",
  "select": ["subjectCn", "notAfter", "daysToExpiry", "service.host.value", "service.port"],
  "where": { "logic": "and", "items": [{ "field": "notAfter", "op": "lt", "value": "now+30d" }] },
  "orderBy": [{ "field": "notAfter", "dir": "asc" }] }
```

## Pattern 3: filter on one thing, return another

`where` can test related rows and lists the result does not show. *"Services running WordPress on a host that has a critical finding."*

```json theme={null}
{ "from": "services",
  "select": ["host.value", "port", "http.title"],
  "where": { "logic": "and", "items": [
    { "field": "technologies.name", "op": "eq", "value": "WordPress" },
    { "field": "host.vulnerabilities.severity", "op": "eq", "value": "critical" } ] } }
```

A filter path may go through related rows before and after a list (`host.vulnerabilities.severity`, `services.http.title`), but through **one list at most**: `services.technologies.name` from `hosts` is refused. Start from the resource one list closer instead, as above.

> [!NOTE]
>
> Each list condition is checked on its own: the host has *some* critical finding, not necessarily an open one even if you add `host.vulnerabilities.isOpen` as a second condition. To tie several conditions to the same related row, use a [relation test](https://docs.oasm.io/attack-surface-query/filtering.md#relation-tests) (pattern 9), or query that row's resource instead (pattern 4).

## Pattern 4: query the child, not the parent

When the question is about the children, read them directly and pull the parent through a related field. This is also the way out of an include that is too large. *"Every service running an outdated nginx, with its host."*

```json theme={null}
{ "from": "technologies",
  "select": ["name", "version", "service.host.value", "service.port"],
  "where": { "logic": "and", "items": [
    { "field": "name", "op": "eq", "value": "nginx" },
    { "field": "version", "op": "in", "value": ["1.18.0", "1.20.1", "1.22.0"] } ] },
  "limit": 200 }
```

## Pattern 5: one row with everything around it

`include` attaches lists to each row; nest them to go two levels down. Keep the parent `limit` small. *"Everything known about api.example.com."*

```json theme={null}
{
  "queries": {
    "host": {
      "from": "hosts",
      "select": ["value", "kind", "firstSeen", "lastSeen", "target.value"],
      "limit": 1,
      "where": { "logic": "and", "items": [{ "field": "value", "op": "eq", "value": "api.example.com" }] },
      "include": {
        "services": {
          "select": ["port", "http.title", "http.statusCode", "http.webserver"],
          "limit": 20,
          "include": { "technologies": { "select": ["name", "version"] } }
        },
        "vulnerabilities": { "select": ["name", "severity", "detectionStatus"], "limit": 20 }
      }
    },
    "ips": {
      "from": "ips",
      "select": ["ip", "source"],
      "where": { "logic": "and", "items": [{ "field": "host.value", "op": "eq", "value": "api.example.com" }] }
    }
  }
}
```

The IPs are a second query of the same batch rather than a third include: a query has at most five includes in total (three for the AI agent), nested ones included.

## Pattern 6: filter the included children

An include's own `where` limits which children are attached, without filtering the parents. *"Every host, with only its admin-looking services."*

```json theme={null}
{ "from": "hosts", "select": ["value"], "limit": 50,
  "include": { "services": {
    "select": ["port", "http.title"], "limit": 5,
    "where": { "logic": "and", "items": [{ "field": "http.title", "op": "contains", "value": "admin" }] } } } }
```

To keep only the hosts that *have* such a service, add the same condition to the parent's `where` as a list path:

```json theme={null}
{ "from": "hosts", "select": ["value"], "limit": 50,
  "where": { "logic": "and", "items": [{ "field": "services.http.title", "op": "contains", "value": "admin" }] },
  "include": { "services": {
    "select": ["port", "http.title"], "limit": 5,
    "where": { "logic": "and", "items": [{ "field": "http.title", "op": "contains", "value": "admin" }] } } } }
```

## Pattern 7: list and count together

A list and its distribution are two queries of one batch, with the same filter.

```json theme={null}
{
  "queries": {
    "critical": { "from": "vulnerabilities", "select": ["name", "cvssScore", "cve", "host.value"],
      "where": { "logic": "and", "items": [
        { "field": "isOpen", "op": "eq", "value": "true" },
        { "field": "severity", "op": "eq", "value": "critical" } ] },
      "orderBy": [{ "field": "cvssScore", "dir": "desc" }], "limit": 100 },
    "criticalPerHost": { "from": "vulnerabilities", "groupBy": ["host.value"], "aggregate": { "n": { "fn": "count" } },
      "where": { "logic": "and", "items": [
        { "field": "isOpen", "op": "eq", "value": "true" },
        { "field": "severity", "op": "eq", "value": "critical" } ] } }
  }
}
```

## Pattern 8: follow up with ids

Every row carries an `id`. Use it in the next batch to drill down, or pass it to other OASM APIs.

```json theme={null}
{ "from": "services", "select": ["port", "http.title"],
  "where": { "logic": "and", "items": [{ "field": "host.id", "op": "in", "value": ["1396ad91-…", "e4e5c8ca-…"] }] } }
```

## Pattern 9: ask about what is missing

Some of the most useful security questions are about absence: a host that serves HTTP but not HTTPS, a target with no host left, a service nobody has fingerprinted. Relation tests say that directly. *"Hosts that answer on port 80 but have nothing on 443."*

```json theme={null}
{ "from": "hosts", "select": ["value", "target.value"], "limit": 100,
  "where": { "logic": "and", "items": [
    { "relation": "services", "where": { "logic": "and", "items": [{ "field": "port", "op": "eq", "value": "80" }] } },
    { "relation": "services", "quantifier": "none", "where": { "logic": "and", "items": [{ "field": "port", "op": "eq", "value": "443" }] } } ] } }
```

The same shape covers *every* (`"quantifier": "every"`: all of a host's ports are web ports) and *how many* (`"count": { "op": "gte", "value": "10" }`: hosts with ten open ports or more).

## Pattern 10: a matrix in one query

When a table needs a column per category, give each aggregate its own `where` instead of running one query per category. Then keep only the interesting rows with `having`. *"Targets ranked by open critical and high findings, only those that have any."*

```json theme={null}
{ "from": "vulnerabilities",
  "groupBy": ["host.target.value"],
  "where": { "logic": "and", "items": [{ "field": "isOpen", "op": "eq", "value": "true" }] },
  "aggregate": {
    "critical": { "fn": "count", "where": { "logic": "and", "items": [{ "field": "severity", "op": "eq", "value": "critical" }] } },
    "high": { "fn": "count", "where": { "logic": "and", "items": [{ "field": "severity", "op": "eq", "value": "high" }] } },
    "hosts": { "fn": "countDistinct", "field": "host.id" } },
  "having": { "logic": "or", "items": [
    { "field": "critical", "op": "gt", "value": "0" },
    { "field": "high", "op": "gt", "value": "0" } ] },
  "orderBy": [{ "field": "critical", "dir": "desc" }, { "field": "high", "dir": "desc" }] }
```

## Pattern 11: a chart without holes

A series for a chart needs one point per day, including quiet days, in the viewer's time zone. `fill` and `options.timezone` give exactly that. *"New ports and new findings per day over the last 30 days, Hanoi time."*

```json theme={null}
{
  "window": { "since": "now-30d" },
  "options": { "timezone": "Asia/Ho_Chi_Minh" },
  "queries": {
    "ports": { "from": "services", "groupBy": [{ "field": "firstSeen", "bucket": "day" }],
               "aggregate": { "n": { "fn": "count" } }, "fill": true },
    "findings": { "from": "vulnerabilities", "groupBy": [{ "field": "firstSeen", "bucket": "day" }],
                  "aggregate": { "n": { "fn": "count" },
                                 "critical": { "fn": "count", "where": { "logic": "and", "items": [{ "field": "severity", "op": "eq", "value": "critical" }] } } },
                  "fill": true }
  }
}
```

Both series have the same days in the same order, so they line up on one chart.

## Cookbook

> [accordion] Targets and how many hosts each has
>
> ```json theme={null}
> { "queries": {
>   "targets": { "from": "targets", "select": ["value", "type", "firstSeen"], "limit": 200 },
>   "hostsPerTarget": { "from": "hosts", "groupBy": ["target.value"], "aggregate": { "hosts": { "fn": "count" } },
>                       "orderBy": [{ "field": "hosts", "dir": "desc" }], "limit": 200 } } }
> ```

> [accordion] New domains and ports of the last 5 days
>
> ```json theme={null}
> { "window": { "since": "now-5d" }, "queries": {
>   "domains": { "from": "hosts", "select": ["value", "firstSeen", "target.value"],
>                "where": { "logic": "and", "items": [{ "field": "kind", "op": "eq", "value": "domain" }] } },
>   "ports": { "from": "services", "select": ["host.value", "port", "http.title", "http.statusCode"] } } }
> ```

> [accordion] Risky services exposed
>
> ```json theme={null}
> { "from": "services", "select": ["host.value", "port", "http.title"], "limit": 200,
>   "where": { "logic": "and", "items": [
>     { "field": "port", "op": "in", "value": ["21", "23", "445", "3306", "3389", "5432", "6379", "9200", "27017"] } ] } }
> ```

> [accordion] Login and admin pages
>
> ```json theme={null}
> { "from": "services", "select": ["host.value", "port", "http.url", "http.title"], "limit": 100,
>   "where": { "logic": "or", "items": [
>     { "field": "http.title", "op": "contains", "value": "admin" },
>     { "field": "http.title", "op": "contains", "value": "login" },
>     { "field": "technologies.name", "op": "in", "value": ["Jenkins", "Grafana", "Kibana", "phpMyAdmin"] } ] } }
> ```

> [accordion] Hosts in a network range
>
> ```json theme={null}
> { "from": "ips", "select": ["ip", "host.value", "host.target.value"],
>   "where": { "logic": "and", "items": [{ "field": "ip", "op": "inSubnet", "value": ["203.0.113.0/24", "198.51.100.0/24"] }] } }
> ```

> [accordion] Technology versions in use
>
> ```json theme={null}
> { "from": "technologies", "groupBy": ["name", "version"], "aggregate": { "services": { "fn": "count" } },
>   "orderBy": [{ "field": "services", "dir": "desc" }], "limit": 50 }
> ```

> [accordion] Where a CVE is present
>
> ```json theme={null}
> { "from": "vulnerabilities", "select": ["name", "severity", "host.value", "affectedUrl", "detectionStatus"],
>   "where": { "logic": "and", "items": [{ "field": "cve", "op": "eq", "value": "CVE-2024-3400" }] } }
> ```

> [accordion] Hosts no scan has seen for 30 days
>
> ```json theme={null}
> { "from": "hosts", "select": ["value", "lastSeen", "target.value"], "limit": 200,
>   "where": { "logic": "and", "items": [{ "field": "lastSeen", "op": "lt", "value": "now-30d" }] },
>   "orderBy": [{ "field": "lastSeen", "dir": "asc" }] }
> ```

> [accordion] Findings per week by severity
>
> ```json theme={null}
> { "window": { "since": "now-90d" }, "queries": { "trend": {
>   "from": "vulnerabilities",
>   "groupBy": [{ "field": "firstSeen", "bucket": "week" }, "severity"],
>   "aggregate": { "n": { "fn": "count" } }, "limit": 100 } } }
> ```

> [accordion] Certificates expiring within 30 days, wildcards first
>
> ```json theme={null}
> { "from": "tls", "select": ["subjectCn", "notAfter", "wildcard", "service.host.value", "service.port"],
>   "where": { "logic": "and", "items": [{ "field": "notAfter", "op": "between", "value": ["now", "now+30d"] }] },
>   "orderBy": [{ "field": "notAfter", "dir": "asc" }] }
> ```

> [accordion] Services with no HTTP response
>
> ```json theme={null}
> { "from": "services", "select": ["host.value", "port", "lastSeen"], "limit": 200,
>   "where": { "logic": "and", "items": [{ "field": "http.title", "op": "isNull" }, { "field": "http.statusCode", "op": "isNull" }] } }
> ```

> [accordion] Production hosts only (exclude dev, staging and internal names)
>
> ```json theme={null}
> { "from": "hosts", "select": ["value", "target.value"], "limit": 200,
>   "where": { "logic": "and", "items": [{ "not": { "logic": "or", "items": [
>     { "field": "value", "op": "startsWith", "value": "dev" },
>     { "field": "value", "op": "startsWith", "value": "staging" },
>     { "field": "value", "op": "endsWith", "value": ".internal" } ] } }] } }
> ```

> [accordion] Findings that carry both of two CVEs
>
> ```json theme={null}
> { "from": "vulnerabilities", "select": ["name", "cve", "host.value"],
>   "where": { "logic": "and", "items": [{ "field": "cve", "op": "containsAll", "value": ["CVE-2024-3400", "CVE-2024-3401"] }] } }
> ```

> [accordion] Hosts outside your known ranges
>
> ```json theme={null}
> { "from": "ips", "select": ["ip", "host.value"], "limit": 200,
>   "where": { "logic": "and", "items": [{ "field": "ip", "op": "notInSubnet", "value": ["10.0.0.0/8", "172.16.0.0/12", "203.0.113.0/24"] }] } }
> ```

> [accordion] DNS records pointing to a provider
>
> ```json theme={null}
> { "from": "dnsRecords", "select": ["recordType", "value", "host.value"],
>   "where": { "logic": "and", "items": [
>     { "field": "recordType", "op": "eq", "value": "CNAME" },
>     { "field": "value", "op": "contains", "value": "cloudfront.net" } ] } }
> ```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.