Prerequisite: log in to io-base from the IndaSheet module.
In the banner, open the IndaSheet tab and click on the Data Recovery button.
A banner on the right appears. It will allow you to enter the desired parameters, in order to query the io-base database and recovering data.
Possible types of queries
You have the possibility to query the io-base with three different types of queries:
RANGE: retrieve data for a metric between two dates
LATEST: retrieve the last value entered in the database for one or more metrics
INSTANT: retrieve the value entered on a date
For the RANGE query, it is possible to choose an aggregation method for the data.
The panel on the right will assist you in constructing the formula, and entering it into the chosen cell. The formula will then work like any other formula in an Excel file. It will pull up, from its cell, all the raw data that is contained in the database, according to the defined parameters.
Construction of a formula
Result cell
The first field, Result Cell, is used to define the cell in which the formula will be entered. You must enter the cell reference ("A1" for example) in which the formula will be written (and therefore from which the results will appear). You can also use the grid icon, which will write the active cell in the field.
Parameters of the formula
This section will allow you to define the parameters of the query. First, choose the type of query to run in the Formula Type field.
RANGE formula
The RANGE formula is used to retrieve the values of a metric between two dates.
Source: name of the database to be queried. This field is a drop-down list, which displays all the existing databases. In general, the production data is found in the prod database.
Data item(s): name of the metric for which data is to be retrieved. If the name of the metric is written in a cell, you can select it by using the grid icon. Otherwise, you can use the magnifying glass icon to display the search window.
Note: A message requesting permission to display the window may appear. For more details on the search window, please refer to the relevant article.
Note : For the RANGE function, only one metric can be entered at a time
Start time: date and time for the start of the data recovery range. You can enter a value directly, or use the calendar icon to select the desired date and time.
Note: If you enter a value, it must be in the following format: yyyy/MM/dd, hh:mm:ss
End time: date and time for the end of the data recovery range. You can enter a value directly, or use the calendar icon to select the desired date and time.
Note: If you enter a value, it must be in the following format: yyyy/MM/dd, hh:mm:ss
Note : The 'Until now' button is provided for you to set the current date as the end date.
Aggregations: you can choose to retrieve raw or aggregated data. To set up the aggregation, you have to activate the button I want to choose aggregations.
Once activated, this button allows you to select the desired aggregation from a list.
You can choose between:
- MIN: minimum value
- MAX: maximum value
- SUM: sum
- AVG : average value
- COUNT : number of values
After the type of aggregation, you have to specify the Frequency. This frequency defines the interval in which the aggregation will be applied. For example, if you enter MIN for a frequency of 1d, the query will return the minimum value for each day. You can enter a number of days, hours, minutes and seconds
(0d0h0m0s)
Note: The frequency is optional. If you do not enter a value, then the result will be the aggregation over the whole selected range. There will therefore only be one result value (for example the average value of the whole selected range).
LATEST formula
The LATEST formula will allow the retrieval of the last value registered in the database for one or several metrics
Source: name of the database to be queried. This field is a drop-down list, which displays all the existing databases. In general, the production data is found in the prod database.
Data item(s): name of the metric(s) for which data is to be retrieved. If the name of the metric(s) is/are in a cell, you can select it using the grid icon. Otherwise, you can use the magnifying glass icon to display the search window.
Note: A message requesting permission to display the window may appear. For more details on the search window, please refer to the relevant article.
Note: For the LATEST function, it is possible to enter several metrics at the same time, separated by ";".
Note : If multiple metrics are selected, they must all be part of the same Source (same database). Otherwise, separate queries will have to be made.
INSTANT formula
The INSTANT formula will allow the retrieval of the value entered at a given date.
Source: name of the database to be queried. This field is a drop-down list, which displays all the existing databases. In general, the production data is found in the prod database.
Data item(s) : name of the metric(s) for which data is to be retrieved. If the name of the metric(s) is/are in a cell, you can select it using the grid icon. Otherwise, you can use the magnifying glass icon to display the search window.
Note: A message requesting permission to display the window may appear. For more details on the search window, please refer to the relevant article.
Note : For the INSTANT function, it is possible to enter several metrics at the same time, separated by ";".
Note : If multiple metrics are selected, they must all be part of the same Source (same database). Otherwise, separate queries will have to be made.
Start time: date and time for which you wish to retrieve the value. You can enter a value directly, or use the calendar icon to select the desired date and time.
Note: If you enter a value, it must be in the following format: yyyy/MM/dd, hh:mm:ss
If no value exists at the exact timestamp selected, the query will retrieve the last value stored in the database just before the selected timestamp.
Display settings
The following fields are used to set up the display of the results.
Display: This button allows you to choose whether you want the results to be displayed, from the result cell, in rows or columns.
Timestamp: Allows you to indicate whether you want the first column of results to contain the timestamps of the points. If you select Without, only the values will appear. If you select With, the first column will contain the timestamps, and the second the values.
If you choose to display the timestamp, you must specify the desired timestamp
standard (ISO or local):
Show the metric name : If you choose Yes for this field, the first line will contain the name of the metric. Before the lines containing the results.
Once all the fields have been filled in, you can click on Apply. The formula is constructed, and it is written to the result cell that is filled in.
The formula is created, and displayed in the selected result cell.
