ManageIQ Database Layout


#1

Listing of current db tables and their relationships.

ems = external management system
vim = virtual infrastructure managmeent

Primary Tables

  • accounts - vm host account
    • references resource (type/id)
  • advanced_settings - miq settings
  • assigned_server_roles - miq server roles
    • references miq server (id), server_role (id)
  • audit_events - audit related events on generic targets
    • references target (id/class), user (id)
  • authentications - generic user auth tracking
    • references user (id), resource (type/id)
  • availability_zones - provider organizational unit
  • binary_blob_parts - parts of downloaded binary data
    • references binary_blob (id)
  • binary_blobs - downloaded binary data
  • bottleneck_events - resource bottleneck related events
  • chargeback_rate_details - specific chargeback metrics
    • references chargeback_rate (id)
  • chargeback_rates - groups of chargeback metrics
  • classifications - heirarchies of tags
    • references tag (id), parent (id)
  • cloud_networks - imported cloud networks & metadata
    • references ems (ref/id), cloud_tenant (id), orchestration_stack (id)
  • cloud_object_store_containers - imported cloud object store containers & metadata
    • references ems (ref, id), cloud_tenant (id)
  • cloud_object_store_objects - imported cloud object store objects & metadata
  • cloud_resource_quotas - cloud service limitations
    • references ems (ref, id), cloud_tenant (id)
  • cloud_subnets - imported cloud subnets
    • references ems (ref, id), cloud_network (id)
  • cloud_tenants - miq specific cloud organizational unit
    • groups vms, templates, quotas, volumes, object stores
    • references ems (ref, id)
  • cloud_volume_snapshots - snapshots of cloud volumes
    • references ems (ref, id), cloud_volume (id), cloud_tenant (id)
  • cloud_volumes - imported cloud storage volumes
    • references ems (ref, id), availability_zone (id), cloud_volume_snapshot (id), cloud_tenant (id)
  • compliance_details - policy specific compliance results
    • references compliance (id), miq_policy (id), condition (id)
  • compliances - criteria which resources pass/fail
    • references resource (id, type)
  • computer_systems - abstract managed entity representation
    • references managed_entity (id, type)
  • conditions - restrictions enforced on compliances
    • references miq_policy (id)
  • configuration_locations - location which configurations may reside
    • references provisioning_manager (id), manager (ref), parent (id, ref)
  • configuration_organizations - organization which configuration may be classified
    • references provisioning_manager (id), parent (id, ref)
  • configuration_profiles - specific customization of a configuration
    • references director_operating_system_flavor (id), configuration_manager (id), direct_cutomization_script (ptable_id, medium_id), manager (ref), parent (id, ref), operating_system_flavor (id), customization_script (medium_id, ptable_id)
  • configuration_tags - configuration taxonomy
    • references manager (ref, id)
  • configurations - config which may be applied to systems
    • references miq_server (id)
  • configured_systems - system w/ applied configuration
    • references direct_operating_system_flavor (id), configuration_profile (id), configuration_manager (id), manager (ref), configuration_location (id), configuration_organization (id), direct_customization_script (ptable_id, medium_id), operating_system_flavor (id), customization_script (medium_id, ptable_id)
  • container_definitions - formal definitions of containers
    • references container_group (id)
  • container_env_vars - environment variables defined in containers
    • references container_definition (id)
  • container_groups - container organizational unit
    • references container_node (id), container_replicator (id), container_project (id)
  • container_image_registries - registries recording container image definitions
    • references ems (id)
  • container_images - images containing containers
    • references image (ref), container_image_registry (id)
  • container_node_conditions - status conditions relating to container nodes
    • references container_node (id)
  • container_nodes - node in container
    • references ems (id), lives_on (type, id)
  • container_port_configs - container network config
    • references container_definition (id)
  • container_projects - organization unit organizing replicators
    • references ems (ref, id)
  • container_replicators - container deployment mechanism
    • references ems (ref, id), container_project (id)
  • container_routes - routes defined in containers
    • references ems (ref, id), container_project (id), container_service (id)
  • container_service_port_configs - port configurations used by services
    • references container_service (id)
  • container_services - services provides by containers
    • references ems (ref, id), container_project (id)
  • containers - reproducable operating system environment
    • references container_definition (id), container_image (id)
  • custom_attributes - resource specific settings
    • references resource (type, id)
  • custom_buttons - user interface buttons
    • references applies_to (class, exp, id)
  • customization_scripts - provisioned scripts customizing a system
    • references provisioning_manager_id
  • customization_specs - ?
    • references ems (id)
  • customization_templates - ?
    • references pxe_image_type (id)
  • database_backups - entire db backups
  • dialog_fields - custom dialog ui fields
    • references dialog_group (id)
  • dialog_groups - group of dialogs to display in a dialog tab
    • references dialog_tag (id)
  • dialog_tabs - tab to display in a dialog
    • references dialog (id)
  • dialogs - custom ui dialogs
  • disks - generic disk representation
    • references hardware (id), storage (id), backing (id, type)
  • drift_states - ?
    • references resource (id, type)
  • ems_clusters - computational clusters in an ems
    • references ems (id, ref_obj, ref)
  • ems_events - events taking place in an ems
    • references host (id), vm_or_template (id), dest_host (id), dest_vm_or_template (id), chain (id), ems (id), ems_cluster (id), dest_ems_cluster (id), availability_zone (id), container_node (id), container_group (id)
  • ems_folders - ?
    • references ems (id, ref_obj, ref)
  • event_logs - operating system events
    • references event (id), operating_system (id)
  • ext_management_systems - eg ems, external entity managed by miq
    • references zone (id), provider (id)
  • file_depots - uri based file locations
  • filesystems - filesystems residing on various resources
    • references miq_set (id), scan_item (id), resource (type, id)
  • firewall_rules - firewall rules residing on various resources
    • references ems (Ref), resource (id, type), source_security_group (id)
  • flavors - cloud environments which may be deployed
    • references ems (id, ref)
  • floating_ips - floating ips residing on a cloud tenant
    • references ems (ref, id), cloud_tenant (id)
  • guest_applications - apps residing in cloud guest environments
    • references vm_or_template (id), host (id)
  • guest_devices - devices residing in cloud guest environments
    • references hardware (id), switch (id), lan (id)
  • hardwares - ?
    • references vm_or_template (id), host (id)
  • host_service_groups - ?
    • references host (id)
  • hosts - backend provider host machine
    • references ems (id, ref_obj, ref), ems_cluster (id), availability_zone (id)
  • import_file_uploads - file uploads imported into miq
  • iso_datastores - locations which isos reside
    • references ems (id)
  • iso_images - images stored on isos
    • references iso_datastore (id), pxe_image_type (id)
  • jobs - queue jobs created by worker agents
    • references target (id, class), agent (id, class, state, message), miq_server (id)
  • lans - local area networks residing in various cloud environments
    • references switch (id)
  • ldap_domains - imported ldap dns data
    • references ldap_domain (id), ldap_region (id)
  • ldap_groups - imported ldap groups
    • references ldap_domain (id)
  • ldap_regions - imported ldap regions
    • references zone (id)
  • ldap_servers - imported ldap servers
    • references ldap_domain (id)
  • ldap_users - imported ldap users
    • references ldap_domain (id)
  • lifecycle_events - vm / template lifecycle events
    • references vm_or_template (id)
  • log_files - tracking of miq log files
    • references resource (id, type), file_depot (id)
  • metrics - statistics collected from resources
    • references resource (id, type), parent_ems_cluster (id), parent_storage (id), parent_ems (id), time_profile (id)
  • miq_actions - generic actions which may be scheduled
  • miq_ae_classes - classes of action events
    • references namespace (id), updated_by_user (id)
  • miq_ae_fields - fields on action event class methods
    • references class (id), method (id), updated_by_user (id)
  • miq_ae_instances - instances of action event classes
    • references class (id), updated_by_user (id)
  • miq_ae_methods - methods on action event classes
    • references class (id)
  • miq_ae_namespaces - namespaces containing action event classes
    • references parent (id), updated_by_user (id)
  • miq_ae_values - values passed to action event fields
    • references instance (id), field (id), update_by_user (id)
  • miq_ae_workspaces - ?
  • miq_alert_statuses - resource alert details
    • references resource (id, type)
  • miq_alerts - alerts raised by miq subsystems
  • miq_approvals - approval state tracking on generic targets
    • references miq_request (id), stamper (name, id), approver (id, type, name)
  • miq_cim_associations - mappings between miq_cim instances and other entities
    • references assoc (class), result (class, role, id), result_obj (name), miq_cim_instance (id), zone (id)
  • miq_cim_derived_metrics - metrics pertaining to miq_cim instances
    • references miq_storage_metric (iod)
  • miq_cim_instances - miq common instance model instances
  • miq_databases - record of databases storing miq data
  • miq_dialogs - custom ui dialogs
  • miq_enterprises - ?
  • miq_events - miq specific events
  • miq_globals - ?
  • miq_groups - miq specific user groups
    • references resource (type,id), miq_user_role (id), tenant_owner (id)
  • miq_policies - security policies on generic objects
  • miq_policy_contents - applications of miq_policies to specific events / actions
    • references miq_policy (id), miq_action (id), miq_event (id)
  • miq_product_features - features miq implementation supports
    • references parent (id)
  • miq_queue - central miq worker queue
    • references target (id), task (id), instance (id), handler (id, type), for_user (id)
  • miq_regions - miq specific organizational unit for cloud resources
  • miq_report_result_details - specific details pertaining to report items
    • references miq_report_result (id)
  • miq_report_results - results from a miq report
    • references miq_report (id), miq_task (id), miq_group (id)
  • miq_reports - reports generated by miq
    • references time_profile (id), miq_group (id)
  • miq_request_tasks - tasks launched by requests to miq
    • references user (id), miq_request (id), source (id), destination (id, type), miq_request_task (id)
  • miq_requests - record of requests made to the miq webapp
    • references requester (id, name), source (id, type), destination (id, type)
  • miq_schedules - used to schedule tasks
    • references miq_search (id), zone (id), file_depot (id)
  • miq_scsi_luns - imported scsi luns
    • references miq_scsi_target (id)
  • miq_scsi_targets - imported scsi targets
    • references guest_device (id)
  • miq_searches - searches saved in the ui
  • miq_servers - servers running the miq webapp
    • references zone (id), log_file_depot (id)
  • miq_sets - ?
    • references owner (id), group (id)
  • miq_shortcuts - ?
  • miq_storage_metrics - metrics collected from cloud storage
  • miq_tasks - tasks scheduled on the miq work queue
    • references miq_server (id)
  • miq_user_roles - roles which users may be assigned to
  • miq_widget_contents - contents of ui widgets
    • references miq_widget (id), miq_report_result (id), miq_group (id), user (id)
  • miq_widget_shortcuts - associate shortcuts with widgets to display them
    • references miq_shortcut (id), miq_widget (id)
  • miq_widgets - custom ui widgets
    • references miq_schedule (id), miq_task (id)
  • miq_workers - record of workers managed by miq
    • references miq_server (id)
  • networks - imported cloud networks
    • references hardware (id), device (id)
  • ontap_aggregate_derived_metrics -
    • references miq_storage_metric (id), miq_cim_instance (id), storage_metrics_metadata (id)
  • ontap_disk_derived_metrics -
    • references
  • ontap_lun_derived_metrics -
    • references
  • ontap_system_derived_metrics -
    • references
  • ontap_volume_derived_metrics -
    • references
  • operating_system_flavors - types of operating systems that may be provisioned
    • references provisioning_manager (id)
  • operating_systems - operating systsem information associated w/ various entities
    • references vm_or_template (id), computer_system (id)
  • orchestration_stack_outputs - outputs associated with an orchestration stack
    • references stack (id)
  • orchestration_stack_parameters - parameters associated with an orchestration stack
    • references stack (id)
  • orchestration_stack_resources - resources managed by orchestration stacks
    • references stack (id)
  • orchestration_stacks - management mechanism with optional retirement
    • references ems (ref, id), orchestration_template (id), cloud_tenant (id)
  • orchestration_templates - templates used to orchestrate systems
  • os_processes - imported operating system processes
    • references operating_system (id)
  • partitions - imported partition data
    • references disk (id), hardware (id)
  • patches - windows service pack patches applied against various resources
    • references vm_or_template (id), host (id)
  • pictures - ?
    • references resource (id, type)
  • policy_event_contents - detailed policy event data
    • references policy_event (id), resource (id, type)
  • policy_events - events related to miq policies
    • references miq_policy (id), target (id, class, name), chain (id), host (id), ems_cluster (id), ems (id)
  • providers - actual cloud providers
    • references tenant_owner (id)
  • pxe_image_types - record of types of pxe images
  • pxe_images - imported pxe images
    • references pxe_server (id), pxe_menu (id), pxe_image_type (id)
  • pxe_menus - imported pxe server menu data
    • references pxe_server (id)
  • pxe_servers - imported pxe servers
  • registry_items - contents of windows registries
    • references miq_set (id), scan_item (id), vm_or_template (id)
  • relationships - generic associations between resources and other entities
    • references resource (type, id)
  • repositories - ?
    • references storage (id)
  • reserves - allocated / claimed resources
    • references resource (type, id)
  • resource_actions - dialog launched actions on resources resulting in an action event
    • references dialog (id), resource (id, type), ae (namespace, class, instance)
  • resource_pools - groups of resources w/ reserves and limits
    • references ems (id, ref, ref_obj)
  • rss_feeds - rss feeds provided by the application
  • scan_histories - record of scans performed against vms / templates
    • references vm_or_template (id), task (id)
  • scan_items - items to be scanned
  • schema_migrations - standard rails schema migrations table
  • security_groups - imported security groups
    • references ems (id, ref), cloud_network (id), cloud_tenant (id), orchestration_stack (id)
  • server_roles - enumeration of roles a miq server can fullfil
  • service_resources - resources provided by / available on services
    • references service_template (id), service (id), source (id, type)
  • service_template_catalogs - groupings of service templates
  • service_templates - templates describing services
    • references service_template (id), evm_owner (id), miq_group (id), service_template_catalog (id)
  • services - cloud services provided by miq
    • references service_template (id), evm_owner (id), miq_group (id), service (id)
  • sessions - user sessions
  • snapshots - imported snapshots of cloud vms
    • references parent (id), vm_or_template (id), em (ref_obj, ref)
  • storage_files - recording of files stored on cloud entities
    • references storage (id), vm_or_template (id)
  • storage_managers - locations of storage management
    • references parent_agent (id)
  • storage_metrics_metadata - storage metadata
  • storages - imported cloud storage
    • references ems (ref, ref_obj)
  • switches - imported network switches
    • references host (id),
  • system_services - services running on various systems
    • references vm_or_template (id), host (id), host_service_group (id)
  • taggings - maps tags to taggable items
    • references tag (id), taggable (id, type)
  • tags - generic taxonomy that can be applied against entities
  • tenant_resources - mapping between resources and tenants which they reside
    • references tenant (id), resource (id, type)
  • tenants - miq specific cloud organizational unit
  • time_profiles - enumeration of time profiles
  • users - record of system users
    • references current_group (id)
  • vim_performance_operating_ranges - performance ranges pertaining to resources
    • references resource (id, type), time_profile (id)
  • vim_performance_states - performance states pertaining to resources
    • references resource (id, type)
  • vim_performance_tag_values - tags & values associated to performance metrics
    • references assoc (ids), metric (id, type)
  • vmdb_database_metrics - miq metrics collected
    • references vmdb_database (id)
  • vmdb_databases - miq (vmdb) databases
  • vmdb_indexes - record of indices on various db tables
    • references vmdb_Table (id)
  • vmdb_metrics - db metrics collected
    • references resource (id, type)
  • vmdb_tables - tables in the miq database
  • vms - imported cloud vms
    • references host (id), storage (id), ems (id), evm_owner (id), miq_group (id), ems_cluster (id), flavor (id), cloud_network (id), cloud_subnet (id), cloud_tenant (id), tenant_owner (id)
  • volumes - imported filesystem volumes
    • references hardware (id)
  • windows_images - images of windows systems available for provisioning
    • references pxe_servier (id), pxe_image_type (id)
  • zones - regions which cloud resources may be allocated
    • references log_file_depot (id)

