Active IQ Unified Manager Discussions

SQL query for vfiler volumes

raovolvoadmin
4,733 Views

Hello,

I would like to Query the All volumes in the Vfiler(not the hosting filers volumes).I tried with below SQL query.but it's not working:-(.

Can somebody help me on this?.

SQL query is :

Select distinct(volume.name) AS Name,volume.size_mb  AS 'Total Size (MB)'

From Storage.volume,storage.vfiler

WHERE  volume.vfiler_id=vfiler.id AND vfiler.name='${Vfiler}'

Thanks.

Regards

Srinivas

4 REPLIES 4

sinhaa
4,732 Views

Hi Srinivas,

     I believe you are trying to create  WFA filter and use the query to obtain all the volumes contained in a given vfiler. Correct?

While this query is right and works fine with an SQL client, WFA infrastructure needs to validate the returned attributes against all the natural keys of the selected dictionary item.


Do the following:

Create a filter with Dictionary Type : Volume : Storage

Use this query:
---------------------------

SELECT DISTINCT(volume.name) AS name,  array.ip AS 'array.ip', volume.size_mb  AS 'Total Size (MB)'

FROM Storage.volume,storage.vfiler, storage.array

WHERE  volume.vfiler_id=vfiler.id
AND vfiler.array_id = array.id
AND vfiler.name='${Vfiler}'

---------------------------

I've also attached a filter with this query.

warm regards,
Abhishek

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

raovolvoadmin
4,732 Views

Hello Abhishek,

Thanks for your response.

My requirement is : Edit the export file entries ( in hosting filer and vfiler).I got dar file from the WFA community to do this.but only issue is,not showing the volume list.So I did the below changes.

preferences --> User Input --> Volume Input --> Values

SELECT DISTINCT(volume.name)

FROM

storage.volume,

storage.array,

storage.vfiler

WHERE volume.array_id = array.id

AND volume.vfiler_id = vfiler.id

AND array.id = vfiler.array_id

AND array.name = '${controllerName}'

AND vfiler.name = '${vfilerName}'

I have attached hte screenshots below..

But not working:-(

Regards

Srinivas.

goodrum
4,732 Views

Srinivas,

I am curious if the vFiler has an volumes in the database.  The query that you are using worked during testing in the lab here.  Your first User Input query (from original post) should have also worked so that leads me to believe that there might be no volumes associated with the vFiler.  Have you confirmed that the vFiler exists in Unified Manager and has associated volumes?

Jeremy Goodrum, NetApp

The Pirate

Twitter: @virtpirate

Blog: www.virtpirate.com

raovolvoadmin
4,732 Views

Yes.Vfiler has volumes.I will try one more time with the Query.

Thanks for your support.

Regards

Rao.

Public