Allow selection of aggregate when creating new volume

[DanielHankins]

I am attempting to customize the workflow "Create a Clustered Data ONTAP NFS Volume". As the workflow functions by default, the workflow selects the aggregate. I need to be able to provide a list of aggregates for the user to choose from, and I have not found a modification that works. Could someone either provide any insight, or point me in a direction that could help me resolve this?

Thanks! 

[NICKBARTON]

@DanielHankins

First make sure you are working with a clone of the built in workflow so you still have a valid copy to take from if you want to start over. 

You need to create a new User Input for the Aggreate drop down list. You can call it whatever you want for this discussion we will use AggregateName. Make AggregateName a SQL query and build you query to select a list of aggreagtes that aren't root based on the cluster selection made previously in the workflow: $ClusterName.

This will populate your aggregate list for the user to choose from. Then you need to edite the volume step in the workflow and remove the existing finder for aggregate and put in your $AggregateName variable that your created earlier. This will direct the volume creation to the aggregate choosen in user input portion of the workflow. 

This is a very high level explanation wasn't sure how much detail you needed if you need more help let me know. 

-Nick 

[DanielHankins]

@NICKBARTON

I appreciate your suggestion, which sounds exactly what I'm attempting to perform. I'm new to SQL queries, and I haven't found any examples to go by. Would you happen to have an example SQL query for aggregate?

Daniel

[DanielHankins]

Would anyone be able to provide me with example SQL query for selecting aggregates for the new volume to be created upon? Any help is greatly appreciated.

Daniel

[NICKBARTON · Accepted solution]

@DanielHankins

Sorry lost track of this for a few days.. You'll want to use something like the following: 

SELECT
aggregate.name,
node.name AS 'node.name',
cluster.primary_address AS 'node.cluster.primary_address'
FROM
cm_storage.cluster,
cm_storage.node,
cm_storage.aggregate
WHERE
node.id = aggregate.node_id
AND cluster.id = node.cluster_id
AND (
cluster.name = '${cluster_name}'
OR cluster.primary_address = '${cluster_name}'
)

You can dig through the filters in the design section of WFA if you ever need some good examples of SQL queries.

[DanielHankins]

@NICKBARTON

That example was helpful to get me started. I have merged together some queries and ended up with this so that I can show capacity info as well:

SELECT
    CONCAT(
    aggregate.name) AS 'Aggregate',
    aggregate.size_mb AS 'Total Size (MB)',
    aggregate.available_size_mb AS 'Available Size (MB)',
    COUNT(volume.id ) AS 'Volume Count'
FROM
    cm_storage.aggregate aggregate
LEFT JOIN
    cm_storage.volume volume
        ON volume.aggregate_id = aggregate.id
JOIN
    cm_storage.node node
        ON aggregate.node_id = node.id
JOIN
    cm_storage.cluster cluster
        ON node.cluster_id = cluster.id
WHERE
    cluster.name = '${ClusterName}'
    AND aggregate.name NOT LIKE '%aggr0%'
GROUP BY
    aggregate.id
ORDER BY
    aggregate.name ASC LIMIT 1000

Greatly appreciate your help! 

[NICKBARTON]

@DanielHankins

The issue is that the parameter aggregate in the command create volume to create the volume has a strict type definition. The object needs to be of type aggregate.cm_storage. The safest way for you to do this is to create your own finder for this workflow. Make it of type aggregage (cm_storage) from the list and choose the Filter Aggregates by Key and Filter Aggrs Not Aggr0 (Added this one in just to be safe) (Screenshots Below). 

Once you get your finder created edit the volume step in your workflow and for aggregate parameter make it automatically selected and choose your finder that you created from above. You will have to provide the variable names for Cluster,Node and Aggregate Name in order for this finder to work so you will want to add node to your values you return from your Query. Once you have this finished it should accept your choosen aggregate as will be of the type it's looking for. 

The other option would be copy the Create Volume Command so that you can modify the parameter in the command to remove the strict type setting it has, you would then use this command in place of the default Create Volume command in your workflow step this would allow you to just set the value to $AggregateName. This seems like a quicker option and will probably work but if/when you look at the code they are using to determine aggregate it's a failrly involved if statement block. This is an option you can try just not sure how much testing you will need to do with it in your environment may work 9 times out of 10. 

Thank You, 

Nick Barton