...
- Log into License Statistics.
- Select the Usage Per User tab from the Reports page.
- Prepare sample data to be used in your report and make settings (for example, filters). Note that the data needs to be sorted alphabetically.
- Download your report in the CSV format using the Save As button.
- Export information about users by clicking the SQL Console tab in the Administration page.
- Paste and run the following query:
SELECT U.Name, CONCAT_WS(" ", FirstName, LastName) as Fullname FROM UserDetails UD INNER JOIN User U ON UD.UserId = U.Id ORDER BY U.Name
- Output the result to the CSV format.
- Paste these two reports in two separate Excel spreadsheets in one Excel file. To do this, you can simply open the exported Usage Per User report and copy LDAP information to another spreadsheet.
Move back to the first sheet, add a new header named "User Details" and paste a VLOOKUP function to every field in this column.
(Note that the syntax of the function may vary depending on whether you're using Excel or LibreOffice Calc.). For example, for Excel 2013 you may use the following syntax:=VLOOKUP($B2;Sheet2!$A$1:$B$7;2)
VLOOKUP is a function that performs a vertical lookup by searching for specific information located in a sorted table.
Column B from Sheet1 contains username and number 2 indicates the number of the current row.
Range of data indicates data that will be used by VLOOKUP, using cells from column B.
Number 2 indicates the column from Sheet2 for which the matching value must be returned (in this case user's first name and last names).
- If everything goes well you will see a final result similar to the following:
If you think that this situation can be a common occurrence, don't hesitate to inform us.
...