topic WFA Select only volume where there is no CIFS share in Active IQ Unified Manager Discussions

WFA Select only volume where there is no CIFS share

masson — Wed, 04 Jun 2025 21:43:20 GMT

Hello Team,

I'm trying to create a workflow where i have to select in a particular SVM only volume without any CIFS share.

I've create the following SQL Query:

SELECT
        volume.name as volname
FROM
        cm_storage.volume as volume,
        cm_storage.vserver as vserver,
        cm_storage.cluster as cluster
WHERE
        cluster.name='${ClusterName}'
   AND vserver.name='${VserverName}'
   AND volume.vserver_id=vserver.id
   AND volume.name LIKE '%vol_${VserverName}_w%'
   AND volume.name NOT LIKE '%w00%'
   AND volume.name NOT IN
(SELECT DISTINCT
   substr(cifs_share.path,2,17) as volumeName
FROM
   cm_storage.cifs_share as cifs_share,
   cm_storage.cluster as cluster,
   cm_storage.vserver as vserver
WHERE
   cluster.name='${ClusterName}'
   AND vserver.name='${VserverName}'
   AND cifs_share.path LIKE '%vol_${VserverName}_w%')

I know that using NOT IN is not recommended and indeed this query is a bit slow.

Do you have any idea to rewrite this query with something more efficient.

Regards.

Re: WFA Select only volume where there is no CIFS share

coreywanless — Mon, 21 Mar 2016 16:39:51 GMT

I love doing joins. Here is my rendition:

select
cluster.name as cluster,
vserver.name as vserver,
volume.name as volume
from
cm_storage.volume
left join cm_storage.vserver on vserver.id = volume.vserver_id
left join cm_storage.cluster on cluster.id = vserver.cluster_id
left join cm_storage.cifs_share on cifs_share.path like concat(volume.junction_path,'%')
where cifs_share.id is null

NOTE: Append any where statements you need

Re: WFA Select only volume where there is no CIFS share

masson — Thu, 24 Mar 2016 08:47:51 GMT

Thanks Coreywanless,

It works very quicky

Best regards.