使用 Grafana 做 Ping 和 測速
文章目錄
背景
開一個網站, 然後要記錄從各地方連過來的 Ping 狀況還有 Speed Test
測速工具
- 使用 e7d/speedtest, 他提供 IP, Ping, Jitter, Upload/Download 的相關資料, 並且會把資料存到 DB. (可惜這專案在 2023/7/4 archived 了)
- 使用 Grafana 讀取該 DB, 並做成 dashboard.
使用方式
-
e7d/speedtest 有提供 docker image, 在 https://hub.docker.com/r/e7db/speedtest, 可直接使用, docker-compose.yml 如下 (他需要搭配一個 PostgreSQL):
1speedtest: 2 image: e7db/speedtest 3 container_name: speedtest 4 networks: 5 - speedtest 6 expose: 7 - 80 8 ports: 9 - 80:80 10 volumes: 11 - /opt/speedtest/results:/app/results 12 - /etc/hosts:/etc/hosts 13postgres: 14 image: postgres:14 15 container_name: postgres 16 networks: 17 - speedtest 18 expose: 19 - 9105 20 ports: 21 - 9105:5432 # Postgres port 22 #- 443:5432 # Postgres port 23 volumes: 24 - /opt/speedtest/postgresql/:/var/lib/postgresql 25 - /opt/speedtest/postgresql/data/:/var/lib/postgresql/data 26 - /etc/hosts:/etc/hosts 27 environment: 28 POSTGRES_USER: postgres # define credentials 29 POSTGRES_PASSWORD: ******** # define credentials 30 POSTGRES_DB: postgres # define database 31 TZ: Asia/Singapore # This is for PostgreSQL's time zone 32 PGTZ: Asia/Singapore # This is for PostgreSQL's time zone -
測速, 直接打開上面的網站, 做幾次測速. 然後可以用 browser 開一些跨國 VPN 來模擬從其他國家來的測速.
-
在幾次測速之後, 資料應該已經存到 DB 了. 假設 table name 的名字是 speedtest2
-
回到 Grafana, 新增一個 Connection 連到該 DB.
-
新增一個 panel, data source 使用上面新增的 connection, SQL 指令輸入如下
1SELECT time - interval '8 hour' as time, ping, ip, org 2FROM speedtest2 3WHERE ip in ($ip) 4AND org in ($org) 5ORDER BY time -
執行 Run query, 就可以看到類似以下 SPC 的圖了
-
依序設定其他 Panel, 這是 Jitter
1SELECT time - interval '8 hour' as time, jitter, ip, org 2FROM speedtest2 3WHERE ip in ($ip) 4AND org in ($org) 5ORDER BY time -
這是 Upload speed
1SELECT time - interval '8 hour' as time, upload, ip, org 2FROM speedtest2 3WHERE ip in ($ip) 4AND org in ($org) 5ORDER BY time -
這是 Download speed
1SELECT time - interval '8 hour' as time, download, ip, org 2FROM speedtest2 3WHERE ip in ($ip) 4AND org in ($org) 5ORDER BY time -
這是 raw data, 注意, 前面都是 Time series, 這個是 Text
1SELECT s.time - interval '8 hour' as time, s.ping, s.jitter, s.upload, s.download, s.ip, i.country_name, i.city_name, s.org 2FROM speedtest2 s, ip2proxy i 3WHERE s.ip >= i.ip_from AND s.ip <= i.ip_to 4AND ip in ($ip) 5AND org in ($org) 6AND s.time >= to_timestamp($__from/1000) 7AND s.time <= to_timestamp($__to/1000) 8ORDER BY time DESC -
最後這個比較特別, 他是 Geomap, 他是 Grafana 內建的 plugin. 給他經緯度, 他就可以幫你標記在地圖上, 很方便.
1SELECT s.ping, time - interval '8 hour' as time, s.download, s.upload, i.country_name, i.city_name, i.latitude, i.longitude 2FROM speedtest2 s, ip2proxy i 3WHERE s.ip >= i.ip_from AND s.ip <= i.ip_to 4AND ip in ($ip) 5AND org in ($org) 6AND s.time >= to_timestamp($__from/1000) 7AND s.time <= to_timestamp($__to/1000) 8ORDER BY time DESC
結果
- 總和以上 6 個 panel, 呈現出來的 dashboard 如下:
Grafana dashboard 的 json
-
其實用上面的 SQL 指令就可以了建出各 panel 了, 我並沒有做甚麼客製化調整, 但還是附上原始 json.
1{ 2 "__inputs": [ 3 { 4 "name": "DS_SPEEDTEST_(POSTGRESQL)", 5 "label": "Speedtest (PostgreSQL)", 6 "description": "", 7 "type": "datasource", 8 "pluginId": "postgres", 9 "pluginName": "PostgreSQL" 10 }, 11 { 12 "name": "DS_PROMETHEUS", 13 "label": "Prometheus", 14 "description": "", 15 "type": "datasource", 16 "pluginId": "prometheus", 17 "pluginName": "Prometheus" 18 } 19 ], 20 "__elements": {}, 21 "__requires": [ 22 { 23 "type": "panel", 24 "id": "geomap", 25 "name": "Geomap", 26 "version": "" 27 }, 28 { 29 "type": "grafana", 30 "id": "grafana", 31 "name": "Grafana", 32 "version": "10.0.2" 33 }, 34 { 35 "type": "datasource", 36 "id": "postgres", 37 "name": "PostgreSQL", 38 "version": "1.0.0" 39 }, 40 { 41 "type": "datasource", 42 "id": "prometheus", 43 "name": "Prometheus", 44 "version": "1.0.0" 45 }, 46 { 47 "type": "panel", 48 "id": "table", 49 "name": "Table", 50 "version": "" 51 }, 52 { 53 "type": "panel", 54 "id": "text", 55 "name": "Text", 56 "version": "" 57 }, 58 { 59 "type": "panel", 60 "id": "timeseries", 61 "name": "Time series", 62 "version": "" 63 } 64 ], 65 "annotations": { 66 "list": [ 67 { 68 "builtIn": 1, 69 "datasource": { 70 "type": "grafana", 71 "uid": "-- Grafana --" 72 }, 73 "enable": true, 74 "hide": true, 75 "iconColor": "rgba(0, 211, 255, 1)", 76 "name": "Annotations & Alerts", 77 "target": { 78 "limit": 100, 79 "matchAny": false, 80 "tags": [], 81 "type": "dashboard" 82 }, 83 "type": "dashboard" 84 } 85 ] 86 }, 87 "description": " ", 88 "editable": true, 89 "fiscalYearStartMonth": 0, 90 "graphTooltip": 0, 91 "id": null, 92 "links": [], 93 "liveNow": false, 94 "panels": [ 95 { 96 "datasource": { 97 "type": "postgres", 98 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 99 }, 100 "fieldConfig": { 101 "defaults": { 102 "color": { 103 "mode": "thresholds" 104 }, 105 "custom": { 106 "axisCenteredZero": false, 107 "axisColorMode": "text", 108 "axisLabel": "", 109 "axisPlacement": "auto", 110 "barAlignment": 0, 111 "drawStyle": "points", 112 "fillOpacity": 0, 113 "gradientMode": "none", 114 "hideFrom": { 115 "legend": false, 116 "tooltip": false, 117 "viz": false 118 }, 119 "lineInterpolation": "linear", 120 "lineWidth": 1, 121 "pointSize": 10, 122 "scaleDistribution": { 123 "type": "linear" 124 }, 125 "showPoints": "auto", 126 "spanNulls": false, 127 "stacking": { 128 "group": "A", 129 "mode": "none" 130 }, 131 "thresholdsStyle": { 132 "mode": "off" 133 } 134 }, 135 "mappings": [], 136 "thresholds": { 137 "mode": "absolute", 138 "steps": [ 139 { 140 "color": "green", 141 "value": null 142 }, 143 { 144 "color": "#EAB839", 145 "value": 30 146 }, 147 { 148 "color": "red", 149 "value": 80 150 } 151 ] 152 }, 153 "unit": "ms" 154 }, 155 "overrides": [] 156 }, 157 "gridPos": { 158 "h": 8, 159 "w": 11, 160 "x": 0, 161 "y": 0 162 }, 163 "id": 11, 164 "options": { 165 "legend": { 166 "calcs": [], 167 "displayMode": "list", 168 "placement": "bottom", 169 "showLegend": false 170 }, 171 "tooltip": { 172 "mode": "single", 173 "sort": "none" 174 } 175 }, 176 "targets": [ 177 { 178 "datasource": { 179 "type": "postgres", 180 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 181 }, 182 "editorMode": "code", 183 "format": "time_series", 184 "group": [], 185 "key": "Q-571eb638-8680-4aea-ba7f-b876cd4588a7-0", 186 "metricColumn": "none", 187 "rawQuery": true, 188 "rawSql": "SELECT time - interval '8 hour' as time, ping, ip, org \n FROM speedtest2\n WHERE ip in ($ip)\n AND org in ($org)\n ORDER BY time", 189 "refId": "A", 190 "select": [ 191 [ 192 { 193 "params": [ 194 "value" 195 ], 196 "type": "column" 197 } 198 ] 199 ], 200 "sql": { 201 "columns": [ 202 { 203 "parameters": [], 204 "type": "function" 205 } 206 ], 207 "groupBy": [ 208 { 209 "property": { 210 "type": "string" 211 }, 212 "type": "groupBy" 213 } 214 ], 215 "limit": 50 216 }, 217 "table": "speedtest_users", 218 "timeColumn": "time", 219 "where": [] 220 } 221 ], 222 "title": "Ping", 223 "type": "timeseries" 224 }, 225 { 226 "datasource": { 227 "type": "prometheus", 228 "uid": "${DS_PROMETHEUS}" 229 }, 230 "description": "", 231 "gridPos": { 232 "h": 2, 233 "w": 13, 234 "x": 11, 235 "y": 0 236 }, 237 "id": 10, 238 "options": { 239 "code": { 240 "language": "plaintext", 241 "showLineNumbers": false, 242 "showMiniMap": false 243 }, 244 "content": "Speed Test Site: http://140.110.11.5", 245 "mode": "markdown" 246 }, 247 "pluginVersion": "10.0.2", 248 "title": " ", 249 "type": "text" 250 }, 251 { 252 "datasource": { 253 "type": "postgres", 254 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 255 }, 256 "fieldConfig": { 257 "defaults": { 258 "custom": { 259 "align": "auto", 260 "cellOptions": { 261 "type": "auto" 262 }, 263 "inspect": false 264 }, 265 "mappings": [], 266 "thresholds": { 267 "mode": "absolute", 268 "steps": [ 269 { 270 "color": "green", 271 "value": null 272 } 273 ] 274 } 275 }, 276 "overrides": [ 277 { 278 "matcher": { 279 "id": "byName", 280 "options": "time" 281 }, 282 "properties": [ 283 { 284 "id": "custom.width", 285 "value": 181 286 }, 287 { 288 "id": "unit", 289 "value": "dateTimeAsIso" 290 }, 291 { 292 "id": "displayName", 293 "value": "Time" 294 } 295 ] 296 }, 297 { 298 "matcher": { 299 "id": "byName", 300 "options": "ping" 301 }, 302 "properties": [ 303 { 304 "id": "custom.width", 305 "value": 75 306 }, 307 { 308 "id": "displayName", 309 "value": "Ping" 310 }, 311 { 312 "id": "unit", 313 "value": "ms" 314 } 315 ] 316 }, 317 { 318 "matcher": { 319 "id": "byName", 320 "options": "jitter" 321 }, 322 "properties": [ 323 { 324 "id": "custom.width", 325 "value": 84 326 }, 327 { 328 "id": "displayName", 329 "value": "Jitter" 330 }, 331 { 332 "id": "unit", 333 "value": "ms" 334 } 335 ] 336 }, 337 { 338 "matcher": { 339 "id": "byName", 340 "options": "upload" 341 }, 342 "properties": [ 343 { 344 "id": "custom.width", 345 "value": 100 346 }, 347 { 348 "id": "unit", 349 "value": "binBps" 350 }, 351 { 352 "id": "displayName", 353 "value": "Upload" 354 } 355 ] 356 }, 357 { 358 "matcher": { 359 "id": "byName", 360 "options": "download" 361 }, 362 "properties": [ 363 { 364 "id": "custom.width", 365 "value": 107 366 }, 367 { 368 "id": "unit", 369 "value": "binBps" 370 }, 371 { 372 "id": "displayName", 373 "value": "Download" 374 } 375 ] 376 }, 377 { 378 "matcher": { 379 "id": "byName", 380 "options": "ip" 381 }, 382 "properties": [ 383 { 384 "id": "custom.width", 385 "value": 128 386 }, 387 { 388 "id": "displayName", 389 "value": "IP Address" 390 } 391 ] 392 }, 393 { 394 "matcher": { 395 "id": "byName", 396 "options": "org" 397 }, 398 "properties": [ 399 { 400 "id": "custom.width", 401 "value": 59 402 }, 403 { 404 "id": "displayName", 405 "value": "Organization" 406 } 407 ] 408 }, 409 { 410 "matcher": { 411 "id": "byName", 412 "options": "Organization" 413 }, 414 "properties": [ 415 { 416 "id": "custom.width", 417 "value": 410 418 } 419 ] 420 }, 421 { 422 "matcher": { 423 "id": "byName", 424 "options": "Time" 425 }, 426 "properties": [ 427 { 428 "id": "custom.width", 429 "value": 156 430 } 431 ] 432 }, 433 { 434 "matcher": { 435 "id": "byName", 436 "options": "Ping (ms)" 437 }, 438 "properties": [ 439 { 440 "id": "custom.width", 441 "value": 80 442 } 443 ] 444 }, 445 { 446 "matcher": { 447 "id": "byName", 448 "options": "Jitter (ms)" 449 }, 450 "properties": [ 451 { 452 "id": "custom.width", 453 "value": 85 454 } 455 ] 456 }, 457 { 458 "matcher": { 459 "id": "byName", 460 "options": "country_name" 461 }, 462 "properties": [ 463 { 464 "id": "displayName", 465 "value": "Country" 466 } 467 ] 468 }, 469 { 470 "matcher": { 471 "id": "byName", 472 "options": "city_name" 473 }, 474 "properties": [ 475 { 476 "id": "displayName", 477 "value": "City" 478 } 479 ] 480 }, 481 { 482 "matcher": { 483 "id": "byName", 484 "options": "Country" 485 }, 486 "properties": [ 487 { 488 "id": "custom.width", 489 "value": 214 490 } 491 ] 492 }, 493 { 494 "matcher": { 495 "id": "byName", 496 "options": "City" 497 }, 498 "properties": [ 499 { 500 "id": "custom.width", 501 "value": 81 502 } 503 ] 504 }, 505 { 506 "matcher": { 507 "id": "byName", 508 "options": "Download" 509 }, 510 "properties": [ 511 { 512 "id": "custom.width" 513 } 514 ] 515 } 516 ] 517 }, 518 "gridPos": { 519 "h": 8, 520 "w": 13, 521 "x": 11, 522 "y": 2 523 }, 524 "id": 8, 525 "options": { 526 "cellHeight": "sm", 527 "footer": { 528 "countRows": false, 529 "fields": "", 530 "reducer": [ 531 "sum" 532 ], 533 "show": false 534 }, 535 "showHeader": true, 536 "sortBy": [ 537 { 538 "desc": true, 539 "displayName": "Time" 540 } 541 ] 542 }, 543 "pluginVersion": "10.0.2", 544 "targets": [ 545 { 546 "datasource": { 547 "type": "postgres", 548 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 549 }, 550 "format": "table", 551 "group": [], 552 "key": "Q-3bd0fb03-3fed-4bba-b2ce-da323646e2d1-0", 553 "metricColumn": "none", 554 "rawQuery": true, 555 "rawSql": "SELECT s.time - interval '8 hour' as time, s.ping, s.jitter, s.upload, s.download, s.ip, i.country_name, i.city_name, s.org\n FROM speedtest2 s, ip2proxy i\n WHERE s.ip >= i.ip_from AND s.ip <= i.ip_to\n AND ip in ($ip)\n AND org in ($org)\n AND s.time >= to_timestamp($__from/1000)\n AND s.time <= to_timestamp($__to/1000)\n ORDER BY time DESC", 556 "refId": "A", 557 "select": [ 558 [ 559 { 560 "params": [ 561 "value" 562 ], 563 "type": "column" 564 } 565 ] 566 ], 567 "timeColumn": "time", 568 "where": [ 569 { 570 "name": "$__timeFilter", 571 "params": [], 572 "type": "macro" 573 } 574 ] 575 } 576 ], 577 "title": "Raw data", 578 "type": "table" 579 }, 580 { 581 "datasource": { 582 "type": "postgres", 583 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 584 }, 585 "fieldConfig": { 586 "defaults": { 587 "color": { 588 "fixedColor": "green", 589 "mode": "thresholds" 590 }, 591 "custom": { 592 "axisCenteredZero": false, 593 "axisColorMode": "text", 594 "axisLabel": "", 595 "axisPlacement": "auto", 596 "barAlignment": 0, 597 "drawStyle": "points", 598 "fillOpacity": 0, 599 "gradientMode": "none", 600 "hideFrom": { 601 "legend": false, 602 "tooltip": false, 603 "viz": false 604 }, 605 "lineInterpolation": "linear", 606 "lineWidth": 1, 607 "pointSize": 10, 608 "scaleDistribution": { 609 "type": "linear" 610 }, 611 "showPoints": "auto", 612 "spanNulls": false, 613 "stacking": { 614 "group": "A", 615 "mode": "none" 616 }, 617 "thresholdsStyle": { 618 "mode": "off" 619 } 620 }, 621 "mappings": [], 622 "thresholds": { 623 "mode": "absolute", 624 "steps": [ 625 { 626 "color": "green", 627 "value": null 628 }, 629 { 630 "color": "#EAB839", 631 "value": 5 632 }, 633 { 634 "color": "red", 635 "value": 10 636 } 637 ] 638 }, 639 "unit": "ms" 640 }, 641 "overrides": [] 642 }, 643 "gridPos": { 644 "h": 8, 645 "w": 11, 646 "x": 0, 647 "y": 8 648 }, 649 "id": 2, 650 "options": { 651 "legend": { 652 "calcs": [], 653 "displayMode": "list", 654 "placement": "bottom", 655 "showLegend": false 656 }, 657 "tooltip": { 658 "mode": "single", 659 "sort": "none" 660 } 661 }, 662 "targets": [ 663 { 664 "datasource": { 665 "type": "postgres", 666 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 667 }, 668 "format": "time_series", 669 "group": [], 670 "key": "Q-571eb638-8680-4aea-ba7f-b876cd4588a7-0", 671 "metricColumn": "none", 672 "rawQuery": true, 673 "rawSql": "SELECT time - interval '8 hour' as time, jitter, ip, org \n FROM speedtest2\n WHERE ip in ($ip)\n AND org in ($org)\n ORDER BY time", 674 "refId": "A", 675 "select": [ 676 [ 677 { 678 "params": [ 679 "value" 680 ], 681 "type": "column" 682 } 683 ] 684 ], 685 "table": "speedtest_users", 686 "timeColumn": "time", 687 "where": [] 688 } 689 ], 690 "title": "Jitter", 691 "type": "timeseries" 692 }, 693 { 694 "datasource": { 695 "type": "postgres", 696 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 697 }, 698 "fieldConfig": { 699 "defaults": { 700 "color": { 701 "mode": "thresholds" 702 }, 703 "custom": { 704 "hideFrom": { 705 "legend": false, 706 "tooltip": false, 707 "viz": false 708 } 709 }, 710 "mappings": [], 711 "thresholds": { 712 "mode": "absolute", 713 "steps": [ 714 { 715 "color": "green", 716 "value": null 717 }, 718 { 719 "color": "#EAB839", 720 "value": 30 721 }, 722 { 723 "color": "red", 724 "value": 100 725 } 726 ] 727 }, 728 "unit": "ms" 729 }, 730 "overrides": [ 731 { 732 "matcher": { 733 "id": "byName", 734 "options": "ping" 735 }, 736 "properties": [ 737 { 738 "id": "displayName", 739 "value": "Ping" 740 }, 741 { 742 "id": "unit", 743 "value": "ms" 744 } 745 ] 746 }, 747 { 748 "matcher": { 749 "id": "byName", 750 "options": "download" 751 }, 752 "properties": [ 753 { 754 "id": "unit", 755 "value": "binBps" 756 }, 757 { 758 "id": "displayName", 759 "value": "Download speed" 760 } 761 ] 762 }, 763 { 764 "matcher": { 765 "id": "byName", 766 "options": "upload" 767 }, 768 "properties": [ 769 { 770 "id": "displayName", 771 "value": "Upload speed" 772 }, 773 { 774 "id": "unit", 775 "value": "binBps" 776 } 777 ] 778 }, 779 { 780 "matcher": { 781 "id": "byName", 782 "options": "country_name" 783 }, 784 "properties": [ 785 { 786 "id": "displayName", 787 "value": "Country" 788 } 789 ] 790 }, 791 { 792 "matcher": { 793 "id": "byName", 794 "options": "city_name" 795 }, 796 "properties": [ 797 { 798 "id": "displayName", 799 "value": "City" 800 } 801 ] 802 }, 803 { 804 "matcher": { 805 "id": "byName", 806 "options": "latitude" 807 }, 808 "properties": [ 809 { 810 "id": "displayName", 811 "value": "Latitude" 812 } 813 ] 814 }, 815 { 816 "matcher": { 817 "id": "byName", 818 "options": "longitude" 819 }, 820 "properties": [ 821 { 822 "id": "displayName", 823 "value": "Longitude" 824 }, 825 { 826 "id": "unit" 827 } 828 ] 829 }, 830 { 831 "matcher": { 832 "id": "byName", 833 "options": "time" 834 }, 835 "properties": [ 836 { 837 "id": "displayName", 838 "value": "Time" 839 } 840 ] 841 } 842 ] 843 }, 844 "gridPos": { 845 "h": 21, 846 "w": 13, 847 "x": 11, 848 "y": 10 849 }, 850 "id": 14, 851 "options": { 852 "basemap": { 853 "config": { 854 "showLabels": true, 855 "theme": "auto" 856 }, 857 "name": "Layer 0", 858 "type": "carto" 859 }, 860 "controls": { 861 "mouseWheelZoom": true, 862 "showAttribution": true, 863 "showDebug": false, 864 "showMeasure": false, 865 "showScale": false, 866 "showZoom": true 867 }, 868 "layers": [ 869 { 870 "config": { 871 "showLegend": true, 872 "style": { 873 "color": { 874 "field": "ping", 875 "fixed": "dark-green" 876 }, 877 "opacity": 0.4, 878 "rotation": { 879 "fixed": 0, 880 "max": 360, 881 "min": -360, 882 "mode": "mod" 883 }, 884 "size": { 885 "field": "ping", 886 "fixed": 5, 887 "max": 15, 888 "min": 2 889 }, 890 "symbol": { 891 "fixed": "img/icons/marker/circle.svg", 892 "mode": "fixed" 893 }, 894 "text": { 895 "field": "ping", 896 "fixed": "", 897 "mode": "fixed" 898 }, 899 "textConfig": { 900 "fontSize": 12, 901 "offsetX": 0, 902 "offsetY": 0, 903 "textAlign": "center", 904 "textBaseline": "middle" 905 } 906 } 907 }, 908 "location": { 909 "mode": "auto" 910 }, 911 "name": "Layer 1", 912 "tooltip": true, 913 "type": "markers" 914 } 915 ], 916 "tooltip": { 917 "mode": "details" 918 }, 919 "view": { 920 "allLayers": true, 921 "id": "zero", 922 "lat": 0, 923 "lon": 0, 924 "zoom": 1 925 } 926 }, 927 "pluginVersion": "10.0.2", 928 "targets": [ 929 { 930 "datasource": { 931 "type": "postgres", 932 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 933 }, 934 "format": "table", 935 "group": [], 936 "key": "Q-5bd26bdd-0e13-48ba-8ccd-9b82337c1e13-0", 937 "metricColumn": "none", 938 "rawQuery": true, 939 "rawSql": "SELECT s.ping, time - interval '8 hour' as time, s.download, s.upload, i.country_name, i.city_name, i.latitude, i.longitude\n FROM speedtest2 s, ip2proxy i\n WHERE s.ip >= i.ip_from AND s.ip <= i.ip_to\n AND ip in ($ip)\n AND org in ($org)\n AND s.time >= to_timestamp($__from/1000)\n AND s.time <= to_timestamp($__to/1000)\n ORDER BY time DESC", 940 "refId": "A", 941 "select": [ 942 [ 943 { 944 "params": [ 945 "value" 946 ], 947 "type": "column" 948 } 949 ] 950 ], 951 "timeColumn": "time", 952 "where": [ 953 { 954 "name": "$__timeFilter", 955 "params": [], 956 "type": "macro" 957 } 958 ] 959 } 960 ], 961 "title": "Map", 962 "type": "geomap" 963 }, 964 { 965 "datasource": { 966 "type": "postgres", 967 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 968 }, 969 "fieldConfig": { 970 "defaults": { 971 "color": { 972 "mode": "thresholds" 973 }, 974 "custom": { 975 "axisCenteredZero": false, 976 "axisColorMode": "text", 977 "axisLabel": "", 978 "axisPlacement": "auto", 979 "barAlignment": 0, 980 "drawStyle": "points", 981 "fillOpacity": 0, 982 "gradientMode": "none", 983 "hideFrom": { 984 "legend": false, 985 "tooltip": false, 986 "viz": false 987 }, 988 "lineInterpolation": "linear", 989 "lineWidth": 1, 990 "pointSize": 10, 991 "scaleDistribution": { 992 "type": "linear" 993 }, 994 "showPoints": "auto", 995 "spanNulls": false, 996 "stacking": { 997 "group": "A", 998 "mode": "none" 999 }, 1000 "thresholdsStyle": { 1001 "mode": "off" 1002 } 1003 }, 1004 "mappings": [], 1005 "thresholds": { 1006 "mode": "absolute", 1007 "steps": [ 1008 { 1009 "color": "green", 1010 "value": null 1011 } 1012 ] 1013 }, 1014 "unit": "binbps" 1015 }, 1016 "overrides": [] 1017 }, 1018 "gridPos": { 1019 "h": 7, 1020 "w": 11, 1021 "x": 0, 1022 "y": 16 1023 }, 1024 "id": 5, 1025 "options": { 1026 "legend": { 1027 "calcs": [], 1028 "displayMode": "list", 1029 "placement": "bottom", 1030 "showLegend": false 1031 }, 1032 "tooltip": { 1033 "mode": "single", 1034 "sort": "none" 1035 } 1036 }, 1037 "targets": [ 1038 { 1039 "datasource": { 1040 "type": "postgres", 1041 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 1042 }, 1043 "format": "time_series", 1044 "group": [], 1045 "key": "Q-e80af284-077f-420b-80c5-f9bd9d7b8aab-0", 1046 "metricColumn": "none", 1047 "rawQuery": true, 1048 "rawSql": "SELECT time - interval '8 hour' as time, upload, ip, org\n FROM speedtest2\n WHERE ip in ($ip)\n AND org in ($org)\n ORDER BY time", 1049 "refId": "A", 1050 "select": [ 1051 [ 1052 { 1053 "params": [ 1054 "value" 1055 ], 1056 "type": "column" 1057 } 1058 ] 1059 ], 1060 "timeColumn": "time", 1061 "where": [ 1062 { 1063 "name": "$__timeFilter", 1064 "params": [], 1065 "type": "macro" 1066 } 1067 ] 1068 } 1069 ], 1070 "title": "Upload Speed", 1071 "type": "timeseries" 1072 }, 1073 { 1074 "datasource": { 1075 "type": "postgres", 1076 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 1077 }, 1078 "fieldConfig": { 1079 "defaults": { 1080 "color": { 1081 "mode": "thresholds" 1082 }, 1083 "custom": { 1084 "axisCenteredZero": false, 1085 "axisColorMode": "text", 1086 "axisLabel": "", 1087 "axisPlacement": "auto", 1088 "barAlignment": 0, 1089 "drawStyle": "points", 1090 "fillOpacity": 0, 1091 "gradientMode": "none", 1092 "hideFrom": { 1093 "legend": false, 1094 "tooltip": false, 1095 "viz": false 1096 }, 1097 "lineInterpolation": "linear", 1098 "lineWidth": 1, 1099 "pointSize": 10, 1100 "scaleDistribution": { 1101 "type": "linear" 1102 }, 1103 "showPoints": "auto", 1104 "spanNulls": false, 1105 "stacking": { 1106 "group": "A", 1107 "mode": "none" 1108 }, 1109 "thresholdsStyle": { 1110 "mode": "off" 1111 } 1112 }, 1113 "mappings": [], 1114 "thresholds": { 1115 "mode": "absolute", 1116 "steps": [ 1117 { 1118 "color": "green", 1119 "value": null 1120 } 1121 ] 1122 }, 1123 "unit": "binbps" 1124 }, 1125 "overrides": [] 1126 }, 1127 "gridPos": { 1128 "h": 8, 1129 "w": 11, 1130 "x": 0, 1131 "y": 23 1132 }, 1133 "id": 6, 1134 "options": { 1135 "legend": { 1136 "calcs": [], 1137 "displayMode": "list", 1138 "placement": "bottom", 1139 "showLegend": false 1140 }, 1141 "tooltip": { 1142 "mode": "single", 1143 "sort": "none" 1144 } 1145 }, 1146 "targets": [ 1147 { 1148 "datasource": { 1149 "type": "postgres", 1150 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 1151 }, 1152 "format": "time_series", 1153 "group": [], 1154 "key": "Q-e80af284-077f-420b-80c5-f9bd9d7b8aab-0", 1155 "metricColumn": "none", 1156 "rawQuery": true, 1157 "rawSql": "SELECT time - interval '8 hour' as time, download, ip, org\n FROM speedtest2\n WHERE ip in ($ip)\n AND org in ($org)\n ORDER BY time", 1158 "refId": "A", 1159 "select": [ 1160 [ 1161 { 1162 "params": [ 1163 "value" 1164 ], 1165 "type": "column" 1166 } 1167 ] 1168 ], 1169 "timeColumn": "time", 1170 "where": [ 1171 { 1172 "name": "$__timeFilter", 1173 "params": [], 1174 "type": "macro" 1175 } 1176 ] 1177 } 1178 ], 1179 "title": "Download Speed", 1180 "type": "timeseries" 1181 } 1182 ], 1183 "refresh": "", 1184 "schemaVersion": 38, 1185 "style": "dark", 1186 "tags": [], 1187 "templating": { 1188 "list": [ 1189 { 1190 "current": {}, 1191 "datasource": { 1192 "type": "postgres", 1193 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 1194 }, 1195 "definition": "select distinct ip from speedtest2", 1196 "description": "label_values(ip)", 1197 "hide": 0, 1198 "includeAll": true, 1199 "label": "IP Address", 1200 "multi": true, 1201 "name": "ip", 1202 "options": [], 1203 "query": "select distinct ip from speedtest2", 1204 "refresh": 2, 1205 "regex": "", 1206 "skipUrlSync": false, 1207 "sort": 1, 1208 "type": "query" 1209 }, 1210 { 1211 "current": {}, 1212 "datasource": { 1213 "type": "postgres", 1214 "uid": "${DS_SPEEDTEST_(POSTGRESQL)}" 1215 }, 1216 "definition": "select distinct org from speedtest2", 1217 "hide": 0, 1218 "includeAll": true, 1219 "label": "Organization", 1220 "multi": true, 1221 "name": "org", 1222 "options": [], 1223 "query": "select distinct org from speedtest2", 1224 "refresh": 2, 1225 "regex": "", 1226 "skipUrlSync": false, 1227 "sort": 1, 1228 "type": "query" 1229 } 1230 ] 1231 }, 1232 "time": { 1233 "from": "now-6h", 1234 "to": "now" 1235 }, 1236 "timepicker": {}, 1237 "timezone": "", 1238 "title": "Speed Test", 1239 "uid": "TTE5-PzVk", 1240 "version": 41, 1241 "weekStart": "" 1242}