Page History

...

1. In Excel, click From Web under the Data ribbon to open the Power Query editor.
   

...

1. 
   Image Added
   
   
2. In the From Web pane, toggle on on Advanced.
   Image Removed
   Image Added
   
   
3. Under the URL parts area, add lines for the parts as shown in the following table and illustration:

...

1. 
   
   

   Part type	Setting
   Host	http://ls-lin-stage.int.x-formation.com
   API path	/api/v3/license-server/15/usage-per-user/json
   Query parameters	lsid=15 sd=2023-01-01 ed=2023-01-31 grat=DAY agrt=USER_HOST offset=0 limit=100

...

1. 
   Image Added
   
   
2. Under “HTTP request header parameters,” add an X-Auth-token header header using a token generated from License Statistics, and click Add header.
   
   Image Modified
   
   
3. Click Click OK to continue.
   
   
4. Choose Anonymous as theweb content access type, and click Connect.
   
   Image Modified
   
   
5. Click Click Info Table  under the Content ribbon.
   
   
   
6. Optionally, you can name the query using the Properties area under Query Settings. In our example, we named the query “Usage Per User” to indicate the type of data being retrieved.
   
   Image Modified
   
   
7. Select data data list from the table.
   
   Image Modified
   

1. 
   
   
2. Click Click To Table under the Transform tab to convert the data list to a table.
   Image Removed
   Image Added
   
   
3. In the resulting “To Table” pane, choose None as the delimiter, and choose Show as errors to handle extra columns.
   
   Image Modified
   
   
4. Click Click OK.
   
5. Click the expand icon in the upper right of the Queries table to expand the table columns.
   
   Image Modified
   
   
6. Click Click Load more at the bottom of the list to load all the columns.
   Image Removed
   Image Added
   
   
7. Select the columns to include in the query; for example:
   
   • un
   • hn
   • lsn
   • fns
   • fv
   • massage
   • hu
   • mu
   • ldtc
   • ft
     
     
8. Optionally, you can rename columns by right-clicking on the column header and choosing choosing Rename from the right-click menu.
   Image Removed
   Image Added
   
   
9. For example you might rename columns to their corresponding label found in License Statistics:
   
   • us = Username
   • hn = Host
   • lsn = License Server
   • fns = Feature Name
   • fv = Feature Version
   • musage = Max Usage
   • hu = Hours Used
   • mu = Max Used
   • ldtc = Denials
   • ft = Total
     
     
10. Click Close & Load.
    

...

1. 
   Image Added
   
   
2. If you want to refresh data automatically, perform the following additional steps:
   
   
   1. Go to Queries & Connections under Excel’s Data tab, and select Properties.
      
Image Removed
3. 1. 
      Image Added
      
      
      
   2. In the External Data Properties dialog, click Query Properties.
      
Image Removed
4. 1. 
      Image Added
      
      
      
   2. Under the Usage tab, set the refresh options as desired.
      
Image Removed

We hope this article has been helpful for understanding all you need to know to import your License Statistics data into Excel using Power Query. If you have questions or comments, please don’t hesitate to contact us!

Meta description: 

Learn how to import data from License Statistics to Power Query.

Social media:

...

1. 1. 
      Image Added