Marcro usage by querying SQL tables in Confluence Server?

[Joe Bloggs]

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? 

[Nicolai Sibler · Accepted solution]

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

[Joe Bloggs]

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

[Nic Brough -Adaptavist-]

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"

[Joe Bloggs]

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.

[Nic Brough -Adaptavist-]

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.

[Joe Bloggs]

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

[Mike Rathwell]

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.

[Joe Bloggs]

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]

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

[Joe Bloggs]

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! :)