Need SQL Query which can pull list of all pages from confluence which are using external gadgets

[Sriram Pallapothu]

Need SQL Query which can pull list of all pages from confluence which are using external gadgets:

we got a requirement to update external gadgets poining URL from old to new, we did by using following suggested procedure 

https://confluence.atlassian.com/confkb/how-to-change-urls-of-external-gadgets-296913316.html 

Now to validate the result we need SQL Query result based on that we access random pages in which gadgets are using and will confirm the customers.

[Dominic Lagger]

Hi @Sriram Pallapothu 

Just search all bodycontent for your URL

select * from bodycontent where body like '%google%'

There you find the contentID and with that you can open the page.

Regards, Dominic

[Sriram Pallapothu]

Hi Dominic.

 Thanks for your help but this did not worked for my requirement.

actually I updated the URL by using update query: 

update BODYCONTENT
set body = replace(body, 'http://test.com:8080', 'http://newtest.com/jira')
where body like '%http://test.com:8080%';

Now I need to view which confluence pages are using these external gadgets and need to check they are working fine as expected. 

For example: this is gadget I updated, now i need to view in which confluence pages using "admin-gadget.xml" 

select count(*) from BODYCONTENT where BODY like "%http://jira.td.com/jira/rest/gadgets/1.0/g/com.atlassian.jira.gadgets:admin-gadget/gadgets/admin-gadget.xml%";

for validating all the changes please suggest the query to view which confluence pages using that particular gadgets.

Regards,

Sriram

[Dominic Lagger]

Hi @Sriram Pallapothu 

Sorry, but I don't know what part is missing...

This select statement will get you ALL pages, where this gadget occurs.

select * from bodycontent where body like '%GADGET HERE%'

In the table of results, you will find a CONTENTID. This contentid you have to use to open the page.

https://CONFLUENCE-URL/pages/viewpage.action?pageId=CONTENTID

Regards, Dominic