Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,297,452
Community Members
 
Community Events
165
Community Groups

Marcro usage by querying SQL tables in Confluence Server?

On a large instance I want to find out how many Draw.IO drawings we have. I don't have admin, but can query SQL. I'm aware of the 6 tables (AO_<HASH>_DRAWIO_<TABLE>), but these don't seem to help to get overall count usage. 

Any ideas how we could query this? I guess I could query all pages but on 1+ million instance that will never work ... are there any other tables that would keep the running tally of macro usages? 

3 answers

1 accepted

0 votes
Answer accepted

Hi @Joe Bloggs ,

there's a select to find out but it really takes too much time to query, even when used directly on the db. I have a 1 million pages instance as well and will only do it on a staging instance's db.

But I have some good news as well:

Every drawio diagram is an attachment and is tagged with the drawio label by default. So in case users don't delete the labels, you'll get the amount of diagrams by filtering the ordinary Confluence Search (Type = attachments, Label = drawio) without typing anything.

Side Note: The Macro Usage solution does not the same. It provides the (number of) pages containing the macro rather than the number of macros/diagrams. So if there are pages with more than one diagram, Confluence Search does the better job.

Hope that helps!

 

Kind regards,

Nicolai

Thanks this is absolutely spot on and got me to what I was fundamentally after.

Like Nicolai Sibler likes this
0 votes

No-one should have access to your database, it's the worst way to look at Confluence data you can possibly imagine.  Only your system admins should have that, and even then, it's only for identifying and fixing some very specific problems.

@Mike Rathwell has already pointed you at the right answer - an admin can use the Macro Usage page to find what you're looking for.

If you want to do it by reading the database, you're going to need to run (highly non-performant, and hence not to be run during working hours) queries that go through a vast amount of data, returning pages that match, then you'll need to somehow remove all the hits on historical versions of pages, then parse all the content to find the actual usage.

You might find it easier to look through the attachment table for matching content, but the right answer really is "stop looking at the database and use the macro usage page"

Thanks Nic, I really respect Adaptavist, we've done some great work with you over the years. As I said we're large install with well clued up base of power users. We have the SQL macros (in Confluence) specifically open for that purpose. We've learnt how to do queries and how not to take down our instance with it. I would still ask if we can keep it more "Stack Overflow" here and less "Discussion Forum". Cheers.

Good to know that you're thinking about it when you do resort to SQL

The macro usage is not held in the database, it's a report run off the index.  So your SQL would have to be complex - reading for every current version of a page and parsing the content for the use of the macro.

Another option might be to look for attachments instead, although I'm not 100% sure that draw.io does it like other apps do - storing the diagrams as attachments.  The search would be more simple because you just read the content for objects of attachment type and you'll be able to identify them easily because the names will contain the file type.

Thanks Nic, this was definitely helpful. What Nicolai suggested above worked, folks leave defaults so 50K+ drawio diagrams were easily found. 

0 votes
Mike Rathwell Community Leader Apr 08, 2022

Hi @Joe Bloggs ,

That one could take some work but I have always found that going to admin General Configuration > Macro Usage has given me enough information. You should see all the pages that have the draw.io macro used with that function.

Hope this helps.

Hi Mike, thanks for the reply. I was specific in my question as admin access is something I don't have and in a very large enteprise it's not really possible to keep asking a central team behind layers of service management for favour, nor could they turn it aroudn quickly.

That's why the question is specifcilaly around gettin this data from SQL, which is available as self-service.

Mike Rathwell Community Leader Apr 08, 2022

Hmm... I will have to hope someone picks this up that has a system with this in to do that query. Note, however, that your admins would need only do that one task (and it does take mere minutes) and then provide you the search results link from the admin console. 

Good Luck

Thanks Mike, I think the query is simple - is the tally macro count kept in the DB or not. If NOT there isn't any other "practical way" (yes we have queries that can parse page content, but I don't want to do that at scale and I'll have to bother admins and wait every time which just slows down our cycles) if YES then which one? Hopefully there's a dev at Atlassian who would know! :)

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

48 views 0 0
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you