Share via

Appendix C: Custom Report Views

  • Article

Applies To: System Center Data Protection Manager 2010

Data Protection Manager 2010 includes several SQL views to help you create custom reports.

SQL views simplify your queries by populating columns with data collected from multiple tables in the database. These views offer several advantages over querying the tables directly:

  • You do not need in-depth knowledge of the entire database or the relationship between tables and keys.

  • If the database structure changes in future versions of the product, the views can be updated so that they behave the same.

For DPM installations that use a separate, dedicated computer for the SQL Server database, the views are queried on the database computer, not the computer running DPM. This results in less competition for resources when large numbers of views are queried over a short period of time.

The potential disadvantages of the SQL views include the following:

  • Because the view runs each time it is queried, server performance may be degraded if the view is used too frequently.

  • The available supported views might not include all of the columns you need.

This appendix lists the views available in DPM.

Vw_DPM_Agents: Contains the list of computers on which a DPM protection agent from this DPM server has been installed.

Field Data type Description

ServerName

String

The name of the computer

Version

String

The version of the DPM agent on that computer

Vw_DPM_Alerts: List of all alerts from the last 30 days.

Field Data type Description

Severity

Integer

0=Error

1=Warning

2=Information

The severity level of the alert

Resolution

Integer

0 = Active

1 = Recommended action in progress

2 = Resolved

The state of the alert

OccurredSince

Date and time

The first time this alert was raised

ResolvedTime

Date and tiime

The time at which the alert was resolved

Type

Integer

See "Alert Types" in this appendix

The type of the alert

Vw_DPM_CurrentOnlineMedia: The tapes that are online in DPM owned libraries currently, as of the last inventory.

Field Data type Description

UserFriendlyName

String

The name of the library

ImportPoolMediaCount

Integer

Tapes imported to this DPM server

FreePoolMediaCount

Integer

Tapes marked as free or blank

AdminPoolMediaCount

Integer

Tapes with active data. Expired tapes change to free when the tape is marked free or the protection group is deleted.

Vw_DPM_Disk_Usage_Replica: Disk usage statistics for replicas in the storage pool.

Field Data type Description

PhysicalPath

String

The name of the protected data source

ReplicaId

GUID

Unique identifier for the replica on DPM disks

PGId

GUID

Unique identifier for the protection group to which this data source belongs

ProductionServerName

String

The name of the server on which the data source exists

DiskAllocated

Big integer

Total disk space allocated to this data source

DiskUsed

Big integer

Total disk space used by this data source

FreeSpace

Big integer

DiskAllocated – DiskUsed

ReplicaAllocated

Big integer

The part of DiskAllocated that is reserved for the replica of the data source

ReplicaUsed

Big integer

The part of ReplicaAllocated that is actually in use

ShadowCopyAllocated

Big integer

The part of DiskAllocated that is reserved for the recovery points of the data source

ShadowCopyUsed

Big integer

The part of ShadowCopyAllocated that is actually in use

StartDateTime

Date and time

The time this statistic was collected

EndDateTime

Date and time

Internal field

ScheduleType

Integer

0=Weekly

1=Monthly

2=Quarterly

3=Yearly

The schedule period which this data represents

Vw_DPM_DiskRecoveryPoints: Counts for disk recovery points available for each data source.

Field Data type Description

DataSourceName

String

The name of the protected data source

PGId

GUID

The unique identifierentifier for the protection group to which this data source belongs

ServerId

GUID

The unique identifierentifier for the server to which this data source belongs

Frequency

Integer

The number of available recovery points

Vw_DPM_LongRecoveries: Provides historical information about recoveries that took longer than 24 hours.

Field Data type Description

DataSourceName

String

The data source that was recovered

TargetServerName

String

The name of the server to which recovery was done

WriterId

GUID

Identifies the type of the data source that was recovered

StartTime

Date and time

The time at which the recovery was started

EndTime

Date and time

The time at which the recovery ended

RecoverySize

Big integer

The size of the data recovered by the job

RecoverySource

Integer

0=Disk

1=Tape

The recovery source

Vw_DPM_Media: Provides information about state of all tapes known to DPM.

Field Data type Description

MediaLabel

String

The label on the tape

MediaBarcode

String

The barcode for the tape

IsOnline

Integer

Whether the tape is online

LibraryName

String

The name of the library in which the tape exists.

NULL if tape is offline

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).

PGName

String

The name of the protection group in which the tape exists

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.

Field Data type Description

LibraryName

String

The name of the library

FreeMedia

Integer

Number of tapes that are free in this library

FreeMediaThreshold

Integer

The threshold below which this library generates an alert

Vw_DPM_ProtectedDataSource: Current disk space usage by various data sources.

Field Data type Description

ReplicaId

GUID

Identifier of the replica

PGId

GUID

Identifier of the protection group to which the replica belongs

AllocatedSize

Big integer

Disk space allocated to the data source

UsedSize

Big integer

Disk space currently used by the data source

ProductionServerName

String

The name of the computer on which the data source exists

StorageNode

String

Always set to the DPM server

Vw_DPM_ProtectedGroup: Table with information about all protection groups.

