Search by content status

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.anyOwner	The list of the IDs of the users who own the content by any means (either directly, via inheritance, or both). Hint: This is most likely the field you are looking for when identifying ownership in most cases! (It can contain zero, one or multiple IDs.)
arch.anyOwner.count	The number of the users who own the content by any means.
arch.anyOwner.setOn	The timestamp when the content owner(s) was set.
arch.directOwner	The list of the IDs of the users who directly own the content (either as an owner or as a tree owner set on the actual content). (It can contain zero, one or multiple IDs.)
arch.directOwner.count	The number of the users who directly own the content.
arch.directOwner.setOn	The timestamp when the content owner(s) was set.
arch.inheritedOwner	The list of the IDs of the users who own the content by inheritance (in other words, the set of tree owners set on any of its ancestors). (It can contain zero, one or multiple IDs.)
arch.inheritedOwner.count	The number of the users who own the content by inheritance.
arch.inheritedOwner.setOn	The timestamp when the content 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.

The following fields are also available, but they are primarily used internally by the app and are not recommended for end users.

Field	Description
arch.owner	The list of the IDs of the users who own the content. (It can contain zero, one or multiple IDs.)
arch.owner.count	The number of the users who own the content.
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 number of the users who own the content tree (the content itself plus all its descendants).
arch.treeOwner.count	The list of the IDs of the users who own the content tree (the content itself plus all its 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.

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:

arch.anyOwner.count > 0

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

arch.treeOwner.count > 0

Contents that have a direct owner but do not inherit any owner:

arch.directOwner.count > 0 and arch.inheritedOwner.count == 0

Contents that have no owner:

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

(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.anyOwner in (currentUser())

Contents owned by a specific user:

arch.anyOwner 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 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.