accountssystemsreport

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

mgm_id

numeric

131089

√

null

The id of the SUSE Manager instance that contains this data

account_id

numeric

131089

√

null

The id of the user account

username

varchar

64

√

null

The username used to login

organization

varchar

128

√

null

The organization that owns this data

system_id

numeric

131089

√

null

The id of the system

group_name

varchar

2147483647

√

null

The name of the group the user belongs to that grants access to the system

is_admin

bool

1

√

null

true, if the user has administrative role

synced_date

timestamptz

35,6

√

null

The timestamp of when this data was last refreshed.

Relationships

Close relationships within degrees of separation

One

View Definition

WITH org_admins AS (
         SELECT accountgroup.mgm_id,
            accountgroup.account_id
           FROM accountgroup
          WHERE ((accountgroup.account_group_type_label)::text = 'org_admin'::text)
        ), system_users AS (
         SELECT true AS is_admin,
            account.mgm_id,
            account.account_id,
            system.system_id,
            NULL::character varying AS group_name
           FROM (system
             JOIN account ON (((system.mgm_id = account.mgm_id) AND ((system.organization)::text = (account.organization)::text))))
        UNION
         SELECT false AS is_admin,
            systemgrouppermission.mgm_id,
            systemgrouppermission.account_id,
            systemgroupmember.system_id,
            systemgrouppermission.group_name
           FROM (systemgrouppermission
             JOIN systemgroupmember ON (((systemgrouppermission.mgm_id = systemgroupmember.mgm_id) AND (systemgrouppermission.system_group_id = systemgroupmember.system_group_id))))
        ), users_details AS (
         SELECT account.mgm_id,
            account.account_id,
            account.username,
            account.organization,
            (org_admins.account_id IS NOT NULL) AS is_admin,
            account.synced_date
           FROM (account
             LEFT JOIN org_admins ON (((account.mgm_id = org_admins.mgm_id) AND (account.account_id = org_admins.account_id))))
        )
 SELECT users_details.mgm_id,
    users_details.account_id,
    users_details.username,
    users_details.organization,
    system_users.system_id,
    system_users.group_name,
    users_details.is_admin,
    users_details.synced_date
   FROM (users_details
     LEFT JOIN system_users ON (((users_details.mgm_id = system_users.mgm_id) AND (users_details.is_admin = system_users.is_admin) AND (users_details.account_id = system_users.account_id))))
  WHERE (system_users.system_id IS NOT NULL)
  ORDER BY users_details.mgm_id, users_details.account_id, system_users.system_id;

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views