KQL Reference for freispace Dashboards

Dashboard widgets use Kusto Query Language (KQL)-style queries over a curated freispace data catalogue. The catalogue exposes business entities, their documented fields and relations, and applies the current user's permissions automatically. Query the entity names and field names shown on this page; the underlying database table names are not queryable.

info

Already familiar with KQL? You can skip ahead to the queryable entities and start querying right away.

Query basics

A query starts from an entity and passes the result through a pipeline. The most common pipeline operations are where, project, extend, summarize, sort, take and join.

bookings
| where is_published == true
| summarize booking_count = count() by project_id
| sort by booking_count desc

Some of the operators you will use most often when building widgets:

Operator	Purpose
where	Filter rows
project	Select or compute specific columns
summarize	Aggregate data (e.g. sum, count, avg)
sort by	Sort results
top	Limit to the top N rows
join	Combine rows from two tables
bin	Group values (e.g. dates) into fixed-size buckets, useful for time-series charts

Use the Microsoft KQL documentation for general operator and function syntax. The freispace catalogue and relation model described below determine which entities and fields are available to a dashboard query.

Relations and joins

Use a documented relation with join instead of joining database tables by hand. For example, this query counts bookings per project and returns the project name:

projects
| join bookings on id == project_id
| summarize booking_count = count() by id, name
| sort by booking_count desc

Relation joins respect visibility on both sides. A left join keeps the parent row when no visible related rows exist; an inner join keeps only rows with a visible match. Link entities such as booking_staff_links are useful when the assignment itself has data, especially its own starts_at and ends_at window.

Time, money and identifiers

• Datetimes are stored in UTC. Date grouping and date-part functions use the calling user's timezone. Dates such as issued_on, start and end have no time or timezone.
• Booking and assignment durations are calculated from ends_at - starts_at; there is no separate duration field. Assignment-level utilization must use the relevant link entity.
• Invoice and offer amounts are integer cents. 1250 means 12.50 in the document currency. Sum first and convert only for display; use todouble before division.
• Never add monetary values across currencies. Group by or filter on currency or cost_currency first.
• UUIDs are stable identifiers, but names and numbers are not guaranteed to be unique. Group by IDs as well as display names where duplicate names are possible.
• NULL values are meaningful. For example, a NULL project ID means that a booking is not attached to a project, and a NULL locked_at means that an invoice or offer is still a draft.

Visibility and permissions

Every query is tenant-scoped and filtered by the caller's permissions. Soft-deleted records and fixed entity filters are applied automatically. Counts can therefore differ from totals shown to another user or elsewhere in the application. In particular, unpublished bookings, restricted projects, clients, staff, resources and suites may be absent.

The staffs.number field is additionally permission-gated. Without the relevant internal-staff permission, it is unavailable; with own-record access, other people's values may be returned as NULL. It cannot be filtered or sorted.

Queryable entities

Only entities marked as queryable in the schema can be used as a query root. Related entities can also be reached through documented relations.

Core entities

bookings

Bookings reserve crew, suites or resources for a time range. Use starts_at < window_end and ends_at > window_start for overlap logic; filtering only starts_at finds bookings that start in the window, not every booking that overlaps it.

Field	Type	Description
id	UUID	Stable booking identifier.
title	string	Booking title; not unique.
byline	string, nullable	Secondary text under the title.
description	string, nullable	Free-text description.
starts_at, ends_at	datetime	Booking interval, stored in UTC.
is_all_day	boolean	Whether the booking is all day. For all-day bookings, compare calendar days rather than time parts.
is_optional	boolean	Whether the booking is tentative and does not block availability.
is_published	boolean	Whether the booking is published. Unpublished rows may already be hidden by permissions.
workload_percent	integer	Whole percentage, such as 50 for 50%.
project_id, order_id, status_id	UUID, nullable	Related project, order and team-defined booking status IDs.
created_at	datetime, nullable	Creation timestamp in UTC.

Relations: project, order, status, staffs, resources, suites, staff_links, resource_links, suite_links, offers and invoices.

Named filters: overlapping accepts from, to and an optional basis (effective or booking); overlapping_booking accepts from and to and checks only the booking interval. Both require from to be earlier than to and use a half-open interval [from, to).

projects

Projects group bookings and orders. Project visibility restrictions apply to query results and to related bookings.

Field	Type	Description
id	UUID	Stable project identifier.
name	string	Project name; not unique.
number	string, nullable	Project number, treated as text.
start, end	date, nullable	Planned calendar dates without timezone.
parent_id	UUID, nullable	Parent project; only one hierarchy level is queryable.
status	enum/integer	1 = active, 0 = inactive/archived.
color	string, nullable	Display color as a hex code.

Relations: bookings, client_links and parent.

clients

Clients can be restricted to individual users. A user without access to a client may see only clients they created themselves.

