Introducing the new Confluence Source Plugin

• August 28, 2024

Confluence is a popular tool for collaboration and documentation, but when paired with CloudQuery, it becomes even more versatile. The new Confluence Source Plugin allows you to tap into Confluence data in previously impossible ways, providing deeper insights and greater control over your information.

Why Confluence + CloudQuery? #

With CloudQuery, you can aggregate all the data stored in your Confluence account into any data store. This gives you a comprehensive view of all your projects and documentation in one place. This is particularly useful for organizations that manage large volumes of content across different teams and departments. Instead of manually piecing together information, CloudQuery does the heavy lifting for you, pulling data from your Confluence account and uploading it to the destination of your choice.

How to Analyze Your Atlassian Documentation with SQL #

Let's examine a couple of examples to help you get started with the Confluence Source Plugin.

In all of these examples, we assume the data was synced from a Confluence source to a Postgres destination. However, you can sync your data to any data store.

Let's familiarize ourselves with the data available from your Confluence sync. You’ll begin by listing the most recent updates across all your spaces, which will give you a quick overview of what people are currently working on without needing to subscribe to each space’s updates via the UI.

select *
from confluence_pages
where ("version"->>'number')::int > 1
order by ("version"->>'createdAt')::timestamp desc
limit 10;

Now, let's dive deeper into your data. The following query will help you identify the most commented-on pages from the past week, which will help you see which topics generate the most discussion.

select
	p.title,
	p._links->>'webui' url,
	count(c.id) total_comments
from confluence_pages p
join confluence_page_comments c on p.id = c.page_id
where  (c.version->>'createdAt')::timestamp >= now() - interval '7 days'
group by p.id
order by total_comments desc;

After exploring page updates and comment activity, let's focus on maintaining clean and organized documentation.

Keeping your documentation up-to-date can be a challenge, especially in large organizations. However, with the data synced through CloudQuery, you can streamline the review process.

For instance, to prevent documentation from becoming fragmented, you could query for pages with similar titles and consider consolidating them into a single, more comprehensive document.

First, make sure the trigram extension is enabled in your database. This powerful tool, built into Postgres, allows for efficient similarity searches.

create extension pg_trgm;

Once that’s set up, you can run a query to find pages with similar titles.

with ranked_similar_titles as (
    select
        p1.space_id,
        p1.id as p1_id, p1.title as p1_title,
        p2.id as p2_id, p2.title as p2_title,
        similarity(p1.title, p2.title) as similarity_score,
        row_number() over (partition by p1.id order by similarity(p1.title, p2.title) desc) as rank
    from confluence_pages p1
    join confluence_pages p2 on p1.id <> p2.id AND p1.space_id = p2.space_id
)

select
	space_id,
    p1_id as page_id, p1_title as page_title,
    p2_id as most_similar_page_id, p2_title as most_similar_title,
    similarity_score
from ranked_similar_titles
where rank = 1
order by similarity_score desc;

How to Add Context To Your Confluence Data with StatusPage Data #

Having explored data from only the Confluence sync, let’s take things a step further by integrating it with our StatusPage Source Plugin. For example, we could query incident postmortem documents that haven’t been updated since the related incident was marked as 'resolved'.

select
	p.title,
	p._links->>'webui' url,
	p.version->>'createdAt' page_last_updated_at,
	i.resolved_at incident_resolved_at
from statuspage_page_incidents i
join confluence_pages p on p.title ilike i.name
where
	i.resolved_at is not null
	and (p.version->>'number')::int > 1
	and (p.version->>'createdAt')::timestamp < i.resolved_at;

Get Started #

Whether you’re managing development tasks in Jira, tracking incidents in StatusPage, building software with Bitbucket, or collaborating on projects in Trello, CloudQuery brings all this data together.

This cross-platform integration means that answers to complex questions spanning multiple Atlassian services are now a short SQL query away. Imagine being able to track how changes in your Bitbucket repositories impact project timelines in Jira, or how updates in Confluence documentation impact the project milestones tracked in Trello.

It's now possible with CloudQuery.

Join the CloudQuery Community to connect with other users and experts.

You can also try out CloudQuery locally with our quick start guide or explore CloudQuery Cloud for a more scalable solution.

Written by Alex Savanovich

Alex is a Senior Software Engineer at CloudQuery, specializing in cloud data infrastructure and application development, with a focus on building scalable solutions for multi-cloud environments.

On this page