WFA cluster mode provisioning - filter aggregates based on vserver

[trentino123]

Dear WFA community,

Was working on a WFA CIFS provisioning, which as a dropdown input had the Cluster dropdown and the SVM dropdown, to choose where to place the Volume.

The problem appears when we try to choose a disk type, that forces us to dropdown aggregates ( disk type is part of the aggregate naming convention ).

Would like to filter the aggregates belonging to the already chosen SVM.

Any idea for a SQL JOIN statement? Have tried joining cm_storage.vserver/vserver_allowed_aggregates/aggregate with no luck yet.

Is there other way to filter aggregates by disk type and SVM?

Thanks in advance!!

[adaikkap]

Suggest you combine two filters like the following in a finder and then use it.

Filter aggregates by disk type

Filter aggregates by delegation to Storage Virtual Machine

Regards

adai

[GaurabBanerjee]

Hello,

I like to understand one statement:

"Would like to filter the aggregates belonging to the already chosen SVM."

Is one to many relations possible wrt SVM?

Thanks,

   --Gaurab

[trentino123]

Thank you for your responses.

Although you can filter aggregates within the Create Volume command automatically by the use of a finder, I was looking to have an aggregate dropdown to choose the node/aggregate to create the volume into based on an aggregate disk type naming convention.

Found this SQL query to work:

SELECT

    aggregate.name,

    node.id,

    cluster.id

FROM

    cm_storage.cluster,

    cm_storage.node,

    cm_storage.aggregate

WHERE

    cluster.name = '${ClusterName}'

    AND node.cluster_id = cluster.id

    AND aggregate.node_id = node.id

    AND aggregate.name NOT LIKE '%aggr0%'

The query will show a pulldown of aggregates where to choose, than can be later by filtered again against a disk type naming convention withing the aggregate name.

The aggregates are shared within the cluster, was also looking if a vserver was 'tied' with certain aggregates only, we could filter those aggregates withing the cm_storage.vserver_allowed_aggregates database, but apparently that seldom used.

Thx!

[shailaja · Accepted solution]

Hi,

As I understand, you are referring to display of the aggregate list when executing the workflow and
allowing the user to choose one of them where to place the volume.

Check if you could write a query in the lines of:

SELECT

aggr.name AS 'Aggregate',

aggr.size_mb AS 'Total Size (MB)',

aggr.used_size_mb AS 'Used Size (MB)',

aggr.available_size_mb AS 'Available Size (MB)'

FROM
cm_storage.aggregate AS aggr

JOIN
cm_storage.node AS node

ON node.id = aggr.node_id

JOIN
cm_storage.cluster AS cluster

ON cluster.id = node.cluster_id

JOIN
cm_storage.vserver_allowed_aggregate AS vserver_allowed_aggregate

ON vserver_allowed_aggregate.aggregate_id = aggr.id

JOIN
cm_storage.vserver AS mapped_vserver

ON vserver_allowed_aggregate.vserver_id = mapped_vserver.id

WHERE
mapped_vserver.restricted_aggregate_count > 0

AND mapped_vserver.name = '${VserverName}'

AND (cluster.name = '${ClusterName}'
OR cluster.primary_address = '${ClusterName}'
)
AND aggr.name LIKE '%${DiskType}%'

UNION

SELECT

aggr.name AS 'Aggregate',

aggr.size_mb AS 'Total Size (MB)',

aggr.used_size_mb AS 'Used Size (MB)',

aggr.available_size_mb AS 'Available Size (MB)'
FROM
cm_storage.aggregate AS aggr

JOIN
cm_storage.node AS node

ON node.id = aggr.node_id

JOIN
cm_storage.cluster AS cluster

ON cluster.id = node.cluster_id

JOIN
cm_storage.vserver AS mapped_vserver

ON cluster.id = mapped_vserver.cluster_id

WHERE
mapped_vserver.restricted_aggregate_count = 0

AND mapped_vserver.name = '${VserverName}'

AND (cluster.name = '${ClusterName}'
OR cluster.primary_address = '${ClusterName}'
)
AND aggr.name LIKE '%${DiskType}%'

- Above query selects aggregates that have been assigned to a SVM or all aggregates in case a SVM is not assigned any i.e it is allowed to use any aggregate in the cluster.
- Last part in the query above "AND aggr.name LIKE '%${DiskType}%'" should be changed to refer to the naming conventions used by you for disk type.
- $DiskType would be a user input similar to $ClusterName and $VserverName

Hope this helps.

Thanks,
Shailaja

[trentino123]

Hi Shailaja !

That was a great SQL query. Worked perfectly.

Thanks!

---