Field	Type	Description
id	UUID	Stable client identifier.
name	string	Company or person name; not unique.
number	string, nullable	Client number, treated as text.
status	boolean	true = active, false = deactivated.
starred	boolean	Whether the client is marked as a favorite.

Relation: project_links.

staffs

Staff members are available as people records. Staff cost, timezone and contract data are not exposed in this catalogue.

Field	Type	Description
id	UUID	Stable staff identifier.
display_name	string	Calendar/list name; not guaranteed unique.
title	string, nullable	Role or job title.
internal	boolean	true = internal staff, false = freelancer or external.
status	enum/integer	1 = active, 0 = inactive.
number	string, gated	Internal staff number; only available with the required permission and not filterable or sortable.

Relation: booking_links to booking_staff_links. Use that link entity for assignment windows and utilization.

resources

Resources include equipment and other bookable items. A resource cost is a rate, not a spend total.

Field	Type	Description
id	UUID	Stable resource identifier.
name	string	Resource name; not unique.
number	string, nullable	Inventory number, treated as text.
status	enum/integer	1 = active, 0 = inactive/retired.
cost	number, nullable	Internal cost rate per cost_unit.
cost_unit	string, nullable	Unit such as hour, day or piece.
cost_currency	string	ISO 4217 currency code.

Relation: booking_links to booking_resource_links.

suites

Suites are rooms or studios that can be booked. Like resource cost, suite cost is a rate rather than a total.

Field	Type	Description
id	UUID	Stable suite identifier.
name	string	Suite name; not unique.
room_number	string, nullable	Room number as text; use isempty() to cover both NULL and empty values.
status	enum/integer	1 = active, 0 = inactive/out of service.
cost	number, nullable	Internal cost rate per cost_unit.
cost_unit	string, nullable	Unit such as hour or day.
cost_currency	string	ISO 4217 currency code.

Relation: booking_links to booking_suite_links.

Financial and commercial entities

invoices

Invoices expose integer-cent amounts. Recurring invoice templates are excluded automatically. Revenue means invoiced net amount by issued_on; payment receipts and payment-level cash flow are not exposed.

Field	Type	Description
id	UUID	Stable invoice identifier.
number	string, nullable	Invoice number; NULL while the invoice is a draft.
subject	string	Invoice subject.
client_id, offer_id, order_id, issuer_id	UUID, nullable	Related record IDs.
issued_on, due_on, payment_due_on, paid_on	date	Invoice, service/due, payment deadline and payment dates. due_on, payment_due_on and paid_on may be NULL.
currency	string	ISO 4217 currency code.
total_net_cents, total_gross_cents	integer	Net and gross totals in cents.
outstanding_cents	integer	Remaining balance in cents. 0 does not necessarily mean paid.
doc_type	enum	Invoice, Cancellation, Reminder, Partial or Final. Recurring invoices are excluded.
locked_at, sent_at, canceled_at, created_at	datetime, nullable	Finalization, sending, cancellation and creation timestamps in UTC.

Calculated fields: is_canceled, is_locked, is_paid, outstanding_percentage, overdue and status. status is one of canceled, draft, paid, partially-paid, overdue or open. For paid business documents, also exclude cancellation documents; cancellation bookkeeping can set paid_on.

Relations: client, offer, order, issuer, bookings and booking_links.

Named filter: status accepts draft, locked, open, overdue, paid, partially-paid or canceled. The named filter's locked category means locked_at is set; it is not the same as the displayed status value.

offers

Offers are quotes sent to clients. Amounts are integer cents and must be grouped by currency before they are summed.

Field	Type	Description
id	UUID	Stable offer identifier.
number	string, nullable	Offer number; NULL while the offer is a draft.
subject	string	Offer subject.
client_id, order_id, issuer_id	UUID, nullable	Related record IDs.
issued_on, valid_until	date	Offer date and last valid date.
currency	string	ISO 4217 currency code.
total_net_cents, total_gross_cents	integer	Net and gross totals in cents.
outcome	enum, nullable	accepted or declined; NULL can mean open or draft depending on locked_at.
outcome_set_at, locked_at, sent_at, created_at	datetime, nullable	Outcome, finalization, sending and creation timestamps in UTC.
order_number	string, nullable	Client purchase-order number, not the same as order_id.

Calculated fields: is_locked, is_sent and status. status is one of draft, accepted, declined or open.

Relations: client, order, issuer, invoices, bookings and booking_links. Named filter status accepts draft, locked, open, accepted or declined.

orders

Orders group offers, invoices and bookings.

Field	Type	Description
id	UUID	Stable order identifier.
number	string	Order number, treated as text.
name	string, nullable	Order name.
status	enum/integer	1 = active, 0 = inactive/completed.

Calculated fields: is_open and status_label (active or inactive). Relations: offers, invoices and bookings.

