CQL examples

This page contains a series of useful CQL (Crisisworks Query Language) searches for use across all registers.

Overview

CQL (Crisisworks Query Language) can be used to perform advanced searches. They allow you to use structured queries to search for content.

The following search queries can be pasted into the Contains box on the relevant datagrid after you select the filter icon.

You MUST be in the correct register for these searches to work

Refer to the Reference Lookup Values table below.

Contacts

The following common CQL searches can be performed in Operations > Contacts:

Name searches

Find contacts with the first name of rick or Rick ✔

firstName:rick

Find contacts with last name matching O’Shea or o’shea ✔

lastname:O'Shea

Find contacts with first name Rick and last name O’Shea (as well as lowercase versions) ✔

firstName:Rick lastName:O'Shea

Find contacts with first name of Rick and last name of O’Shea or any Crisisworks user. ✔

(firstName:Rick lastName:O'Shea) OR (isUser:true)

Find contacts that have a first name starting with Ric and contacts with last that sounds like Smith. For example this could return a contact called Rick Smith or Ricky Smyth ✘

firstName:Ric* lastName:~Smith

Perform a full-text search on all fields with the term Rick ✔

"Rick"

Media Searches ✔

Find contacts where an avatar file has been uploaded

avatar:any

Find contacts where the avatar file field is empty

avatar:none

Find contacts where a signature file has been uploaded

signature:any

Find contacts where the signature file field is empty

signature:none

Requests

The following common CQL searches can be performed in Operations > Requests:

Due date searches ✔

Find items with due date of 25 March 2026

due:2026-03-25

Find items with an exact due date and time

due:"2026-04-01 10:15:00"

Find items that were due yesterday

due:-1ddue

Find items due between the date range provided. For this example it will find all items due between 1st and 20th March 2026

due:>2026-03-01 due:<2026-03-20

Find items due between between date range and time provided. For this example it will find all items due on 1st April 2026 between 10am and 11am

due:>"2026-04-01 10:00:00" due:<"2026-04-01 11:00:00"

Find items greater than 7 days ago and less than 6 days ago

due:>-7d due:<-6d

Find items where no due date is set

due:none

Find items where the due date is set

due:any

Requestor searches ✔

Find items where the requestor has a firstName of Rick

requestor:(firstName:Rick)

Find items where requestor has the firstName of Rick and the lastName of Jones (items must have both of these values set to return a match)

requestor:(firstName:Rick lastName:Jones)

Find items where the requestor has either the firstName of Rick or the lastName of Jones. For example Rick Smith will be a match and Bob Jones will also be a match.

requestor:(firstName:Rick OR lastName:Jones)

Find items where no requestor has been set

requestor:none

Find items that have a requestor set

requestor:any

Status searches ✔

Find all items in the status of Requested.

status:requested

Find items in both the Assigned and In Progress statuses. You can use either of these, status key or label.

status:[requested,in_progress]

status:["Requested","In Progress"]

Region searches ✘

Find items where the region is not set (ie empty)

regionId:none

Find items where the region is either not set or set to the region “default”.

regionId:[none,default]

Assignment searches ✔

Find all items assigned to the MEMO Deputy position.

assigned:"MEMO Deputy"

Cost Recovery Method searches ✔

Find items where “Cost Recovery Method” is set to None

payer:noauth

Find items where “Cost Recovery Method” is set to “MEMO - authorised council to pay”

payer:council

Find items where “Cost Recovery Method” is set to “MERC - authorised as an incident cost”

payer:incident

Find items where “Cost Recovery Method” is set to “Requestor - authorised and has agreed to pay”

payer:requestor

Find items where “Cost Recovery Method” is set to “Other”

payer:other

Media Searches ✔

Find items that have no Attachments uploaded

media:none

Find items that have Attachments uploaded to the item

media:any

Information

The following common CQL searches can be performed in Operations > Information:

Promote on Dashboard searches ✔

Find all items that have the “Promote on dashboard” checkbox ticked. Any of these options will work.

promoteOnDashboard:true

promoteOnDashboard:yes

promoteOnDashboard:1

Find all items where the “Promote on dashboard” checkbox is unticked.

promoteOnDashboard:0

promoteOnDashboard:false

promoteOnDashboard:no

Date searches ✔

Find items where the “Expires at” date is before the current date ie Overdue

validUntil:<now

Find items where the Valid From date is after 1st Jan 2025.

validFrom:>2025-01-01

Find items where the Schedule A Review checkbox is ticked

remind:Yes

Find items where Schedule A Review checkbox is ticked, and where Valid from date is after 1st Jan 2026

remind:Yes validFrom:>2026-01-01

Find items where the Next Review date is after 1st Jan 2025

remindAt:>2025-01-01

Find items where the Next Review date is after 1st Jan 2025

remindAt:none

Find items where the Next Review date is set

remindAt:any

Media Searches ✔

Find items that have no Attachments uploaded

media:none

Find items that have Attachments uploaded to the item

media:any

Recovery Cases

Affected Person(s) Searches

The following common CQL searches can be performed in Recovery > Cases:

Affected Person(s) searches

Find cases where the Affected Person is unknown

isApplicantKnown:unknown

Find cases where the Affected Person is known

isApplicantKnown:known

Find cases where the Primary Contact has consented to share full details

sharingLevel:full

Find cases where the Primary Contact have not consented to share their details

sharingLevel:none

Find cases where the Primary Contact has not been asked about consent to share their details

sharingLevel:notYetAsked

Find cases where the Primary Contact or group are of Aboriginal or Torres Strait Islander origin

ATSIOrigin:yes

Below are other ATSI Origin searches where the values are No, Prefer Not to Say or Not Yet Asked

ATSIOrigin:noATSIOrigin:preferNotToSaypreferNotToSay:notYetAsked

Find cases where the Primary Contact’s Contact Type is set to Individual

applicant:(contactType:individual)

Find cases where the Primary Contact’s Contact Type is set to Organisation

applicant:(contactType:organisation)

Find cases where applicants have the First Name set to Kelly. This will be an exact search for the word “Kelly”. If a middle name is set in this field it will not return a result.

applicant:(givenNames:Kelly)

Alternatively you can use a wildcard search as per the example below which will find results such as Kelly, Kelly-Anne or Kelly Rose.

applicant:(givenNames:Kelly*)

Find cases where applicants have the Family Name set to Smith

applicant:(familyName:Smith)

Find cases where the applicant has the Family Name set to Smith and the Given Names that start with the word Kelly

applicant:(givenNames:Kelly* AND familyName:Smith)

Find cases where the applicant has the Family Name set to either Smith or Brown

applicant:(familyName:Smith OR familyName:Brown)

Find cases where the applicant has the email address “matt.brown@gmail.com"

applicant:(email:matt.brown@gmail.com)

If the entire email address is not known, a wildcard search can be useful. This will find cases where the applicant’s email address has the word brown anywhere in the email address.

applicant:(email:*brown*)

Property Use searches

Find cases where the Property Use has the Residential checkbox ticked

propertyUseResidential:true

Find cases that have either the Residential - Primary or the Residential - Secondary checkboxes

propertyUseResidentialPrimary:true OR propertyUseResidentialSecondary:true

Find cases that do not have the Residential - Primary checkbox ticked.

propertyUseResidentialPrimary:false

Below is a list of all Property Use search terms that can be used to find one of more property uses:

propertyUseResidential:
propertyUseResidentialPrimary:
propertyUseResidentialSecondary:
propertyUseResidentialUnclassified: 
propertyUseCommercial:
propertyUseAccommodation: 
propertyUseFarm:
propertyUseCommunity: 
propertyUseGovernment: 
propertyUseVacant:
propertyUseInfrastructure: 
propertyUseOther:

Private Asset searches

Find cases that have one or more Private Assets added

privateAssets:any

Find cases that have no Private Assets added

privateAssets:none

Find cases that have a Private Asset added with the category set to Dwelling

privateAssets:(buildingCategory:dwelling)

Find cases that have a Private Asset added with the category set to either Dwelling or Equipment

privateAssets:(buildingCategory:dwelling OR buildingCategory:equipment)

Find cases that have a Private Asset added with the Asset Type set to Dairy

privateAssets:(buildingType:dairy)

Find cases that have a Private Asset added with the Damage Level set to Totally Destroyed

privateAssets:(buildingDamageLabel:Totally Destroyed)

Find cases that have a Private Asset with the Category of Dwelling and has the Damage Level set to either “Totally Destroyed” or “Damaged- Habitable / Functioning”.

Note - this query is using the label of the Damage Level field so the label must be entered exactly as it is displayed.

privateAssets:(buildingCategory:dwelling AND 
buildingDamageLabel:Damaged - Habitable / Functioning OR 
buildingDamageLabel:Totally Destroyed)

Alternatively you can use a wildcard to search for the word Damaged within the Damage Level label:

privateAssets:(buildingCategory:dwelling AND 
buildingDamageLabel:Damaged* OR 
buildingDamageLabel:Totally*)

This will return the same result as the previous search without the need for entering the entire label. This will return all cases that have Private Assets with the Category of Dwelling and has the Damage Level starting with the either the words “Damaged” or “Totally”

Alternatively, you can use the numerical keys of the Damage Level field (which is called buildingDamage in the back end).

privateAssets:(buildingCategory:dwelling) AND 
privateAssets:(buildingType:dwelling/caravan)

This will allow you to perform the following search using these numerical keys

This will return the same result as the previous search where the Private Asset Category is set to Dwelling and has the Damage Level set to either “Damaged- Habitable / Functioning” or “Totally Destroyed”.

privateAssets:(buildingCategory:dwelling AND buildingDamage:1 OR buildingDamage:4)

Will find private assets with the category of Dwelling where the Is this a Primary Place of Residence checkbox is ticked

privateAssets:(buildingCategory:dwelling AND primaryPlaceOfResidence:true)

Needs & Tasks

The following common CQL searches can be performed in Recovery > Needs & Tasks:

Need & Task searches

Find cases that have a Needs & Task record with the category set to Accommodation

tasks:(category:accommodation)

Find cases that have a Needs & Task record with the category set to either Accommodation or Counselling

tasks:(category:accommodation OR category:counselling)

You can also use the label of the category when searching

tasks:(categoryLabel:Accommodation Temporary)

You can use wildcard searches when using the label if you’re unsure of the entire label

tasks:(categoryLabel:Livestock*)

This will return cases that have a Needs & Task record with the category that starts with Livestock. This will return results where the category is set to Livestock Burial, Livestock Feed and Livestock Transport.

General Case Queries

Active cases with no damaged assets and no active tasks searches

Active cases with no damaged assets and no active tasks searches

is:active privateAssets:none tasks:none

Active cases with active recovery tasks

is:active tasks:any

Date searches

Find cases where the Referral Date is after 1st Jan 2025

referralDate:>2025-01-01

Find cases where the Referral Date is not set

referralDate:none

Find cases where the Referral Date is set

referralDate:any

Media Searches

Find cases where the field “General Photo(s) of property” is empty

photos:none

“General Photo(s) of property” field

photos:any

Find cases where the media field “Case Documents” is empty

caseDocuments:none

Find cases that have photos uploaded into the “Case Documents” field

caseDocuments:any

Cases without Secondary Impact Assessments (SIA) inspections

The objective is to find cases that came from the FRV import process that have been incorrectly closed without having an SIA follow-up. It assumes the council is using one or more tags containing sia to track cases having SIA data or requirements to collect data.

NOT is:active tags:iia-frv NOT tags:*sia*

Cases with tasks assigned

The objective is to find active cases where a Needs & Tasks record was created and assigned the MRM Deputy position

is:active tasks:(assigned:"MRM Deputy")

Reconciling cases with windemere tag against assignments to Windemere

Some councils use tags at the case level to identify records they want to assign before assignment actually occurs. They then assign the contractor via Needs & Tasks.

To extend the above recipe, this reconciles cases against the windemere tag as follows: the objective is to find records tagged with windemere that have NOT been formally referred to Windemere using a Needs & Tasks record.

is:active tags:windermere NOT tasks:(assigned:"Windemere")

Records Tagged with arborist that don't have a recovery task for “arborist”

We want to find all active records that have a tag starting with "arborist" that don't have a task with a label starting with "arborist" so we can bulk create a new task, note once done remove these tags so you are only working with the needs/tasks for the recovery cases

is:active tags:arborist* NOT tasks:(categoryLabel:Arborist*)

Find the use of the words tree that doesn’t have a recovery task for “arborist”

Look for words in the case anywhere where "tree" or "arborist" appear where there is no task created yet, so you can bulk add a task, you may want to review each record to be sure

tree is:active not tasks:(categoryLabel:Arborist*)

Find tagged cases starting with "Fence" that don’t have an affected private asset of "Fence"

tags:fence* NOT privateAssets:(buildingTypeLabel:Fencing)

Find tagged cases starting with "Fence" that don’t have an affected private asset of "Fence"

tags:fence* NOT privateAssets:(buildingTypeLabel:Fencing)

Private Assets

The following common CQL searches can be performed in Recovery > Private Assets:

Private assets without a building type ✔

This CQL finds open records without a buildingType attribute, which is typically a mistake.

is:active buildingType:none

Private asset dwellings as primary residence, destroyed or uninhabitable, with a related Case ✔

This CQL finds any private assets with an 'active' status, has buildingCategory of 'dwelling' that is a primaryPlaceofResidence, is linked to an active Case and is either destroyed or uninhabitable.

is:active 
buildingCategory:dwelling buildingdamage:[4,2] 
primaryplaceofresidence:Yes 
relatedcase:(status:any)

Private assets caravans linked to an active Case ✔

Private assets with category of dwelling and building Type of Caravan

buildingCategory:dwelling AND 
buildingType:dwelling/caravan 
relatedcase:(status:any)

Private assets with an attachment uploaded without the tag import ✔

This CQL finds open records with a PDF uploaded that do not have the tag import

is:active photos:any NOT tags:import

Environmental Health Assessments

The following common CQL searches can be performed in Recovery > Environmental Health:

EHAs in ‘inspection required’ even though the inspection was conducted

Some inspections were conducted but the status incorrectly set to ‘inspection required’. We can achieve this by checking if one of the inspection fields has been set.

electricityOnsite:any status:inspection-required

EHAs in ‘inspection required’ and no inspection was conducted

electricityOnsite:none status:inspection-required

Infrastructure Assessments

The following common CQL searches can be performed in Recovery > Infrastructure:

Infrastructure searches

Find Infrastructure Assessments with the type of either Council Facility or Council Property with the Damage Level of either “Damaged- Habitable / Functioning” or “Unknown - Requires Assessment”

is:active AND (infrastructureType:property OR infrastructureType:facility) 
AND (damageLevel:1 OR damageLevel:5)