d4all_pg.json (4737B)
1 { 2 "annotations": { 3 "list": [ 4 { 5 "builtIn": 1, 6 "datasource": { 7 "type": "grafana", 8 "uid": "-- Grafana --" 9 }, 10 "enable": true, 11 "hide": true, 12 "iconColor": "rgba(0, 211, 255, 1)", 13 "name": "Annotations & Alerts", 14 "type": "dashboard" 15 } 16 ] 17 }, 18 "editable": true, 19 "fiscalYearStartMonth": 0, 20 "graphTooltip": 0, 21 "id": 39, 22 "links": [], 23 "panels": [ 24 { 25 "datasource": { 26 "type": "grafana-postgresql-datasource", 27 "uid": "postgres-d4all" 28 }, 29 "fieldConfig": { 30 "defaults": { 31 "custom": { 32 "align": "auto", 33 "cellOptions": { 34 "type": "auto" 35 }, 36 "filterable": true, 37 "inspect": false 38 }, 39 "mappings": [], 40 "thresholds": { 41 "mode": "absolute", 42 "steps": [ 43 { 44 "color": "green" 45 }, 46 { 47 "color": "red", 48 "value": 80 49 } 50 ] 51 } 52 }, 53 "overrides": [] 54 }, 55 "gridPos": { 56 "h": 16, 57 "w": 24, 58 "x": 0, 59 "y": 0 60 }, 61 "id": 1, 62 "options": { 63 "cellHeight": "sm", 64 "footer": { 65 "countRows": false, 66 "enablePagination": true, 67 "fields": [ 68 "protected_room_count" 69 ], 70 "reducer": [ 71 "sum" 72 ], 73 "show": true 74 }, 75 "showHeader": true 76 }, 77 "pluginVersion": "12.0.2", 78 "targets": [ 79 { 80 "datasource": { 81 "type": "grafana-postgresql-datasource", 82 "uid": "postgres-d4all" 83 }, 84 "editorMode": "code", 85 "format": "table", 86 "rawQuery": true, 87 "rawSql": "WITH mjolnir_senders AS (\n SELECT\n DISTINCT room_id,\n sender\n FROM\n events\n WHERE\n type = 'org.matrix.mjolnir.setting'\n),\nmember_events AS (\n SELECT\n room_id,\n event_id,\n sender,\n state_key,\n depth,\n ROW_NUMBER() OVER (\n PARTITION BY room_id\n ORDER BY\n depth\n ) AS member_index\n FROM\n events\n WHERE\n type = 'm.room.member'\n AND sender LIKE '%:draupnir.midnightthoughts.space'\n),\nprotected_counts AS (\n SELECT\n user_id,\n jsonb_array_length(content :: jsonb -> 'rooms') AS room_count\n FROM\n account_data\n WHERE\n account_data_type = 'org.matrix.mjolnir.protected_rooms'\n)\nSELECT\n m.room_id,\n m.state_key,\n m.sender AS member_sender,\n COALESCE(pc.room_count, 0) AS protected_room_count\nFROM\n member_events m\n JOIN mjolnir_senders mj ON m.room_id = mj.room_id\n AND m.sender = mj.sender\n LEFT JOIN protected_counts pc ON m.sender = pc.user_id\nWHERE\n m.member_index = 2\nORDER BY\n m.room_id;", 88 "refId": "A", 89 "sql": { 90 "columns": [ 91 { 92 "parameters": [], 93 "type": "function" 94 } 95 ], 96 "groupBy": [ 97 { 98 "property": { 99 "type": "string" 100 }, 101 "type": "groupBy" 102 } 103 ], 104 "limit": 50 105 } 106 } 107 ], 108 "title": "New Panel", 109 "type": "table" 110 } 111 ], 112 "preload": false, 113 "schemaVersion": 41, 114 "tags": [], 115 "templating": { 116 "list": [] 117 }, 118 "time": { 119 "from": "now-1h", 120 "to": "now" 121 }, 122 "timepicker": {}, 123 "timezone": "browser", 124 "title": "D4All V2", 125 "uid": "fe9e4ec8-fe82-458f-8181-7ca3be36c6bb", 126 "version": 1 127 }