Predefined SQL view reports
Updated: May 13, 2016
Applies To: System Center 2012 SP1 - Data Protection Manager, System Center 2012 - Data Protection Manager, System Center 2012 R2 Data Protection Manager
System Center 2012 – Data Protection Manager (DPM) includes several SQL views to help you create custom reports. SQL views provide a simpler method that querying tables directly, by populating columns with data collected from multiple tables in the database. You don’t need -depth knowledge of the entire database or the relationship between tables and keys.
On the other hand SQL views can degrade performance if used too frequently because the view runs each time it’s queries. In addition the supported views might not include all the columns you need.
SQL views
The following table summarizes the predefined SQL views.
| View | Field | Data type | Description |
|---|---|---|---|
| Vw_DPM_Agents Contains the list of computers on which a DPM protection agent from this DPM server is installed. |
ServerName | String | The name of the computer |
| Vw_DPM_Agents | Version | String | DPM agent version on that computer |
| Vw_DPM_Alerts List of alerts from last 30 days |
Severity | Integer 0=Error 1=Warning 2=Information |
Alert severity |
| Vw_DPM_Alerts | Resolution | Integer 0 = Active 1 = Recommended action in progress 2 = Resolved |
Alert state |
| Vw_DPM_Alerts | OccurredSince | Date and Time | First time alert was raised |
| Vw_DPM_Alerts | ResolvedTime | Date and Time | Time when alert was resolved |
| Vw_DPM_Alerts | Type | Integer | Alert type |
| Vw_DPM_CurrentOnlineMedia Tapes currently online in DPM owned libraries, as of the last inventory. |
UserFriendlyName | String | Library name |
| Vw_DPM_CurrentOnlineMedia | ImportPoolMediaCount | Integer | Tapes imported to the DPM server |
| Vw_DPM_CurrentOnlineMedia | FreePoolMediaCount | Integer | Tapes marked as free or blank |
| Vw_DPM_CurrentOnlineMedia | AdminPoolMediaCount | Integer | Tapes with active data |
| Vw_DPM_Disk_Usage_Replica Disk usage statistics for replicas in the storage pool. |
PhysicalPath | String | Protected data source name |
| Vw_DPM_Disk_Usage_Replica | ReplicaId | GUID | Unique identifier for replica |
| Vw_DPM_Disk_Usage_Replica | PGId | GUID | Unique identifier for protection group to which the data source belongs |
| Vw_DPM_Disk_Usage_Replica | ProductionServerName | String | Server on which data source is located |
| Vw_DPM_Disk_Usage_Replica | DiskAllocated | Big Integer | Total disk space allocated to data source |
| Vw_DPM_Disk_Usage_Replica | DiskUsed | Big Integer | Total disk space used by data source |
| Vw_DPM_Disk_Usage_Replica | FreeSpace | Big Integer | DiskAllocated - DiskUsed |
| Vw_DPM_Disk_Usage_Replica | ReplicaAllocated | Big Integer | Part of DiskAllocated reserved for replica of data source |
| Vw_DPM_Disk_Usage_Replica | ReplicaUsed | Big Integer | Part of ReplicaAllocated actually in use |
| Vw_DPM_Disk_Usage_Replica | ShadowCopyAllocated | Big Integer | Part of DiskAllocated reserved for recovery points of data source |
| Vw_DPM_Disk_Usage_Replica | ShadowCopyUsed | Big Integer | Part of ShadowCopyAllocated actually in use |
| Vw_DPM_Disk_Usage_Replica | StartDateTime | Date and time | |
| Vw_DPM_Disk_Usage_Replica | EndDateTime | Date and time | |
| Vw_DPM_Disk_Usage_Replica | ScheduleType | Integer 0=weekly; 1=monthly; 2=quarterly; 3=yearly |
|
| Vw_DPM_DiskRecoveryPoints Counts for disk recovery points available for each data source. |
DataSourceName | String | Protected data source name |
| Vw_DPM_DiskRecoveryPoints | PGId | GUID | Unique identifier for protection group to which data source belongs |
| Vw_DPM_DiskRecoveryPoints | ServerId | GUID | Unique identifier for server to which data source belongs |
| Vw_DPM_DiskRecoveryPoints | Frequency | Integer | Number of available recovery points |
| Vw_DPM_LongRecoveries Historical information about recoveries that took longer than 24 hours. |
DataSourceName | String | The data source that was recovered |
| Vw_DPM_LongRecoveries | TargetServerName | String | The name of the server to which recovery was done |
| Vw_DPM_LongRecoveries | WriterId | GUID | Identifies the type of the data source that was recovered |
| Vw_DPM_LongRecoveries | StartTime | Date and time | The time at which the recovery was started |
| Vw_DPM_LongRecoveries | EndTime | Date and time | The time at which the recovery ended |
| Vw_DPM_Media Information about state of all tapes known to DPM. |
MediaLabel | String | The label on the tape |
| Vw_DPM_Media | MediaBarcode | String | The barcode for the tape |
| Vw_DPM_Media | IsOnline | Integer | Whether the tape is online |
| Vw_DPM_Media | LibraryName | String | The name of the library in which the tape exists. NULL if tape is offline |
| Vw_DPM_Media | MediaSlotNumber | Integer | The slot number in which the tape exists. NULL if tape is offline If in a drive, this represents the home slot of the tape (to which the tape returns on a dismount). |
| Vw_DPM_Media | PGName | String | The name of the protection group in which the tape exists |
| Vw_DPM_Media | MediaExpiryDate | Date and time | The time when all data sets on this tape will expire. Can have the date in the past or NULL if the tape is free. |
| Vw_DPM_MediaPool_Media Tape counts for a given library. |
LibraryName | String | The name of the library |
| Vw_DPM_MediaPool_Media | FreeMedia | Integer | Number of tapes that are free in this library |
| Vw_DPM_MediaPool_Media | FreeMediaThreshold | Integer | The threshold below which this library generates an alert |
| Vw_DPM_ProtectedDataSource Current disk space usage by various data sources. |
ReplicaId | GUID | Identifier of the replica |
| Vw_DPM_ProtectedDataSource | PGId | GUID | Identifier of the protection group to which the replica belongs |
| Vw_DPM_ProtectedDataSource | AllocatedSize | Big integer | Disk space allocated to the data source |
| Vw_DPM_ProtectedDataSource | UsedSize | Big integer | Disk space currently used by the data source |
| Vw_DPM_ProtectedDataSource | ProductionServerName | String | The name of the computer on which the data source exists |
| Vw_DPM_ProtectedDataSource | StorageNode | String | Always set to the DPM server |
| Vw_DPM_ProtectedGroup Table with information about all protection groups. |
PGId | GUID | Unique identifier for the protection group |
| Vw_DPM_ProtectedGroup | ProtectionGroupName | String | Protection group name |
| Vw_DPM_ProtectedGroup | CreationTime | Date and time | Time protection group was created |
| Vw_DPM_RecoveryDuration History of counts for recovery jobs in various time durations. |
StartDateTime | Date and time | The time at which the statistic was collected |
| Vw_DPM_RecoveryDuration | EndDateTime | Date and time | Internal |
| Vw_DPM_RecoveryDuration | ScheduleType | Integer | The frequency for which this particular statistic was collected |
| Vw_DPM_RecoveryDuration | RecoveryDuration | Integer | Indicates if the recovery was less than 6 hours, between 6-24 hours, or greater than 24 hours |
| Vw_DPM_RecoveryDuration | RecoveryCount | Integer | Number of recoveries |
| Vw_DPM_RecoveryJob Detailed information about recent recovery jobs. |
DataSourceName | String | The data source for which recovery was run |
| Vw_DPM_RecoveryJob | ServerName | String | The server to which recovery was performed |
| Vw_DPM_RecoveryJob | CreationTime | Date and time | Time at which the recovery job was run |
| Vw_DPM_RecoveryJob | FailureCode | Integer | Error code in case of failure of the recovery job |
| Vw_DPM_RecoveryJob | Status | Integer 0/1=Progress 2=Succeeded 3=Failure |
Status of the recovery job |
| Vw_DPM_RecoveryPointDisk Status of recent recovery point creation jobs on disk. |
String | The data source for which the backup was created | String |
| Vw_DPM_RecoveryPointDisk | String | The server on which the data source exists | String |
| Vw_DPM_RecoveryPointDisk | Date and time | The time at which the recovery point creation job was run | Date and time |
| Vw_DPM_RecoveryPointDisk | Integer 0/1=Progress 2=Succeeded 3=Failure |
Status of the recovery point creation job | Integer 0/1=Progress 2=Succeeded 3=Failure |
| Vw_DPM_RecoveryPointDisk | Integer | Zero if succeeded. Else, set to a DPM error code. |
Integer |
| Vw_DPM_RecoveryPointTape Status of recent recovery point creation jobs on tape. |
|||
| Vw_DPM_RecoveryPointTape | String | The data source for which the backup was created | String |
| Vw_DPM_RecoveryPointTape | String | The server on which the data source exists | String |
| Vw_DPM_RecoveryPointTape | Date and time | The time at which the recovery point creation job was run | Date and time |
| Vw_DPM_RecoveryPointTape | Integer 0/1=Progress 2=Succeeded 3=Failure |
Status of the recovery point creation job | Integer 0/1=Progress 2=Succeeded 3=Failure |
| Vw_DPM_Replica Listing of all replicas managed by DPM. |
ReplicaId | GUID | Unique identifierentifier generated by DPM for the replica volume |
| Vw_DPM_Replica | PhysicalPath | String | The name of the data source on the replica |
| Vw_DPM_Replica | ServerName | String | Name of the server to which the data source belongs |
| Vw_DPM_Replica | ValidFrom | Date and time | When the replica was created |
| Vw_DPM_Replica | ValidTo | Date and time | The date on which the replica was made inactive |
| Vw_DPM_Replica | PGId | GUID | Unique identifierentifier generated by DPM for the protection group to which the data source belongs |
| Vw_DPM_Replica | StorageNode | String | Always set to the DPM server |
| Vw_DPM_Server List of all protected computers. |
ServerId | GUID | Unique identifierentifier generated by DPM for the protected computer |
| Vw_DPM_Server | ServerName | String | Fully qualified domain name for the computer |
| Vw_DPM_Server | NetBiosName | String | Name |
| Vw_DPM_Server | DomainName | String | Domain in which the computer belongs |
| Vw_DPM_Server | IsRG | Integer | If this computer represents a Resource Group |
| Vw_DPM_TapeRecoveryPoints Counts for tape recovery points available for each data source. |
DataSourceName | String | The name of the protected data source |
| Vw_DPM_TapeRecoveryPoints | PGId | GUID | The unique identifierentifier for the protection group to which this data source belongs |
| Vw_DPM_TapeRecoveryPoints | ServerId | GUID | The unique identifierentifier for the server to which this data source belongs |
| Vw_DPM_TapeRecoveryPoints | Frequency | Integer | The number of available recovery points |
| Vw_DPM_TapeRecoveryPoints | Term | Integer 0=ShortTerm; 1=LongTerm |
The schedule to which the recovery point corresponds |
| Vw_DPM_TapeStat Historical information on tape usage counts. |
StartDateTime | Date and time | |
| Vw_DPM_TapeStat | EndDateTime | Date and time | |
| Vw_DPM_TapeStat | ScheduleType | Integer | Integer 0=Weekly 1=Monthly 2=Quarterly 3=Yearly |
| Vw_DPM_TapeStat | Free | Integer | Number of free tapes at end-time |
| Vw_DPM_TapeStat | Online | Integer | Number of online tapes at end time |
| Vw_DPM_TapeUsagePerPG Historical tape usage data per protection group. |
StartDateTime | Date and time | Start time |
| Vw_DPM_TapeUsagePerPG | EndDateTime | Date and time | End time |
| Vw_DPM_TapeUsagePerPG | PGName | String | Name of the protection group |
| Vw_DPM_TapeUsagePerPG | ScheduleType | Integer | Integer 0=Weekly 1=Monthly 2=Quarterly 3=Yearly |
| Vw_DPM_TapeUsagePerPG | Online | Integer | Number of online tapes at end time |
| Vw_DPM_TapeUsagePerPG | Offline | Integer | Number of offline tapes at end time |
| Vw_DPM_Total_Disk_Trend Total disk space usage historical trend. |
StartDateTime | Date and time | |
| Vw_DPM_Total_Disk_Trend | EndDateTime | Date and time | |
| Vw_DPM_Total_Disk_Trend | ScheduleType | Integer | Integer 0=Weekly 1=Monthly 2=Quarterly 3=Yearly |
| Vw_DPM_Total_Disk_Trend | DiskSpaceCapacity | Big integer | The total storage in storage pool at end-time |
| Vw_DPM_Total_Disk_Trend | PreviousDiskSpaceCapacity | Big integer | Total storage in storage pool in previous corresponding period |
| Vw_DPM_Total_Disk_Trend | DiskSpaceAllocated | Big integer | The disk space from storage pool that has been allocated |
| Vw_DPM_Total_Disk_Trend | PreviousDiskSpaceAllocated | Big integer | The disk space from storage pool that was allocated in the previous corresponding period |
| Vw_DPM_Total_Disk_Trend | DiskSpaceUsed | Big integer | The actual disk space usage |
| Vw_DPM_Total_Disk_Trend | PreviousDiskSpaceUsed | Big integer | The used disk space in the previous corresponding period |
| Vw_DPM_Total_RecoveryPoint Information about all recent recovery point jobs. |
DataSourceName | String | The name of the protected data source |
| Vw_DPM_Total_RecoveryPoint | ServerName | String | The server to which the data source belongs |
| Vw_DPM_Total_RecoveryPoint | CreationTime | Date and time | The time at which the recovery point creation job was run |
| Vw_DPM_Total_RecoveryPoint | Status | Integer 0/1=Progress 2=Succeeded 3=Failure |
Status of the recovery point creation job |
| Vw_DPM_Total_RecoveryPoint | ErrorCode | Integer | Error code in recovery point creation |
Alert types
The following table summarizes the alert types.
| -1 | RestoreDBAlert |
| 0 | NullType |
| 1 | AgentIncompatibleAlert |
| 2 | AgentUnreachableAlert |
| 5 | MediaVerificationFailedAlert |
| 6 | MediaEraseFailedAlert |
| 7 | DetailedInventoryFailedAlert |
| 8 | MediaDecommissionedAlert |
| 9 | MediaDataEraseAlert |
| 10 | FreeMediaThresholdAlert |
| 11 | DataSetCopyFailedAlert |
| 12 | BackupToTapeFailedAlert |
| 13 | BackupToTapeCatalogFailedAlert |
| 14 | LibraryDriveAlert |
| 15 | LibraryNotAvailableAlert |
| 16 | LibraryNotWorkingEfficientlyAlert |
| 17 | MediaRequiredAlert |
| 18 | ReplicaInitializationInProgressAlert |
| 19 | SynchronizationFailedAlert |
| 20 | StopProtectionFailedAlert |
| 21 | RecoveryInProgressAlert |
| 22 | RecoveryPartiallySuccessfulAlert |
| 23 | RecoverySuccessfulAlert |
| 24 | RecoveryFailedAlert |
| 25 | ShadowCopyFailedAlert |
| 26 | ReplicaInMissingStateAlert |
| 27 | ReplicaInInvalidStateAlert |
| 28 | PartialDeployedClusterAlert |
| 29 | AgentTaskFailAlert |
| 30 | SqmOptInAlert |
| 31 | DiskThresholdCrossedAlert |
| 32 | VerificationInProgressAlert |
| 33 | DiskMissingAlert |
| 34 | CatalogThresholdCrossedAlert |
| 35 | DatasetDataVerificationFailed |
| 36 | SCDiskThresholdCrossedAlert |
| 37 | ConfigureProtectionFailedAlert |
| 38 | ReplicaManualLoadPendingAlert |
| 39 | ReplicaInitializationPendingAlert |
| 40 | CertificateExpiringAlert |
| 41 | EvalShareInquiryAlert |
| 42 | ShadowCopyConsolidationRequired |