Please assist with instructions on how to connect to the cloud db using an ODBC Connection.
We trying to to replicate the below connection and data extract for local reporting:
ODBC CONNECT32 TO Confluence (XUserId is [Username], XPassword is [Password])
Content:
sql select
concat(‘https://jira.isometrix.net/confluence/pages/viewpage.action?pageId=’, c.contentid),
concat(‘https://jira.isometrix.net/confluence/display/’, s.spacekey,‘/’,replace(c.title,’ ‘,’+') ) as displayurl,
lower(lu.username) as likedby,
lower(lu.username) = lower(cu.username) as likedbyself,
c.contentid,
c.contenttype,
c.title,
s.spacekey,
lower(cu.username) as username,
c.creationdate,
to_char(c.creationdate, ‘MONTH YYYY’) as createmonthandyear,
to_char(c.creationdate, ‘MONTH’) as concreatemonth,
to_char(c.creationdate, ‘YYYY’) as concreateyear
FROM content c
left JOIN spaces s ON c.spaceid = s.spaceid
LEFT JOIN bodycontent bc ON c.contentid = bc.contentid
left join likes l on c.contentid=l.contentid
left join user_mapping lu on l.username = lu.user_key
left join user_mapping cu on c.creator = cu.user_key
WHERE c.prevver IS NULL
and lu.username is not null
AND c.contenttype IN (‘PAGE’, ‘BLOGPOST’);
TeamContent:
IntervalMatch (creationdate,username)
Welcome to the Atlassian developer community @Mdu.
The short answer is you can’t.
The longer answer is, even if you could, Confluence Cloud’s database schema is no longer compatible with Confluence Server/Data Center. This query wouldn’t work. Most notably, all the user-centric information is now inside of Atlassian Identity. However, we know people still want to “SQL their data” so there was a public announcement about a data lake for Jira Software Cloud. Maybe Confluence Cloud would follow? In the meantime, maybe a Marketplace reporting app or the Confluence Cloud REST APIs would do instead?
@ibuchanan , has anything changed with the above statements for Confluence? we know that Confluence Data like is coming out. However that is for Enterprise customers only. Any other options aside from Data lake?
Welcome to the Atlassian developer community @PeterI,
No change. We still do not allow customers to “SQL their data” directly. As such, the only Atlassian option for SQL access is the Enterprise tier feature known as Atlassian Analytics:
Thanks @ibuchanan , looks like this is the only viable option. I don’t think any of the marketplace vendors have this capability either, which makes it hard to execute any sql custom queries against the Cloud DB. Hope that Atlassian is thinking about at least providing some basic capabilities to lower tiers of customers
Can you elaborate? What is it that you are looking for that is not possible with the REST APIs available at all levels of the product? What is it about SQL that you think is important?