Export (0) Print
Expand All

Use predefined SQL views

Updated: March 12, 2014

Applies To: System Center 2012 - Data Protection Manager, System Center 2012 R2 Data Protection Manager, System Center 2012 SP1 - 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

-----
For additional resources, see Information and Support for System Center 2012.

Tip: Use this query to find online documentation in the TechNet Library for System Center 2012. For instructions and examples, see Search the System Center 2012 Documentation Library.
-----
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft