How to use historical data?

📘

Required module

Required modules: Data Studio and Live Data. Check your modules at Admin > Licenses. To obtain these modules, contact your IXON account manager or IXON distributor.

If you have configured historical data in the IXON Cloud, your IXrouter or IXagent will send PLC data to the IXON Cloud. The data will be sent in a frequency selected by the user when setting up historical data. You can use the APIv2 to request and use this data in your software application. This article will explain how to use the APIv2 to retrieve and structure historical data.

Historical data basics

With historical data, you basically are storing the value of a certain variable of your machine with a timestamp. This is done in a special database called a time series database. Information on how you log a variable and how often you log a variable is stored in a data tag. A data tag also keeps information on how long data of that variable should be stored. You can log variable data in 3 different manners:

• Interval: periodically on a set timeframe
• Change: every time the value of a variable changes
• Trigger: every time when a certain machine condition is met

Historical data can be aggregated before and after it is stored. Data aggregation is downsampling of data. You can aggregate data by combining multiple values according to a certain rule (e.g. Mean, Median, Sum, etc.). When you do this before storing the data this is called pre-aggregation, if you use this data is stored aggregated in the IXON database, which means that the raw data is not stored. If you apply post-aggregation, data is aggregated after storage and the raw data remains available. The IXON Cloud stores logging data with the pre-aggregator set to raw. You can choose the post-aggregator yourself.

Requesting data

You can request historical data from the API with the endpoint DataList. To do a successful request, you first need to obtain the following data with different requests:

You can request data for multiple tags at once. For every tag you need to give the tag ID (Int32) or slug (string). Both can be requested using the AgentDeviceDataTagList endpoint. You have to set the pre-aggregator to raw.

Because the data is stored in a time series database you always have to include a timequery to the request with both a start and an end time. The applicable timezone has to be added if you want to use a different timezone then the default (UTC). Timezones have to be added in the format of the tz database. The example below contains the exact required format of the request including the POST body and the correct time notation.

curl --request POST \
     --url '<<url>>:443/api/data' \
     --header 'Api-Version: 2' \
     --header "Api-Application: $application_id" \
     --header "Api-Company: $company_id" \
     --header 'Content-Type: application/json' \
     --header "Authorization: Bearer $bearer_token" \
     --data '[
       {
           "source": {
               "publicId": "$source_id"
           },
           "tags": [
               {
                   "id": "$tag_id",
                   "slug": "$slug"
                   "preAggr": "raw",
                   "queries": [
                       {
                           "ref": "a",
                           "limit": 0,
                           "offset": 0
                       }
                   ]
               }
           ],
           "start": "2021-02-02T00:00:00+00:00",
           "end": "2021-02-02T23:59:59+00:00",
           "timeZone": "Europe/Amsterdam"
       }
     ]'

Data return

Your data will be returned in a JSON format. You will receive all requested values for a certain timestamp. You can see this format clearly in the example below.

{
    "type": "Data",
    "data": [
        {
            "start": "2021-02-02T00:00:00+00:00",
            "end": "2021-02-02T23:59:59+00:00",
            "timeZone": "CET",
            "source": {
                "publicId": "$source_id",
                "reference": {
                    "name": "source"
                }
            },
            "points": [
                {
                    "time": "2021-02-02T00:00:00.000+00:00",
                    "values": {
                        "a": 1,
                    },
                    {
                    "time": "2021-02-02T00:00:30.000+00:00",
                    "values": {
                        "a": 2,
                    },
                    {...}
                }
            ]
        }
    ],
    "moreAfter": null,
    "status": "success"
}

📘

Export to CSV-file

If you prefer your data to be returned in a CSV-file you can send the request to the DataExport endpoint.

There are a few common ways to downsample data, to make it more suitable for data analysis. You can do this using query fields and post aggregators. Query fields are parameters that determine how data should be returned. Post aggregation is one of the available query fields. The table below describes the possible parameters and their description:

A single value can be helpful to determine all kinds of things about your machine. You can very easily get a single value by setting a very short timeframe or adding the query field limit and setting it to 1. To get more value out of the single value a post-aggregator can be helpful. You can use the combination of the factor and operator queries if you want to do a calculation on the single value.

If you want to add data to a data table, it is best to go with the raw data, without post-aggregator and export it to a CSV-file using the DataExport endpoint instead of the DataList endpoint. You can use an offset query field equal to the given limit to get a subsequent set of datapoints compared to your first request. (e.g. limit=500, offset=0 for first 500 values and limit=500, offset=500 for second 500). You can use the combination of the factor and operator queries if you want to do a calculation on the single value.

To make data ready for depiction in a graph, query fields can be helpful as well. Post aggregation using the limit or step query field can make the amount of data for your graph both easier to handle and easier to understand. That’s why both of these queries are popular for this purpose. It is not possible to use both limit and step queries.

The moreAfter attribute in this API response is always null. With other endpoints this attribute has a function to make sure you received everything. When requesting all logged data, use offset as explained in the above example instead of using moreAfter.

Example

The Python script below is an example of how you can request historical data.