Skip to content

Commit d2436b9

Browse files
disqdisq
authored
fix(azure): Migrate Grafana dashboards (#2628)
plus a policy fix, apparently updating some tables policies wasn't updated.
1 parent 94b3e83 commit d2436b9

File tree

7 files changed

+23
-37
lines changed

7 files changed

+23
-37
lines changed

‎plugins/source/azure/dashboards/grafana/asset_inventory.json‎

Lines changed: 9 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -123,7 +123,7 @@
123123
"group": [],
124124
"metricColumn": "none",
125125
"rawQuery": true,
126-
"rawSql": "select count(*) from azure_resources where subscription_id in (${subscription_ids}) and location in (${locations}) and cq_table in (${cq_tables}) ;",
126+
"rawSql": "select count(*) from azure_resources where subscription_id in (${subscription_ids}) and location in (${locations}) and _cq_table in (${cq_tables}) ;",
127127
"refId": "A"
128128
}
129129
],
@@ -183,7 +183,7 @@
183183
"group": [],
184184
"metricColumn": "none",
185185
"rawQuery": true,
186-
"rawSql": "select subscription_id, count(*) from azure_resources where location in (${locations}) and cq_table in (${cq_tables}) group by subscription_id order by count desc;",
186+
"rawSql": "select subscription_id, count(*) from azure_resources where location in (${locations}) and _cq_table in (${cq_tables}) group by subscription_id order by count desc;",
187187
"refId": "A"
188188
}
189189
],
@@ -295,19 +295,7 @@
295295
{
296296
"matcher": {
297297
"id": "byName",
298-
"options": "cq_meta"
299-
},
300-
"properties": [
301-
{
302-
"id": "custom.width",
303-
"value": 392
304-
}
305-
]
306-
},
307-
{
308-
"matcher": {
309-
"id": "byName",
310-
"options": "cq_table"
298+
"options": "_cq_table"
311299
},
312300
"properties": [
313301
{
@@ -343,7 +331,7 @@
343331
{
344332
"matcher": {
345333
"id": "byName",
346-
"options": "fetch_date"
334+
"options": "_cq_sync_time"
347335
},
348336
"properties": [
349337
{
@@ -355,7 +343,7 @@
355343
{
356344
"matcher": {
357345
"id": "byName",
358-
"options": "cq_id"
346+
"options": "_cq_id"
359347
},
360348
"properties": [
361349
{
@@ -395,7 +383,7 @@
395383
"group": [],
396384
"metricColumn": "none",
397385
"rawQuery": true,
398-
"rawSql": "select cq_id, cq_table, subscription_id, location, id, fetch_date from azure_resources where subscription_id in (${subscription_ids}) and location in (${locations}) and cq_table in (${cq_tables}) ;",
386+
"rawSql": "select _cq_id, _cq_table, subscription_id, location, id, _cq_sync_time from azure_resources where subscription_id in (${subscription_ids}) and location in (${locations}) and _cq_table in (${cq_tables}) ;",
399387
"refId": "A",
400388
"select": [
401389
[
@@ -468,7 +456,7 @@
468456
"group": [],
469457
"metricColumn": "none",
470458
"rawQuery": true,
471-
"rawSql": "select cq_table, count(*) from azure_resources where subscription_id in (${subscription_ids}) and location in (${locations}) group by cq_table order by count desc;",
459+
"rawSql": "select _cq_table, count(*) from azure_resources where subscription_id in (${subscription_ids}) and location in (${locations}) group by _cq_table order by count desc;",
472460
"refId": "A"
473461
}
474462
],
@@ -605,13 +593,13 @@
605593
},
606594
{
607595
"current": {},
608-
"definition": "select distinct(cq_table) from azure_resources;",
596+
"definition": "select distinct(_cq_table) from azure_resources;",
609597
"hide": 0,
610598
"includeAll": true,
611599
"multi": true,
612600
"name": "cq_tables",
613601
"options": [],
614-
"query": "select distinct(cq_table) from azure_resources;",
602+
"query": "select distinct(_cq_table) from azure_resources;",
615603
"refresh": 1,
616604
"regex": "",
617605
"skipUrlSync": false,

‎plugins/source/azure/dashboards/grafana/compliance.json‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -200,7 +200,7 @@
200200
"type": "postgres",
201201
"uid": "${DS_POSTGRESQL}"
202202
},
203-
"description": "Time since the most recent CloudQuery fetch happened.",
203+
"description": "Time since the most recent CloudQuery sync happened.",
204204
"fieldConfig": {
205205
"defaults": {
206206
"color": {
@@ -254,11 +254,11 @@
254254
"group": [],
255255
"metricColumn": "none",
256256
"rawQuery": true,
257-
"rawSql": "select (cq_meta::JSONB->>'last_updated')::timestamp with time zone as time, 1 from azure_compute_virtual_machines order by time desc limit 1;",
257+
"rawSql": "select _cq_sync_time as time, 1 from azure_compute_virtual_machines order by time desc limit 1;",
258258
"refId": "A"
259259
}
260260
],
261-
"title": "Last Fetch",
261+
"title": "Last Sync",
262262
"type": "stat"
263263
},
264264
{

‎plugins/source/azure/policies_v1/queries/authorization/custom_roles.sql‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,12 +3,12 @@ SELECT :'execution_time'
33
:'framework' AS framework,
44
:'check_id' AS check_id,
55
'External accounts with owner permissions should be removed from your subscription' AS title,
6-
sub.subscription_id AS subscription_id,
6+
mc.subscription_id AS subscription_id,
77
mc.id AS resource_id,
88
CASE
99
WHEN enable_rbac IS NOT TRUE
1010
THEN 'fail'
1111
ELSE 'pass'
1212
END AS status
1313
FROM azure_container_managed_clusters AS mc
14-
INNER JOIN azure_subscriptions AS sub ON sub.subscription_id = mc.subscription_id
14+
INNER JOIN azure_subscriptions AS sub ON sub.id = mc.subscription_id

‎plugins/source/azure/policies_v1/queries/monitor/activitylog_administrativeoperations_audit.sql‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -29,13 +29,13 @@ SELECT
2929
:'framework',
3030
:'check_id',
3131
'An activity log alert should exist for specific Administrative operations',
32-
azure_subscriptions.subscription_id,
33-
azure_subscriptions.subscription_id
32+
azure_subscriptions.id,
33+
azure_subscriptions.id
3434
FROM
3535
azure_subscriptions
36-
LEFT JOIN alert_condition A ON azure_subscriptions.subscription_id = A.subscription_id
36+
LEFT JOIN alert_condition A ON azure_subscriptions.id = A.subscription_id
3737
WHERE
3838
A.subscription_id IS NULL
3939
GROUP BY
40-
azure_subscriptions.subscription_id,
40+
azure_subscriptions.id,
4141
display_name;

‎plugins/source/azure/policies_v1/queries/sql/sqlserverauditing_audit.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@ SELECT
88
azure_subscriptions.display_name AS subscription_name,
99
case
1010
when azure_sql_server_blob_auditing_policies.sql_server_id = azure_sql_servers.id
11-
AND azure_subscriptions.subscription_id = azure_sql_servers.subscription_id
11+
AND azure_subscriptions.id = azure_sql_servers.subscription_id
1212
AND azure_sql_server_blob_auditing_policies.state = 'Disabled'
1313
then 'fail' else 'pass'
1414
end

‎plugins/source/azure/views/README.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,4 +4,4 @@ This directory contains useful aggregated views that you can create against the
44

55
Those views are also tests in CI to make sure it works with the latest schema.
66

7-
For dashboard examples that reuse those views take a look at [https://github.com/cloudquery/dashboards](https://github.com/cloudquery/dashboards).
7+
For dashboard examples that reuse those views take a look at [dashboards](../dashboards).

‎plugins/source/azure/views/resource.sql‎

Lines changed: 3 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -22,21 +22,19 @@ LOOP
2222
-- create an SQL query to select from table and transform it into our resources view schema
2323
-- we use the double reverse here because split_part with negative indexes is not available in PostgreSQL < 14; https://pgpedia.info/postgresql-versions/postgresql-14.html#system_function_changes
2424
strSQL = strSQL || format('
25-
SELECT cq_id, cq_meta, %L as cq_table, subscription_id, reverse(split_part(reverse(id), ''/''::TEXT, 1)) as id,
26-
%s as name, %s as kind, %s as location,
27-
COALESCE(%s, (cq_meta->>''last_updated'')::timestamp) as fetch_date
25+
SELECT _cq_id, _cq_source_name, _cq_sync_time, %L as _cq_table, subscription_id, reverse(split_part(reverse(id), ''/''::TEXT, 1)) as id,
26+
%s as name, %s as kind, %s as location
2827
FROM %s',
2928
tbl,
3029
CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE column_name='name' AND table_name=tbl) THEN 'name' ELSE 'NULL' END,
3130
CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE column_name='kind' AND table_name=tbl) THEN 'kind' ELSE 'NULL' END,
3231
CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE column_name='location' AND table_name=tbl) THEN 'location' ELSE E'\'unavailable\'' END,
33-
CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE column_name='fetch_date' AND table_name=tbl) THEN 'fetch_date' ELSE 'NULL::timestamp' END,
3432
tbl);
3533

3634
END LOOP;
3735

3836
IF strSQL = ''::TEXT THEN
39-
RAISE EXCEPTION 'No tables found with ID and SUBSCRIPTION_ID columns. Run a fetch first and try again.';
37+
RAISE EXCEPTION 'No tables found with ID and SUBSCRIPTION_ID columns. Run a sync first and try again.';
4038
ELSE
4139
EXECUTE FORMAT('CREATE VIEW azure_resources AS (%s)', strSQL);
4240
END IF;

0 commit comments

Comments
 (0)