topic Re: Query returns no results in WFA but returns correct result when run directly against the DB in Active IQ Unified Manager Discussions

Query returns no results in WFA but returns correct result when run directly against the DB

MATTHEWKOOSWN — Wed, 04 Jun 2025 21:51:43 GMT

Hi there,

I am running the following SQL query to automatically determine the correct value for an input parameter. The query will discover the Netapp cluster based on a selected VM datastore. The query runs fine when run directly against the database, however, when run from WFA it returns no results. Is there a character limit in WFA that may be preventing it from getting the correct result? The query runs fine in WFA only if the datastore name is shorter than the one in the example case I have pasted below.

SELECT

a.name

from

cm_storage.cluster a

inner join

(

SELECT

a.name,

a.cluster_id

FROM

cm_storage.vserver a

inner join

(

SELECT

a.name,

a.vserver_id

FROM

cm_storage.volume a

inner join

(

SELECT

DISTINCT REPLACE(b.share_path,

'/',

'') as share,

a.name

FROM

vc.data_store a

join

vc.nas_share b

ON a.id = b.data_store_id

) b

on a.name = b.share

where

b.name = 'WDGT1OS01SRM_clone_mkoo_ssumner'

) b

on a.id = b.vserver_id

where

a.name not like '%DRN0%'

) b

on a.id = b.cluster_id

Re: Query returns no results in WFA but returns correct result when run directly against the DB

coreywanless — Tue, 08 Mar 2016 18:31:17 GMT

Within WFA, are you passing a user input variable in your "where b.name" statement? If so, what is the query for that statement?

Re: Query returns no results in WFA but returns correct result when run directly against the DB

MATTHEWKOOSWN — Tue, 08 Mar 2016 19:31:04 GMT

Yes, the datastore is a user selectable list that is built by simply querying for all the datastores that are not clones:

SELECT
name
FROM
vc.data_store
where
type = 'NFS'
and name like '%clone%'
order by
name;

That is cascaded into the parameter in question as '${datastore}'; I replaced the variable with the actual value and it returns no results in WFA but returns correctly in MySQL workbench running the query against the DB. When I modify the query to specify a datastore with a shorter name it succeeds in returning the correct result in WFA.

Re: Query returns no results in WFA but returns correct result when run directly against the DB

sinhaa — Wed, 09 Mar 2016 16:25:55 GMT

Matt,

Mysql clients sometimes resolve some things which a native mysql query would not be able to. Not this case, but I've seen other cases where mysql client return different data as they can resolve some things. Thats why sometimes some things work on clients like mysql workbench but not at WFA end.

Let's see what's happening here. I want to execute the query keeping WFA and Mysql Clients aside. Just a native code to run query. It has no built-in intelligence to do anything.

Can you try the following:

On your WFA windows server open a powershell ISE console.

Import-module 'C:\Program Files\NetApp\WFA\PoSH\profile.ps1'

If you have default installation else edit it accordingly. Now define variable for query

$query="SELECT
a.name
from
cm_storage.cluster a
inner join
(
SELECT
a.name,
a.cluster_id
FROM
cm_storage.vserver a
inner join
(
SELECT
a.name,
a.vserver_id
FROM
cm_storage.volume a
inner join
(
SELECT
DISTINCT REPLACE(b.share_path,
'/',
'') as share,
a.name
FROM
vc.data_store a
join
vc.nas_share b
ON a.id = b.data_store_id
) b
on a.name = b.share
where
b.name = 'WDGT1OS01SRM_clone_mkoo_ssumner'
) b
on a.id = b.vserver_id
where
a.name not like '%DRN0%'
) b
on a.id = b.cluster_id"

Execute the query.

Invoke-MySqlQuery -Query $query

If you are getting 0 results, then there is something wrong with your query which somehow Mysql workbench is able to resolve. If you get data same as mysql workbench and still nothing at WFA, WFA needs to be debugged.

Waiting for your response.

sinhaa

Re: Query returns no results in WFA but returns correct result when run directly against the DB

MATTHEWKOOSWN — Thu, 10 Mar 2016 13:29:28 GMT

Hi Sinhaa,

When running the query from powershell on the WFA server it does return the correct value, the result is the same when I run the query from MySQL workbench:

PS C:\Users\koomadm> $query="SELECT a.name from cm_storage.cluster a inner join (SELECT a.name, a.cluster_id FROM cm_sto
rage.vserver a inner join (SELECT a.name, a.vserver_id FROM cm_storage.volume a inner join ( SELECT DISTINCT REPLACE(b.s
hare_path, '/', '') as share, a.name FROM vc.data_store a join vc.nas_share b ON a.id = b.data_store_id ) b on a.name =
b.share where b.name = 'WDGT1OS01SRM_clone_mkoo_ssumner' ) b on a.id = b.vserver_id where a.name not like '%DRN0%' ) b o
n a.id = b.cluster_id"
PS C:\Users\koomadm> Invoke-MySqlQuery -Query $query
1

name
----
HOSTOCLPDN01

When I run this query via WFA it returns no results. However, if I replace the datastore name with another that is shorter then WFA will return the correct result.

Re: Query returns no results in WFA but returns correct result when run directly against the DB

sinhaa — Fri, 11 Mar 2016 07:46:43 GMT

Matt,

Okay so now that it works fine outside of WFA/mysql clients, the issue has to be with WFA. I would suggest you open a NetApp support case, provide your AutoSupport data.

We can debug this there on. We will get back as soon as possible.

Till then your inconvinience is regretted.

sinhaa

Re: Query returns no results in WFA but returns correct result when run directly against the DB

MATTHEWKOOSWN — Fri, 11 Mar 2016 14:06:42 GMT

OK, just opened case 2006192616 and sent autosupport data.

@sinhaa wrote:
Matt,

Okay so now that it works fine outside of WFA/mysql clients, the issue has to be with WFA. I would suggest you open a NetApp support case, provide your AutoSupport data.

We can debug this there on. We will get back as soon as possible.

Till then your inconvinience is regretted.

sinhaa