SQL QUERY NOT RETURNING ANY VOLUMES - WFA 4.0

[Vikramjeet_Singh]

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

[rkiran]

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]

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]

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]

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]

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]

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]

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]

SOLVED!!!

Everything good for now. 

Will bug you guys again if any problem.

THANKS.

Cheers,

Vik

[Rutul]

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]

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]

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.

 

[Vikramjeet_Singh]

Thanks a lot, you guys have been really helpful.