How to get the Source Iamge name from Instace from a Query

Automation/IaC
,
1

I need a Query to get the instance Source image, any idea which table i need to look for that?

2

It Looks like the table “compute_server” has a row “source_image_id”. This id can then be used in the “virtual_image” table

1 Like
3

Thank you so Much Bob!

4

This SQL snippet will get you from instance ID to the virtual image, but it looks like there’s a more direct way in the solution, which is great to know :+1:

select i.id as instanceId, i.name as instanceName, vi.id as imageId, vi.name as imageName from instance i inner join instance_type_layout_container_type_set ic inner join container_type_set cts inner join container_type ct inner join virtual_image vi where i.layout_id = ic.instance_type_layout_containers_id and ic.container_type_set_id = cts.id and cts.container_type_id = ct.id and ct.virtual_image_id = vi.id and i.id = 2;
5

That Query is returning me zero results.

6

It should work. You’ve changed the ‘id’ value at the end of the query to represent an instance on your appliance?

Edit: I probably should have mentioned that TBF :wink:

7

Thank you, it is working now

1 Like
8

@Ollie_Phillips @rboyd , i need help with one more Query,
We have a user, and we need to see what Groups ( Infrastructure → Groups) that user have access too.

9

Nothing I can immediately offer on that one sorry. But for more eyeballs you should post the question as a new topic. I only saw this by chance.

10

As a starter, permissions are like this in Morpheus:

Cloud → Group → Role → User

So you’d be including, Group, Role & User type tables in your SQL query to get at the info you need (I would expect)

11

No luck yet, i will spend some time with some tables now, if i still dont find it then i will open a new post.

12

Really you should be looking at what roles/authorities are assigned to the user, then look up those roles to see which groups/sites are access full, then you can look up those groups/sites to see what clouds/zones are associated.

Ideally this would be done via the API

1 Like
13

@cbunge i see what role is assigned to a users, when i look at that role in the table, i cant see what Groups that role is giving access to. Do you know which tabel we need to look for in DB to see what group access we have for a role?

14

Need to utilize the role_compute_site table and associate role_id with the authority / role, and site_id is the group

1 Like