Where in the Confluence db can I find the current number of licenses?

[Judah]

I am using Microsoft SQL Server Management Studio to query my Confluence db.

I want to pull out the number of licenses, but cannot find it. Any help would be appreciated. 

I am running this query, which searches the db for any keyword. 

``` --CREATE PROCEDURE FindMyData_String
DECLARE @DataToFind NVARCHAR(4000)
DECLARE @ExactMatch BIT = 0

SET @DataToFind = '%license%'


DECLARE @Eleni TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

INSERT INTO @Eleni(TableName,SchemaName, ColumnName, DataType)
SELECT C.TABLE_NAME,C.TABLE_SCHEMA, C.COLUMN_NAME, C.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS AS C
INNER Join INFORMATION_SCHEMA.TABLES AS T
ON C.TABLE_NAME = T.TABLE_NAME
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE TABLE_TYPE = 'BASE TABLE'
And DATA_TYPE In ('ntext','text','nvarchar','nchar','varchar','char')


DECLARE @i INT
DECLARE @ymmuse INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1
THEN 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
= ''' + @DataToFind + '''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
ELSE 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
Like ''%' + @DataToFind + '%''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
END,
@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1

SELECT @i = 1, @ymmuse = MAX(RowId)
FROM @Eleni

WHILE @i <= @ymmuse
BEGIN
SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
FROM @Eleni
WHERE RowId = @i


PRINT @SQL
EXECUTE sp_executesql @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

IF @DataExists =1
UPDATE @Eleni SET DataFound = 1 WHERE RowId = @i

SET @i = @i + 1
END

SELECT SchemaName,TableName, ColumnName
FROM @Eleni
WHERE DataFound = 1```

[Edwin Kyalangalilwa · Accepted solution]

Hi @Judah,

Try the query on this KB article.

How to get a list of active users counting towards the Confluence license

[Judah]

@Edwin Kyalangalilwa Looks great. Thank you! 

[Igor M.]

Hello,

Can you clarify what license you are trying to pull?

• License key for Confluence
• License keys for plug-ins?
• A number of users counting towards your Confluence user license?

[Judah]

Thanks for your response @Igor M_

Once a month I report certain statistics regarding Confluence into a separate monitoring application (tableau). Currently, I am able find these stats in the admin section of the GUI, which I report manually. 

My problem has arisen from the fact that I am trying to automate the dispersal of these stats into tableau, via SSIS. 

I am using Microsoft SQL Server Management Studio to query the database which houses my Confluence data but I am unable to find some of my stats. I assume that if I can see these stats in the GUI, they must be housed in the db somewhere.

The stats I need:

-Total allocated Memory

-Memory Used

-A number of users counting towards your Confluence user license

Can offer any guidance on how to query the db in order to find the above statistics.

It appears that @Edwin Kyalangalilwa has provided the answer to my license query.

I am still in need of the 'memory used' and 'total allocated memory' stats. 

Any guidance would be a god send. 

[Igor M.]

Hello,

Unfortunately, such settings are not stored in DB, memory allocation parameter is defined in setenv.sh file and is loaded upon Confluence boot (or directly on the process if you running as service on Windows), based on -xmx parameter value a set amount of memory will be allocated to Confluence Java process. So the value is not pulled from DB but from configuration file and memory.

Your best option for monitoring system resources is to use JMX as described in Live Monitoring Using the JMX Interface

[Judah]

@Igor M_

Makes sense. Thank you!