Link entities

Link entities represent an individual relationship and are queryable because the relationship itself has useful data. Their rows are visible only when both ends are visible.

booking_staff_links

Fields: id (integer), booking_id (UUID), staff_id (UUID), activity_id (UUID, nullable), starts_at and ends_at (datetime, nullable), and position (integer, nullable). The assignment window is on this entity, not on staffs. If assignment times are NULL, use the related booking's times before measuring duration. Use dcount(booking_id) when counting bookings per staff member; count() counts assignments.

Relations: booking, staff and activity.

booking_resource_links

Fields: id (integer), booking_id (UUID), resource_id (UUID), activity_id (UUID, nullable), starts_at and ends_at (datetime, nullable), and position (integer, nullable). The assignment window is on this entity, not on resources; combine it with the related resource's cost and cost_unit when analyzing rates.

Relations: booking, resource and activity.

booking_suite_links

Fields: id (integer), booking_id (UUID), suite_id (UUID), activity_id (UUID, nullable), starts_at and ends_at (datetime, nullable), and position (integer, nullable). The assignment window is on this entity, not on suites.

Relations: booking, suite and activity.

client_project_links

Fields: id (integer), client_id (UUID) and project_id (UUID). Use this entity to bridge clients and projects, especially when rolling project bookings up to clients. Relations: client and project.

invoice_booking_links

Fields: id (UUID), invoice_id (UUID) and booking_id (UUID). Use it as a bridge between invoices and bookings. An invoice linked to several bookings appears several times, so do not sum invoice totals at link grain; aggregate invoices at invoice grain first. Relations: invoice and booking.

offer_booking_links

Fields: id (UUID), offer_id (UUID) and booking_id (UUID). Use it as a bridge between offers and bookings. Relations: offer and booking.

Examples

The following examples use the entity and field names from the schema. Choose the visualization after the query returns the desired result.

Bookings per project

projects
| join bookings on id == project_id
| summarize booking_count = count() by id, name
| sort by booking_count desc

Projects with no bookings

projects
| join kind=leftouter bookings on id == project_id
| summarize booking_count = count() by id, name, number
| where booking_count == 0

Monthly finalized net revenue per currency

invoices
| where isnotnull(locked_at)
| summarize revenue_net_cents = sum(total_net_cents) by month = bin(issued_on, 1mo), currency
| sort by month asc

Overdue invoices

invoices
| where overdue == true
| project id, number, status, payment_due_on, currency, outstanding_cents, outstanding_percentage

Offers per outcome

| summarize offer_count = count() by outcome

Staff assignment count

booking_staff_links
| summarize assignment_count = count(), booking_count = dcount(booking_id) by staff_id
| sort by booking_count desc

Resource rates by unit and currency

resources
| where status == 1
| summarize resource_count = count(), rate_total = sum(cost), rate_average = avg(cost) by cost_unit, cost_currency

Cross-entity calculations

For multi-step calculations, use a named intermediate query (let) and join its result back to the entity you want to display. For example, calculate resource rates per booking first, then roll them up by project:

let resource_rates = booking_resource_links
| join kind=leftouter resources on resource_id == id
| summarize booking_rate = sum(cost) by booking_id;
bookings
| join kind=leftouter resource_rates on id == booking_id
| summarize project_rate = sum(booking_rate) by project_id
| join kind=leftouter projects on project_id == id
| project project_id, name, project_rate

The exact query editor representation may expose these operations as a structured pipeline. The entity names, field names, relation names and semantics remain those documented here.

Widget-friendly results

Visualization	Recommended result
Table	Detail rows or several columns, such as bookings with titles and dates.
Number / KPI	One row with one aggregated value, such as count() or sum(total_net_cents).
Bar chart	A category and a measure, such as status and count().
Line chart	A date bucket and a measure, such as bin(issued_on, 1mo) and revenue.
Donut chart	A small set of categories and their counts or totals.

From query result to widget

Once your query returns a result, choose a visualization type for the widget:

Visualization	Best suited for
Table	Detailed, row-level results
Number / KPI	A single aggregated value, e.g. count() or sum()
Bar chart	Comparing values across categories
Line chart	Trends over time, typically combined with summarize ... by bin(Date, 1d)
Donut chart	Showing proportions between a small number of categories

Example queries

Count of bookings per client in the last 30 days, as a bar chart:

Bookings
| where StartDate >= ago(30d)
| summarize BookingCount = count() by Client
| order by BookingCount desc

Outstanding invoice total, as a Number/KPI widget:

Invoices
| where Status == "open"
| summarize TotalOpen = sum(Amount)

Accepted quotes per month, as a line chart:

Offers
| where Status == "accepted"
| summarize AcceptedCount = count() by bin(CreatedDate, 30d)
| order by CreatedDate asc