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×pan=-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
andtimespan
parameters are query time limits. This means we will search for the last 100 points (before current time). You can also use thestart
orend
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 themy.selector
metrics that have astag
the valueTest
. - The
format
key is used to retrieve the data intsv
format. Two values are possibletsv
to not retrieve the series tags andfulltsv
to retrieve the series tags - The
token
key to set the Metrics authentication token.
You need to replace the READ_TOKEN key by your own Metrics token, and the selector by one matching the data you want to retrieve. Be careful with the time limits set in your queries, as you may load too much data for an Excel sheet.
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×pan=["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!
Cette documentation vous a-t-elle été utile ?
N’hésitez pas à nous proposer des suggestions d’amélioration afin de faire évoluer cette documentation.
Images, contenu, structure… N’hésitez pas à nous dire pourquoi afin de la faire évoluer ensemble !
Vos demandes d’assistance ne seront pas traitées par ce formulaire. Pour cela, utilisez le formulaire "Créer un ticket" .
Merci beaucoup pour votre aide ! Vos retours seront étudiés au plus vite par nos équipes..