HP Device Manager 4.5 - Database Schema Guide

ManualsBrandsHP ManualsSoftwareHP Device Manager

HP Device Manager 4.5

Database Schema

Guide

Guidance

for HPDM administrators to better understand the

HPDM

database schema

Technical white paper

Summary of content (65 pages)

PAGE 1
HP Device Manager 4.
PAGE 2
© Copyright 2012 Hewlett-Packard Development Company, L.P. Microsoft and Windows are U.S. registered trademarks of Microsoft Corporation. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license.
PAGE 3
Table of contents Overview ............................................................................................................................................ 5 Tables ................................................................................................................................................ 6 Repository-related tables ................................................................................................................... 6 dm_repositories .........................
PAGE 4
dm_rule ..................................................................................................................................... 46 dm_schedule .............................................................................................................................. 47 dm_filter .................................................................................................................................... 48 dm_filter_fields ...........................................................
PAGE 5
Overview The purpose of this document is to explain the database schema of HP Device Manager 4.5. Also, this document will provide some examples of how to use tables to produce a desired report.
PAGE 6
Tables Repository-related tables dm_repositories This is the repository table. 1. Table columns Column Name Type Name Column Size Nullable repo_id Int 10 NO repo_address nvarchar 50 NO repo_name nvarchar 50 NO sync_date datetime 23 YES 2. Relevant UI Primary Key Foreign Key √ dm_repo_mapping.repo_id; dm_repo_protocols.
PAGE 7
dm_repo_protocols This is the repository protocols table. 1. Table columns Column Name Type Name repo_id Int protocol_type Int password Nullable Primary Key 10 NO √ 10 NO √ Nvarchar 100 YES The encrypted password repo_path Nvarchar 50 NO port Int 10 NO Repository root path Port: -1: default port for this type of protocol Other value: customized port value username Nvarchar 70 YES Username 2.
PAGE 8
dm_repo_mapping This is the repository mapping table. 1. Table columns Column Name Type Name repo_id Int 50 NO category Int 10 NO map_key Nvarchar 10 NO 2.
PAGE 9
Device-related tables dm_devices This is the devices table. 1.
PAGE 10
p6 nvarchar 50 NO product_type nvarchar 100 NO Product type product_version nvarchar 100 NO Product version pull_interval smallint 5 YES Pull interval update_date datetime 23 NO Update date vnc_pwd nvarchar 32 YES grouping int 10 YES VNC password 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
PAGE 11
dm_inv_display This is the inventory display table. 1. Table columns Column Name Type Name device_id nvarchar color_depth refresh_rate resolution update_date 2.
PAGE 12
dm_inv_ewf This is the inventory write filter table. 1. Table columns Column Name Type Name device_id nvarchar ewf_id tinyint 3 boot_command tinyint 3 drive_label nchar 1 state tinyint update_date datetime 2.
PAGE 13
dm_inv_hardware This is the inventory hardware table. 1.
PAGE 14
dm_inv_max_hotfix This is the inventory Maxspeed hotfix table. 1. Table columns Column Name Type Name device_id nvarchar 50 NO hotfix_id nvarchar 50 NO hotfix_value nvarchar 100 YES update_date datetime 23 NO 2.
PAGE 15
dm_inv_ms_hotfix This is the inventory MS hotfix table. 1. Table columns Column Name Type Name device_id nvarchar hotfix_id comment Column Size Nullable 50 NO nvarchar 50 NO nvarchar 100 YES installed_by nvarchar 100 YES installed_date nvarchar 20 YES service_pack tinyint 3 YES update_date datetime 23 NO 2.
PAGE 16
dm_inv_nic This is the inventory network interface card table. 1. Table columns Column Name Type Name device_id nvarchar nic_id description Column Size Nullable 50 NO nvarchar 10 NO nvarchar 100 YES gateway nvarchar 15 YES hostname nvarchar 100 YES ip nvarchar 15 YES is_dhcp nchar 1 YES is_dnsdhcp nchar 1 YES mac nvarchar 17 NO mask nvarchar 15 YES primarydns nvarchar 255 YES secondarydns nvarchar 15 YES update_date datetime 23 NO 2.
PAGE 17
dm_inv_partition This is the inventory partition table. 1. Table columns Column Name Type Name device_id nvarchar partition_id available Column Size Nullable 50 NO nvarchar 50 NO 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.
PAGE 18
dm_inv_software This is the inventory software table. 1. Table columns Column Name Type Name 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.
PAGE 19
dm_inv_time This is the inventory time table. 1. Table columns Column Name Type Name device_id nvarchar device_time server_time Nullable Primary Key 50 NO √ nvarchar 50 YES nvarchar 50 YES time_zone nvarchar 50 YES update_date datetime 23 NO 2.
PAGE 20
dm_group_values This is the grouping values table, which stores the grouping values set from the Console. Values in this table have a higher priority than values in the dm_devices table when the Console displays grouping values. 1. Table columns Column Name Type Name device_id nvarchar p1 Nullable Primary Key 50 NO √ nvarchar 50 YES p2 nvarchar 50 YES p3 nvarchar 50 YES p4 nvarchar 50 YES p5 nvarchar 50 YES p6 nvarchar 50 YES grouping int 10 YES 2.
PAGE 21
Task-related tables dm_tasks This is the task table, which stores basic task-related information. 1. Table columns Column Name Type Name action_type smallint 5 YES ewf_policy smallint 5 YES hidden nchar 1 YES is_sequential nchar 1 YES History: there was a requirement that was needed to keep all tasks, ‘1’ means visible, ‘0’ means invisible.
PAGE 22
dm_sub_tasks This is the sub-task table, which stores sub-task information. 1. Table columns Column Name Type Name subtask_id nvarchar 50 NO task_id nvarchar 50 NO base_name nvarchar 50 NO file_name nvarchar 255 YES task_comment nvarchar 255 YES task_type nvarchar 50 YES 2. Relevant UI Column Size Nullable Primary Key √ √ Description If it is a sequence task: it will be consist of ‘0’, ‘1’, or ‘2’; If it is not: the field value will be blank. Refer to dm_tasks.
PAGE 23
dm_task_temp This is the task template table, which stores task template information (including user-defined templates). 1. Table columns Column Name Type Name os_type nvarchar 50 NO template_name nvarchar 50 NO category nvarchar 50 NO Template category create_time datetime 23 NO Create time of template description nvarchar 255 YES is_sequential nchar 1 NO Description Whether it is a sequence template: ‘Y’: is sequence template; ‘N’: not sequence template.
PAGE 24
dm_tasklog This is the task log table, which stores the task log information. 1. Table columns Column Name Type Name device_id nvarchar sequence_num Column Size Nullable Primary Key 50 NO √ numeric 19 NO subtask_id nvarchar 50 NO task_id nvarchar 50 NO comment ntext 1073741823 YES The log comment error_code nvarchar 16 YES Error code of log error_detail nvarchar 255 YES Error detail information update_date datetime 23 NO Update time √ √ √ 2.
PAGE 25
dm_device_subtasks This is the device sub task table, which stores the device’s related tasks. 1. Table columns Column Name Type Name Column Size Nullable 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 visible char error_code varchar update_date datetime 23 NO 2.
PAGE 26
dm_snapshottask This is the snapshot task table. 1. Table columns Column Name Type Name task_id nvarchar comment smallint task_time datetime Column Size Nullable Primary Key Foreign Key 50 NO √ dm_snapshottaskresult.task_id 5 YES 23 NO 2.
PAGE 27
dm_snapshottaskreport This is the snapshot task report table. 1. Table columns Column Name Type Name device_id nvarchar 50 NO task_id nvarchar 50 NO active nvarchar 50 NO 2.
PAGE 28
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.
PAGE 29
dm_basic_template This is the basic template table. 1. Table columns Column Name Type Name os_type nvarchar 50 NO template_name nvarchar 50 NO action_type smallint 5 YES Action type (inner attribute) category nvarchar 50 NO Category 2.
PAGE 30
dm_favorite_temp This is the favorite template table. 1. Table columns Column Name Type Name os_type nvarchar 50 NO template_name nvarchar 50 NO √ √ user_id nvarchar 50 NO √ 2.
PAGE 31
dm_report_template This is the report template table, which stores report template information. 1. Table columns Column Name Type Name report_name nvarchar report_type nvarchar report_content ntext update_date datetime 2.
PAGE 32
Gateway-related tables dm_gateway This is the gateway table. 1.
PAGE 33
dm_gateway_walking_scope This is the gateway walking scope table. 1. Table columns Column Name Type Name scope_name nvarchar creator file_location update_date 2.
PAGE 34
dm_gateway_walkingtask This is the gateway walking task table. 1. Table columns Column Name Type Name Column Size Nullable Primary Key Foreign Key √ dm_gateway_walkingtaskre sult.task_id √ Description Discover gateway task ID task_id nvarchar 16 NO end_time datetime 23 YES progress int 10 YES scope_name nvarchar 50 NO Task end time Process status: 0~100 Related scope name start_time datetime 23 YES Task start time 2.
PAGE 35
dm_gateway_walkingtaskresult This is the gateway walking task result table. 1. Table columns Column Name Type Name Ip nvarchar 16 NO task_id nvarchar 16 NO Status Int 10 YES Gateway task ID Task result status: 0: success 1: unconnected 2: deny 3: error walking_time datetime 23 YES Result walking time 2.
PAGE 36
Privilege-related tables dm_group This is the group table. 1. Table columns Column Name Type Name group_name nvarchar dn Nullable Primary Key Foreign Key Description 50 NO √ dm_group_sec_filter.group_name nvarchar 250 YES description nvarchar 200 YES group_type int 10 NO Group name Distinguished name: Only when group type is LDAP will it have a value. Description information Group type: 0: unknown 1: DB (HPDM local group) 2: LDAP (LDAP Server group) 2.
PAGE 37
dm_group_sec_filter This is the security filter table. 1. Table columns Column Name Type Name filter_id nvarchar 32 NO group_name nvarchar 50 NO 2.
PAGE 38
dm_user This is the user table. 1. Table columns Column Name Type Name user_id nvarchar dn Nullable Primary Key 50 NO √ nvarchar 255 YES description nvarchar 50 NO User name Distinguish name (only when user type is LDAP will it have a value) Description information password nvarchar 50 NO Encrypted password privilege int 10 YES user_type int 10 NO Privilege User type: 0: unknown 1: local 2: ldap security_filter nvarchar 32 YES Security filter name 2.
PAGE 39
dm_user_sec_filter This is the user security filter table. 1. Table columns Column Name Type Name filter_id nvarchar 32 NO user_id nvarchar 50 NO 2.
PAGE 40
dm_group_user This is the group and user table. 1. Table columns Column Name Type Name group_name nvarchar 50 NO user_id nvarchar 50 NO 2.
PAGE 41
dm_authority This is the authority table. 1. Table columns Column Name Type Name auth_id int auth_name description Column Size Nullable Primary Key 10 NO √ nvarchar 50 NO nvarchar 255 YES Description 2. Relevant UI This is an HPDM inner table. It will be created when installing the database, and the record values are fixed.
PAGE 42
dm_auth_group This is the authority in group table. 1. Table columns Column Name Type Name group_name nvarchar auth_id int 2.
PAGE 43
dm_key This is the key table. 1. Table columns Column Name Type Name authkey nvarchar create_date datetime expire_interval smallint import_date datetime 2.
PAGE 44
dm_keylog This is the key log table. 1. Table columns Column Name Type Name logdescription nvarchar logevent smallint logtime datetime 2.
PAGE 45
dm_keyzero This is the keyzero table. 1. Table columns Column Name Type Name authkey nvarchar create_date datetime expire_interval smallint import_date datetime Column Size Nullable Primary Key 250 NO √ 23 YES 5 NO 23 YES Description 2. Relevant UI N/A This is an HPDM inner table. It will be created when installing the database, and the record values are fixed.
PAGE 46
Rule- and Filter-related tables dm_rule This is the rule table. 1.
PAGE 47
dm_schedule This is the schedule table. 1.
PAGE 48
dm_filter This is the filter table. 1. Table columns Column Name Type Name filter_id nvarchar logical_operator update_date 2. Relevant UI Column Size Nullable 32 NO nvarchar 3 YES datetime 23 NO Primary Key Foreign Key √ dm_filter_fields.field_id dm_group_sec_filter.field_id dm_rule.field_id dm_user.security_filter dm_user_filter.field_id dm_user_sec_filter.
PAGE 49
dm_filter_fields This is the filter fields table. 1. Table columns Column Name Type Name field_name nvarchar filter_id operand operator 2.
PAGE 50
Grouping-related tables dm_group_attribute This is the grouping attribute table, and it is an inner table (used by dynamic group) and should NOT be changed. 1. Table columns Column Name Type Name Nullable Primary Key attr_id nvarchar 50 NO √ attr_name nvarchar 50 NO 2.
PAGE 51
dm_group_policy This is the dynamic grouping policy table. 1. Table columns Column Name Type Name policy_id nvarchar alias attrs user_id 2.
PAGE 52
dm_group_path This is the grouping path information table. 1. Table columns Column Name Type Name path_id int path nvarchar 2. Relevant UI Column Size Nullable Primary Key 10 NO √ 255 NO Foreign Key dm_devices.grouping dm_group_values.grouping dm_manual_grouping.
PAGE 53
dm_group_schema This is the manual grouping schema table. 1. Table columns Column Name Type Name schema_id int schema_name user_id 2. Relevant UI Column Size Nullable Primary Key Foreign Key 10 NO √ dm_manual_grouping.
PAGE 54
dm_manual_grouping This is the manual grouping table, which stores the device relationship with a manual schema and path. 1. Table columns Column Name Type Name device_id nvarchar 50 NO path_id int 10 NO schema_id int 10 NO 2.
PAGE 55
Configuration-related tables dm_conf This is the configure table. 1. Table columns Column Name Type Name conf_option nvarchar conf_value nvarchar Column Size 2. Relevant information conf_option conf_value AutoUpgradeAgent false CancelTaskSwitch on deviceSequence 1 EWFPolicy 2 ExcludeWorkTime 0-540-1020 folderId 1 FTPBatchAmount 5 FTPBatchInterval 2 FTPPort 21 FTPProtocol FTP GatewayControlLevel 0 GeneralBatchAmount 0 GeneralBatchInterval 5 GeneralTimeout 30 hpdm.
PAGE 56
p5 other p6 other PollingInterval 30 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 d
PAGE 57
dm_ipscope This is the IP scope table. 1. Table columns Column Name Type Name alias nvarchar start_ip stop_ip 2.
PAGE 58
dm_network_alias This is the network alias table. 1. Table columns Column Name Type Name network nvarchar alias nvarchar Column Size Nullable Primary Key 50 NO √ 50 NO Description 2. Relevant UI N/A dm_os_types This is the OS type table. 1. Table columns Column Name Type Name os_type nvarchar Column Size 50 Nullable Primary Key Foreign Key Description NO √ dm_rule.os_type OS type 2. Relevant Information It stores all activated OS type information.
PAGE 59
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_inv_ex_property dm_ftp_servers dm_device_ftp dm_subnet_ftp
PAGE 60
Database diagram Device-related diagram
PAGE 61
Filter- and User-related diagram
PAGE 62
Other diagrams
PAGE 63
Examples Generate device information 1. We need all devices of all OS types and only need the device’s name and status (which should be ON). We can use the Device Report function to generate results, but it contains ALL of the columns, so we need to write SQL statements (connecting to Database Server) to generate the desired results. a. We need device information. In the Device-related tables category, locate the table dm_devices. b.
PAGE 64
Generate all device inventory information We need a device’s inventory information, so we will need to join the dm_devices table and other inventory tables to get the desired information. 1. Locate the dm_devices table and the inventory-related tables. 2. Write the SQL statements: Use left join to connect all the tables you need (you just “left join” the table you need, and it will generate the related results): -- You can replace the "*" with specified columns you care about select * from DB_NAME.dbo.
PAGE 65
Generate unsuccessful task information We need task information where the status is NOT “success”. We cannot generate this kind of report using the HPDM Task Report function because, currently, a criterion can only be set once. Let’s write an SQL statement to do it: 1. Locate the Task-related tables category and the table named dm_device_subtasks. 2. Write the SQL statement: select * from DB_NAME.dbo.dm_device_subtasks where dm_device_subtasks.status != 'success'; 3.