In this page

What is CQL?

CQL is a fairly new, but extremely powerful search possibility in Confluence. It is a structured query language which allows flexibly searching for pages, blog posts and other types of content.

If you are not familiar with it yet, we strongly recommend that you learn more about CQL.

How to use CQL?

CQL is not available directly in the Confluence web user interface for now. There exists a lightweight app called CQL Search that integrates it into the Confluence experience in two ways:

  1. The new page CQL Search available in the "..." menu at each page can be used to run ad-hoc searches.
  2. The new macro CQL Query can be used to run a pre-defined search and display the hits in any Confluence page.

Learn more about the CQL Search app as it can be useful addition to your Confluence site.

Search for content by status with CQL

The Better Content Archiving app makes the content status among several other CQL fields available for CQL searches. You can easily build advanced searches against these fields:

Also, you can check the field values for any particular page or blog post in the CQL Fields tab of the Content Status Indicator:

Note that the same values are presented in a more user-friendly way in other parts of the user interface. You need to visit this tab only to see the precise field names or when troubleshooting searches.

CQL fields

The Better Content Archiving app introduces the following CQL fields that you can use in your queries.

Field Description
arch.status The name of the content's status.
arch.status.id The ID of the content's status.
(Searching by status ID is less readable than by name, but it is more stable, because it will not break when the status is renamed. To figure out the ID of a status, see the value of the "arch.status.id" CQL field for any content in that status through the Status details → CQL fields tab.)
arch.previousStatus The name of the content's previous status.
(It is the status from which the content entered its current status.)
arch.previousStatus.id The ID of the content's previous status.
(Searching by status ID is less readable than by name, but it is more stable, because it will not break when the status is renamed. To figure out the ID of a status, see the value of the "arch.status.id" CQL field for any content in that status through the Status details → CQL fields tab.)
arch.status.changedOn The timestamp when the status was last changed.
(It is when the content entered its current status.)
arch.event.lastViewedBy The ID of the user who last viewed the content.
arch.event.lastViewedOn The timestamp when the content was last viewed.
(Page views are tracked only since the installation of the app. That's why it is particularly dangerous to archive or delete contents that were last viewed e.g. more than 100 days ago if the app was installed less than 100 days ago!)
arch.event.lastUpdatedBy The ID of the user who last updated the content.
arch.event.lastUpdatedOn The timestamp when the content was last updated.
arch.event.archivedOn The timestamp when the content was archived.
arch.owner The list of the IDs of the users who own the content.
(It can contain zero, one or multiple IDs.)
arch.owner.setBy The ID of the user who set the content owner(s).
arch.owner.setOn The timestamp when the content owner(s) was set.
arch.treeOwner The list of the IDs of the users who own the content tree (the root content plus all descendants).
(It can contain zero, one or multiple IDs.)
arch.treeOwner.setBy The ID of the user who set the content tree owner(s).
arch.treeOwner.setOn The timestamp when the content tree owner(s) was set.
arch.expirationDate The timestamp when the content expires.
arch.expirationDate.inherited A flag that represents whether descendant pages inherit the expiration date.
arch.expirationDate.setBy The ID of the user who set the content expiration date.
arch.expirationDate.setOn The timestamp when the expiration date was set.
arch.archivingDate The timestamp when the content is going to be archived.
arch.archivingDate.inherited A flag that represents whether descendant pages inherit the archiving date.
arch.archivingDate.setBy The ID of the user who set the archiving date.
arch.archivingDate.setOn The timestamp when the archiving date was set.
arch.exclusion A flag that represents whether the content is excluded from content lifecycle management.
arch.exclusion.inherited A flag that represents whether descendant pages are also excluded (i.e. "inherit the exclusion").
arch.exclusion.setBy The ID of the user who set the exclusion.
arch.exclusion.setOn The timestamp when the exclusion was set.

You can, of course, use these fields in combination with the Confluence Cloud built-in CQL fields or fields provided by other third party apps. Note that the arch. prefix is used to avoid conflict with other CQL fields, effectively creating a unique namespace for the Better Content Archiving app.

CQL query samples

The following samples demonstrate how to use the CQL fields provided by Better Content Archiving in combination with other fields in frequent searches.

Status

Contents in the status "Expired": 

arch.status = "Expired"

Contents in the status #123: 

arch.status.id = 123

(Searching by status ID is less readable than by name, but it is more stable, because it will not break when the status is renamed. To figure out the ID of a status, see the value of the "arch.status.id" CQL field for any content in that status through the Status details → CQL fields tab.)

Contents in the space "Foo" and in the status "Expired": 

space = "Foo" and arch.status = "Expired"

Contents in the spaces "Foo" or "Bar" and in the status #123: 

space in ("Foo","Bar") and arch.status.id = 123

(Searching by status ID is less readable than by name, but it is more stable, because it will not break when the status is renamed. To figure out the ID of a status, see the value of the "arch.status.id" CQL field for any content in that status through the Status details → CQL fields tab.)

Contents with their status changed in the last 24 hours: 

arch.status.changedOn > now("-24h")

Contents in the status "To archive" and changed to that this week: 

arch.status = "To archive" and arch.status.changedOn > startOfWeek()

Contents in the status "Expired" and changed to that more than 10 days ago: 

arch.status = "Expired" and arch.status.changedOn < now("-10d")

Contents with previous status "Up-to-date": 

arch.previousStatus = "Up-to-date"

Contents transitioned from status "Expired" to "Up-to-date" in the last 7 days: 

arch.previousStatus = "Expired" and arch.status = "Up-to-date" and arch.status.changedOn > now("-7d")

Contents in the spaces "Foo" or "Bar" and their current status changed from status #123: 

space in ("Foo","Bar") and arch.previousStatus.id = 123

(Searching by status ID is less readable than by name, but it is more stable, because it will not break when the status is renamed. To figure out the ID of a status, see the value of the "arch.status.id" CQL field for any content in that status through the Status details → CQL fields tab.)

Analytics (views and updates)

Contents not viewed in the last 100 days: 

arch.event.lastViewedOn < now("-100d")

Contents last viewed by me: 

arch.event.lastViewedBy = currentUser()

Contents last viewed by a specific user: 

arch.event.lastViewedBy = "123abcde95673300697be2ba"

(The alphanumeric string is the Atlassian account ID of the user. How to figure out?)

Contents not updated in the last 50 days: 

arch.event.lastUpdatedOn < now("-50d")

Contents last updated by me: 

arch.event.lastUpdatedBy = currentUser()

Contents last updated by a specific user: 

arch.event.lastUpdatedBy = "123abcde95673300697be2ba"

(The alphanumeric string is the Atlassian account ID of the user. How to figure out?)

Owners

Contents that have an owner (not inherited by descendants): 

arch.owner.setOn <= now()

(The multi-valued owner field cannot be tested for emptiness in CQL. Therefore, this query tests the timestamp when the owner was set.)

Contents tree roots that have a tree owner (inherited by descendants): 

arch.treeOwner.setOn <= now()

(The multi-valued owner field cannot be tested for emptiness in CQL. Therefore, this query tests the timestamp when the owner was set.)

Contents that have either an owner or a tree owner: 

arch.owner.setOn <= now() or arch.treeOwner.setOn <= now()

(The multi-valued owner field cannot be tested for emptiness in CQL. Therefore, this query tests the timestamp when the owner was set.)

Contents that have neither an owner nor a tree owner: 

type in (page,blogpost) and not arch.owner.setOn <= now() and not arch.treeOwner.setOn <= now()

(The otherwise useless clause on the "type" field is needed because a negative expression cannot be the first clause in CQL.)

Contents owned by me: 

arch.owner in (currentUser())

Contents owned by a specific user: 

arch.owner in ("123abcde95673300697be2ba")

(The alphanumeric string is the Atlassian account ID of the user. How to figure out?)

Contents with the owner set by me: 

arch.owner.setBy = currentUser()

Contents with the owner set today: 

arch.owner.setOn > startOfDay()

Content tree roots owned by me: 

arch.treeOwner in (currentUser())

Content tree roots owned by a specific user: 

arch.treeOwner in ("123abcde95673300697be2ba")

(The alphanumeric string is the Atlassian account ID of the user. How to figure out?)

Content tree roots with the tree owner set by me: 

arch.treeOwner.setBy = currentUser()

Content tree roots with the tree owner set today: 

arch.treeOwner.setOn > startOfDay()

Expiration

Contents with an expiration date: 

arch.expirationDate.setOn <= now()

(This actual date field cannot be tested for emptiness in CQL. Therefore, this query tests timestamp when the actual date was set.)

Contents without an expiration date: 

type in (page,blogpost) and not arch.expirationDate.setOn <= now()

(The otherwise useless clause on the "type" field is needed because a negative expression cannot be the first clause in CQL.)

Contents with a passed expiration date: 

arch.expirationDate < now()

Contents with an expiration date inherited by descendants: 

arch.expirationDate.inherited = "true"

Contents with an expiration date set by me: 

arch.expirationDate.setBy = currentUser()

Contents with an expiration date set by a specific user: 

arch.expirationDate.setBy = "123abcde95673300697be2ba"

(The alphanumeric string is the Atlassian account ID of the user. How to figure out?)

Contents with an expiration date set today: 

arch.expirationDate.setOn > startOfDay()

Archiving

Contents with an archiving date: 

arch.archivingDate.setOn <= now()

(This actual date field cannot be tested for emptiness in CQL. Therefore, this query tests timestamp when the actual date was set.)

Contents without an archiving date: 

type in (page,blogpost) and not arch.archivingDate.setOn <= now()

(The otherwise useless clause on the "type" field is needed because a negative expression cannot be the first clause in CQL.)

Contents with a passed archiving date: 

arch.archivingDate < now()

Contents with an archiving date inherited by descendants: 

arch.archivingDate.inherited = "true"

Contents with archiving date set by me: 

arch.archivingDate.setBy = currentUser()

Contents with archiving date set today: 

arch.archivingDate.setOn >= startOfDay()

Contents archived yesterday: 

arch.event.archivedOn > startOfDay("-1d") and arch.event.archivedOn < startOfDay()

(At the "Search In" checkboxes check the "Archived pages" option for this query, otherwise it will not find anything!)

Contents archived this week: 

arch.event.archivedOn > startOfWeek()

(At the "Search In" checkboxes check the "Archived pages" option for this query, otherwise it will not find anything!)

Exclusions (contents not tracked)

Contents excluded from content lifecycle management: 

arch.exclusion = "true"

Contents excluded from content lifecycle management together with descendants: 

arch.exclusion.inherited = "true"

Contents excluded by me: 

arch.exclusion.setBy = currentUser()

Contents excluded today: 

arch.exclusion.setOn >= startOfDay()

Questions?

Ask us any time.