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
{
"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
{
"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
{
"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
}Contracts
Get Total Number of Contracts by Status
Permissions:
- Contracts:
contracts:read
{
"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
{
"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
{
"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
{
"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
{
"builder": {
"payments": {
"fields": [
{
"name": "application_id",
"as": "application_id"
},
{
"name": "amount",
"op": "sum",
"as": "total_amount"
}
]
}
},
"sort": "-total_amount",
"limit": 50
}Get Number of Payments by Payment Method
Permissions:
- Software Charges:
spend:read
{
"builder": {
"payments": {
"fields": [
{
"name": "payment_method",
"as": "payment_method"
},
{
"name": "id",
"op": "count",
"as": "total_payments"
}
]
}
},
"sort": "-total_payments",
"limit": 20
}PO Line Items
Get Price by Whether it is or isn't Saas for Each Department
Permissions:
- PO Line Items:
applications:readANDspend:read
{
"builder": {
"po_line_items": {
"fields": [
{
"name": "department",
"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
{
"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 Total Number of Users by Role
Permissions:
- Zylo Users:
admin
{
"builder": {
"zylo_users": {
"fields": [
{
"name": "role",
"as": "role"
},
{
"name": "id",
"op": "count",
"as": "total_users"
}
]
}
},
"sort": "-total_users",
"limit": 20
}Get Number of Users Last Login by Date
Permissions:
- Zylo Users:
admin
{
"builder": {
"zylo_users": {
"fields": [
{
"name": "last_login",
"as": "last_login"
},
{
"name": "id",
"op": "count",
"as": "total_users"
}
]
}
},
"sort": "-last_login",
"limit": 50
}