Active IQ Unified Manager Discussions

SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Vikramjeet_Singh
6,000 Views

I am using the follwing query to find the volume in a particular vserver by uing SQL query but it shows empty list even when there are number of volumes present.

 

 

SELECT
volume.name AS Name,
aggregate.name as Aggregate,
volume.size_mb AS 'Total Size (MB)',
volume.used_size_mb AS 'Used Size (MB)',
volume.space_guarantee AS 'Space Guarantee'
FROM
cm_storage.cluster,
cm_storage.aggregate,
cm_storage.vserver,
cm_storage.volume
WHERE
cluster.id = vserver.cluster_id
AND aggregate.id = volume.aggregate_id
AND vserver.id = volume.vserver_id
AND cluster.name = '${ClusterName}'
AND vserver.name = '${VserverName}'
AND aggregate.name = '${AggregateName}'
ORDER BY
volume.name ASC

12 REPLIES 12

rkiran
5,923 Views

Hi,

I was able to execute your query, and was able to see some of the volumes present in a particular vserver. Please double check if the valued specified to the query are correct or not.
You can use some SQL IDE tools to cross check (outside WFA) if the query returns any volumes or not.

 

Rutul
5,915 Views

Hi ,

 

The query looks good. What I can guess is in the where condition "cluster.name=${ClusterName} " you might be writing IP address instead of name of that cluster. For IP address there is another column for example cluster.primary_address="10.10.10.10". So if you dont want to change the condition than try writing the name instead of ip address. And if you want to check with IP address then change the column name in the condition. I tested in mysql and got the output with the same query. Check the bold part for more understanding

 

 

SELECT
volume.name AS Name,
aggregate.name as Aggregate,
volume.size_mb AS 'Total Size (MB)',
volume.used_size_mb AS 'Used Size (MB)',
volume.space_guarantee AS 'Space Guarantee'
FROM
cm_storage.cluster,
cm_storage.aggregate,
cm_storage.vserver,
cm_storage.volume
WHERE
cluster.id = vserver.cluster_id
AND aggregate.id = volume.aggregate_id
AND vserver.id = volume.vserver_id
AND cluster.name = 'Rmcluster.eng.btc.netapp.in'
AND vserver.name = 'c81377-UCSCloud_resv'
AND aggregate.name = 'sas600ag3_e_nasa23'
ORDER BY
volume.name ASC

 

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

 

SELECT
volume.name AS Name,
aggregate.name as Aggregate,
volume.size_mb AS 'Total Size (MB)',
volume.used_size_mb AS 'Used Size (MB)',
volume.space_guarantee AS 'Space Guarantee'
FROM
cm_storage.cluster,
cm_storage.aggregate,
cm_storage.vserver,
cm_storage.volume
WHERE
cluster.id = vserver.cluster_id
AND aggregate.id = volume.aggregate_id
AND vserver.id = volume.vserver_id
AND cluster.primary_address = '10.238.4.13'
AND vserver.name = 'c81377-UCSCloud_resv'
AND aggregate.name = 'sas600ag3_e_nasa23'
ORDER BY
volume.name ASC

 

I ran this queries and they are working fine

Vikramjeet_Singh
5,861 Views

The query did not work. Just for testing purposes if I try to use finder/ fiters to list the volumes in the vserver still the query is excuted but no results/ empty list.

 

I even tried to using the resizing command directly in the workflow without any modification still it doesnot work. Shows me the following error

 

ERROR:   Found value '$VserverName' - expected object reference of type 'cm_storage.Vserver'
Location: 'Resize volume' command > 'Volume' tab > 'volume1' variable > 'vserver' property

 

I even tried to change the parameter mapping but then it gives me the error about the attribute. The ATTRIBUTE is name.

 

A QUICK REPLY WILL BE APPRECIATED.

 

THANKS....

Rutul
5,841 Views

Hi Vikram,

 

 

 

Are WFA and OCUM connected properly ? Have you configured and acquired the OCUM datasource properly? Also check that are you able to see your cluster storage systems in the OCUM ? If it is not acquired properly it will not return any result. 

 

If all the checks are good from OCUM side then try to restart OCUM server once -> Acquire Datasource again and then execute your query.

Vikramjeet_Singh
5,812 Views

Queries are working fine for cluster, vserver and aggregate..its just the volume thats giving me a problem.

 

Do you still think I should restart the cluster?

Rutul
5,797 Views

No you do not need to restart the cluster. I am talking about OCUM ( Oncommand Unified Manager). Restart that server on which OCUM is installed. Try query after that whether it is working or not.

If No then try your query in mysql workbench or any database management tool. Take backup from WFA and import the database to mysql workbench. Try your query there. If you are seeing proper results then query is fine. Also Please check and give proper values for conditions. 

Vikramjeet_Singh
5,783 Views

You were right, its is not able to connect with UM. The data is was pulling was from the cache.

 

 

ERROR: JZ006: Caught IOException: java.net.ConnectException: Connection timed out: connect

 

ANY ADVICE PLEASE?

 

 

Thanks.

Vikramjeet_Singh
5,774 Views

SOLVED!!!

 

Everything good for now. 

 

Will bug you guys again if any problem.

 

THANKS.

 

Cheers,

 

Vik

Rutul
5,766 Views

Sure. Thanks. 

 

Please accept the solution if you have found it valuable and correct. So that it will be helpful to someone next time who has the same kind of questions.

Vikramjeet_Singh
4,785 Views

ERROR: Found value '$VserverName' - expected object reference of type 'cm_storage.Vserver'
Location: 'Resize volume' command > 'Volume' tab > 'volume1' variable > 'vserver' property

 

All the queries are perfectly fine but I am getting ths error.

 

Any IDEA?

Rutul
4,762 Views

You should not write variable name when the field is type of reference. It is an object reference type field. Select filter there "Find Vserver by Key" and fill up the required fields.  Reference objects should be searched automatically. So click on browse button in vserver textbox and select appropriate filter in Resource selection. Put your variables there and click OK.

 

 

vs1.PNG

 

 

 

 vs1.PNG

 

 

 

Vikramjeet_Singh
4,754 Views

Thanks a lot, you guys have been really helpful.

Public