Use Metrics data in Excel

Last updated 18th May, 2020

Objective

Excel is a common tool used to quickly plot and compute some KPI over a data set. In this guide, you will learn how to configure an Excel query to load Metrics data.

Requirements

• a valid OVHcloud Metrics account.
• a READ token that can be found on Metrics's manager
• This documentation is for an Excel data sheet. This can be used as a start to use Metrics on similar tools as LibreOffice or PowerBI for instance.

Instructions

Create an Excel query file

First, you will need to set-up a Web query on Excel. Let's create an .iqy file. .igy files are the one used by Excel to perform WebQueries. Let's write the following content in a warp10.iqy file:

WEB
1
https://warp10.gra1.metrics.ovh.net/api/v0/fetch?now=now&timespan=-100&selector=my.selector{tag=Test}&format=fulltsv&token=READ_TOKEN

Selection=1
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

To resume what we are doing in the warp10.iqy file: we use the Warp10 native fetch entrypoint as it can generate tsv files. Those files can natively be loaded by Excel.

How does the Warp10 fetch endpoint works:

• The now and timespan parameters are query time limits. This means we will search for the last 100 points (before current time). You can also use the start or end parameter to set ISO string dates as time limits.
• The selector parameter is used to select the data to retrieve. In this query example we retrieve the my.selector metrics that have as tag the value Test.
• The format key is used to retrieve the data in tsv format. Two values are possible tsv to not retrieve the series tags and fulltsv to retrieve the series tags
• The token key to set the Metrics authentication token.

Retrieve Metrics data on Excel

To execute the previously created query, click on data menu then on get external data and finally on Run web query and load your warp10.iqy file. Excel will then load your data from the Metrics Data Platform.

You will now get access to them directly in your Excel sheet:

The first column A represents the series name ("my.selector"), B represents the series tags, D each datapoints timestamps and H each measurements values.

Use custom cell values to retrieve data

If you want to refresh the Web query based on some Excel sheet data, this can be done as described below.

First let's update the warp10.iqy file, we will replace the set timespan by an excel_timespan Excel query variable.

WEB
1
https://warp10.gra1.metrics.ovh.net/api/v0/fetch?now=now&timespan=["excel_timespan"]&selector=my.selector{tag=Test}&format=fulltsv&token=READ_TOKEN

Selection=1
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

Once this is done, create a timespan cell on your Excel sheet:

Load your new Warp10 warp10.iqy query file as done previously. Excel will later ask you which cell to use for the excel_timespan query variable. Fill it with the relative timespan cell data:

Ticking Use this value/reference for future refreshes will enable the use of this variable for each refresh.

Data will be loaded with the custom timespan set. You can update it to change the result.

It's now time to enjoy your Excel analysis and own customisation with Metrics Data Platform!

Go further

• Documentation: Guides
• Vizualize your data: https://grafana.metrics.ovh.net/login
• Reach the Metrics team: gitter room
• Community hub: https://community.ovh.com
• Create an account: Try it free!

Ces guides pourraient également vous intéresser...