Snapshot policy query: how to list in order by number of volumes already assigned to policy

[CAPATEL_NET1984]

Hello All,

just working on WFA and seems it will be very helpful if I can build intelligence around how the listing of snapshot policy works for a primary volume. is it possible to show some details info in the query for ex number of volumes or number of snapshots taken by it for that whole cluster so user know if he should use any other policy?

[CAPATEL_NET1984 · Accepted solution]

I was able to found the query from wfaguy.com.

posting here hoping someone can fidn it useful.

in regex input you can just put a whilecard for ex .*T1 

where T1 is first two digits of policy name.

and at the end I put LIMIT 100 to list all policy but you can put it as 1 and thats the least used policy.

all creadit goes to wfaguy 

SELECT
snapshot_policy.name,
vserver.name AS 'vserver.name',
cluster.primary_address AS 'vserver.cluster.primary_address',
count(volume.name)
FROM
cm_storage.snapshot_policy
LEFT JOIN
cm_storage.volume
ON volume.snapshot_policy_id=snapshot_policy.id
LEFT JOIN
cm_storage.vserver
ON snapshot_policy.vserver_id = vserver.id
LEFT JOIN
cm_storage.cluster
ON vserver.cluster_id = cluster.id
WHERE
snapshot_policy.name REGEXP '${regex}'
AND (
cluster.name = '${cluster_name}'
OR cluster.primary_address = '${cluster_name}'
)
AND vserver.name = '${vserver_name}'
GrOUp by
snapshot_policy.name,
vserver.name,
cluster.primary_address
ORDER BY
count(volume.name) LIMIT 100

[CAPATEL_NET1984]

also if the policy is created at cluster level then in the vserevr use admin vserver name. you can find admin vserver name by using vserver show command