SQL Query - Get amount of user-installed plugins + conf version

[Tim Krapf]

Hi,

1. I would like to count the user-installed plugins via an SQL-Query. Can someone help me?
   If I execute the following query, I get 77 in return. But correct would be something near 48.
   
   

   select count(plugindataid) from PLUGINDATA

   Can I filter for user-installed plugins in the database? 

   
   
   
   
   
2. I would like to get the current installed confluence version via an SQL query.
   But with the following query I only get the buildnumber. Is there also a table with the conf version?
   

   SELECT BUILDNUMBER, INSTALLDATE
   
   FROM CONFVERSION
   
   ORDER BY INSTALLDATE DESC
   
   Limit 1;

Thank you in advance!

[Daniel Ebers]

Hi Tim,

although not tested for Confluence on my side the better approach would be to query REST API of UPM (UPM is the caring mother in easy words for all Apps installed in on-prem Atlassian products).
If I were to gather data from Apps I would ask UPM (and not database, like Nic also said):
https://ecosystem.atlassian.net/wiki/spaces/UPM/pages/6094960/UPM+REST+API

You can surely do something with the support of Grafana - but I doubt for the Apps it will be helpful in particular unless I am missing a point. Let's say you have a process on how to bring in new Apps - you have to assume that everybody is aligned with it.
There should not appear an App at once nobody was aware of :)

Apart from that I want to emphasize the monthly reporting is a good thing and Grafana can contribute to a big amount.

Cheers,
Daniel

[Nic Brough -Adaptavist-]

No, for both questions.  This is one of the reasons I tell people to not bother looking at Atlassian databases - they are a data store, not suitable for reporting, and quite often don't contain the data you are guessing they would.

1. The database is useless here because the type of installation is worked out by code when you go to the manage apps screen.  The code in the apps tells Confluence what the type is.

It's also not actually of much use to you - some "system" apps are user-installable, and some "user instealled" apps are installed by the system

2. The version of Confluence is extracted from a properties file in the install and held in memory so it can be displayed when needed. 

You can use https://developer.atlassian.com/server/confluence/confluence-build-information/ to look up the version number from the build (assuming you are not using a customised Confluence built from source)

I would be curious as to what you're trying to learn from these numbers though - there may be a better way to do what you're using the numbers to inform.

[Tim Krapf]

Thanks for your reply!

I want to get some data from confluence to display them in a Grafana dashboard for monthly reporting.

I also get some data via the Prometheus exporter for confluence but the metrics here show the wrong amount of installed plugins and there is no metric to get the conf version. If you have other tips to reach the goal, please let me know.  :)

[Nic Brough -Adaptavist-]

Ahh, monitoring, always a good thing!

I suspect the Prometheus exporter is pretty much doing what your SQL is doing as well, it's not interrogating the code, it's looking at the database.

I don't think you're going to be able to build what you need without doing some coding. 

I'd think about using script-runner to create a REST end point which can calculate and expose the actual data you need.  (I'm a Prometheus beginner, not yet looked to see if I could persuade it to hit a custom REST endpoint for data).  But you may not have SR, in which case I'd think of writing a small plugin to do much the same.  You could then expose whatever you can get from classes like SystemInfo* and Plugin*