# CQL examples ## Overview {% content-ref url="" %} [](https://docs.cw.crisisworks.com/concepts-and-fundamentals/on-a-computer/searching-filtering-and-querying-registers) {% endcontent-ref %} 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.

Example of CQL search to find all the 'Ricks' and all the 'O'Sheas'

{% hint style="warning" %} You MUST be in the correct register for these searches to work {% endhint %} **Refer to the** [**Reference Lookup Values**](#reference-lookup-values) **table below.** ## Contacts {% content-ref url="../../../modules/operations/contacts" %} [contacts](https://docs.cw.crisisworks.com/modules/operations/contacts) {% endcontent-ref %} The following common CQL searches can be performed in **Operations > Contacts:**
Name searches Find contacts with the first name of rick or Rick ✔ ```sparql firstName:rick ``` Find contacts with last name matching O’Shea or o’shea ✔ ```sparql lastname:O'Shea ``` Find contacts with first name Rick **and** last name O’Shea (as well as lowercase versions) ✔ ```sparql firstName:Rick lastName:O'Shea ``` Find contacts with first name of Rick and last name of O’Shea or any Crisisworks user. ✔ ```sparql (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 ✘ ```sparql firstName:Ric* lastName:~Smith ``` Perform a full-text search on all fields with the term Rick ✔ ```sparql "Rick" ```
Media Searches ✔ Find contacts where an avatar file has been uploaded ```sparql avatar:any ``` Find contacts where the avatar file field is empty ```sparql avatar:none ``` Find contacts where a signature file has been uploaded ```sparql signature:any ``` Find contacts where the signature file field is empty ```sparql signature:none ```
## Requests {% content-ref url="../../../modules/operations/requests" %} [requests](https://docs.cw.crisisworks.com/modules/operations/requests) {% endcontent-ref %} The following common CQL searches can be performed in **Operations > Requests**:
Due date searches ✔ Find items with due date of 25 March 2026 ```sparql due:2026-03-25 ``` Find items with an exact due date and time ```sparql due:"2026-04-01 10:15:00" ``` Find items that were due yesterday ```sparql 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 ```sparql 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 ```sparql 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 ```sparql due:>-7d due:<-6d ``` Find items where no due date is set ```sparql due:none ``` Find items where the due date is set ```sparql due:any ```
Requestor searches ✔ Find items where the requestor has a firstName of Rick ```sparql 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) ```sparql 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. ```sparql requestor:(firstName:Rick OR lastName:Jones) ``` Find items where no requestor has been set ```sparql requestor:none ``` Find items that have a requestor set ```sparql requestor:any ```
Status searches ✔ Find all items in the status of Requested. ```sparql status:requested ``` Find items in both the Assigned and In Progress statuses. You can use either of these, status key or label. ```sparql status:[requested,in_progress] ``` ```sparql status:["Requested","In Progress"] ```
Region searches ✘ Find items where the region is not set (ie empty) ```sparql regionId:none ``` Find items where the region is either not set or set to the region “default”. ```sparql regionId:[none,default] ```
Assignment searches ✔ Find all items assigned to the MEMO Deputy position. ```sparql assigned:"MEMO Deputy" ```
Cost Recovery Method searches ✔ Find items where “Cost Recovery Method” is set to None ```sparql payer:noauth ``` Find items where “Cost Recovery Method” is set to “MEMO - authorised council to pay” ```sparql payer:council ``` Find items where “Cost Recovery Method” is set to “MERC - authorised as an incident cost” ```sparql payer:incident ``` Find items where “Cost Recovery Method” is set to “Requestor - authorised and has agreed to pay” ```sparql payer:requestor ``` Find items where “Cost Recovery Method” is set to “Other” ```sparql payer:other ```
Media Searches ✔ Find items that have no Attachments uploaded ```sparql media:none ``` Find items that have Attachments uploaded to the item ```sparql media:any ```
## Information {% content-ref url="../../../modules/operations/information" %} [information](https://docs.cw.crisisworks.com/modules/operations/information) {% endcontent-ref %} 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. ```sparql promoteOnDashboard:true ``` ```sparql promoteOnDashboard:yes ``` ```sparql promoteOnDashboard:1 ``` Find all items where the “Promote on dashboard” checkbox is unticked. ```sparql promoteOnDashboard:0 ``` ```sparql promoteOnDashboard:false ``` ```sparql promoteOnDashboard:no ```
Date searches ✔ Find items where the “Expires at” date is before the current date ie Overdue ```sparql validUntil:2025-01-01 ``` Find items where the Schedule A Review checkbox is ticked ```sparql remind:Yes ``` Find items where Schedule A Review checkbox is ticked, and where Valid from date is after 1st Jan 2026 ```sparql remind:Yes validFrom:>2026-01-01 ``` Find items where the Next Review date is after 1st Jan 2025 ```sparql remindAt:>2025-01-01 ``` Find items where the Next Review date is after 1st Jan 2025 ```sparql remindAt:none ``` Find items where the Next Review date is set ```sparql remindAt:any ```
Media Searches ✔ Find items that have no Attachments uploaded ```sparql media:none ``` Find items that have Attachments uploaded to the item ```sparql media:any ```
## **Recovery Cases** {% content-ref url="../../../modules/recovery/cases" %} [cases](https://docs.cw.crisisworks.com/modules/recovery/cases) {% endcontent-ref %} ### **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 ```sparql isApplicantKnown:unknown ``` Find cases where the Affected Person is known ```sparql isApplicantKnown:known ``` Find cases where the Primary Contact has consented to share full details ```sparql sharingLevel:full ``` Find cases where the Primary Contact have not consented to share their details ```sparql sharingLevel:none ``` Find cases where the Primary Contact has not been asked about consent to share their details ```sparql sharingLevel:notYetAsked ``` Find cases where the Primary Contact or group are of Aboriginal or Torres Strait Islander origin ```sparql ATSIOrigin:yes ``` Below are other ATSI Origin searches where the values are No, Prefer Not to Say or Not Yet Asked ```sparql ATSIOrigin:noATSIOrigin:preferNotToSaypreferNotToSay:notYetAsked ``` Find cases where the Primary Contact’s Contact Type is set to Individual ```sparql applicant:(contactType:individual) ``` Find cases where the Primary Contact’s Contact Type is set to Organisation ```sparql 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. ```sparql 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. ```sparql applicant:(givenNames:Kelly*) ``` Find cases where applicants have the Family Name set to Smith ```sparql 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 ```sparql applicant:(givenNames:Kelly* AND familyName:Smith) ``` Find cases where the applicant has the Family Name set to either Smith or Brown ```sparql applicant:(familyName:Smith OR familyName:Brown) ``` Find cases where the applicant has the email address “" ```sparql 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. ```sparql applicant:(email:*brown*) ```
Property Use searches Find cases where the Property Use has the Residential checkbox ticked ```sparql propertyUseResidential:true ``` Find cases that have either the Residential - Primary or the Residential - Secondary checkboxes ```sparql propertyUseResidentialPrimary:true OR propertyUseResidentialSecondary:true ``` Find cases that do not have the Residential - Primary checkbox ticked. ```sparql propertyUseResidentialPrimary:false ``` Below is a list of all Property Use search terms that can be used to find one of more property uses: ```sparql 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 ```sparql privateAssets:any ``` Find cases that have no Private Assets added ```sparql privateAssets:none ``` Find cases that have a Private Asset added with the category set to Dwelling ```sparql privateAssets:(buildingCategory:dwelling) ``` Find cases that have a Private Asset added with the category set to either Dwelling or Equipment ```sparql privateAssets:(buildingCategory:dwelling OR buildingCategory:equipment) ``` Find cases that have a Private Asset added with the Asset Type set to Dairy ```sparql privateAssets:(buildingType:dairy) ``` Find cases that have a Private Asset added with the Damage Level set to Totally Destroyed ```sparql 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. ```sparql 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: ```sparql 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). ```sparql 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”. ```sparql 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 ```sparql privateAssets:(buildingCategory:dwelling AND primaryPlaceOfResidence:true) ```
### **Needs & Tasks** {% content-ref url="../../../modules/recovery/needs-and-tasks" %} [needs-and-tasks](https://docs.cw.crisisworks.com/modules/recovery/needs-and-tasks) {% endcontent-ref %} 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 ```sparql tasks:(category:accommodation) ``` Find cases that have a Needs & Task record with the category set to either Accommodation or Counselling ```sparql tasks:(category:accommodation OR category:counselling) ``` You can also use the label of the category when searching ```sparql tasks:(categoryLabel:Accommodation Temporary) ``` You can use wildcard searches when using the label if you’re unsure of the entire label ```sparql 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 ```sparql is:active privateAssets:none tasks:none ``` Active cases with active recovery tasks ```sparql is:active tasks:any ```
Date searches Find cases where the Referral Date is after 1st Jan 2025 ```sparql referralDate:>2025-01-01 ``` Find cases where the Referral Date is not set ```sparql referralDate:none ``` Find cases where the Referral Date is set ```sparql referralDate:any ```
Media Searches Find cases where the field “General Photo(s) of property” is empty ```sparql photos:none ``` “General Photo(s) of property” field ```sparql photos:any ``` Find cases where the media field “Case Documents” is empty ```sparql caseDocuments:none ``` Find cases that have photos uploaded into the “Case Documents” field ```sparql 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. ```sparql 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 ```sparql 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. ```sparql 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 ```sparql 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 ```sparql tree is:active not tasks:(categoryLabel:Arborist*) ```
Find tagged cases starting with "Fence" that don’t have an affected private asset of "Fence" 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 ```sparql tags:fence* NOT privateAssets:(buildingTypeLabel:Fencing) ```
Find tagged cases starting with "Fence" that don’t have an affected private asset of "Fence" 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 ```sparql tags:fence* NOT privateAssets:(buildingTypeLabel:Fencing) ```
### Private Assets {% content-ref url="../../../modules/recovery/private-assets" %} [private-assets](https://docs.cw.crisisworks.com/modules/recovery/private-assets) {% endcontent-ref %} 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. ```sparql is:active buildingType:none ```
Private assets with no related Case ✔ This CQL finds any private assets with an 'active' status that have been removed from a Case. ```sparql is:active not relatedcase:(status:any) ```
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. ```sparql 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 ```sparql 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` ```sparql is:active photos:any NOT tags:import ```
### Environmental Health Assessments {% content-ref url="../../../modules/recovery/environmental-health" %} [environmental-health](https://docs.cw.crisisworks.com/modules/recovery/environmental-health) {% endcontent-ref %} 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. ```sparql electricityOnsite:any status:inspection-required ```
EHAs in ‘inspection required’ and no inspection was conducted ```sparql electricityOnsite:none status:inspection-required ```
EHAs with no related Case ✔ This CQL finds any environmental health assessments with an 'active' status that have been removed from a Case. ```sparql is:active not relatedcase:(status:any) ```
### Infrastructure Assessments {% content-ref url="../../../modules/recovery/infrastructure" %} [infrastructure](https://docs.cw.crisisworks.com/modules/recovery/infrastructure) {% endcontent-ref %} 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” ```sparql is:active AND (infrastructureType:property OR infrastructureType:facility) AND (damageLevel:1 OR damageLevel:5) ```
## Reference Lookup Values | Category | Label | Value | | ------------------------------ | ------------------------------------ | ------------------------------ | | Building Category | Dwelling | dwelling | | Building Category | Facility | facility | | Building Category | Structure | structure | | Building Category | Equipment | equipment | | Building Category | Vehicle | vehicle | | Building Category | Other | other | | Building Type | Bikes | vehicle/bike | | Building Type | Boats | vehicle/boat | | Building Type | Business Economic Impact | other/business-economic-impact | | Building Type | Cars | vehicle/car | | Building Type | Caravans | dwellling/caravan | | Building Type | Community Centre(s) | facility/community | | Building Type | Dairy | equipment/dairy | | Building Type | Other Dwelling | dwelling/other | | Building Type | Other Equipment | equipment/other | | Building Type | Other Facility | facility/other | | Building Type | Fencing | structure/fencing | | Building Type | Flats/Units | dwelling/flat | | Building Type | Government | facility/gov | | Building Type | Holiday House | dwelling/holiday-house | | Building Type | Holiday Rental | dwelling/holiday-rental | | Building Type | A Primary Place of Residence | dwelling/house | | Building Type | Irrigation Pumps | equipment/irrigation | | Building Type | Machinery | equipment/machinery | | Building Type | Other | other/other | | Building Type | Shed/s | structure/shed | | Building Type | Shops/Factories | facility/shop | | Building Type | Other Structure | structure/other | | Building Type | Tank | structure/tank | | Building Type | Trailers | vehicle/trailers | | Building Type | Other Vehicle | vehicle/other | | Building Damage (Damage Level) | Habitable / Functioning | 1 | | Building Damage (Damage Level) | Uninhabitable / Non-Functioning | 2 | | Building Damage (Damage Level) | Partially Destroyed (no longer used) | 3 | | Building Damage (Damage Level) | Destroyed | 4 | | Building Damage (Damage Level) | Unknown - Requires Assessment | 5 | | Building Damage (Damage Level) | Not Damaged | 6 |