Create a report in Microsoft Excel out of the raw license usage data
If you have Tricentis License Server version 1.2.0 installed, you have access to Tricentis License Usage Tracking. The license usage database collects your licensing data and updates the information every minute. You can query this data and create reports, for example in Microsoft Excel.
The Tricentis License Server Manual describes how to query the database and get a result file.
This article describes how to import the result data into Microsoft Excel and create a report out of it.
You want to create a report out of the raw data in the result file. More specifically, you want to have an overview of the max license utilization per day. Why? Because this shows whether you currently have enough licenses or whether you might need more.
Please note that this article uses Excel 2016. For more information on how to work with PivotTables, see the Microsoft Excel documentation on PivotTables.
To import your data from the result file into Excel, follow the steps below:
- Open Microsoft Excel and navigate to Data->Get Data->From File->From JSON.
- In the subsequent dialog, select the result file and click Import.
- Excel loads the data and opens the Power Query Editor. In the Editor, click on Into Table.
- Click on the arrow icon in the Value column and select Expand to new rows.
- Click the arrow icon in the Value column again.
- In the subsequent dialog, disable Use original column name as prefix:
- Click OK.
- Click on the arrow icon in the _source column.
- In the subsequent dialog, ensure that Use original column name as prefix is disabled, and click OK.
- In the Power Query Editor menu, click Close & Load. This transfers the raw data from the JSON file into your Excel sheet:
- The timeStamp column contains a full timestamp. In this example, we are only interested in the date, so create a new column with the name Date and use the formula =LEFT(B2;FIND("T";B2)-1).
The B2 refers to the cell that contains the timestamp. If you change the order of the columns, make sure that you refer to the right cell.
GROUP YOUR DATA
The next step is to organize your data, so you can prepare your report. To do so, follow the steps below:
- Navigate to a new sheet in the Excel document and select Insert->PivotTable.
- In the Create PivotTable dialog, select the data range:
- Click on the arrow icon.
- Navigate to the sheet that contains the data and highlight everything with your mouse, including headers.
- Press ENTER and click OK.
- The PivotTable Fields bar on the right shows all parameters you can use to build your report. Drag and drop the following parameters onto the following panels:
- Date, packageName, and hostName into the Rows panel
- used into the Values panel
- Format each parameter by clicking on the arrow icon next to it:
- Date, packageName, and hostName:
Navigate to Field Settings->Layout & Print. Enable Show item labels in tabular form and Repeat item labels.
Navigate to Value Field Settings->Summarize Values by and choose Max.
- Date, packageName, and hostName:
Your PivotTable Fields bar should now look like this:
And your PivotTable like this:
CREATE THE FINAL REPORT
Since the maximum use per package per day is the focus of this report, create another PivotTable out of the existing one. To do so, follow the steps below:
- Select Insert->PivotTable and select the existing PivotTable as the range.
- Drag and drop the following parameters onto the following panels:
- packageName into the Columns panel
- Date into the Rows panel
- Max of used into the Values panel
This creates the final table:
It shows the max used per time of a certain package for each day. Compare this to the number of packages purchased to check whether you reached your limits.
CREATE A CHART
- To create a chart out of the result, click into a cell in the PivotTable.
- In the Insert menu, click PivotChart and select the desired chart type from the Insert Chart dialog.