In this page

What is CQL?

CQL is an 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.

You can, of course, use these fields in combination with the 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.

Status

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.)

Views (view analytics)

Field Description
arch.event.lastViewedBy The user ID 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!)

Updates (update analytics)

Field Description
arch.event.lastUpdatedBy The user ID who last updated the content.
arch.event.lastUpdatedOn The timestamp when the content was last updated.

Owners

Field Description
arch.anyOwner The list of the user IDs who own the content, either directly or through inheritance.
This field consolidates both types of ownership into a single list.
(It can contain zero, one or multiple IDs.)
arch.anyOwner.count The number of the users who own the content, either directly or through inheritance.
arch.anyOwner.setOn The timestamp when the content owners were set, either directly or through inheritance.
arch.directOwner The list of the user IDs who own the content, directly.
It includes the owners and tree owners set on this content.
(It can contain zero, one or multiple IDs.)
arch.directOwner.count The number of the users who own the content, directly.
arch.directOwner.setOn The timestamp when the content owners were set, directly.
arch.inheritedOwner The list of the user IDs who own the content, through inheritance.
It includes the tree owners set on any ancestor of this content.
(It can contain zero, one or multiple IDs.)
arch.inheritedOwner.count The number of the users who own the content, through inheritance.
arch.inheritedOwner.setOn The timestamp when the content owners were set, through inheritance.

The following, more granular fields are also available. These are primarily used internally by the app and are less useful for end-user searches.

Field Description
arch.owner The list of the user IDs who own the content and are not inherited by descendants. (It can contain zero, one or multiple IDs.)
arch.owner.count The number of the users who own the content and are not inherited by descendants.
arch.owner.setBy The user ID who set the content owners.
arch.owner.setOn The timestamp when the content owners were set.
arch.treeOwner The list of the user IDs who own the content and are inherited by descendants.
These effectively own the entire content tree rooted at this content.
(It can contain zero, one or multiple IDs.)
arch.treeOwner.count The number of the users who own the content tree.
arch.treeOwner.setBy The user ID who set the content tree owners.
arch.treeOwner.setOn The timestamp when the content tree owners were set.

Expiration

Field Description
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 user ID who set the content expiration date.
arch.expirationDate.setOn The timestamp when the expiration date was set.

Archiving

Field Description
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 user ID who set the archiving date.
arch.archivingDate.setOn The timestamp when the archiving date was set.
arch.event.archivedOn The timestamp when the content was archived.

Excluded

Field Description
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 user ID who set the exclusion.
arch.exclusion.setOn The timestamp when the exclusion was set.

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.)

Views (view analytics)

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?)

Updates (update analytics)

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 owners, either directly or through inheritance: 

arch.anyOwner.count > 0

Contents that have no owner: 

type in (page,blogpost) and not arch.anyOwner.count > 0

Contents owned by me, either directly or through inheritance: 

arch.anyOwner in (currentUser())

Contents owned by a specific user, either directly or through inheritance: 

arch.anyOwner in ("123abcde95673300697be2ba")

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

Contents that have owners changed this week: 

arch.anyOwner.count > 0 and arch.anyOwner.setOn > startOfWeek()

Contents with owners, both those that are and aren't inherited by descendants, set by me: 

arch.owner.setBy = currentUser() or arch.treeOwner.setBy = currentUser()

Roots of content trees with tree owners (that are inherited by descendants): 

arch.treeOwner.count > 0

Contents with owners, that are not inherited by descendants, set today: 

arch.owner.setOn > startOfDay()

Roots of content trees with tree owners (that are inherited by descendants) set this week: 

arch.treeOwner.setBy = "123abcde95673300697be2ba"

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

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!)

Excluded

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.