CQL examples

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

Overview

Searching, Filtering and Querying Registers

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

Registers

Contacts

The following common CQL searches can be performed on the Contacts Register

First and Last 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 on the Request register.

Due Date searches

Find items with due date of 1st March 2023

due:2023-05-01

Find items with an exact due date and time

due: 2023-05-01 01:01:01

Find items that were due yesterday.

due:-1ddue:now-1d

Find items due between the date range provided. For this example it will find all items due between 1st May and 3rd May 2024

due:>2024-05-01 due:<2024-05-03

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

due:>"2024-05-01 10:00:00" due:<"2024-05-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 the 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

Promote on Dashboard Searches

Find all items that have the “Promote on dashboard” checkbox ticked.

promoteOnDashboard:truepromoteOnDashboard:1promoteOnDashboard:yes

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

promoteOnDashboard:0promoteOnDashboard:falsepromoteOnDashboard: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 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

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.

Note 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 “[email protected]"

applicant:(email:[email protected])

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)

Reference Lookup Values

Needs and Tasks

Needs & Tasks

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

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

IIA cases without 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.

Case CQL:

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.

Case CQL:

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)

Private Assets

Private assets without a building type

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

Private Asset CQL:

is:active buildingType:none

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

Private assets with category of dwelling and building Type of Caravan

buildingCategory:dwelling AND buildingType:dwelling/caravan

Environmental Health Assessments

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.

EHA CQL:

electricityOnsite:any status:inspection-required

EHAs in ‘inspection required’ and no inspection was conducted

electricityOnsite:none status:inspection-required

Infrastructure Assessments

Infrastructure

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)

Below are the Damage Level numerical keys:

Damaged - Habitable / Functioning = 1 Inaccessible / Non-Functioning = 2 Partially Destroyed = 3 Totally Destroyed = 4 Unknown - Requires Assessment = 5 Not Damaged = 6

This will allow you to perform searches on the damageLevel field using these numerical keys

PreviousCQL for different field types NextVictorian Emergency Management

Last updated 4 months ago

Overview

Registers

Contacts

First and Last Name searches

Media searches

Requests

Due Date searches

Requestor searches

Status searches

Region searches

Assignment searches

Cost Recovery Method searches

Media searches

Information

Promote on Dashboard Searches

Date searches

Media searches

Recovery Cases

Affected Person(s) Searches

Reference Lookup Values

Needs and Tasks

General Case Queries

Active cases with no damaged assets and no active tasks

IIA cases without SIA inspections

Cases with tasks assigned

Reconciling cases with windemere tag against assignments to Windemere.

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

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

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

Private Assets

Private assets without a building type

Private assets with an attachment uploaded without the tag import

Environmental Health Assessments

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

EHAs in ‘inspection required’ and no inspection was conducted

Infrastructure Assessments

Reconciling cases with `windemere` tag against assignments to Windemere.

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

Private assets with an attachment uploaded without the tag `import`