Coresuite Administration: Available Licenses stored in table?
Hello,
we have a central system for quarterly invocing the License costs to our Subsidiaries worldwide. Additionally we use this to have a quick overview also in case an employee leaves the company (what accesses/licenses are assigned and need to be removed).
Therefore we read for each Database the coresuite license to User assignments.
Additionally we track in this central tool the count of "total bought licenses" and compare it with the sum of "total number of assigned licenses" to see how much licenses we have "available".
This we can also see in "coresuite administration" form under "license management".
Bus as mentioned we want to see that in our central system as well.
Therefore my question is: Where is this information about "bought" (and "available") stored? Is it a Database table, udfs, XML files on client side?
Thanks for your help and best regards,
Gerrit
-
Hi Gerrit,
licenses are stored in
[dbo].[@SWA_LICENCES] :
Shows amounts in xml
[dbo].[@COR_COR_FS]
Is the connection between user and license
Perhabs you can you use this for sql:
/*Query Shows all Licenses together from SAP itself (B1Upf) and Addons*/
/*If necessary add more UDT's from other Addons or Change path to B1Upf*/
/*Author: LoH*/
/*Update: 20220801*/
/*ChangeLog:*/
/*20211203 / Added Named CKS License*/
/*20210921 / Added Outlook Entry control*/
/*20220321 / Added User-Email*/
/*20220801 / Added CKS-Category*//*Variable to store License-xml B1Upf*/
DECLARE @xmlFile XML/*Put License-xml in variable - Perhabs it is necessary in the future to change path*/
SET @xmlFile = (SELECT * FROM OPENROWSET(BULK '\\XXXX\lib\B1Upf.xml', SINGLE_BLOB) AS xmldata)--SELECT @xmlFile /*Debug only*/
;
/*Get User_Code from xml*/
WITH UserNodes AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS UserIndex
, One.Users.value('(UserName/text())[1]', 'nvarchar(max)') AS UserCode
, One.Users.value('(IsConnected/text())[1]', 'nvarchar(max)') AS IsConnected
, One.Users.query('.') AS CustomerNodeFROM
@xmlFile.nodes('Users/User') AS One(Users)
)--SELECT * FROM UserNodes /*Debug only*/
/*Assign Licenses to User_Code*/
, UserLicense AS
(
SELECT
Lic.*
, ROW_NUMBER() OVER(PARTITION BY Lic.UserIndex ORDER BY (SELECT NULL)) AS ModuleIndex
, Two.Modules.value('KeyType[1]','nvarchar(max)') AS LicType
, Two.Modules.value('KeyDesc[1]','nvarchar(max)') AS LicDescFROM
UserNodes AS Lic
CROSS APPLY
Lic.CustomerNode.nodes('User/Modules/Module') As Two(Modules)
)--SELECT * FROM UserLicense /*Debug only*/
/*Put all Licenses together*/
SELECT
[OUSR].[USERID]
, [OUSR].[USER_CODE] AS [Benutzercode]
, [OUSR].[U_Name] AS [Benutzername]
, [OUSR].[Locked]
, (
SELECT MAX([USR5].[Date])
from [USR5]
WHERE [USR5].[UserCode] = [OUSR].[USER_CODE]
group by [USR5].[UserCode] , [USR5].[Action]
having [USR5].[Action] = 'I'
) AS [Letzter Login]
, CASE WHEN CAST([OUSR].[U_XXX_Password] AS nvarchar(max)) <> '' THEN 'Y' ELSE '' END AS [MobLogUser]
, CASE WHEN [OUSR].[U_XXX_MobilePwd] <> '' THEN 'Y' ELSE '' END AS [MobPhotoUser]
, CASE
WHEN
[OUSR].[GROUPS] = 99
THEN
'Y'
ELSE ''
END AS [User wurde Gelöscht]
, [OUSR].[SUPERUSER]
--, [UserLicense].[LicType]
, [UserLicense].[LicDesc] AS [SAP-Lizenz-User]
, (SELECT
STUFF((
SELECT
', ' + [UserLicense].[LicDesc]
FROM
[UserLicense]WHERE
[UserLicense].[LicDesc] not like '%User%'
AND
[UserLicense].[UserCode] = OUSR.USER_CODE
FOR XML PATH ('')), 1, 2, '')
) AS [SAP Sonstige]
, (SELECT
STUFF((
SELECT
', ' +
CASE [U_LicType]
WHEN 101 THEN 'Prof'
WHEN 201 THEN 'Web'
WHEN 601 THEN 'ADC'
ELSE
'Unbekannt'
END
FROM
[dbo].[@CKS_LICENSEUSER]WHERE
[dbo].[@CKS_LICENSEUSER].[U_UserCode] = [OUSR].[USER_CODE]
ORDER BY
[U_LicType] ASC
FOR XML PATH ('')), 1, 2, '')
) AS [CKS]
, CASE WHEN (SELECT TOP 1 [U_LicType] FROM [dbo].[@CKS_LICENSEUSER] WHERE [dbo].[@CKS_LICENSEUSER].[U_UserCode] = [OUSR].[USER_CODE]) is not null and [T6].[Code] is null
THEN 'Anlegen'
ELSE [T6].[Code]
END AS [CKSOutl]
--, ISNULL(T8.[U_CategoryName],'') AS [CKSVorgang]
--, CASE
-- WHEN T7.U_Permission = 1 THEN 'Volle Berechtigung'
-- WHEN T7.U_Permission = 2 THEN 'Lese Berechtigung'
-- WHEN T7.U_Permission = 3 THEN 'Keine Berechtigung'
-- ELSE 'nicht bekannt'
-- END AS [VorgBerechtigung]
, (SELECT
STUFF((
SELECT
' | ' + T98.U_CategoryName + ': ' +
CASE
WHEN T99.U_Permission = 1 THEN 'VB'
WHEN T99.U_Permission = 2 THEN 'LB'
WHEN T99.U_Permission = 3 THEN 'Keine'
ELSE 'N.A.'
END
FROM
[dbo].[@CKS_USRCATPRMSSNS] T99
INNER JOIN [dbo].[@CKS_CATEGORY] T98
ON T98.DocEntry = T99.U_CategoryId
WHERE
T99.U_USERID = [OUSR].[USERID]
FOR XML PATH ('')), 1, 3, '')) AS [CKSVorgang]
, [T1].[U_IsLUsed] AS [COR_CUSTOMIZE]
, [T2].[U_IsLUsed] AS [COR_EnterpriseSearch]
, [T3].[U_IsLUsed] AS [COR_LD_ACCOUNTING]
, [T4].[U_IsLUsed] AS [Swiss Layout Designer]
, [T5].[U_IsLUsed] AS [COR_SERVICE]
, [OUSR].[E_Mail] AS [User-Email]FROM
[OUSR]
LEFT JOIN [dbo].[@COR_COR_LICENCE] [T1]
ON [T1].[U_UserId] = [OUSR].[USERID] AND [T1].[U_ModuleGuid] = 'COR_CUSTOMIZE'LEFT JOIN [dbo].[@COR_COR_LICENCE] [T2]
ON [T2].[U_UserId] = [OUSR].[USERID] AND [T2].[U_ModuleGuid] = 'COR_EnterpriseSearch'LEFT JOIN [dbo].[@COR_COR_LICENCE] [T3]
ON [T3].[U_UserId] = [OUSR].[USERID] AND [T3].[U_ModuleGuid] = 'COR_LD_ACCOUNTING'LEFT JOIN [dbo].[@COR_COR_LICENCE] [T4]
ON [T4].[U_UserId] = [OUSR].[USERID] AND [T4].[U_ModuleGuid] = 'Swiss Layout Designer'LEFT JOIN [dbo].[@COR_COR_LICENCE] [T5]
ON [T5].[U_UserId] = [OUSR].[USERID] AND [T5].[U_ModuleGuid] = 'COR_SERVICE'LEFT JOIN [dbo].[@CKS_MAILSETTINGS] [T6]
ON [T6].[Name] = [OUSR].[USER_CODE]
LEFT JOIN [dbo].[@CKS_USRCATPRMSSNS] [T7]
ON [T7].[U_USERID] = [OUSR].[USERID]
LEFT JOIN [dbo].[@CKS_CATEGORY] [T8]
ON T8.DocEntry = [T7].[U_CategoryId]
/*Be careful !!! Old-License-Desc = Professional User -> New-License-Desc = SAP Business One Professional User*/
LEFT JOIN [UserLicense]
ON [UserLicense].[UserCode] = [OUSR].[USER_CODE] AND UserLicense.LicDesc like '%User%'
--WHERE [OUSR].[Locked] = 'N'ORDER BY [OUSR].[USER_CODE]
Change it on your own needs
Regards
Lothar
0 -
Great thanks Lothar for this fast reply and suggestion. We will try it out. BR, Gerrit
0
Please sign in to leave a comment.
Comments
2 comments