Hi there,
I’m a newbie to grafana so please demonstrate by making examples
I have the following SELECT
SELECT “latitude” as lat, “longitude” as lon,“cpm” as cpm, “cpm_level” as cpm_level, “time” as date, “satellites” as satellites, “elevation” as elevation, “dist_initial” as dist_initial, “dist_last” as dist_last , “time” as time_index FROM “GPSCPM” WHERE $timeFilter
and would like to shorten the table and show only every 5m row.
Can yo help me ?
Kind regards
what datasource plugin are you working with?
Hi,
it is influxdb.
At the moment I do some trickery when collecting the data points - I have a field display=[bool] which will be written all 5min to influxdb and then in the query i do a WHERE display=1 - But this is very dirty and not the solution.
Thank you
There is a sample Dashboard: gpx-radiation-tracker/Grafana Dashboards at main · denisimmoos/gpx-radiation-tracker · GitHub
Sample JSON of data:
{ "request": { "method": "GET", "url": "api/datasources/proxy/1/query", "params": { "db": "gpscpm", "q": "SELECT \"latitude\" as lat, \"longitude\" as lon,\"cpm\" as cpm, \"cpm_level\" as cpm_level, \"display\" as display, \"time\" as date, \"satellites\" as satellites, \"elevation\" as elevation, \"dist_initial\" as dist_initial, \"dist_last\" as dist_last , \"time\" as time_index FROM \"GPSCPM\" WHERE time >= now() - 5m and time <= now() AND display = 1", "epoch": "ms" }, "data": null, "precision": "ms", "hideFromInspector": false }, "response": { "results": [ { "statement_id": 0, "series": [ { "name": "GPSCPM", "columns": [ "time_index", "lat", "lon", "cpm", "cpm_level", "display", "satellites", "elevation", "dist_initial", "dist_last" ], "values": [ [ 1651226677871, 47.55495766666667, 7.791704666666667, 15, "Normal (15)", 1, 8, 273.6, 6.4, 0 ], [ 1651226691041, 47.554956, 7.7916973333333335, 17, "Normal (17)", 1, 8, 275.9, 5.8, 0 ], [ 1651226704218, 47.55495233333333, 7.7916834999999995, 19, "Normal (19)", 1, 8, 276.9, 5.8, 0 ], [ 1651226717363, 47.55501233333333, 7.791746833333333, 16, "Normal (16)", 1, 8, 276.6, 6.1, 0 ], [ 1651226730561, 47.55504733333333, 7.791785666666667, 18, "Normal (18)", 1, 7, 276.2, 11.1, 0 ], [ 1651226743711, 47.55500983333333, 7.791721833333333, 15, "Normal (15)", 1, 6, 281.3, 7.1, 0 ], [ 1651226756857, 47.55499566666666, 7.791755333333334, 15, "Normal (15)", 1, 6, 278.2, 6.1, 0 ], [ 1651226769980, 47.55495583333333, 7.791779833333333, 18, "Normal (18)", 1, 7, 268.8, 10, 0 ], [ 1651226783125, 47.55488533333333, 7.791690833333333, 18, "Normal (18)", 1, 8, 253.4, 12.9, 0 ], [ 1651226796318, 47.554862166666666, 7.791600333333333, 20, "Normal (20)", 1, 7, 235.9, 13.4, 0 ], [ 1651226809459, 47.554860166666664, 7.791559666666666, 22, "Normal (22)", 1, 8, 223.1, 19.5, 0 ], [ 1651226822653, 47.555003, 7.7916455, 25, "Normal (25)", 1, 8, 242, 1.8, 0 ], [ 1651226835836, 47.555036333333334, 7.7916549999999996, 26, "Normal (26)", 1, 8, 252, 4.4, 0 ], [ 1651226848967, 47.555039666666666, 7.791658666666667, 26, "Normal (26)", 1, 7, 259.3, 4.4, 0 ], [ 1651226862099, 47.55506183333333, 7.791661833333333, 25, "Normal (25)", 1, 8, 260.5, 5.8, 0 ], [ 1651226875233, 47.55508716666667, 7.791631166666667, 27, "Normal (27)", 1, 8, 263.5, 9.6, 0 ], [ 1651226888385, 47.5550735, 7.7916485, 25, "Normal (25)", 1, 7, 268.1, 8.7, 0 ], [ 1651226901679, 47.555069499999995, 7.791662666666666, 28, "Normal (28)", 1, 8, 272.5, 8.7, 0 ], [ 1651226915141, 47.55504716666666, 7.791703833333333, 29, "Normal (29)", 1, 8, 279.4, 6.6, 0 ], [ 1651226928440, 47.55504233333333, 7.791709333333333, 31, "Normal (31)", 1, 7, 280.8, 6.7, 0 ], [ 1651226941654, 47.55503516666666, 7.7917125, 32, "Normal (32)", 1, 8, 280.7, 6.6, 0 ], [ 1651226955160, 47.555020666666664, 7.791737333333333, 32, "Normal (32)", 1, 8, 280, 7, 0 ], [ 1651226968399, 47.555020666666664, 7.791743, 29, "Normal (29)", 1, 7, 279.6, 7.2, 0 ] ] } ] } ], "executedQueryString": "SELECT \"latitude\" as lat, \"longitude\" as lon,\"cpm\" as cpm, \"cpm_level\" as cpm_level, \"display\" as display, \"time\" as date, \"satellites\" as satellites, \"elevation\" as elevation, \"dist_initial\" as dist_initial, \"dist_last\" as dist_last , \"time\" as time_index FROM \"GPSCPM\" WHERE time >= now() - 5m and time <= now() AND display = 1" }}
Sample CSV:
ame,time,GeigerUnit,cpm,cpm_level,display,dist_initial,dist_last,elevation,latitude,longitude,satellites
GPSCPM,1651215580528384512,CPM,30,Normal (30),1,0,0,283.9,47.55499783333333,7.791646666666667,5
GPSCPM,1651215590615718481,CPM,32,Normal (32),1,0,-1,283.8,47.55499833333333,7.7916463333333335,5
GPSCPM,1651215601389339935,CPM,33,Normal (33),0,0.1,0,282.1,47.55500833333333,7.791609833333333,5
GPSCPM,1651215611148207032,CPM,33,Normal (33),1,3,0,280.6,47.555013833333334,7.791592166666667,5
GPSCPM,1651215619851553826,CPM,30,Normal (30),0,4.5,0,279.9,47.555023,7.791578666666666,5
GPSCPM,1651215623165313107,CPM,32,Normal (32),1,5.8,0,279.9,47.55502183333333,7.791580333333333,5
GPSCPM,1651215626460062502,CPM,32,Normal (32),0,5.6,0,279.3,47.555018499999996,7.7915925,5
GPSCPM,1651215629804315727,CPM,32,Normal (32),0,4.7,0,279.3,47.555020666666664,7.7915885,5
GPSCPM,1651215633186540832,CPM,31,Normal (31),0,5,0,279.3,47.55501883333333,7.791596,5
GPSCPM,1651215636479291339,CPM,29,Normal (29),1,4.5,0,279.3,47.55501816666666,7.791601666666667,5
GPSCPM,1651215639816046699,CPM,30,Normal (30),0,4.1,0,281.1,47.55501783333333,7.791596833333333,5
GPSCPM,1651215643101159303,CPM,32,Normal (32),0,4.4,0,283.7,47.55501566666666,7.791591666666666,5
GPSCPM,1651215646380845962,CPM,32,Normal (32),0,4.6,0,281.7,47.55501683333333,7.791590666666666,5
GPSCPM,1651215649690507531,CPM,32,Normal (32),1,4.7,0,280.8,47.555015166666664,7.791596999999999,5
GPSCPM,1651215653078180816,CPM,32,Normal (32),0,4.2,0,280.5,47.55501366666666,7.791604166666667,5
GPSCPM,1651215656356725575,CPM,31,Normal (31),0,3.6,0,279,47.5550125,7.7916215,5
GPSCPM,1651215659632460376,CPM,31,Normal (31),0,2.5,0,280.4,47.5550115,7.7916126666666665,5
GPSCPM,1651215663027003729,CPM,32,Normal (32),1,3,0,281.9,47.55500816666667,7.791620666666667,5
GPSCPM,1651215666357109373,CPM,30,Normal (30),0,2.3,0,280.3,47.55501066666666,7.7916221666666665,5
GPSCPM,1651215669643779221,CPM,27,Normal (27),0,2.3,0,280.6,47.555009,7.7916255,5
GPSCPM,1651215673058158588,CPM,24,Normal (24),0,2,0,279.7,47.555008666666666,7.7916343333333336,5
GPSCPM,1651215676371268390,CPM,24,Normal (24),1,1.5,0,279.5,47.55500716666666,7.7916438333333335,5