This site requires JavaScript to be enabled
IE BUMPER

Logo

Dear Tricentis qTest users,

If you would like to submit a Tricentis qTest ticket,

please use this support request form.

Tosca Version
IncidentLookup using list
Language
How can we help?

Solution Suggestions

Please enter your question to get suggestions.
  Help
IE BUMPER
Categories
Boolean Operators
OR or vertical bar symbol (|)
Finds a match if either the terms exist in a document (a union using sets).
AND
Finds a match if both terms exist in a document (an intersection of sets).
NOT, minus (-), or exclamation point (!)
Excludes documents that contain the term after NOT (a difference of sets).
Wildcards
Asterisk (*)
The asterisk symbol performs a multi character wildcard search.
Percent sign (%)
The percent sign performs a single character wildcard search.
Phrases
Quotation marks (")
Use quotation marks to search for an exact phrase with multiple terms.
For examples and more information look at our Knowledge Base: Advanced Search On The Support Portal

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.


Situation

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

Solution

IMPORT DATA

To import your data from the result file into Excel, follow the steps below:

  1. Open Microsoft Excel and navigate to Data->Get Data->From File->From JSON.
     
  2. In the subsequent dialog, select the result file and click Import.
     
  3. Excel loads the data and opens the Power Query Editor. In the Editor, click on Into Table.
     
  4. Click on the arrow icon in the Value column and select Expand to new rows.


     
  5. Click the arrow icon in the Value column again.
     
  6. In the subsequent dialog, disable Use original column name as prefix:


     
  7. Click OK.
     
  8. Click on the arrow icon in the _source column.
     
  9. In the subsequent dialog, ensure that Use original column name as prefix is disabled, and click OK.
     
  10. In the Power Query Editor menu, click Close & Load. This transfers the raw data from the JSON file into your Excel sheet:


     
  11. 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:

  1. Navigate to a new sheet in the Excel document and select Insert->PivotTable.
     
  2. 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.


        
  3. Press ENTER and click OK.
     
  4. 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
       
  5. 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.
       
    • Used:
      Navigate to Value Field Settings->Summarize Values by and choose Max.

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:

  1. Select Insert->PivotTable and select the existing PivotTable as the range.
     
  2. 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
 

  1. To create a chart out of the result, click into a cell in the PivotTable.
     
  2. In the Insert menu, click PivotChart and select the desired chart type from the Insert Chart dialog.

IE BUMPER

Tosca is the perfect solution

Optimize - Manage - Automate

Download Trial