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.

The following table summarizes the predefined SQL views.

ViewFieldData typeDescription
Vw_DPM_Agents

Contains the list of computers on which a DPM protection agent from this DPM server is installed.
ServerNameStringThe name of the computer
Vw_DPM_AgentsVersionStringDPM agent version on that computer
Vw_DPM_Alerts

List of alerts from last 30 days
SeverityInteger

0=Error

1=Warning

2=Information
Alert severity
Vw_DPM_AlertsResolutionInteger

0 = Active

1 = Recommended action in progress

2 = Resolved
Alert state
Vw_DPM_AlertsOccurredSinceDate and TimeFirst time alert was raised
Vw_DPM_AlertsResolvedTimeDate and TimeTime when alert was resolved
Vw_DPM_AlertsTypeIntegerAlert type
Vw_DPM_CurrentOnlineMedia

Tapes currently online in DPM owned libraries, as of the last inventory.
UserFriendlyNameStringLibrary name
Vw_DPM_CurrentOnlineMediaImportPoolMediaCountIntegerTapes imported to the DPM server
Vw_DPM_CurrentOnlineMediaFreePoolMediaCountIntegerTapes marked as free or blank
Vw_DPM_CurrentOnlineMediaAdminPoolMediaCountIntegerTapes with active data
Vw_DPM_Disk_Usage_Replica

Disk usage statistics for replicas in the storage pool.
PhysicalPathStringProtected data source name
Vw_DPM_Disk_Usage_ReplicaReplicaIdGUIDUnique identifier for replica
Vw_DPM_Disk_Usage_ReplicaPGIdGUIDUnique identifier for protection group to which the data source belongs
Vw_DPM_Disk_Usage_ReplicaProductionServerNameStringServer on which data source is located
Vw_DPM_Disk_Usage_ReplicaDiskAllocatedBig IntegerTotal disk space allocated to data source
Vw_DPM_Disk_Usage_ReplicaDiskUsedBig IntegerTotal disk space used by data source
Vw_DPM_Disk_Usage_ReplicaFreeSpaceBig IntegerDiskAllocated - DiskUsed
Vw_DPM_Disk_Usage_ReplicaReplicaAllocatedBig IntegerPart of DiskAllocated reserved for replica of data source
Vw_DPM_Disk_Usage_ReplicaReplicaUsedBig IntegerPart of ReplicaAllocated actually in use
Vw_DPM_Disk_Usage_ReplicaShadowCopyAllocatedBig IntegerPart of DiskAllocated reserved for recovery points of data source
Vw_DPM_Disk_Usage_ReplicaShadowCopyUsedBig IntegerPart of ShadowCopyAllocated actually in use
Vw_DPM_Disk_Usage_ReplicaStartDateTimeDate and time
Vw_DPM_Disk_Usage_ReplicaEndDateTimeDate and time
Vw_DPM_Disk_Usage_ReplicaScheduleTypeInteger

0=weekly; 1=monthly; 2=quarterly; 3=yearly
Vw_DPM_DiskRecoveryPoints

Counts for disk recovery points available for each data source.
DataSourceNameStringProtected data source name
Vw_DPM_DiskRecoveryPointsPGIdGUIDUnique identifier for protection group to which data source belongs
Vw_DPM_DiskRecoveryPointsServerIdGUIDUnique identifier for server to which data source belongs
Vw_DPM_DiskRecoveryPointsFrequencyIntegerNumber of available recovery points
Vw_DPM_LongRecoveries

Historical information about recoveries that took longer than 24 hours.
DataSourceNameStringThe data source that was recovered
Vw_DPM_LongRecoveriesTargetServerNameStringThe name of the server to which recovery was done
Vw_DPM_LongRecoveriesWriterIdGUIDIdentifies the type of the data source that was recovered
Vw_DPM_LongRecoveriesStartTimeDate and timeThe time at which the recovery was started
Vw_DPM_LongRecoveriesEndTimeDate and timeThe time at which the recovery ended
Vw_DPM_Media

Information about state of all tapes known to DPM.
MediaLabelStringThe label on the tape
Vw_DPM_MediaMediaBarcodeStringThe barcode for the tape
Vw_DPM_MediaIsOnlineIntegerWhether the tape is online
Vw_DPM_MediaLibraryNameStringThe name of the library in which the tape exists.

NULL if tape is offline
Vw_DPM_MediaMediaSlotNumberIntegerThe 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_MediaPGNameStringThe name of the protection group in which the tape exists
Vw_DPM_MediaMediaExpiryDateDate and timeThe 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.
LibraryNameStringThe name of the library
Vw_DPM_MediaPool_MediaFreeMediaIntegerNumber of tapes that are free in this library
Vw_DPM_MediaPool_MediaFreeMediaThresholdIntegerThe threshold below which this library generates an alert
Vw_DPM_ProtectedDataSource

