How to Get a Empty Volume List via SQL Query or PowerShell

[raj_shrivastava11]

Hi Team,

Need help in Getting a Empty Volume List either via MySQL Query or PowerShell.

I have checked the MySQL database side but does not get any option to get this.

Also tried via PowerShell, by getting "Used %" but that is not correct info.

Thanks and Regars

Raj

[mbeattie]

Hi Raj,

What are you ultimately trying to achieve or automate once you have this information?

As you've mentioned MySQL as an option are you using WFA as the source for your query?

If so you could use something like:

For 7-Mode:

#'---------------------------------------
#'Select the empty data volumes.
#'---------------------------------------
SELECT
   volume.name AS 'volume_name',
   aggregate.name AS 'aggregate_name',
   vfiler.name AS 'vfiler_name',
   array.name AS 'controller_name',
   array.ip AS 'controller_address'
FROM
   storage.volume,
   storage.aggregate,
   storage.vfiler,
   storage.array
WHERE
   volume.vfiler_id = vfiler.id
AND
   vfiler.array_id = array.id
AND
   volume.aggregate_id = aggregate.id
AND
   volume.used_size_mb = 0;
#'---------------------------------------

For cDOT

#'---------------------------------------
#'Select the empty data volumes.
#'---------------------------------------
SELECT
   volume.name AS 'volume_name',
   aggregate.name AS 'aggregate_name',
   vserver.name AS 'vserver_name',
   node.name AS 'node_name',
   cluster.name AS 'cluster_name',
   cluster.primary_address
FROM
   cm_storage.volume,
   cm_storage.aggregate,
   cm_storage.vserver,
   cm_storage.node,
   cm_storage.cluster
WHERE
   volume.vserver_id = vserver.id
AND
   vserver.cluster_id = cluster.id
AND
   volume.aggregate_id = aggregate.id
AND
   aggregate.node_id = node.id
AND
   volume.junction_path <> '/'
AND
   volume.used_size_mb = 0;
#'---------------------------------------

Note: or change 'volume.used_size_mb <= {$VolumeSizeMB}' assuming you want to enumerate volumes of a small (but not totally empty) size.

/matt

[raj_shrivastava11]

Thanks Guys.

Actually i m working on a workflow which can give me a list of completely empty volumes.

Used % will not give me the exact list.

I am also trying to look into Read-NaDirectory where i can do LS on each volume and see if there is any content and return one which does not have any content.

BR

Raj

[asulliva]

What was incorrect about used %?  Aside from it's not very accurate (1% of 1GB = 10MB, 1% of 1TB = 10GB...that's a big difference).

As @JGPSHNTAP mentioned, it's difficult to find a truly empty volume because the system has a few KB for metadata, etc.  I think the closest thing would be to look for a volume with used space of less than 512KB:

Get-NcVol -Query @{VolumeSpaceAttributes=@{SizeUsed='<512KB'};VolumeStateAttributes=@{IsNodeRoot=$false;IsVserverRoot=$false}}

That should narrow the returned volumes back significantly.  Theoretically it might still be possible to have a volume with something in it, for example and thin provisioned LUN which hasn't had any data written to it, or a share/export which has empty files/folders.  If you want to be 100% safe, you'll want to mount the volume (NFS/CIFS) and check the contents.

Hope that helps.

Andrew

[JGPSHNTAP · Accepted solution]

I assume from his tag, this is for 7-mode so it's easier..

get-navol | ? {$_.sizeused < xxxx}

[JGPSHNTAP]

Anything can be done in powershell.  

The issue is that there is truly no ZERO volume b/c when you create a volume there is a small amount of data on the volume.

You can query for all volumes with size less than x and then go from there.