はじめに
先日弊社の大阪オフィス移転に伴い「DevelopersIO OSAKA Day One -re:union-」というイベントが開催されました。 今回はこちらのイベントで展示したAmazon Monitronのデータ可視化について実装までの流れを記事にしたいと思います。
なぜ展示したの？
Monitronには標準機能として取得したデータを可視化する機能があります。 この標準機能ではセンサーごとにグラフを切り替えてデータの可視化が可能です。 しかし、グラフをカスタマイズして使いたい！という要件では標準の機能だけでは実現するのは難しいです。 そこでAWSのマネージドサービスを使い、より自由度の高い可視化をやってみたい！ということで今回の展示を行いました。
全体像
まずは全体のアーキテクチャです。
大きく分けると、データ取得する部分とデータ可視化する部分に分かれます。 それでは早速データ取得部分から見ていきましょう。
Kinesis Data Streamsの作成
先に注意点です。 現在Monitronは限られたリージョンでサポートされています。 そのため、Data StreamsはMonitronのリージョンに合わせる必要があります。
今回はus-east-1で作成します。
モードをプロビジョンドに設定して、他はデフォルトのまま作成します。
Monitronからのデータは1時間に1回決まったデータが送信されるのでキャパシティを動的にする必要はないので、よりコストの低いプロビジョンに設定しています。
S3バケットの作成
Firehoseの配信先になるS3バケットを先に作成しておきます。 S3バケットはデフォルの設定で作成します。
Data Firehoseの作成
Firehoseを作成します。
ソース：先ほど作成したKinesis Data Streams
送信先：先ほど作成したS3バケット
Firehoseはコンソール画面から作成すると、データをやり取りするためのIAMロールをよしなに作成してくれるのでコンソールから作成するのがおすすめです。 これでデータを保管する準備ができたのでMonitronの設定を行います。
Monitronの設定
Monitronはセットアップ済みであることを前提としています。 セットアップが済んでいない方はこちらの記事を参考にセットアップして下さい。
対象のプロジェクトを選択し、Start live data exportをクリックします。
先ほど作成したData Streamsを選択します。
これでデータ取得側の構成は終了です。 この時点でデータがS3に保存されることを確認したい場合は、Monitronのモバイルアプリから手動でデータ取得することも可能です。 参照：手動でデータ取得する
データが送信されるとS3に年月日のプレフィックスが自動で生成され、データが保存されます。
Athenaの設定
クエリ結果の保存場所
Athenaのクエリ結果を保存するためのS3バケットを作成します。
バケット名をgrafana-athena-query-results-から始まるバケットを作成します。（Grafanaの設定を容易にするため）
他の設定は全てデフォルトです。
S3バケットが作成できたらAthenaのクエリエディタ>設定から管理をクリックします。
作成したS3バケットを選択して保存します。
テーブルの作成
Athenaのクエリエディタからテーブルを作成します。
データソースはS3バケットを選択します。
テーブル名とテーブルを保存するデータベースの名前を入力します。
入力データセットの場所は、「S3バケットの作成」で作成したバケットを選択します。
ファイル形式はJSONを選びます。
次にMonitronのデータスキーマを定義します。
列を一括追加します。
以下を入力してまとめて列を追加します。
timestamp,eventid,version,accountid,projectname,projectid,eventtype,eventpayload列が追加できたら列のタイプを選択します。
eventpayloadはstructで、それ以外はstringです。
eventpayloadはネストになっているので、以下の構造体タイプを入力します。
struct<siteName:string,assetName:string,positionName:string,assetPositionURL:string,sensor:struct<physicalId:string,rssi:int>,gateway:struct<physicalId:string>,measurementTrigger:string,sequenceNo:int,features:struct<acceleration:struct<band0To6000Hz:struct<xAxis:struct<rms:double>,yAxis:struct<rms:double>,zAxis:struct<rms:double>>,band10To1000Hz:struct<totalVibration:struct<absMax:double,absMin:double,crestFactor:double,rms:double>,xAxis:struct<rms:double>,yAxis:struct<rms:double>,zAxis:struct<rms:double>>>,velocity:struct<band10To1000Hz:struct<totalVibration:struct<absMax:double,absMin:double,crestFactor:double,rms:double>,xAxis:struct<rms:double>,yAxis:struct<rms:double>,zAxis:struct<rms:double>>>,temperature:double>,models:struct<temperatureML:struct<previousPersistentClassificationOutput:string,persistentClassificationOutput:string,pointwiseClassificationOutput:string>,vibrationISO:struct<isoClass:string,mutedThreshold:string,previousPersistentClassificationOutput:string,persistentClassificationOutput:string,pointwiseClassificationOutput:string>,vibrationML:struct<previousPersistentClassificationOutput:string,persistentClassificationOutput:string,pointwiseClassificationOutput:string>>>これでテーブルを作成します。
クエリエディタの画面を更新すると、プルダウンに作成したデータベースが追加されているので選択します。
テーブル名の右にある3点から「テーブルをプレビュー」をクリックします。
生成されたクエリを実行してデータが出力されることを確認します。 問題なくテーブルが作成されていればクエリの結果部分にMonitronのデータが表示されるはずです。
タグの設定
Athenaのワークグループのタグの設定を行います。 これはGrafanaのデータソースとしてAthena使用する際に必要な設定です。
タグから「タグを管理」をクリックします。
タグは
キー：GrafanaDataSource
値：true
を追加します。
Grafanaで可視化する
ワークスペースの作成
先ほど作成したテーブルを使ってGrafanaで可視化します。 まずはGrafanaのワークスペースを作成します。
私の使用する環境はAWS Organizationsが使えない環境なので、認証方法はSAML認証を選択しました。
プラグイン管理を有効にします。
データソースにはAthenaを選択します。
その他はデフォルトの設定で作成します。 ワークスペースを作成してしばらくするとステータスがアクティブになります。
ポリシーの追加
ワークスペース作成時にIMAロールが自動的に作成されますが、内容が不足しているのでポリシーを追加します。
まずは作成したワークスペースのIAMロール名を控えます。
IAMロール一覧より、先ほど控えたIMAロールを検索します。
IAMロールにポリシーをアタッチします。
AmazonS3ReadOnlyAccessを追加します。
ワークスペースの認証設定
Grafanaの画面に戻り、作成したワークスペースの認証設定を行います。
認証には色々なSAML IdPが使用できます。 私は以下のブログを参考にAuth0を使った認証を行いました。
他のSAML IdP認証についも弊社のブログで解説されているものがあるので参考にしてみてください。
認証が完了したらワークスペースのURLからGrafanaにログインします。
Add your first data sourceを選択します。
Pluginの一覧からAmazon Athenaを選択します。
画面右上のInstallをクリックしてAthenaをインストールします。
これでデータソースとしてAthenaが使えるようになりました。
ダッシュボードの作成
画面左上のメニューからApps>AWS Data Sourcesをクリックします。
Athenaを選択します。
データソースとなるデータベースを選択します。 まずはサービスとリージョンを選択します。 Service：Athena Region：us-east-1
最後は先ほど「Athenaでテーブルを作成」で作成したデータベースを選択してAdd 1 data sourceをクリックします。
data sourceが作成されたので、Go to settingsを選択します。
続いて詳細設定を行います。
基本的にはデフォルトのままで、最後のOutput Locationには「Athenaでテーブルを作成」で作成したクエリ結果の保存場所のS3パスを入力します。
Save & testをクリックし、保存ができたらExploreを選択します。
適当なSQLクエリでデータが表示されることを確認しましょう。
SELECT * FROM "<database_name>"."<table_name>" limit 10;
画面右上のAdd to dashboardをクリックします。
New dashboardでOpen dashboardをクリックします。
新しいダッシュボードに先ほどのクエリを行った結果がパネルとして表示されていると思います。
ここにパネルを追加してダッシュボードを完成させていきます。 今回すべてを紹介するのは難しいので一部パネルのクエリを紹介します。
各プロジェクトごとのセンサー数とゲートウェイ数を表示してみます。 クエリはこんな感じです。
SELECT
projectname,
COUNT(DISTINCT eventpayload.sensor.physicalid) AS sensor_count,
COUNT(DISTINCT eventpayload.gateway.physicalid) AS gateway_count
FROM "AwsDataCatalog"."demo_db"."demo_table"
WHERE projectname = '<your_project_name>'
GROUP BY projectname
ORDER BY projectname<your_project_name>にはMonitronのプロジェクト名を入力します。
<your_project_name>で指定したプロジェクト毎にセンサーとゲートウェイ数を集計しています。
クエリ実行後、グラフの種類を選択します。
今回は数字だけ見れたらいいのでStatを選びました。
文字が左によっているのでStat stylesのText alignmentをCenterにします。
これでなんとなくいい感じになりました。
変更が完了したら右上のSaveをクリックしてApplyでダッシュボードに適用します。
ダッシュボードにパネルが追加されました。 同じ要領で他のパネルも追加していきます。
ダッシュボードのインポート
すべてのパネルを説明することはできないのでDashboardをJSONにしたものを添付します。 以下のJSONをインポートしてダッシュボードとして使うこともできます。
dashboard.json
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": {
"type": "grafana",
"uid": "-- Grafana --"
},
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"target": {
"limit": 100,
"matchAny": false,
"tags": [],
"type": "dashboard"
},
"type": "dashboard"
}
]
},
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
"id": 1,
"links": [],
"liveNow": false,
"panels": [
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 8,
"w": 12,
"x": 0,
"y": 0
},
"id": 30,
"options": {
"colorMode": "none",
"graphMode": "area",
"justifyMode": "center",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"textMode": "auto"
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "<your_db_name>",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"rawSQL": "WITH cte AS (\n SELECT \n projectname,\n eventpayload.gateway.physicalid AS gateway,\n eventpayload.sensor.physicalid AS sensor\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>'\n GROUP BY \n projectname,\n eventpayload.gateway.physicalid,\n eventpayload.sensor.physicalid\n)\nSELECT \n projectname,\n COUNT(DISTINCT sensor) AS sensor_count,\n COUNT(DISTINCT gateway) AS gateway_count\nFROM cte\nGROUP BY \n projectname\nORDER BY\n projectname;",
"refId": "A",
"table": "<your_table_name>"
}
],
"title": "OsakaProject Device Info",
"type": "stat"
},
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 8,
"w": 12,
"x": 12,
"y": 0
},
"id": 11,
"options": {
"colorMode": "none",
"graphMode": "area",
"justifyMode": "center",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"textMode": "auto"
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"rawSQL": "WITH cte AS (\n SELECT \n projectname,\n eventpayload.gateway.physicalid AS gateway,\n eventpayload.sensor.physicalid AS sensor\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>'\n GROUP BY \n projectname,\n eventpayload.gateway.physicalid,\n eventpayload.sensor.physicalid\n)\nSELECT \n projectname,\n COUNT(DISTINCT sensor) AS sensor_count,\n COUNT(DISTINCT gateway) AS gateway_count\nFROM cte\nGROUP BY \n projectname\nORDER BY\n projectname;",
"refId": "A"
}
],
"title": "TokyoProject Device Info",
"type": "stat"
},
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"fixedColor": "red",
"mode": "palette-classic"
},
"custom": {
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
}
},
"mappings": []
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "INITIALIZING count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "green",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "WARNING count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "yellow",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "HEALTHY count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "blue",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 0,
"y": 8
},
"id": 31,
"options": {
"displayLabels": [
"value"
],
"legend": {
"displayMode": "list",
"placement": "bottom",
"showLegend": true,
"values": []
},
"pieType": "pie",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'INITIALIZING'\n) \nGROUP BY classification;",
"refId": "INITIALIZING"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'ALARM'\n) \nGROUP BY classification;",
"refId": "ALARM"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'HEALTHY'\n) \nGROUP BY classification;",
"refId": "HEALTHY"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'WARNING'\n) \nGROUP BY classification;",
"refId": "WARNING"
}
],
"title": "<your_site_name> All Status",
"type": "piechart"
},
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"fixedColor": "red",
"mode": "palette-classic"
},
"custom": {
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
}
},
"mappings": []
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "ALARM count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "red",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "WARNING count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "yellow",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 12,
"y": 8
},
"id": 23,
"options": {
"displayLabels": [
"value"
],
"legend": {
"displayMode": "list",
"placement": "bottom",
"showLegend": true,
"values": []
},
"pieType": "pie",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'INITIALIZING'\n) \nGROUP BY classification;",
"refId": "INITIALIZING"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'ALARM'\n) \nGROUP BY classification;",
"refId": "ALARM"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'HEALTHY'\n) \nGROUP BY classification;",
"refId": "HEALTHY"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'WARNING'\n) \nGROUP BY classification;",
"refId": "WARNING"
}
],
"title": "<your_site_name> All Status",
"type": "piechart"
},
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
}
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "INITIALIZING count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "green",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "HEALTHY count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "blue",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "ALARM count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "red",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "WARNING count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "yellow",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 0,
"y": 16
},
"id": 22,
"options": {
"colorMode": "background",
"graphMode": "area",
"justifyMode": "center",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": true
},
"textMode": "auto"
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'INITIALIZING'\n) \nGROUP BY classification;",
"refId": "INITIALIZING"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'HEALTHY'\n) \nGROUP BY classification;",
"refId": "HEALTHY"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'WARNING'\n) \nGROUP BY classification;",
"refId": "WARNING"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'ALARM'\n) \nGROUP BY classification;",
"refId": "ALARM"
}
],
"type": "stat"
},
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "ALARM count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "red",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "WARNING count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "yellow",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 12,
"y": 16
},
"id": 24,
"options": {
"colorMode": "background",
"graphMode": "area",
"justifyMode": "center",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": true
},
"textMode": "value_and_name"
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'INITIALIZING'\n) \nGROUP BY classification;",
"refId": "INITIALIZING"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'ALARM'\n) \nGROUP BY classification;",
"refId": "ALARM"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'HEALTHY'\n) \nGROUP BY classification;",
"refId": "HEALTHY"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n classification,\n COUNT(*) AS count\nFROM (\n SELECT \n classification,\n physicalId,\n timestamp,\n rn\n FROM (\n SELECT \n eventpayload.models.temperatureml.pointwiseclassificationoutput AS classification,\n eventpayload.sensor.physicalId AS physicalId,\n timestamp,\n ROW_NUMBER() OVER (\n PARTITION BY eventpayload.sensor.physicalId \n ORDER BY timestamp DESC\n ) AS rn\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE eventpayload.sitename = '<your_site_name>'\n )\n WHERE rn = 1\n AND classification = 'WARNING'\n) \nGROUP BY classification;",
"refId": "WARNING"
}
],
"type": "stat"
},
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "Temperture",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 0,
"gradientMode": "opacity",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineStyle": {
"fill": "solid"
},
"lineWidth": 2,
"pointSize": 4,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": true,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "dashed"
}
},
"decimals": 0,
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "#EAB839",
"value": 25
},
{
"color": "red",
"value": 30
}
]
},
"unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "circulator temperature"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "green",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "motor temperature"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "yellow",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "Fan temperature"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "blue",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 0,
"y": 24
},
"id": 21,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.temperature\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'",
"refId": "circulator"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.temperature\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n LIMIT 20",
"refId": "motor"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.temperature\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n LIMIT 20",
"refId": "Fan"
}
],
"title": "Recent Temperature",
"transformations": [
{
"id": "convertFieldType",
"options": {
"conversions": [
{
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"destinationType": "time",
"targetField": "timestamp"
}
],
"fields": {}
}
}
],
"type": "timeseries"
},
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "Temperture",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 0,
"gradientMode": "opacity",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineStyle": {
"fill": "solid"
},
"lineWidth": 2,
"pointSize": 4,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"decimals": 0,
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
}
]
},
"unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "motor temperature"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "yellow",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "circulator temperature"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "blue",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 12,
"y": 24
},
"id": 26,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.temperature\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n",
"refId": "circulator"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.temperature\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n",
"refId": "motor"
}
],
"title": "Recent Temperature",
"transformations": [
{
"id": "convertFieldType",
"options": {
"conversions": [
{
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"destinationType": "time",
"targetField": "timestamp"
}
],
"fields": {}
}
}
],
"type": "timeseries"
},
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "Acceleration",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 0,
"gradientMode": "opacity",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineStyle": {
"fill": "solid"
},
"lineWidth": 2,
"pointSize": 4,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": true,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "dashed"
}
},
"decimals": 0,
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "#EAB839",
"value": 2
},
{
"color": "red",
"value": 5
}
]
},
"unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "motor rms"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "yellow",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "circulator rms"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "green",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "Fan rms"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "blue",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 0,
"y": 32
},
"id": 27,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.acceleration.band10To1000Hz.totalVibration.rms\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n -- 必要に応じてphysicalIDを変える\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n",
"refId": "circulator"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.acceleration.band10To1000Hz.totalVibration.rms\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n",
"refId": "motor"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.acceleration.band10To1000Hz.totalVibration.rms\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n",
"refId": "Fan"
}
],
"title": "Recent Acceleration",
"transformations": [
{
"id": "convertFieldType",
"options": {
"conversions": [
{
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"destinationType": "time",
"targetField": "timestamp"
}
],
"fields": {}
}
}
],
"type": "timeseries"
},
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "Acceleration",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 0,
"gradientMode": "opacity",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineStyle": {
"fill": "solid"
},
"lineWidth": 2,
"pointSize": 4,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"decimals": 0,
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
}
]
},
"unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "motor rms"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "yellow",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "circulator rms"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "blue",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 12,
"y": 32
},
"id": 25,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.acceleration.band10To1000Hz.totalVibration.rms\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n -- 必要に応じてphysicalIDを変える\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n",
"refId": "circulator"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.acceleration.band10To1000Hz.totalVibration.rms\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n",
"refId": "motor"
}
],
"title": "Recent Acceleration",
"transformations": [
{
"id": "convertFieldType",
"options": {
"conversions": [
{
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"destinationType": "time",
"targetField": "timestamp"
}
],
"fields": {}
}
}
],
"type": "timeseries"
},
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "Velocity",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 0,
"gradientMode": "opacity",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineStyle": {
"fill": "solid"
},
"lineWidth": 2,
"pointSize": 4,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": true,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"decimals": 0,
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
}
]
},
"unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "motor rms"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "yellow",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "circulator rms"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "green",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "Fan rms"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "blue",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 0,
"y": 40
},
"id": 28,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.velocity.band10To1000Hz.totalVibration.rms\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n -- 必要に応じてphysicalIDを変える\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'",
"refId": "circulator"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.velocity.band10To1000Hz.totalVibration.rms\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n",
"refId": "motor"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.velocity.band10To1000Hz.totalVibration.rms\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n",
"refId": "Fan"
}
],
"title": "Recent Velocity",
"transformations": [
{
"id": "convertFieldType",
"options": {
"conversions": [
{
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"destinationType": "time",
"targetField": "timestamp"
}
],
"fields": {}
}
}
],
"type": "timeseries"
},
{
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "Velocity",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 0,
"gradientMode": "opacity",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineStyle": {
"fill": "solid"
},
"lineWidth": 2,
"pointSize": 4,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"decimals": 0,
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
}
]
},
"unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "motor rms"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "yellow",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "circulator rms"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "blue",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 12,
"x": 12,
"y": 40
},
"id": 29,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "9.4.7",
"targets": [
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.velocity.band10To1000Hz.totalVibration.rms\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n -- 必要に応じてphysicalIDを変える\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n",
"refId": "circulator"
},
{
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default",
"resultReuseEnabled": false,
"resultReuseMaxAgeInMinutes": 60
},
"datasource": {
"type": "grafana-athena-datasource",
"uid": "K5DJuJoij"
},
"format": 1,
"hide": false,
"rawSQL": "SELECT \n timestamp,\n eventpayload.features.velocity.band10To1000Hz.totalVibration.rms\n FROM \"AwsDataCatalog\".\"<your_db_name>\".\"<your_table_name>\"\n WHERE projectname = '<your_project_name>' AND eventpayload.sensor.physicalId = '<your_physicalId>'\n LIMIT 10",
"refId": "motor"
}
],
"title": "Recent Velocity",
"transformations": [
{
"id": "convertFieldType",
"options": {
"conversions": [
{
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"destinationType": "time",
"targetField": "timestamp"
}
],
"fields": {}
}
}
],
"type": "timeseries"
}
],
"refresh": "",
"revision": 1,
"schemaVersion": 38,
"style": "dark",
"tags": [],
"templating": {
"list": []
},
"time": {
"from": "now-6h",
"to": "now"
},
"timepicker": {},
"timezone": "",
"title": "Amazon Monitron Dashboard",
"uid": "LTne2QbIk",
"version": 105,
"weekStart": ""
}GrafanaのメニューよりDashboardsをクリックします。
NewのプルダウンからImportをクリックします。
上記のJSONファイルを読み込ます。
Import (Overwrite)をクリックしてImportします。
これでインポートは完了です。 JSON内の<your_xxx_name>の部分はそれぞれ自身の値に変更して下さい。 あくまでも一部のデータを取得したグラフなので参考程度にご使用ください。
他に試しこと
Glueを使った構成
Monitronからデータが出力される度にS3バケットのプレフィックスが増えるので、GlueのCrawlerを使って1時間ごとにテーブルを作成する方法を考えていました。 最終的にGlueを採用しなかったのは
- データサイズが小さい
- スキーマが都度変わらない
というMonitronのデータ条件からAthenaでフルスキャンしても速度、コスト面でネックになることはないと判断し、Glueの採用は見送りました。
クロスアカウント構成
クロスアカウントでMonitronのデータを可視化も試してみました。 MonitronはOrganizationsが必須のサービスなので、Organizationsが使用できない環境では別途Monitronのデータ取得用のアカウントを作成する必要があります。 しかし、データは普段使用しているアカウントに転送して可視化まで行いたいという要件もあるかなと思います。（まさに私がそうでした）
この場合、Monitronから直接クロスアカウントはできないので、DataFirehose→S3の部分でクロスアカウントを行います。 詳しい方法についてはこちらの記事を参照くだい。
クロスアカウント以外の部分については今回の記事と同じ手順で可視化までできます。
まとめ
今回はMonitronのデータ可視化のアーキテクチャの一例を紹介しました。 Monitronを導入した or したいけど迷っている方の参考になれば幸いです。
今回の展示では可視化以外にもMonitronを知らない方に知ってもらうという目的もありました。 実際に機器が動いてデータが可視化されているところを見てもらうことで少しでも多くの方にMonitronを知ってもらう良い機会になりました。
1
