topic Re: unable to query oci dwh database tables from wfa in Active IQ Unified Manager Discussions

unable to query oci dwh database tables from wfa

Aravind — Thu, 05 Jun 2025 05:07:46 GMT

when i am trying to build a filter i am getting the error as :

Failed getting filter attributes - SELECT command denied to user 'restricted'@'localhost' for table 'volume'

tried to grant permissions but unable to do so, as dwhuser doesnt have permissions.

please help on this.

Re: unable to query oci dwh database tables from wfa

ostiguy — Mon, 09 Feb 2015 15:19:49 GMT

Hey Aravind,

I think you have something misconfigured in how you are passing your username to MySQL - the error message you are seeing indicates that you are passing "restricted" as the username to mysql

Re: unable to query oci dwh database tables from wfa

karale — Mon, 09 Feb 2015 15:36:30 GMT

can you post the query?

Re: unable to query oci dwh database tables from wfa

Aravind — Mon, 09 Feb 2015 16:49:29 GMT

select * from dwh_inventory.volume;

Re: unable to query oci dwh database tables from wfa

Aravind — Tue, 10 Feb 2015 05:05:10 GMT

i am connecting to the dwh database through dwhuser ,not as restricted. but i dont no why it is unable to execute a select query.

Re: unable to query oci dwh database tables from wfa

hotz — Tue, 10 Feb 2015 06:00:41 GMT

It should work. I just tested with the dwhuser and works fine for me:

Re: unable to query oci dwh database tables from wfa

Aravind — Tue, 10 Feb 2015 06:08:10 GMT

from the command line and mysql client it is working fine. but its not working from wfa ,when i try to create a filter.

Re: unable to query oci dwh database tables from wfa

hotz — Tue, 10 Feb 2015 06:54:14 GMT

Can you try to access the db with a remote mysql client with dwhuser? I wonder if access is restricted to localhost...

Re: unable to query oci dwh database tables from wfa

hotz — Tue, 10 Feb 2015 06:59:27 GMT

No, that's not it. I tried from my linux box and it works fine. Could it be the firewall?

Re: unable to query oci dwh database tables from wfa

Aravind — Tue, 10 Feb 2015 07:12:00 GMT

i am quite confused why the username in the error is showing as 'restricted', while i am connecting as dwhuser. and moreover dwhuser has the select permissions.

and i am connecting to the db through remote sql client only. i am able to query from every possible way except from wfa.

Re: unable to query oci dwh database tables from wfa

hotz — Tue, 10 Feb 2015 07:21:50 GMT

Sounds like a bug in WFA. Can you reach out to the wfa community?

Re: unable to query oci dwh database tables from wfa

sinhaa — Wed, 11 Feb 2015 09:20:03 GMT

This is not any Bug etc.. you need to understand what WFA filters are and how they work. At least bascis. Filter queries run locally on WFA DB. In your query, you are giving DB name which is in OCI DB. Giving the DB(dictionary ) name which is in WFA. It will work.

Re: unable to query oci dwh database tables from wfa

Aravind — Wed, 11 Feb 2015 10:15:54 GMT

Hi sinhaa,

Thanks a lot for your help.

i corrected it.now its working fine.

Re: unable to query oci dwh database tables from wfa

trentino123 — Wed, 11 Feb 2015 13:45:50 GMT

Hello Arevind,

WFA has readonly databases that is pulls from OCUM/DFM, but it does not have OCI as a datasource.

Therefore when you try to pull information from WFA, it does not connect to the OCI datawarehouse.

In order to connect to the OCI datawarehouse and run a query, you need some powershell code. The following code can retrieve your query, just paste it on a new command, it will write the results on c:\temp\ocitest.csv

You will need to change the $MySQLHost variable.

function Invoke-MySQL {
Param(
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$Query
)

$MySQLAdminUserName = 'dwhuser'
$MySQLAdminPassword = 'netapp123'
$MySQLDatabase = 'dwh_inventory'
$MySQLHost = 'OCIDWHSERVER.yourdomain.com'
$ConnectionString = "server=" + $MySQLHost + "; port=3306; uid=" + $MySQLAdminUserName + "; pwd=" + $MySQLAdminPassword + "; database="+$MySQLDatabase

Try {
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$Command.Dispose()
#$Table=$DataSet.Tables["data"] | FT -auto
$DataSet.Tables["data"] | Export-Csv "c:\temp\ocitest.csv"
Get-WFALogger -Info -message $("OCI query result" + $DataSet.Tables["data"] )

}

Catch {
throw "ERROR : Unable to run query : $query `n$Error[0]"
}

Finally {
$Connection.Close()
}
} # end function Invoke-MYSQL

$result = Invoke-MySQL -Query " select * from dwh_inventory.volume "

Let me know if it helped.

Re: unable to query oci dwh database tables from wfa

Aravind — Wed, 11 Feb 2015 14:22:04 GMT

hi trentino,

actually i made a mistake by quering oci db while creating a filter. which wil not be possible and which is not the way to do.

now i am able to fetch the oci db data into local wfa dictionary.

thanks for the response,now its working fine.

Re: unable to query oci dwh database tables from wfa

trentino123 — Wed, 11 Feb 2015 18:54:41 GMT

Glad to know it worked Aravind , please post the filter. Attaching the command .dar for anyone that tries to export any MySQL query to any server into a csv.

Re: unable to query oci dwh database tables from wfa

Aravind — Thu, 12 Feb 2015 08:54:15 GMT

thanks a lot ,its working fine now.

Re: unable to query oci dwh database tables from wfa

sinhaa — Thu, 12 Feb 2015 15:25:03 GMT

You don't need to write a separate code for this. WFA3.0 has provided PoSh cmdlet Invoke-MySqlQuery and Perl method invokeMySqlQuery for executing queries to a Mysql DB for Posh and Perl commands respectively.

sinhaa

Re: unable to query oci dwh database tables from wfa

Aravind — Fri, 20 Feb 2015 05:53:21 GMT