Bridge Tables

  • cloud_tenants_vms -
    • references cloud_tenants (id), vms (id)
  • conditions_miq_policies -
    • references conditions (id), miq_policies (id)
  • configuration_locations_configuration_profiles
    • references configuration_locations (id), configuration_profiles (id)
  • configuration_organizations_configuration_profiles
    • references configuration_organizations (id), configuration_profiles (id)
  • configuration_profiles_configuration_tags
    • references configuration_profiles (id), configuration_tag (id)
  • configuration_tags_configured_systems
    • references configuration_tags (id), configured_systems (id)
  • container_groups_container_services
    • references container_groups (id), container_services (id)
  • customization_scripts_operating_system_flavors
    • references customization_scripts (id), operating_system_flavors (id)
  • direct_configuration_tags_configured_systems
    • references direct_configuration_tags (id), configured_systems (id)
  • hosts_storages
    • references storage (id), host (id)
  • key_pairs_vms
    • references key_pairs (id), vms (id)
  • ldap_managements
    • references manager_id (id), ldap_user (id)
  • miq_groups_users
    • references miq_groups (id), user (id)
  • miq_roles_features
    • references miq_user_role (id), miq_product_feature (id)
  • miq_servers_product_updates
    • product_update (id), miq_server (id)
  • security_groups_vms
    • references security_group (id), vm (id)
  • storages_vms_and_templates
    • references storage (id), vm_or_template (id)

Rollups

  • metric_rollups (& 01-12)
  • metrics_00 - metrics_23
  • ontap_aggregate_metrics_rollups
  • ontap_disk_metrics_rollups
  • ontap_lun_metrics_rollups
  • ontap_system_metrics_rollups
  • ontap_volume_metrics_rollups

#2

Actually, VIM is “Virtual Infrastructure Management”, a term used specifically by VMware. “vim” in the table names is a legacy term, as we don’t just store vim specifics in there (although one could argue that the metrics are all in “vim style” cc @blomquisg).

Also, please post this and all the other docmentation efforts to ManageIQ/guides. Thanks!


#3

Hey @mmorsi, this is good stuff.

Take a look at ManageIQ/guides. I’m thinking this would probably better fit there, no?


#4

D’oh, @Fryguy beat me to it :slight_smile:


#5

@Fryguy @blomquisg that’s fine, how often is the website sync’d from the git repo?


#6

The DB UML can be seen here:

(Source Dia: http://people.redhat.com/~mmorsi/cfme_db.dia)


#7

Looks cool @mmorsi ! Can you change the title to have the SHA of the commit this was generated against? I think that would be a lot more accurate than just the month/year.


#8

Alternative version (split up)


#9

Note, most likely this is a bit outdated though large portions should still be applicable.

The process of automating this generation of this doc is still underway. (help is appreciated, just ping me!)