[wc_login_form_rk]
[wc_reg_form_rk]
Close

How to import data from License Statistics to Excel using Power Query

Many of our License Statistics customers are using Excel’s Power Query tool to transform and prepare data. Power Query not only lets you import data from a variety of sources, but gives you the ability to transform and use the data to suit your needs. Using Power Query, you can set up a query once, and then reuse the query by “refreshing” the data as often as needed.

This article gives you step-by-step instructions specifically for importing License Statistics data directly into Excel using Power Query.

  1. In Excel, click From Web under the Data ribbon to open the Power Query editor.
    Import License Statistics data into Excel using Power Query 1
  2. In the From Web pane, toggle on Advanced
    Import License Statistics data into Excel using Power Query
  3. Under the URL parts area, add lines for the parts as shown in the following table and illustration:
    Part typeSetting

    Host

    http://ls-lin-www.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

    Import License Statistics data into Excel using Power Query 2

  4. Under “HTTP request header parameters,” add an X-Auth-token header using a token generated from License Statistics, and click Add header.Import License Statistics data into Excel using Power Query 3
  5. Click OK to continue.
  6. Choose Anonymous as the web content access type, and click Connect.Import License Statistics data into Excel using Power Query 4
  7. Click Info Table under the Content ribbon.
  8. 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.Import License Statistics data into Excel using Power Query 5
  9. Select data list from the table.Import License Statistics data into Excel using Power Query 6
  10. Click To Table under the Transform tab to convert the data list to a table.Import License Statistics data into Excel using Power Query 7
  11. In the resulting “To Table” pane, choose None as the delimiter, and choose Show as errors to handle extra columns.Import License Statistics data into Excel using Power Query 8
  12. Click OK.
  13. Click the expand icon in the upper right of the Queries table to expand the table columns.Import License Statistics data into Excel using Power Query 9
  14. Click Load more at the bottom of the list to load all the columns.
    Import License Statistics data into Excel using Power Query 10
  15. Select the columns to include in the query; for example:
    • un
    • hn
    • lsn
    • fns
    • fv
    • massage
    • hu
    • mu
    • ldtc
    • ft
      Import License Statistics data into Excel using Power Query 11
  1. Optionally, you can rename columns by right-clicking on the column header and choosing Rename from the right-click menu.Import License Statistics data into Excel using Power Query 12
    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
  1. Click Close & Load.Import License Statistics data into Excel using Power Query 14
  2. If you want to refresh data automatically, perform the following additional steps:
    • Go to Queries & Connections under Excel’s Data tab, and select Properties.
      Import License Statistics data into Excel using Power Query 15
    • In the External Data Properties dialog, click Query Properties.
      Import License Statistics data into Excel using Power Query 16
    • Under the Usage tab, set the refresh options as desired.
      Import License Statistics data into Excel using Power Query 17

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!

Get to know License Statistics