Hello dear people,
so I am very new to creating Grafana dashboards and I am currently out of ideas with my problem here. I realize that similar issues have been brought up in the discussions and I am trying to get a hang of everything, but I have not found a thread discussing my specific issue, at least not with the data source I am using.
I am using Grafana Cloud and my data source is SensorThings API with the STA plugin as I am fetching data from an IoT database. My query returns a “Time” field (YYYY-MM-DDThh:mm:ssZ) and a “Result” field with an integer. It looks as follows:
/Datastreams(<iot id>)/Observations?$orderby=phenomenonTime asc&$filter=phenomenonTime ge 2010-01-01T00:00:00Z and phenomenonTime le 2024-06-30T23:59:59Z
The “phenomenonTime”-field is then represented as the “Time” variable as can be seen from thies screenshot of the table-view:
I realize that the time format kind of correlates with the line thickness in some way I don’t understand fully. What I think I know is that I somehow have to transform the time field to be “YYYY-MM-DD” instead of “YYYY-MM-DD hh:mm:ss” so Grafana understands this is “daily” data and not data that is represented in a timestamp.
The SensorThings API doesn’t support “$groupby” which would be an easy way to solve this and you instead have to use “$apply=aggregate()” which doesn’t grant a result either. Here’s the query I’ve tried:
/Datastreams(4502)/Observations?$apply=aggregate(sum(Result) as TotalUsage) by floor(phenomenonTime/1000)/86400&$orderby=phenomenonTime asc&$filter=phenomenonTime ge 2010-01-01T00:00:00Z and phenomenonTime le 2024-06-30T23:59:59Z
The “Group By” transformation option doesn’t do anything at all:
Also I have tried adjusting the query options, but I don’t know if I did it correctly:
I have tried verious other transformation options which do not correspond with what I am trying to achieve.
Am I on the right track at all? I really don’t know what else to try or to do…
Here’s how the current chart looks with the “Bar width factor” “Line width” and “Fill opacity” maxed out:
JSON dump of the query inspector:
{
"id": 1,
"type": "timeseries",
"title": "Panel Title",
"gridPos": {
"x": 0,
"y": 0,
"h": 8,
"w": 12
},
"fieldConfig": {
"defaults": {
"custom": {
"drawStyle": "bars",
"lineInterpolation": "linear",
"barAlignment": 0,
"barWidthFactor": 1,
"lineWidth": 10,
"fillOpacity": 100,
"gradientMode": "none",
"spanNulls": false,
"insertNulls": false,
"showPoints": "auto",
"pointSize": 5,
"stacking": {
"mode": "none",
"group": "A"
},
"axisPlacement": "auto",
"axisLabel": "",
"axisColorMode": "text",
"axisBorderShow": false,
"scaleDistribution": {
"type": "linear"
},
"axisCenteredZero": false,
"hideFrom": {
"tooltip": false,
"viz": false,
"legend": false
},
"thresholdsStyle": {
"mode": "off"
}
},
"color": {
"mode": "palette-classic"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"value": null,
"color": "green"
},
{
"value": 80,
"color": "red"
}
]
}
},
"overrides": []
},
"pluginVersion": "11.3.0-74868",
"targets": [
{
"datasource": {
"type": "iosb-sensorthings-datasource",
"uid": "cdvowokci0c8wd"
},
"refId": "A",
"cacheDurationSeconds": 300,
"method": "GET",
"queryParams": "",
"urlPath": "/Datastreams(4502)/Observations?$orderby=phenomenonTime asc&$filter=phenomenonTime ge 2010-01-01T00:00:00Z and phenomenonTime le 2024-06-30T23:59:59Z",
"fields": [
{
"name": "Time",
"jsonPath": "$.value[*].phenomenonTime",
"language": "jsonpath"
},
{
"name": "Result",
"jsonPath": "$.value[*].result",
"language": "jsonpath"
}
]
}
],
"maxDataPoints": 31,
"datasource": {
"uid": "cdvowokci0c8wd",
"type": "iosb-sensorthings-datasource"
},
"interval": "1d",
"options": {
"tooltip": {
"mode": "single",
"sort": "none"
},
"legend": {
"showLegend": true,
"displayMode": "list",
"placement": "bottom",
"calcs": []
}
},
"timeFrom": "1d"
}
Sorry for being a noob and thank you in advance for your time and effort! I am happy to supply additional information if needed!
