Effective December 3, NetApp adopts Microsoft’s Business-to-Customer (B2C) identity management to simplify and provide secure access to NetApp resources.
For accounts that did not pre-register (prior to Dec 3), access to your NetApp data may take up to 1 hour as your legacy NSS ID is synchronized to the new B2C identity.
To learn more, read the FAQ and watch the video.
Need assistance? Complete this form and select “Registration Issue” as the Feedback Category.

Active IQ Unified Manager Discussions

WFA Select only volume where there is no CIFS share

masson

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.

1 ACCEPTED SOLUTION

coreywanless

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

View solution in original post

2 REPLIES 2

coreywanless

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

View solution in original post

masson

Thanks Coreywanless,

 

It works very quicky

 

Best regards.

Announcements
NetApp on Discord Image

We're on Discord, are you?

Live Chat, Watch Parties, and More!

Explore Banner

Meet Explore, NetApp’s digital sales platform

Engage digitally throughout the sales process, from product discovery to configuration, and handle all your post-purchase needs.

NetApp Insights to Action
I2A Banner
Public