Salesforce User report to fetch licence type of Every User

3:37 PM

I wanted to run a report to fetch all users in our salesforce org and then group them by their License type.

Problem with Standard Report: You can use the Standard report on the User object and use the "UserType" field. But the problem is that this field is not very specific. For instance, this field has a type name "Salesforce" for both the "Salesforce" license type and the "Salesforce Platform" license type.

To get a more in-depth report, i used the Apex Data Loader and exported data from a few objects to arrive at the final report.

Objects to consider:

Profile:
API doc http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_objects_profile.htm
Field: UserLicenseId

User:
API doc:http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_objects_user.htm
Field: ProfileId

UserLicense:
API doc: http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_objects_userlicense.htm
Field: LicenseDefinitionKey

Open Apex Data Loader. Select "Export" and select the object "User" and paste the following query.

Select Id,Name,Profile.Name,Role.Name,Profile.UserLicenseId from User 


Open Apex Data Loader. Select "Export" and select the checkbox "Show all Salesforce objects". Now, select the object "UserLicense" and paste the following query.

Select LicenseDefinitionKey,Name from UserLicense

Now, do a "Excel Vlookup" between the Profile.UserLiceseId column in the first csv file and the "LicenseDefinitionKey" in the second csv file. The Name column in the second csv file denotes the actual salesforce license Name.

If you are not familiar with Vlookup, then watch this video

 

2 comments

  1. You can also just include profile.userlicense.name into the first query and not use a vLookup!

    ReplyDelete
  2. For some reason i wasn't able to use profile.userlicense.name with the Data Loader... Yes, If that works, that's the best way

    ReplyDelete