Find spaces in confluence that use specific plugin (Confluence 5.1)

[karthikys]

Hi,

Can anyone help with the below query to retrieve the list of spaces/pages in Confluence that uses community forum macro.

we tried using following keywords(community, forum, forum:) but nothing helped us.

Select CONTENT.CONTENTID, Title, SpaceID, CREATOR, LASTMODDATE
from CONTENT, BODYCONTENT
WHERE
PREVVER IS NULL
AND (CONTENTTYPE = 'PAGE') 
AND CONTENT.CONTENTID = BODYCONTENT.CONTENTID 
AND CONTENT_STATUS = 'current'
AND (BODY like '%{macroName%')

Thank you.

[FelipeA · Accepted solution]

Hi Karthik

This is what I did:

Select CONTENT.CONTENTID, Title, SpaceID, CREATOR, LASTMODDATE
from CONTENT, BODYCONTENT
WHERE
PREVVER IS NULL
AND (CONTENTTYPE = 'PAGE') 
AND CONTENT.CONTENTID = BODYCONTENT.CONTENTID 
AND CONTENT_STATUS = 'current'
AND ((BODY like '%ac:name=\”communities\”%') OR (body LIKE '%ac:name=\"user-info\"%'));

Basically you'll have to list all Community Bubbles macros. The above query will return just pages with {communities} OR {user-info}, so you'll have to extend this OR to include all macros listed on that page.

Best regards,

Felipe Alencastro

[Shaun Dyble]

Hi guys, Im using Python to perform the above query and Im finding its taking a very long time to return any results. Is this to be expected?

[Honeywell JIRA Admin]

Yes, this is expected. Mostly depends on the no. of spaces it needs to go through.

[karthikys]

Yes, this is expected. Mostly depends on the no. of spaces it needs to go through.

[FelipeA]

Hi Karthik,

They're not stored as {macroName} on the database. This is what they look like on storage format:

<ac:structured-macro ac:name"macroName">

Please let me know if the below query works for you:

Select CONTENT.CONTENTID, Title, SpaceID, CREATOR, LASTMODDATE
from CONTENT, BODYCONTENT
WHERE
PREVVER IS NULL
AND (CONTENTTYPE = 'PAGE') 
AND CONTENT.CONTENTID = BODYCONTENT.CONTENTID 
AND CONTENT_STATUS = 'current'
AND (BODY like '%ac:name=\”macroName\”%');

Kind Regards,

Felipe Alencastro

[karthikys]

Hi Felipe,

Thanks for taking time to respond.

We have already tried this way but we couldn't make the query work for Community bubbles macro, maybe because of 25 sub macro that comes along with this macro.

Could you please let us know the storage format of this macro or any suggestions on how to query this !!

Regards,

Karthik

[Honeywell JIRA Admin]

Hi Felipe,

Thanks for taking time to respond.

We have already tried this way, but we couldn't make the query work for Community Bubbles Macro maybe because of 25 sub-macros included with this macro.

Could you please let us know the storage format of Community bubbles Macro or any suggestions on how to query !!

Regards,