---
updatedAt: 2026-09-24T14:01:28.000Z
agentTools:
  projectIndex: https://developer.zylo.com/llms.txt
---

# Query Examples

Examples and use cases for the query builder.

Below is a list of examples and use cases for the builder.

# Application Users

## Get Active and Inactive User Counts, Grouped by Application Id and Last Activity Date

**Permissions**:

* Application Users: `team:read`

```json
{
  "builder": {
    "application_users": {
      "fields": [
        {
          "name": "application_id",
          "as": "application_id"
        },
        {
          "name": "last_activity_date",
          "as": "last_activity_date"
        },
        {
          "name": "active",
          "op": "count",
          "filters": {
            "active": true
          },
          "as": "is_active"
        },
        {
          "name": "active",
          "op": "count",
          "filters": {
            "active": false
          },
          "as": "is_inactive"
        }
      ]
    }
  },
  "sort": "-last_activity_date",
  "limit": 50
}
```

# Applications

## Get Application Count by IT Owner Email

**Permissions**:

* Applications: `applications:read`

```json
{
  "builder": {
    "applications": {
      "fields": [
        {
          "name": "it_owner_user_email",
          "as": "owner"
        },
        {
          "name": "id",
          "op": "count",
          "as": "app_count"
        }
      ]
    }
  },
  "sort": "-app_count",
  "filters": {
    "owner": "!,null"
  },
  "limit": 50
}
```

# Contract Line Items

## Get Total Price of Contract Line Items that are Expiring in October 2025

**Permissions**:

* Contract Line Items: `contracts:read`

```json
{
  "builder": {
    "contract_line_items": {
      "fields": [
        {
          "name": "total_price",
          "op": "sum",
          "as": "total_price"
        }
      ],
      "filters": {
        "end_date": "2025-10-01,gte,2025-10-31,lte"
      }
    }
  },
  "sort": "+total_price",
  "limit": 1
}
```

## Get Applications Whose Line-Item Contracts Are Expired or Not Yet Active

Filters on `parent_contract_status` instead of `end_date`, since status already accounts for open-ended (null end\_date) contracts as "Active". Covers multi-app contracts, which surface here as line items.

**Permissions**:

* Contract Line Items: `contracts:read`

```json
{
  "builder": {
    "contract_line_items": {
      "fields": [
        {
          "name": "application_id",
          "as": "application_id"
        },
        {
          "name": "contract_id",
          "as": "contract_id"
        },
        {
          "name": "parent_contract_status",
          "as": "parent_contract_status"
        }
      ],
      "filters": {
        "parent_contract_status": "!,Active"
      }
    }
  },
  "sort": "+application_id",
  "limit": 50
}
```

# Contracts

## Get Total Number of Contracts by Status

**Permissions**:

* Contracts: `contracts:read`

```json
{
  "builder": {
    "contracts": {
      "fields": [
        {
          "name": "status",
          "as": "status"
        },
        {
          "name": "id",
          "op": "count",
          "as": "total_contracts"
        }
      ]
    }
  },
  "sort": "-total_contracts",
  "limit": 50
}
```

## Get Total Contract Value by Owner in a Given Date Range

**Permissions**:

* Contracts: `contracts:read`

```json
{
  "builder": {
    "contracts": {
      "fields": [
        {
          "name": "owner",
          "as": "owner"
        },
        {
          "name": "total_contract_value",
          "op": "sum",
          "filters": {
            "start_date": "2025-01-01,gte,2025-12-31,lte"
          },
          "as": "total_value"
        }
      ]
    }
  },
  "sort": "-total_value",
  "limit": 50
}
```

# Integrations

## Get Total of Failed and Successful Integrations

**Permissions**:

* Integrations: `integrations:read`

```json
{
  "builder": {
    "integrations": {
      "fields": [
        {
          "name": "status",
          "as": "status"
        },
        {
          "name": "id",
          "op": "count",
          "as": "total_integrations"
        }
      ]
    }
  },
  "sort": "-total_integrations",
  "limit": 50
}
```

# Software Charges

## Get One, Three and Six Month Payments Spend by Application Id

**Permissions**:

* Software Charges: `spend:read`

```json
{
  "builder": {
    "payments": {
      "fields": [
        {
          "name": "application_id",
          "as": "application_id"
        },
        {
          "name": "amount",
          "op": "sum",
          "filters": {
            "payment_date": "2025-01-01,gte,2025-01-31,lte"
          },
          "as": "one_month"
        },
        {
          "name": "amount",
          "op": "sum",
          "filters": {
            "payment_date": "2025-01-01,gte,2025-03-31,lte"
          },
          "as": "three_month"
        },
        {
          "name": "amount",
          "op": "sum",
          "filters": {
            "payment_date": "2025-01-01,gte,2025-06-30,lte"
          },
          "as": "six_month"
        }
      ]
    }
  },
  "sort": "-application_id",
  "limit": 30
}
```

## Get Total Payments by Application

**Permissions**:

* Software Charges: `spend:read`

```json
{
  "builder": {
    "payments": {
      "fields": [
        {
          "name": "application_id",
          "as": "application_id"
        },
        {
          "name": "amount",
          "op": "sum",
          "as": "total_amount"
        }
      ]
    }
  },
  "sort": "-total_amount",
  "limit": 50
}
```

# PO Line Items

## Get Price by Whether it is or isn't Saas for Each Department

**Permissions**:

* PO Line Items: `applications:read` AND `spend:read`

```json
{
  "builder": {
    "po_line_items": {
      "fields": [
        {
          "name": "department_name",
          "as": "department"
        },
        {
          "name": "total_spend_against",
          "op": "sum",
          "filters": {
            "is_saas": true
          },
          "as": "saas_spend"
        },
        {
          "name": "total_spend_against",
          "op": "sum",
          "filters": {
            "is_saas": false
          },
          "as": "non_saas_spend"
        },
        {
          "name": "total_spend_against",
          "op": "sum",
          "as": "total_spend"
        }
      ]
    }
  },
  "sort": "-total_spend",
  "limit": 50
}
```

# Usage Connect

## Get Trend of Usage Connect Job Statuses by Day

**Permissions**:

* Usage Connect: `integrations:read`

```json
{
  "builder": {
    "usage_connect": {
      "fields": [
        {
          "name": "upload_date",
          "as": "upload_date"
        },
        {
          "name": "id",
          "op": "count",
          "as": "total_jobs"
        },
        {
          "name": "id",
          "op": "count",
          "filters": {
            "import_status": "complete"
          },
          "as": "complete_jobs"
        },
        {
          "name": "id",
          "op": "count",
          "filters": {
            "import_status": "error"
          },
          "as": "error_jobs"
        }
      ]
    }
  },
  "sort": "-upload_date",
  "limit": 50
}
```

# Zylo Users

## Get Number of Users Last Login by Date

**Permissions**:

* Zylo Users: `admin`

```json
{
  "builder": {
    "zylo_users": {
      "fields": [
        {
          "name": "last_login",
          "as": "last_login"
        },
        {
          "name": "id",
          "op": "count",
          "as": "total_users"
        }
      ]
    }
  },
  "sort": "-last_login",
  "limit": 50
}
```