vault backup: 2025-04-04 14:31:53

1 files changed, 77 insertions, 0 deletions

diff --git a/Updating the image os admin user to fix password resets.md b/Updating the image os admin user to fix password resets.md
new file mode 100644
index 0000000..48852de
--- /dev/null
+++ b/Updating the image os admin user to fix password resets.md
@@ -0,0 +1,77 @@
+---
+tags:
+  - work
+  - nova
+---
+
+**instances**
+```
++-------------+-----------------------+------+-----+---------+----------------+
+| Field       | Type                  | Null | Key | Default | Extra          |
++-------------+-----------------------+------+-----+---------+----------------+
+| deleted_at  | datetime              | YES  |     | NULL    |                |
+| uuid        | varchar(36)           | NO   | UNI | NULL    |                |
++-------------+-----------------------+------+-----+---------+----------------+
+```
+**instance_system_metadata**
+```
++---------------+--------------+------+-----+---------+----------------+
+| Field         | Type         | Null | Key | Default | Extra          |
++---------------+--------------+------+-----+---------+----------------+
+| created_at    | datetime     | YES  |     | NULL    |                |
+| updated_at    | datetime     | YES  |     | NULL    |                |
+| deleted_at    | datetime     | YES  |     | NULL    |                |
+| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
+| instance_uuid | varchar(36)  | NO   | MUL | NULL    |                |
+| key           | varchar(255) | NO   |     | NULL    |                |
+| value         | varchar(255) | YES  |     | NULL    |                |
+| deleted       | int(11)      | YES  |     | NULL    |                |
++---------------+--------------+------+-----+---------+----------------+
+```
+
+**Insert new record**
+```
+insert into instance_system_metadata (created_at, instance_uuid, `key`, `value`) values (NOW(), "4e675ad6-f01a-476f-8827-8300e540447c", "image_os_admin_user", "administrator"
+```
+
+**Updated existing values**
+```
+update instance_system_metadata set `value` = "administrator", updated_at = NOW() where deleted_at is NULL and `key` = "image_os_admin_user" and instance_uuid in (select instance_uuid from instance_system_metadata where deleted_at is NULL and `key` = "image_os_t
+ype" and `value` = "linux");
+```
+
+**Which instances currently do not have image_os_admin_user**
+```
+select i.uuid from instances i left join instance_system_metadata ism on ism.instance_uuid = i.uuid and ism.key = "image_os_admin_user" where i.deleted_at is null and ism.id is null
+```
+
+**Which instances of those above, do have qemu guest agent stuff set**
+```
+select * from instance_system_metadata ism1 where ism1.instance_uuid in (select i.uuid from instances i left join instance_system_metadata ism on ism.instance_uuid = i.uuid and ism.key = "image_os_admin_user" where i.deleted_at is null and ism.id is null) and ism1.key = "image_hw_qemu_guest_agent";
+```
+
+**Insert the image_os_admin_user for those records that miss it but do have qemu guest agent metadata**
+```
+INSERT INTO instance_system_metadata (created_at, instance_uuid, `key`, `value`, deleted)
+SELECT
+    NOW(),                      -- Current timestamp for created_at
+    i.uuid,                     -- The UUID of the instance needing the new key
+    'image_os_admin_user',      -- The fixed key you want to insert
+    'administrator',            -- The fixed value for the new key
+    0
+FROM
+    instances i
+INNER JOIN -- Use INNER JOIN to ensure the instance MUST have the guest agent key
+    instance_system_metadata ism_agent ON ism_agent.instance_uuid = i.uuid
+                                     AND ism_agent.key = 'image_hw_qemu_guest_agent'
+LEFT JOIN -- Use LEFT JOIN to check for the PRESENCE of the admin user key
+    instance_system_metadata ism_admin ON ism_admin.instance_uuid = i.uuid
+                                     AND ism_admin.key = 'image_os_admin_user'
+WHERE
+    i.deleted_at IS NULL        -- Ensure the instance is not deleted
+    AND ism_admin.instance_uuid IS NULL; -- This is the crucial part: Select only rows where the LEFT JOIN did NOT find an existing 'image_os_admin_user' key
+```
+
+```
+insert into instance_system_metadata (created_at, instance_uuid, `key`, `value`, deleted) select NOW(), i.uuid, 'image_os_admin_user', 'administrator', 0 from instances i inner join instance_system_metadata ism_agent on ism_agent.instance_uuid = i.uuid and ism_agent.key = 'image_hw_qemu_guest_agent' left join instance_system_metadata ism_admin on ism_admin.instance_uuid = i.uuid and ism_admin.key = 'image_os_admin_user' where i.deleted_at is null and ism_admin.instance_uuid is null;
+```
\ No newline at end of file