How To: Automate Data Reports Using the GQL API

Overview

This post serves as a brief introduction to how the ExoSense GraphQL API and Reports features can be used in concert to enhance and automate interactions with your data outside of the ExoSense UI.

Note: When requesting a Signal Data Report, whether via the UI or the ExoSense GraphQL API, there is one key constraint which must be met: Any given Report of Signal data must fall within the “Signal Days” limit set for your Application Tier.

For example, with a limit of 600 Signal Days, a report targeting 10 Signals could encompass up to a 60 day period, while a report of 30 Signals would be limited up to 20 days, and so on.


Orientation

When leveraging the ExoSense GraphQL API to request a Signal Data Report, use of system-defined identifiers for the target Asset(s) and Signal(s) will be required. To that end, we’ll start with some examples for deriving those identifiers for later use.

Note: It is recommended to always make explicit use of the Pagination input, where available.

Identify Target Asset(s)

While there are various ways to control the scope of a search for a given Asset (or set of them), in this example we’ll explore one way to find a single Asset’s ID using its name.

Using the text field of the AssetFilters input, the assets query will return a pre-filtered result set including only those Assets which contain a match for the specified string in either their name or description.

Example Query

query assets($pagination: Pagination, $filters: AssetFilters) {
  assets(pagination: $pagination, filters: $filters) {
    id
    name
  }
}

Example Variables

{
  "pagination": {
    "offset": 0,
    "limit": 100
  },
  "filters": {
    "text": "Example Asset"
  }
}

Example Response

{
  "data": {
    "assets": [
      {
        "name": "Example Asset",
        "id": "394a7c05-868f-41df-8b3a-7c9fe3de831c"
      }
    ]
  }
}

Identify Target Signal(s)

With a target Asset’s ID, the asset query can be used to derive the IDs, and other potentially useful context, of its Signals.

Example Query

query asset($id: ID!) {
  asset(id: $id) {
    id
    name
    signals {
      id
      name
      units {
        abbr
      }
    }
  }
}

Example Variables

{
  "id": "394a7c05-868f-41df-8b3a-7c9fe3de831c"
}

Example Response

{
  "data": {
    "asset": {
      "signals": [
        {
          "units": {
            "abbr": "A"
          },
          "name": "Current Draw",
          "id": "20fd773c-89fc-4a2b-88b4-de25ffad9a5a"
        },
        {
          "units": {
            "abbr": "psi"
          },
          "name": "Discharge Pressure",
          "id": "13bfab2b-4144-44a2-ba7c-0ac6bfecb67d"
        },
        {
          "units": {
            "abbr": "GPM"
          },
          "name": "Flow Rate",
          "id": "b4ab84f7-4078-4fcd-ad2d-8a841e4f7f43"
        },
        {
          "units": {
            "abbr": "°F"
          },
          "name": "Motor Bearing Temperature",
          "id": "15ad58b8-034a-40f4-909b-da050191fdb2"
        },
        {
          "units": {
            "abbr": "°F"
          },
          "name": "Pump Temperature",
          "id": "d15f3e93-8a04-4c8d-a6a1-1283b913e7b5"
        },
        {
          "units": {
            "abbr": "rpm"
          },
          "name": "Speed",
          "id": "86375d4a-b588-46f9-bc5d-a8440e291780"
        },
        {
          "units": {
            "abbr": "psi"
          },
          "name": "Suction Pressure",
          "id": "bd7aad17-391b-448d-ab3d-08fba1847c03"
        },
        {
          "units": {
            "abbr": "in/s"
          },
          "name": "Vibration RMS Peak",
          "id": "5c59c93c-2877-482d-8fa2-1bf8ded76acb"
        }
      ],
      "name": "Example Asset",
      "id": "394a7c05-868f-41df-8b3a-7c9fe3de831c"
    }
  }
}

On Demand Report

When an on demand report is requested, a job will be queued to generate it. The status of any given job can be queried and, once completed, a URL to download the report can be generated and used.

Request On Demand Report

The createReport mutation takes a report_info input (type CreateReport), the structure of which you can be referenced in the brief descriptions and example variables object below.

Field Descriptions:

  • start_timestamp and end_timestamp are both Unix epoch time integers (seconds).
    • In this case, they bound the request to a specific single day (2022-03-01).
    • Note: Recall the Signal Day limit mentioned at the beginning of this post.
  • timezone has two subfields of its own:
    • offset is an integer offset to be applied to the timestamps of the result data after it is pulled (e.g. -6 for GMT-06:00)
    • format is a standard date/time format string to be applied to the result data.
  • assetNames is an array of strings, providing the name(s) of any Asset(s) from which the target Signals are taken.
  • signals is an array of ExportSignal inputs, having their own subfields:
    • id is the Signal for which the column data will be generated.
    • name configures the column header for the Signal.
      • In this case, several pieces from the Signal context collected in the preceding example are combined:
        • {asset_name} - {signal_name} ({signal_unit_abbreviation})
        • Note: The inclusion of this extra context is optional.

