topic Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0 in Active IQ Unified Manager Discussions

SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Vikramjeet_Singh — Wed, 04 Jun 2025 19:55:53 GMT

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

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

rkiran — Mon, 18 Jul 2016 07:07:08 GMT

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.

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Rutul — Mon, 18 Jul 2016 08:54:14 GMT

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

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Vikramjeet_Singh — Mon, 18 Jul 2016 20:08:57 GMT

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....

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Rutul — Tue, 19 Jul 2016 04:58:07 GMT

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.

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Vikramjeet_Singh — Tue, 19 Jul 2016 13:48:21 GMT

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?

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Rutul — Tue, 19 Jul 2016 15:20:01 GMT

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.

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Vikramjeet_Singh — Tue, 19 Jul 2016 18:07:17 GMT

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.

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Vikramjeet_Singh — Tue, 19 Jul 2016 19:08:30 GMT

SOLVED!!!

Everything good for now.

Will bug you guys again if any problem.

THANKS.

Cheers,

Vik

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Rutul — Tue, 19 Jul 2016 19:52:11 GMT

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.

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Vikramjeet_Singh — Tue, 19 Jul 2016 20:13:51 GMT

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?

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Rutul — Wed, 20 Jul 2016 13:36:01 GMT

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.

Re: SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

Vikramjeet_Singh — Wed, 20 Jul 2016 14:16:46 GMT

Thanks a lot, you guys have been really helpful.