SAP B1 - User License Assignment Reporting

[Former Member]

Can anyone assist me in creating a report that lists all users and the license types they are assigned to? I know how to look them up one by one, but I would like a summary report of all users.

I know how to report on the OUSR table, however the LIcense Type field is not tied to the actual license assignment and so I am not confident it is accurate. In many cases, no license type is specified at all.

I am also aware of how to report on authorizations, but struggling with license allocation itself. Thanks

[Former Member · Accepted solution]

Hi,

Welcome you post on the forum.

You may check this first:

Thanks,

Gordon

[Former Member · Accepted solution]

Thanks Gordon, but that method is for Authorizations and one at a time. I already am able to report on authorizations out of the USR3 table linked to the OUSR table.

I need to report on the actual License type itself (PROFESSIONAL, CRM, LOGISTICS, B1UP, ETC). Is there a way to report on that? I'm not all that familiar with forms in B1, but it looks to me like the license summary information is presented in a form and it doesn't give me a clue as to which table in the database the data is being stored in.

[Former Member · Accepted solution]

The info is saved under licensing server folder as B1upf.xml.

[Former Member]

I wanted to provide a script I wrote up a while ago that gives all users with a professional license. This helps us with our user audits and shows us when we can revoke a license due to last login. It imports the xml file into a table in SQL and then queries off of it to get user information.

IF object_id('tempdb..#TempLicenseUsers') IS NOT NULL
BEGIN
   DROP TABLE #TempLicenseUsers
END

USE tempdb
CREATE TABLE #TempLicenseUsers (XmlCol xml);
GO

INSERT INTO #TempLicenseUsers(XmlCol)
SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files (x86)\SAP\SAP Business One ServerTools\License\B1Upf.xml',
   SINGLE_BLOB) AS x;

SELECT
      usr.value('(UserName/text())[1]', 'varchar(50)') UserName
      ,license.value('(text())[1]', 'varchar(50)') License
    ,u.SUPERUSER SuperUser
    ,u.Locked
    ,u.updateDate
    ,u.lastLogin
FROM  
      #TempLicenseUsers CROSS APPLY
      XmlCol.nodes('/Users/User') AS Users(usr) CROSS APPLY
      usr.nodes('Modules/Module/KeyType') AS Modules(license)   
    JOIN dbo.OUSR u ON u.USER_CODE = usr.value('(UserName/text())[1]', 'varchar(50)')
WHERE license.value('(text())[1]', 'varchar(50)') = 'PROFESSIONAL'
ORDERBY u.lastLogin ASC

[Former Member]

No user interface within SAP B1 to provide report for tracking user license assignment. Unfortunately, the license server xml file is the only source.

[Former Member]

So no easy way to report on it from the user interface, bummer. I don't want to provide license server configuration file access to our Security Team. I was hoping for a nice user interface where they could query on demand.

Thanks so much for the insight.