inventoryreport

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

mgm_id

numeric

√

null

The id of the SUSE Manager instance that contains this data

system_id

numeric

√

null

The id of the system

profile_name

varchar

128

√

null

The unique descriptive name of the system

hostname

varchar

128

√

null

The hostname that identifies this system

minion_id

varchar

256

√

null

The identifier of the minion, if the system is a Salt Minion

machine_id

varchar

256

√

null

The identifier of the machine

registered_by

varchar

√

null

The user account who onboarded this system

registration_time

timestamptz

35,6

√

null

When this system was onboarded

last_checkin_time

timestamptz

35,6

√

null

When this system was visible and reachable last time

kernel_version

varchar

√

null

The version of the kernel installed on this system

organization

varchar

128

√

null

The organization that owns this data

architecture

varchar

√

null

The architecture of the system

hardware

text

2147483647

√

null

A brief description of the hardware specification of this system

primary_interface

varchar

√

null

The name of the system primary network interface

hardware_address

varchar

√

null

The MAC address of the network interface

ip_address

varchar

√

null

The IPv4 address of the primary network interface of the system

ip6_addresses

text

2147483647

√

null

The list of IPv6 addresses and their scopes of the primary network interface of the system, separated by ;

configuration_channels

text

2147483647

√

null

The list of configuration channels the system is subscribed to, separated by ;

entitlements

text

2147483647

√

null

The list of entitlements of the system, separated by ;

system_groups

text

2147483647

√

null

The list of groups of the system, separated by ;

virtual_host

numeric

√

null

The id of the host of the system, if any

is_virtualized

bool

√

null

True if the system is virtualized

virt_type

varchar

128

√

null

The type of virtualization, if the system is virualized

software_channels

text

2147483647

√

null

THe list of software channels the system is subscribed to, separated by ;

packages_out_of_date

int8

√

null

The number of packages installed on the system that can be updated

errata_out_of_date

int8

√

null

The number of patches that can be applied to the system

synced_date

timestamptz

35,6

√

null

The timestamp of when this data was last refreshed.

Relationships

Close relationships within degrees of separation

View Definition

WITH entitlements AS (
         SELECT systementitlement.mgm_id,
            systementitlement.system_id,
            string_agg(((systementitlement.system_group_id || ' - '::text) || (systementitlement.name)::text), ';'::text) AS entitlements
           FROM systementitlement
          GROUP BY systementitlement.mgm_id, systementitlement.system_id
        ), groups AS (
         SELECT systemgroupmember.mgm_id,
            systemgroupmember.system_id,
            string_agg(((systemgroupmember.system_group_id || ' - '::text) || (systemgroupmember.group_name)::text), ';'::text) AS system_groups
           FROM systemgroupmember
          GROUP BY systemgroupmember.mgm_id, systemgroupmember.system_id
        ), configchannels AS (
         SELECT systemconfigchannel.mgm_id,
            systemconfigchannel.system_id,
            string_agg(((systemconfigchannel.config_channel_id || ' - '::text) || (systemconfigchannel.name)::text), ';'::text) AS configuration_channels
           FROM systemconfigchannel
          GROUP BY systemconfigchannel.mgm_id, systemconfigchannel.system_id
        ), channels AS (
         SELECT systemchannel.mgm_id,
            systemchannel.system_id,
            string_agg(((systemchannel.channel_id || ' - '::text) || (systemchannel.name)::text), ';'::text) AS software_channels
           FROM systemchannel
          GROUP BY systemchannel.mgm_id, systemchannel.system_id
        ), v6addresses AS (
         SELECT systemnetaddressv6.mgm_id,
            systemnetaddressv6.system_id,
            systemnetaddressv6.interface_id,
            string_agg(((((systemnetaddressv6.address)::text || ' ('::text) || (systemnetaddressv6.scope)::text) || ')'::text), ';'::text) AS ip6_addresses
           FROM systemnetaddressv6
          GROUP BY systemnetaddressv6.mgm_id, systemnetaddressv6.system_id, systemnetaddressv6.interface_id
        )
 SELECT system.mgm_id,
    system.system_id,
    system.profile_name,
    system.hostname,
    system.minion_id,
    system.machine_id,
    system.registered_by,
    system.registration_time,
    system.last_checkin_time,
    system.kernel_version,
    system.organization,
    system.architecture,
    system.hardware,
    systemnetinterface.name AS primary_interface,
    systemnetinterface.hardware_address,
    systemnetaddressv4.address AS ip_address,
    v6addresses.ip6_addresses,
    configchannels.configuration_channels,
    entitlements.entitlements,
    groups.system_groups,
    systemvirtualdata.host_system_id AS virtual_host,
    (systemvirtualdata.virtual_system_id IS NULL) AS is_virtualized,
    systemvirtualdata.instance_type_name AS virt_type,
    channels.software_channels,
    COALESCE(systemoutdated.packages_out_of_date, (0)::bigint) AS packages_out_of_date,
    COALESCE(systemoutdated.errata_out_of_date, (0)::bigint) AS errata_out_of_date,
    system.synced_date
   FROM (((((((((system
     LEFT JOIN systemvirtualdata ON (((system.mgm_id = systemvirtualdata.mgm_id) AND (system.system_id = systemvirtualdata.virtual_system_id))))
     LEFT JOIN systemoutdated ON (((system.mgm_id = systemoutdated.mgm_id) AND (system.system_id = systemoutdated.system_id))))
     LEFT JOIN systemnetinterface ON (((system.mgm_id = systemnetinterface.mgm_id) AND (system.system_id = systemnetinterface.system_id) AND systemnetinterface.primary_interface)))
     LEFT JOIN systemnetaddressv4 ON (((system.mgm_id = systemnetaddressv4.mgm_id) AND (system.system_id = systemnetaddressv4.system_id) AND (systemnetinterface.interface_id = systemnetaddressv4.interface_id))))
     LEFT JOIN v6addresses ON (((system.mgm_id = v6addresses.mgm_id) AND (system.system_id = v6addresses.system_id) AND (systemnetinterface.interface_id = v6addresses.interface_id))))
     LEFT JOIN entitlements ON (((system.mgm_id = entitlements.mgm_id) AND (system.system_id = entitlements.system_id))))
     LEFT JOIN groups ON (((system.mgm_id = groups.mgm_id) AND (system.system_id = groups.system_id))))
     LEFT JOIN configchannels ON (((system.mgm_id = configchannels.mgm_id) AND (system.system_id = configchannels.system_id))))
     LEFT JOIN channels ON (((system.mgm_id = channels.mgm_id) AND (system.system_id = channels.system_id))))
  ORDER BY system.mgm_id, system.system_id;

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views