WFA - Filtering Export Policies by Volume and Qtree

[lil_baby_james]

Folks,

I'm looking to provide a server deployment team with the ability to add servers to existing export policies via a REST call to a workflow.

The problem is that their team has no knowledge of the export policy name among other fields, which requires some granular filtering on our part. 

Right now they have the vserver and mount path, which could either be /volume or /volume/qtree. It should be filterable with an SQL query, but I can't seem to nail it down. Does anyone have any suggestions to facilitate this? I've linked my SQL query for the export policy filter below. 

Thanks

SELECT
export_policy.name,
vserver.name AS 'vserver.name',
cluster.primary_address AS 'vserver.cluster.primary_address'
FROM
cm_storage.qtree,
cm_storage.volume,
cm_storage.export_policy,
cm_storage.vserver,
cm_storage.cluster
WHERE
qtree.name = '${qtree_name}'
AND volume.name = '${volume_name}'
AND vserver.name = '${vserver_name}'
AND qtree.volume_id = volume.id
AND volume.vserver_id = vserver.id
AND cluster.id = vserver.cluster_id
AND export_policy.vserver_id = vserver.id

[moep]

Do you use Export Policies on the Qtree level? The default schema of WFA has no information of the export policy at Qtree level.

If you want this information you have to create a new Dictionary Entry and Cache Query for that. I can provide an example if needed.

[lil_baby_james]

Yes it's mostly at the qtree level, but its not uncommon to see it at the volume level as well. I'd really appreciate looking at your example.

[moep · Accepted solution]

Here is my Finder Query.

SELECT
    export_policy.name,
    vserver.name AS 'vserver.name',
    cluster.primary_address AS 'vserver.cluster.primary_address' 
FROM
    cm_storage.export_policy,
    cm_storage.volume,
    cm_storage.qtree_custom,
    cm_storage.vserver,
    cm_storage.cluster 
WHERE
    export_policy.id = qtree_custom.export_policy_id     
    AND qtree_custom.volume_id = volume.id     
    AND volume.name = '${vol_name}'     
    AND volume.vserver_id = vserver.id     
    AND qtree_custom.name = '${qtree_name}'     
    AND vserver.name = '${vserver_name}'     
    AND vserver.cluster_id = cluster.id     
    AND (
        cluster.name = '${cluster_name}'         
        OR cluster.primary_address = '${cluster_name}'     
    )

I attached the Dictionary entry and Cache Query from my WFA 4.2. I called it "Qtree_Custom".

[lil_baby_james]

Very helpful, I appreciate it.