Current disk space usage by various data sources.
ReplicaIdGUIDIdentifier of the replica
Vw_DPM_ProtectedDataSourcePGIdGUIDIdentifier of the protection group to which the replica belongs
Vw_DPM_ProtectedDataSourceAllocatedSizeBig integerDisk space allocated to the data source
Vw_DPM_ProtectedDataSourceUsedSizeBig integerDisk space currently used by the data source
Vw_DPM_ProtectedDataSourceProductionServerNameStringThe name of the computer on which the data source exists
Vw_DPM_ProtectedDataSourceStorageNodeStringAlways set to the DPM server
Vw_DPM_ProtectedGroup

Table with information about all protection groups.
PGIdGUIDUnique identifier for the protection group
Vw_DPM_ProtectedGroupProtectionGroupNameStringProtection group name
Vw_DPM_ProtectedGroupCreationTimeDate and timeTime protection group was created
Vw_DPM_RecoveryDuration

History of counts for recovery jobs in various time durations.
StartDateTimeDate and timeThe time at which the statistic was collected
Vw_DPM_RecoveryDurationEndDateTimeDate and timeInternal
Vw_DPM_RecoveryDurationScheduleTypeIntegerThe frequency for which this particular statistic was collected
Vw_DPM_RecoveryDurationRecoveryDurationIntegerIndicates if the recovery was less than 6 hours, between 6-24 hours, or greater than 24 hours
Vw_DPM_RecoveryDurationRecoveryCountIntegerNumber of recoveries
Vw_DPM_RecoveryJob

Detailed information about recent recovery jobs.
DataSourceNameStringThe data source for which recovery was run
Vw_DPM_RecoveryJobServerNameStringThe server to which recovery was performed
Vw_DPM_RecoveryJobCreationTimeDate and timeTime at which the recovery job was run
Vw_DPM_RecoveryJobFailureCodeIntegerError code in case of failure of the recovery job
Vw_DPM_RecoveryJobStatusInteger

0/1=Progress

2=Succeeded

3=Failure
Status of the recovery job
Vw_DPM_RecoveryPointDisk

Status of recent recovery point creation jobs on disk.
StringThe data source for which the backup was createdString
Vw_DPM_RecoveryPointDiskStringThe server on which the data source existsString
Vw_DPM_RecoveryPointDiskDate and timeThe time at which the recovery point creation job was runDate and time
Vw_DPM_RecoveryPointDiskInteger

0/1=Progress

2=Succeeded

3=Failure
Status of the recovery point creation jobInteger

0/1=Progress

2=Succeeded

3=Failure
Vw_DPM_RecoveryPointDiskIntegerZero if succeeded.

Else, set to a DPM error code.
Integer
Vw_DPM_RecoveryPointTape

Status of recent recovery point creation jobs on tape.
Vw_DPM_RecoveryPointTapeStringThe data source for which the backup was createdString
Vw_DPM_RecoveryPointTapeStringThe server on which the data source existsString
Vw_DPM_RecoveryPointTapeDate and timeThe time at which the recovery point creation job was runDate and time
Vw_DPM_RecoveryPointTapeInteger

0/1=Progress

2=Succeeded

3=Failure
Status of the recovery point creation jobInteger

0/1=Progress

2=Succeeded

3=Failure
Vw_DPM_Replica

Listing of all replicas managed by DPM.
ReplicaIdGUIDUnique identifierentifier generated by DPM for the replica volume
Vw_DPM_ReplicaPhysicalPathStringThe name of the data source on the replica
Vw_DPM_ReplicaServerNameStringName of the server to which the data source belongs
Vw_DPM_ReplicaValidFromDate and timeWhen the replica was created
Vw_DPM_ReplicaValidToDate and timeThe date on which the replica was made inactive
Vw_DPM_ReplicaPGIdGUIDUnique identifierentifier generated by DPM for the protection group to which the data source belongs
Vw_DPM_ReplicaStorageNodeStringAlways set to the DPM server
Vw_DPM_Server

List of all protected computers.
ServerIdGUIDUnique identifierentifier generated by DPM for the protected computer
Vw_DPM_ServerServerNameStringFully qualified domain name for the computer
Vw_DPM_ServerNetBiosNameStringName
Vw_DPM_ServerDomainNameStringDomain in which the computer belongs
Vw_DPM_ServerIsRGIntegerIf this computer represents a Resource Group
Vw_DPM_TapeRecoveryPoints

