Determine VMs in a SRM recovery plan using PowerShell

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

#requires -version 2

# Description: List the VMs in a SRM recovery plan.
# Usage: .\ListVMsInRecoveryPlan.ps1 -recoveryPlan <Recovery plan name> -dbServer <DB server name> -dbName <SRM db name>

param
(
    [String]$recoveryPlan = $(throw "A -recoveryPlan must be specified."),
    [String]$dbServer = $(throw "A -dbServer must be specified."),
    [String]$dbName = $(throw "A -dbName must be specified.")
)

$vmList = @()

# Wrap the specific .Net errors with our own user friendly message.
try
{
    # Setup SQL connection using trusted authentication
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=$dbServer; Database=$dbName; Integrated Security=True"
    $SqlConnection.Open()
}
catch
{
    throw ("Error connecting to the DB using '{0}'." -f $SqlConnection.ConnectionString)
}

# Query the database
try
{

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection

    $query = @"
        SELECT sv.shadowvmname AS shadowvm_name
        FROM pdsr_shadowvm sv,
        	(SELECT sg.mo_id AS groupmoid, convert(varchar(255), g.string_val) AS shadowvmmoid
        		FROM pdsr_shadowgroup sg
        		LEFT OUTER JOIN g_string_array g
        		ON sg.vmmoids = g.seq_id) sg,
        	(SELECT rp.name AS plan_name, convert(varchar(255), g.string_val) AS shadowgroupmoid
        		FROM pdsr_recoveryprofile rp
        		LEFT OUTER JOIN g_string_array g
        		ON rp.shadowgroupmoids = g.seq_id) rp
        WHERE sg.shadowvmmoid = sv.mo_id
        	AND rp.shadowgroupmoid = sg.groupmoid
            AND rp.plan_name LIKE '{0}'
"@ -f $recoveryPlan

    $SqlCmd.CommandText = $query
    # Execute the query
    $data = $SqlCmd.ExecuteReader()

    while ($data.Read())
    {
        $vmList += $data.GetValue(0)
    }
}
catch
{
    throw "Error reading data from the database."
}
finally
{
    # Clean up the DB connections.
    $data.Close()
    $SqlConnection.Close()
}

# Return the results.
$vmList