Example Query

mutation createReport($report_info: CreateReport!) {
  createReport(report_info: $report_info) 
}

Example Variables

{
  "report_info": {
    "start_timestamp": 1646092800,
    "end_timestamp": 1646179200,
    "timezone": {
      "offset": 0,
      "format": "%Y-%m-%dT%H:%M:%S.%f%z"
    },
    "assetNames": [
      "Example Asset"
    ],
    "signals": [
      {
        "id": "20fd773c-89fc-4a2b-88b4-de25ffad9a5a",
        "name": "Example Asset - Current Draw (A)"
      },
      {
        "id": "13bfab2b-4144-44a2-ba7c-0ac6bfecb67d",
        "name": "Example Asset - Discharge Pressure (psi)"
      },
      {
        "id": "b4ab84f7-4078-4fcd-ad2d-8a841e4f7f43",
        "name": "Example Asset - Flow Rate (GPM)"
      }
    ]
  }
}

Example Response

{
  "data": {
    "createReport": "09124d04-a0a0-11ec-bfce-02598f76caed"
  }
}

Note: The returned id should be stored, to be referenced later in checking on its status and ultimately retrieving the completed report.

Check Status of On Demand Report

As needed, the reportStatus query can be used to understand the status of a given report. The UUID returned in response to a createReport mutation (see preceding example) serves as the job_id field value in this query.

Example Query

query reportStatus($job_id: ID!) {
  reportStatus(job_id: $job_id) {
    state
    length
  }
}

Example Variables

{
  "job_id": "09124d04-a0a0-11ec-bfce-02598f76caed"
}

Example Response

{
  "data": {
    "reportStatus": {
      "state": "completed",
      "length": 72486
    }
  }
}

Retrieve Download URL for On Demand Report

Today, completed reports are downloaded using a pre-signed URL. A pre-signed URL for a given report can be retrieved using its job_id, with the expires_in field optionally allowing the default 600 second expiration window to be overridden if desired.

Example Query

query report($job_id: ID!, $expires_in: Int) {
  report(job_id: $job_id, expires_in: $expires_in)
}

Example Variables

{
  "job_id": "09124d04-a0a0-11ec-bfce-02598f76caed",
  "expires_in": 3600
}

Example Response

{
  "data": {
    "report": "https://s3.us-west-1.amazonaws.com/murano-content-service-prod/qq5jo11owdcw0000/export_1646936974.csv?response-content-type=application%2Foctet-stream&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAXP6RGEJ4JJMD2R5F%2F20220310%2Fus-west-1%2Fs3%2Faws4_request&X-Amz-Date=20220310T183048Z&X-Amz-Expires=600&X-Amz-SignedHeaders=host&X-Amz-Signature=83c90ed0df66c07dc6edefe5e0009ec0b492c2276738aefb13555603addafbba"
  }
}

Download Report

For simplicity, this example makes use of the cURL command line tool to download the report, using the pre-signed URL, and store it in a local file.

curl "https://s3.us-west-1.amazonaws.com/murano-content-service-prod/qq5jo11owdcw0000/export_1646936974.csv?response-content-type=application%2Foctet-stream&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAXP6RGEJ4JJMD2R5F%2F20220310%2Fus-west-1%2Fs3%2Faws4_request&X-Amz-Date=20220310T183048Z&X-Amz-Expires=600&X-Amz-SignedHeaders=host&X-Amz-Signature=83c90ed0df66c07dc6edefe5e0009ec0b492c2276738aefb13555603addafbba" > 2022-03-01-report-Example-Asset.csv

Recurring Report

When a new recurring report is requested, the system will begin to recurringly queue a job to generate it on the specified interval. The jobs associated with a recurring report will grow over time.

Request Recurring Report

The createReport2 mutation takes a report input (type: ReportCreate) and a signals input (type: [ID!]), the structure of which you can be referenced in the brief descriptions and example variables object below.

Field Descriptions:

  • ReportCreate
    • name is a string
    • frequency is a string, one of daily, weekly, or monthly
    • timezone is a string representation (e.g. UTC or America/Chicago)
    • format is a standard date/time format string to be applied to the result data
    • dayOfWeek is an integer, 0-7 (Sunday-Saturday)
    • hourOfDay is an integer, 0-23 (used for both daily and weekly reports)
  • [ID!] is an array of Signal IDs

Based on its configuration, the following example will generate a new report for the target Signals at the end of every week.

Example Query

mutation createReport2($report: ReportCreate!, $signals: [ID]!) {
  createReport2(report: $report, signals: $signals) {
    id
    name
    enabled
    notifications
  }
}

Example Variables

{
  "report": {
    "name": "Example Asset - Weekly",
    "frequency": "weekly",
    "timezone": "America/Chicago",
    "format": "%Y-%m-%dT%H:%M:%S.%f%z",
    "dayOfWeek": 0,
    "hourOfDay": 0
  },
  "signals": [
    "57a9236b-6bd4-4ebd-b68b-c9b6047cb7bf",
    "b9c89b5f-16b8-48c9-8abd-05876ba78ae7",
    "3e8fd9aa-60b0-4292-8f62-011061c59e76"
  ]
}

Example Response

{
  "data": {
    "createReport2": {
      "notifications": true,
      "enabled": true,
      "name": "Example Asset - Weekly",
      "id": "89cb90b9-74bd-466b-a9c7-122f21118dca"
    }
  }
}

Note: The returned id should be stored, to be referenced later in checking for jobs and their statuses, to ultimately retrieve completed reports. Remember, in this case it is not the ID of a job, but rather of a recurring report which will contain a collection of jobs over time.

Check Status of Recurring Report Job(s)

Example Query

query report2($id: ID!) {
  report2(id: $id) {
    name
    enabled
    jobs {
      id
      createdAt
      updatedAt
      state
    }
  }
}

Example Variables

{
  "id": "89cb90b9-74bd-466b-a9c7-122f21118dca"
}

Example Response

{
  "data": {
    "report2": {
      "enabled": true,
      "name": "Example Asset - Weekly",
      "jobs": [
        {
          "updatedAt": "Sun Mar 12 2023 05:00:00 GMT+0000 (Coordinated Universal Time)",
          "state": "processed",
          "id": "cd0349f0-7cac-11ed-ab0f-02fa8a8d5df5",
          "createdAt": "Sun Mar 12 2023 05:00:00 GMT+0000 (Coordinated Universal Time)"
        }
      ]
    }
  }
}

Note: The returned array of jobs will contain a set of the most recently queued/processed jobs. The id of any job whose state is processed can be used in ultimately downloading the resulting report.

Retrieve Download URL for Recurring Report

Today, completed reports are downloaded using a pre-signed URL. A pre-signed URL for a given report can be retrieved using its job_id, with the expires_in field optionally allowing the default 600 second expiration window to be overridden if desired.

Example Query

query getJobDownloadUrl($id: ID!) {
  getJobDownloadUrl(id: $id) {
    id
    url
  }
}

Example Variables

{
  "id": "cd0349f0-7cac-11ed-ab0f-02fa8a8d5df5"
}

Example Response

{
  "data": {
    "getJobDownloadUrl": {
      "url": "https://s3.us-west-1.amazonaws.com/murano-content-service-prod/qq5jo11owdcw0000/recurring_report_Example_Asset_Weekly_weekly_1678608000000.csv?response-content-type=application%2Foctet-stream&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAXP6RGEJ4JJMD2R5F%2F20221205%2Fus-west-1%2Fs3%2Faws4_request&X-Amz-Date=20221205T183704Z&X-Amz-Expires=300&X-Amz-SignedHeaders=host&X-Amz-Signature=9f2979d3ba757e9f5a99866b2a1ebd4a59ed2a103509906d2705bec285f3dcd2",
      "id": "recurring_report_Example_Asset_Weekly_weekly_1678608000000.csv"
    }
  }
}

Download Report

For simplicity, this example makes use of the cURL command line tool to download the report, using the pre-signed URL, and store it in a local file.

curl "https://s3.us-west-1.amazonaws.com/murano-content-service-prod/qq5jo11owdcw0000/recurring_report_Example_Asset_Weekly_weekly_1678608000000.csv?response-content-type=application%2Foctet-stream&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAXP6RGEJ4JJMD2R5F%2F20221205%2Fus-west-1%2Fs3%2Faws4_request&X-Amz-Date=20221205T183704Z&X-Amz-Expires=300&X-Amz-SignedHeaders=host&X-Amz-Signature=9f2979d3ba757e9f5a99866b2a1ebd4a59ed2a103509906d2705bec285f3dcd2" > 2023-03-12-Example-Asset-Weekly-Report.csv
1 Like