Counts for tape recovery points available for each data source.
DataSourceNameStringThe name of the protected data source
Vw_DPM_TapeRecoveryPointsPGIdGUIDThe unique identifierentifier for the protection group to which this data source belongs
Vw_DPM_TapeRecoveryPointsServerIdGUIDThe unique identifierentifier for the server to which this data source belongs
Vw_DPM_TapeRecoveryPointsFrequencyIntegerThe number of available recovery points
Vw_DPM_TapeRecoveryPointsTermInteger

0=ShortTerm; 1=LongTerm
The schedule to which the recovery point corresponds
Vw_DPM_TapeStat

Historical information on tape usage counts.
StartDateTimeDate and time
Vw_DPM_TapeStatEndDateTimeDate and time
Vw_DPM_TapeStatScheduleTypeIntegerInteger

0=Weekly

1=Monthly

2=Quarterly

3=Yearly
Vw_DPM_TapeStatFreeIntegerNumber of free tapes at end-time
Vw_DPM_TapeStatOnlineIntegerNumber of online tapes at end time
Vw_DPM_TapeUsagePerPG

Historical tape usage data per protection group.
StartDateTimeDate and timeStart time
Vw_DPM_TapeUsagePerPGEndDateTimeDate and timeEnd time
Vw_DPM_TapeUsagePerPGPGNameStringName of the protection group
Vw_DPM_TapeUsagePerPGScheduleTypeIntegerInteger

0=Weekly

1=Monthly

2=Quarterly

3=Yearly
Vw_DPM_TapeUsagePerPGOnlineIntegerNumber of online tapes at end time
Vw_DPM_TapeUsagePerPGOfflineIntegerNumber of offline tapes at end time
Vw_DPM_Total_Disk_Trend

Total disk space usage historical trend.
StartDateTimeDate and time
Vw_DPM_Total_Disk_TrendEndDateTimeDate and time
Vw_DPM_Total_Disk_TrendScheduleTypeIntegerInteger

0=Weekly

1=Monthly

2=Quarterly

3=Yearly
Vw_DPM_Total_Disk_TrendDiskSpaceCapacityBig integerThe total storage in storage pool at end-time
Vw_DPM_Total_Disk_TrendPreviousDiskSpaceCapacityBig integerTotal storage in storage pool in previous corresponding period
Vw_DPM_Total_Disk_TrendDiskSpaceAllocatedBig integerThe disk space from storage pool that has been allocated
Vw_DPM_Total_Disk_TrendPreviousDiskSpaceAllocatedBig integerThe disk space from storage pool that was allocated in the previous corresponding period
Vw_DPM_Total_Disk_TrendDiskSpaceUsedBig integerThe actual disk space usage
Vw_DPM_Total_Disk_TrendPreviousDiskSpaceUsedBig integerThe used disk space in the previous corresponding period
Vw_DPM_Total_RecoveryPoint

Information about all recent recovery point jobs.
DataSourceNameStringThe name of the protected data source
Vw_DPM_Total_RecoveryPointServerNameStringThe server to which the data source belongs
Vw_DPM_Total_RecoveryPointCreationTimeDate and timeThe time at which the recovery point creation job was run
Vw_DPM_Total_RecoveryPointStatusInteger

0/1=Progress

2=Succeeded

3=Failure
Status of the recovery point creation job
Vw_DPM_Total_RecoveryPointErrorCodeIntegerError code in recovery point creation

The following table summarizes the alert types.

-1RestoreDBAlert
0NullType
1AgentIncompatibleAlert
2AgentUnreachableAlert
5MediaVerificationFailedAlert
6MediaEraseFailedAlert
7DetailedInventoryFailedAlert
8MediaDecommissionedAlert
9MediaDataEraseAlert
10FreeMediaThresholdAlert
11DataSetCopyFailedAlert
12BackupToTapeFailedAlert
13BackupToTapeCatalogFailedAlert
14LibraryDriveAlert
15LibraryNotAvailableAlert
16LibraryNotWorkingEfficientlyAlert
17MediaRequiredAlert
18ReplicaInitializationInProgressAlert
19SynchronizationFailedAlert
20StopProtectionFailedAlert
21RecoveryInProgressAlert
22RecoveryPartiallySuccessfulAlert
23RecoverySuccessfulAlert
24RecoveryFailedAlert
25ShadowCopyFailedAlert
26ReplicaInMissingStateAlert
27ReplicaInInvalidStateAlert
28PartialDeployedClusterAlert
29AgentTaskFailAlert
30SqmOptInAlert
31DiskThresholdCrossedAlert
32VerificationInProgressAlert
33DiskMissingAlert
34CatalogThresholdCrossedAlert
35DatasetDataVerificationFailed
36SCDiskThresholdCrossedAlert
37ConfigureProtectionFailedAlert
38ReplicaManualLoadPendingAlert
39ReplicaInitializationPendingAlert
40CertificateExpiringAlert
41EvalShareInquiryAlert
42ShadowCopyConsolidationRequired
Show: