WFA Mysql query statement to list snapshot policies in a cluster

[krishgudd]

Hi,

I am looking for WFA mysql query statement to list the snapshot policies in a cluster/vserver. Can someone help me on this.

Thanks in Advance,

Krishgudd

[MattInCO · Accepted solution]

@krishgudd

This is what I came up with real quick. I listed a few extra attributes in here, which may or may not be helpful, and in this case a cluster needs to be defined via another variable. Under “SVM” it will show the cluster name if it is a cluster-defined snapshot policy, or the SVM name if it is an SVM-defined snapshot policy. I’m not a MySQL query expert by any stretch, so this could probably be cleaner, but it looks to work. I hope this points you in the right direction, at least.

SELECT
     snapshot_policy.name AS 'Snapshot Policy',
     schedule.name AS 'Schedule name',
     snapshot_policy_schedule.snapshot_count AS 'Snapshots to Retain',
     vserver.name AS 'SVM'
FROM
     cm_storage.cluster,
     cm_storage.vserver,
     cm_storage.snapshot_policy,
     cm_storage.snapshot_policy_schedule,
     cm_storage.schedule
WHERE
     vserver.cluster_id = cluster.id
     AND snapshot_policy.vserver_id = vserver.id
     AND snapshot_policy_schedule.snapshot_policy_id = snapshot_policy.id
     AND snapshot_policy_schedule.schedule_id = schedule.id
     AND cluster.name = '${cluster}'
ORDER BY
    snapshot_policy.name,
    snapshot_policy_schedule.snapshot_count,
    schedule.name,
    vserver.name

[mbeattie]

Hi,

Does this work for you?

SELECT
    snapshot_policy.name,
    vserver.name AS 'vserver.name',
    cluster.primary_address AS 'vserver.cluster.primary_address'
FROM
    cm_storage.snapshot_policy,
    cm_storage.vserver,
    cm_storage.cluster
WHERE
    snapshot_policy.vserver_id = vserver.id
AND
    vserver.cluster_id = cluster.id
AND
    vserver.name =  '${VserverName}'
AND (
    cluster.name = '${ClusterName}'
    OR cluster.primary_address = '${ClusterName}'
)

That should list all the snapshot policies assigned to a cluster\vserver. Alternately if you want to filter to a specific snapshot policy name then change the AND clause to add the policy name

AND
    vserver.cluster_id = cluster.id
AND
    vserver.name =  '${VserverName}'
AND
    snapshot_policy.name = '${SnapShotPolicyName}'
AND (
    cluster.name = '${ClusterName}'
    OR cluster.primary_address = '${ClusterName}'
)

Hope that helps

/Matt