Technical white paper HP Device Manager 4.6 Database Schema Guide Table of contents Overview .......................................................................................................................................................................................... 4 Tables............................................................................................................................................................................................... 5 Repository-related tables ..
dm_gateway ........................................................................................................................................................................ 31 dm_gateway_walking_scope .......................................................................................................................................... 32 dm_gateway_walkingtask ...........................................................................................................................................
For more information .................................................................................................................................................................
Overview The purpose of this document is to explain the database schema of HP Device Manager 4.6. Also, this document will provide some examples of how to use tables to produce a desired report.
Tables Repository-related tables dm_repositories This is the repository table. 1. Table columns Column name Type name Column size Nullable Primary key Foreign key Description repo_id int 10 NO √ dm_repo_mapping.repo_id; Repository ID dm_repo_protocols.repo_id 2. repo_address nvarchar 255 NO Repository address repo_name nvarchar 50 NO Repository name sync_date datetime 23 YES Last synchronization time Relevant UI.
dm_repo_protocols This is the repository protocols table. 1.
dm_repo_mapping This is the repository mapping table. 1. Table columns Column name Type name Column size Nullable Primary key Description repo_id int 50 NO √ Repository ID category int 10 NO Mapping type: 1: Map by gateway 2: Map by subnet 3: Map by device map key nvarchar 10 NO √ Map key: Gateway id; Subnet address; Device_id 2. 8 Relevant UI.
Device-related tables dm_devices This is the devices table. 1. Table columns Column name Type name Colu mn size Nullable Primary key Foreign key Description device_id nvarchar 50 NO √ dm_group_values.device_id; Device ID dm_inv_display.device_id; dm_inv_ewf.device_id; dm_inv_ex_property.device_id; dm_inv_hardware.device_id; dm_inv_max_hotfix.device_id; dm_inv_ms_hotfix.device_id; dm_inv_nic.device_id; dm_inv_partition.device_id; dm_inv_software.device_id; dm_inv_time.
on pull_interval smallint 5 YES Pull interval update_date datetime 23 NO Update date vnc_pwd nvarchar 32 YES VNC password grouping int 10 YES Manual grouping path ID, reported by device tpm_owned nvarchar 3 YES Device owns TPM module has_tpm nvarchar 3 YES Device has TPM module Asset_tage nvarchar 200 YES Asset tag Bios_version nvarchar 20 YES BIOS version 2.
dm_inv_display This is the inventory display table. 1. Table columns Column name Type name Column size Nullable Primary key device_id nvarchar 50 NO √ color_depth tinyint 3 YES refresh_rate tinyint 3 YES resolution nvarchar 10 YES update_date datetime 23 NO 2.
dm_inv_ewf This is the inventory write filter table. 1. Type name Column size Primary key device_id nvarchar 50 √ ewf_id tinyint 3 √ boot_command tinyint 3 drive_label nchar 1 state tinyint 3 update_date datetime 23 2.
dm_inv_hardware This is the inventory hardware table. 1.
dm_inv_max_hotfix This is the inventory Maxspeed hotfix table. 1. Table columns Column name Type name Column size Nullable Primary key device_id nvarchar 50 NO √ hotfix_id nvarchar 50 NO √ hotfix_value nvarchar 100 YES update_date datetime 23 NO Description dm_inv_ms_hotfix This is the inventory MS hotfix table. 1.
dm_inv_nic This is the inventory network interface card table. 1.
dm_inv_partition This is the inventory partition table. 1. Type name Column size Nullable Primary key device_id nvarchar 50 NO √ partition_id nvarchar 50 NO √ available nvarchar 255 YES capacity nvarchar 255 YES filesystem nvarchar 50 YES update_date datetime 23 NO Disk_capacity nvarchar 255 YES Disk_id nvarchar 255 YES Disk_type nvarchar 255 YES 2.
dm_inv_software This is the inventory software table. 1. Table columns Column name Type name Column size Nullable Primary key device_id nvarchar 50 NO √ sw_name nvarchar 128 NO √ installed_date nvarchar 20 YES size nvarchar 100 YES update_date datetime 23 NO vendor nvarchar 100 YES version nvarchar 100 YES 2.
dm_inv_time This is the inventory time table. 1. Type name Column size Nullable Primary key device_id nvarchar 50 NO √ device_time nvarchar 50 YES server_time nvarchar 50 YES time_zone nvarchar 50 YES update_date datetime 23 NO 2.
dm_group_values This is the grouping values table, which stores the grouping values set from the HPDM Console. When the HPDM Console displays grouping values, the values in this table have a higher priority than the values in the dm_devices table. 1.
dm_inv_ex_property This is the extended property table of a device. 1. Type name Column size Nullable Primary key device_id nvarchar 50 NO √ property_name nvarchar 50 NO √ property_value nvarchar 100 NO update_date date 23 NO 2.
Task-related tables dm_tasks This is the task table, which stores basic task-related information. 1. Table columns Column name Type name Column size Nullable action_type smallint 5 YES ewf_policy smallint 5 YES Primary key Description Inner column to identify the template action type Writer Filter Policy (default is 2): 0: If the Write Filter is on, send back failure information. 1: Execute regardless of Write Filter status.
dm_sub_tasks This is the subtasks table, which stores subtask information. 1. Table columns Column name Type name Column size Nullable Primary key Description subtask_id nvarchar 50 NO √ If it is a sequence task: it will be 0, 1, or 2. If it is not: the field value will be blank. task_id nvarchar 50 NO √ See task_id in dm_tasks. base_name nvarchar 50 NO Base template name. file_name nvarchar 255 YES The generated task file name. The file is stored in HPDM_DIR/Server/tasks.
dm_task_temp This is the task template table, which stores task template information, including user-defined templates. 1.
dm_tasklog This is the task log table, which stores the task log information. 1.
dm_device_subtasks This is the device subtasks table, which stores a device’s related tasks. 1. Table columns Column name Type name Column size Nullable Primary key task_id varchar 50 NO √ subtask_id varchar 50 NO √ device_id varchar 50 NO √ start_time datetime 23 NO end_time datetime 23 NO status varchar 16 YES visible char 1 YES error_code varchar 20 YES update_date datetime 23 NO 2.
dm_snapshottask This is the snapshot task table. 1. Table columns Column name Type name Column size Nullable Primary key Foreign key Description task_id nvarchar 50 NO √ dm_snapshottaskresult.task_id Snapshot task id comment smallint 5 YES Snapshot task comment task_time datetime 23 NO Snapshot task start time 2. Relevant UI From the HPDM Console menu, select Tools > Status snapshot.
dm_snapshottaskreport This is the snapshot task report table. 1. Table columns Column name Type name Column size Nullable Primary key Description device_id nvarchar 50 NO √ Device ID task_id nvarchar 50 NO √ Snapshot task ID active nvarchar 50 NO 2.
Template-related tables dm_unit_template This is the unit template table, which stores unit template information, including the templates in a sequence template. 1.
dm_basic_template This is the basic template table. 1. Table columns Column name Type name Column size Nullable Primary key Description os_type nvarchar 50 NO √ Operating system type template_name nvarchar 50 NO √ Template name action_type smallint 5 YES Action type (inner attribute) category nvarchar 50 NO Category 2. Relevant UI dm_favorite_temp This is the favorite template table. 1.
dm_report_template This is the report template table, which stores report template information. 1. Table columns Column name Type name Column size Nullable Primary key Description report_name nvarchar 32 NO √ Report name report_type nvarchar 32 NO √ Report type: Device: device type, Task: task type report_content ntext 1073741823 YES Report content update_date datetime 23 NO Update date 2.
Gateway-related tables dm_gateway This is the gateway table. 1.
dm_gateway_walking_scope This is the gateway walking scope table. 1. Type name Column size Nullable Primary key Description scope_name nvarchar 50 NO √ Scope name creator nvarchar 32 NO Creator (user ID) file_location nvarchar 50 YES File location, by default blank update_date datetime 23 YES Update time 2.
dm_gateway_walkingtask This is the gateway table. 1. Table columns Column name Type name Column size Nullable Primary key Foreign key Description task_id nvarchar 16 NO √ dm_gateway_walkingtaskresult.task_id Discover gateway task ID end_time datetime 23 YES Task end time progress int 10 YES Process status: 0~100 scope_name nvarchar 50 NO Related scope name start_time datetime 23 YES Task start time 2.
dm_gateway_walkingtaskreport This is the gateway walking task report table. 1. Table columns Column name Type name Column size Nullable Primary key Description Ip nvarchar 16 NO √ Gateway IP task_id nvarchar 16 NO √ Gateway task ID status int 10 YES Task result status: 0: success 1: unconnected 2: deny 3: error walking_time 2.
Privilege-related tables dm_group This is the group table. 1. Table columns Column name Type name Column size Nullable Primary key Foreign key Description group_name nvarchar 50 NO √ dm_group_sec_filter.group_name Group name dn nvarchar 250 YES Distinguished name, which only has a value when the group type is LDAP description nvarchar 200 YES Description information group_type int 10 NO Group type: 0: unknown 1: DB (HPDM local group) 2: LDAP (LDAP server group) 2.
dm_group_sec_filter This is the security filter table. 1. Table columns Column name Column size Nullable Primary key Description filter_id nvarchar 32 NO √ Filter ID group_name nvarchar 50 NO √ Group name 2.
dm_user This is the user table. 1. Table columns Column name Type name Column size Nullable Primary key Foreign key Description user_id nvarchar 50 NO √ dm_user_filter.user_id User name dm_user_sec_filter.
dm_user_sec_filter This is the user security filter table. 1. Table columns Column name Type name Column size Nullable Primary key filter_id nvarchar 32 NO √ user_id nvarchar 50 NO √ Description dm_group_user This is the group and user table. 1. Column name Type name Column size Nullable Primary key group_name nvarchar 50 NO √ user_id nvarchar 50 NO √ 2.
dm_authority This is the authority table. 1. Table columns Column name Type name Column size Nullable Primary key √ auth_id int 10 NO auth_name nvarchar 50 NO description nvarchar 255 YES 2. Description Relevant UI This is an HPDM inner table. It is created when the database is installed, and the record values are fixed.
dm_auth_group This is the authority in group table. 1. Type name Column size Nullable Primary key group_name nvarchar 50 NO √ auth_id int 10 NO 2.
dm_key This is the key table. 1. Table columns Column name Type name Column size Nullable Primary key authkey nvarchar 250 NO √ create_date datetime 23 YES expire_interval smallint 5 NO import_date datetime 23 YES 2.
dm_keylog This is the key log table. 1. Table columns Column name Type name Column size logdescription nvarchar 200 NO √ logevent smallint 5 NO √ logtime datetime 23 NO √ 2. Nullable Primary key Description Relevant UI dm_keyzero This is the keyzero table. 1.
Rule- and filter-related tables dm_rule This is the rule table. 1.
dm_schedule This is the schedule table. 1. Table columns Column name Type name Column size Nullable Primary key Foreign key Description schedule_id nvarchar 50 NO √ dm_rule.schedule.id Schedule ID dm_walkingschedule.
dm_filter This is the filter table. 1. Table columns Column name Type name Column size Nullable Primary key Foreign key Description filter_id nvarchar 32 NO √ dm_filter_fields.field_id Filed ID dm_group_sec_filter.field_id dm_rule.field_id dm_user.security_filter dm_user_filter.field_id dm_user_sec_filter.field_id logical_operator nvarchar 3 YES update_date datetime 23 NO 2.
dm_filter_fields This is the filter fields table. 1. Table columns Column name Type name Column size Nullable Primary key field_name filter_id nvarchar 32 NO √ nvarchar 32 NO √ operand nvarchar 255 NO operator nvarchar 32 NO Description It contains: “=”, “>”, “<”, “>=”, “<=”, like ”, “has software”, “has NIC”, “has harddisk driver”, “has hotfix”, “has Microsoft hotfix”, “starts with”, “regardless” 2.
Grouping-related tables dm_group_attribute This is the grouping attribute table. It is an inner table, used by the dynamic group, and should not be changed. 1. Table columns Column name Type name Column size Nullable Primary key attr_id nvarchar 50 NO √ attr_name nvarchar 50 NO 2.
dm_group_policy This is the dynamic grouping policy table. 1. Type name Column size Nullable Primary key Description policy_id nvarchar 50 NO √ Dynamic grouping ID alias nvarchar 50 NO attrs nvarchar 50 NO user_id nvarchar 50 NO 2.
dm_group_path This is the grouping path information table. 1. Table columns Column name Type name Column size Nullable Primary key Foreign key Description path_id int 10 NO √ dm_devices.grouping Path ID dm_group_values.grouping dm_manual_grouping.path_id path 2.
dm_group_schema This is the manual grouping schema table. 1. Table columns Column name Type name Column size Nullable Primary key Foreign key Description schema_id int 10 NO √ dm_manual_grouping.schema_id Manual schema ID schema_name nvarchar 50 NO Manual schema name user_id nvarchar 50 NO The creator’s user ID 2. Relevant UI dm_manual_grouping This is the manual grouping table, which stores the device relationship with a manual schema and path. 1.
Configuration-related tables dm_conf This is the configuration table. 1. Table columns Column name Type name Column size Nullable Primary key Description conf_option nvarchar 50 NO √ The configuration name conf_value nvarchar 255 NO 2.
ProgressSwitch on PXEBatchAmount 2 PXEBatchinterval 10 TelnetPassword1 Undefined TelnetPassword2 Undefined TelnetPassword3 Undefined TelnetUsername1 Undefined TelnetUsername2 Undefined TelnetUsername3 Undefined TryOnceSwitch off ValidTime 1440 VNCPassword maxspeed WalkingGroupAmount 50 WalkingRetry 3 WalkingTimeout 15 BIOSPassword TPMPassword RepoMappingType 2 RepoPort 21 RepoProtocol FTP BrokerRequestID 0 UseSAMBA True dm_dbversion This is the database version table.
dm_Ipscope This is the IP scope table. 1. Table columns Column name Type name Column size Nullable Primary key Description alias nvarchar 50 NO √ Alias name start_ip nvarchar 50 NO Starting IP address stop_ip nvarchar 50 NO Ending IP address 2. Relevant UI dm_network_alias This is the network alias table. 1.
Deprecated tables • dm_tasks_attachment • dm_template_attachment • dm_walkingschedule • dm_walkingscope • dm_walkingtask • dm_walkingtaskresult • dm_buildid_alias • dm_user_filter • dm_updatelog • dm_upgarde_agent • dm_ftp_servers • dm_device_ftp • dm_subnet_ftp 54
Database diagrams Device-related diagram 55
Filter- and user-related diagram 56
Other diagrams Examples Generate device information To find the device name and status for all operating system types, use the following procedure. The Device Report function will also generate these results, but will include more information than necessary. 1. 2. 3. 4. Connect to the database server. Locate the table dm_devices. Write the following SQL statements, which include only the device name and if the status is on: select device_name, active from DB_NAME.dbo.dm_devices where dm_devices.
Use the following procedure to determine which devices do not use auto-map FTP based on the results of the previous procedure. 1. 2. 3. Locate the table dm_repo_mapping. Join the tables dm_devices and dm_repo_mapping using the following SQL statements: Select dm_devices.device_NAME, dm_devices.active from DB_NAME.dbo.dm_devices, DB_NAME.dbo.dm_repo_mapping where dm_devices.active = 'on' and dm_devices.device_id = dm_repo_mapping.map_key and dm_repo_mapping.category = 3; View the results.
Generate unsuccessful task information The HPDM Task Report function cannot be used to generate task information where the status is not success, because a criterion can only be set once. To find this task information, use the following procedure. 1. 2. 3. Locate the dm_device_subtasks table. Write the following SQL statement: select * from DB_NAME.dbo.dm_device_subtasks where dm_device_subtasks.status != 'success'; View the results. Display the task count grouped by task status 1. 2. 3.
For more information To read more about HP Device Manager, go to hp.com/go/hpdm. Sign up for updates hp.com/go/getupdated © Copyright 2014 Hewlett-Packard Development Company, L.P. Microsoft and Windows are U.S. registered trademarks of the Microsoft group of companies. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.
