topic Re: SQL Query for "inactive" volumes in Active IQ Unified Manager Discussions

SQL Query for "inactive" volumes

coreywanless — Wed, 04 Jun 2025 23:08:52 GMT

I'm stumped, and am hoping someone can help me find my flaw in the sql query I'm trying to write. I'm writing a query that will be used in a multi-select user input. I am attempting to find 'inactive' volumes to offline. This means that I'm looking for volumes that have no associated shares, nfs exports, and active luns. Below is my query. When it runs, it returns no results. (Which I know is completely wrong) I did some troubleshooting and have narrowed it down with it not liking the nfs export line. If I comment out that line, I get a result. As soon as I add in the nfs export line the result becomes zero. I would appreciate any input anyone has.

select array.name as Array, volume.name as Volume, volume.discovery_timestamp
FROM storage.volume join storage.array on volume.array_id = array.id
where
##Make sure the Volume does not contain an active LUN.
volume.id NOT IN (select lun.volume_id from storage.lun where lun.is_online = '1' AND lun.mapped = '1')
##Make sure the volume doesn't have an active share associated with it.
AND volume.id NOT IN (select storage.cifs_share.volume_id FROM storage.cifs_share)
##Make sure the volume doesn't have an active share associated with it.
AND volume.id NOT IN (select nfs_export.volume_id FROM storage.nfs_export)
order by Array,Volume,discovery_timestamp

Re: SQL Query for "inactive" volumes

coreywanless — Wed, 07 Oct 2015 12:43:36 GMT

*Bump* - I know this is not good etiquette, but I'm stumped on this seemingly simple issue.

Re: SQL Query for "inactive" volumes

korns — Thu, 05 Nov 2015 17:55:32 GMT

You've may have resolved by now, but if I remember correctly in 7-mode there is a setting (option?) called auto export or something similar. So even if a volume is created and never intended for nfs use it may still be exported. Have you peeked in the /etc/exports file for any remnents of a line that references said volume(s)?

The better debug technique would be to use a SQL query tool (MySql workbench, Heidi, etc) to look at the contents of the storage.nfs_exports table

Re: SQL Query for "inactive" volumes

coreywanless — Wed, 18 Nov 2015 15:35:56 GMT

I just wanted to give an update. I was able to devote some time to this, and redesign the query. Along the way I had to make some corrections within WFA and DFM.

1. WFA by default doesn't pull the cifs shares on the 7 mode data sources. - You have to enable the acquisition under Designer -> Dictionaries

2. DFM wasn't pulling the export data properly from the 7-Mode filers. I had to fix configurations on some of the filers within DFM. (Without doing that some of the volumes would show up as not being used)

Finally, Attached was the query that I have re-writen since my original post.

DISCLAIMER: This query is provided at NO guarantee. You should manually check your volumes outside of WFA before making any action based on the query I provide. This query is only to assist you in not shooting yourself in the foot by accidently offlining/destroying a volume that has something currently active.