Field Data type Description

PGId

GUID

Unique identifierentifier for the protection group

ProtectionGroupName

String

Name of the protection group

CreationTime

Date and time

The time at which the protection group was created

Vw_DPM_RecoveryDuration: History of counts for recovery jobs in various time durations.

Field Data type Description

StartDateTime

Date and time

The time at which the statistic was collected

EndDateTime

Date and time

Internal

ScheduleType

Integer

The frequency for which this particular statistic was collected

RecoveryDuration

Integer

Indicates if the recovery was less than 6 hours, between 6-24 hours, or greater than 24 hours

RecoveryCount

Integer

Number of recoveries

Vw_DPM_RecoveryJob: Detailed information about recent recovery jobs.

Field Data type Description

DataSourceName

String

The data source for which recovery was run

ServerName

String

The server to which recovery was performed

CreationTime

Date and time

Time at which the recovery job was run

FailureCode

Integer

Error code in case of failure of the recovery job

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.

Field Data type Description

DataSourceName

String

The data source for which the backup was created

ServerName

String

The server on which the data source exists

CreationTime

Date and time

The time at which the recovery point creation job was run

Status

Integer

0/1=Progress

2=Succeeded

3=Failure

Status of the recovery point creation job

ErrorCode

Integer

Zero if succeeded.

Else, set to a DPM error code.

Vw_DPM_RecoveryPointTape: Status of recent recovery point creation jobs on tape.

Field Data type Description

DataSourceName

String

The data source for which the backup was created

ServerName

String

The server on which the data source exists

CreationTime

Date and time

The time at which the recovery point creation job was run

Status

Integer

0/1=Progress

2=Succeeded

3=Failure

Status of the recovery point creation job

ErrorCode

Integer

Zero if succeeded.

Else, set to a DPM error code.

Vw_DPM_Replica: Listing of all replicas managed by DPM.

Field Data type Description

ReplicaId

GUID

Unique identifierentifier generated by DPM for the replica volume

PhysicalPath

String

The name of the data source on the replica

ServerName

String

Name of the server to which the data source belongs

ValidFrom

Date and time

When the replica was created

ValidTo

Date and time

The date on which the replica was made inactive

PGId

GUID

Unique identifierentifier generated by DPM for the protection group to which the data source belongs

StorageNode

String

Always set to the DPM server

Vw_DPM_Server: List of all protected computers.

Field Data type Description

ServerId

GUID

Unique identifierentifier generated by DPM for the protected computer

ServerName

String

Fully qualified domain name for the computer

NetBiosName

String

Name

DomainName

String

Domain in which the computer belongs

IsRG

Integer

If this computer represents a Resource Group

Vw_DPM_TapeRecoveryPoints: Counts for tape recovery points available for each data source.

Field Data type Description

DataSourceName

String

The name of the protected data source

PGId

GUID

The unique identifierentifier for the protection group to which this data source belongs

ServerId

GUID

The unique identifierentifier for the server to which this data source belongs

Frequency

Integer

The number of available recovery points

Term

Integer

0=ShortTerm

1=LongTerm

The schedule to which this recovery point corresponds

Vw_DPM_TapeStat: Historical information on tape usage counts.

Field Data type Description

StartDateTime

Date and time

 

EndDateTime

Date and time

 

ScheduleType

Integer

 Integer

0=Weekly

1=Monthly

2=Quarterly

3=Yearly

Free

Integer

Number of free tapes at end-time

Online

Integer

Number of online tapes at end time

Vw_DPM_TapeUsagePerPG: Historical tape usage data per protection group.

Field Data type Description

StartDateTime

Date and time

 Start time

EndDateTime

Date and time

 End time

PGName

String

Name of the protection group

ScheduleType

Integer

  Integer

0=Weekly

1=Monthly

2=Quarterly

3=Yearly

Online

Integer

Number of online tapes at end time

Offline

Integer

Number of offline tapes at end time

Vw_DPM_Total_Disk_Trend: Total disk space usage historical trend.

Field Data type Description

StartDateTime

Date and time

 

EndDateTime

Date and time

 

ScheduleType

Integer

 Integer

0=Weekly

1=Monthly

2=Quarterly

3=Yearly

DiskSpaceCapacity

Big integer

The total storage in storage pool at end-time

PreviousDiskSpaceCapacity

Big integer

Total storage in storage pool in previous corresponding period

DiskSpaceAllocated

Big integer

The disk space from storage pool that has been allocated

PreviousDiskSpaceAllocated

Big integer

The disk space from storage pool that was allocated in the previous corresponding period

DiskSpaceUsed

Big integer

The actual disk space usage

PreviousDiskSpaceUsed

Big integer

The used disk space in the previous corresponding period

Vw_DPM_Total_RecoveryPoint: Information about all recent recovery point jobs.

Field Data type Description

DataSourceName

String

The name of the protected data source

ServerName

String

The server to which the data source belongs

CreationTime

Date and time

The time at which the recovery point creation job was run

Status

Integer

0/1=Progress

2=Succeeded

3=Failure

Status of the recovery point creation job

ErrorCode

Integer

Error code in recovery point creation

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