Active IQ Unified Manager Discussions

unable to query oci dwh database tables from wfa

Aravind
9,308 Views

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.

 

Untitled.png

 

 

1 ACCEPTED SOLUTION

sinhaa
9,197 Views

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.

 

 

 

 

 

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

View solution in original post

18 REPLIES 18

ostiguy
9,237 Views

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

Aravind
9,199 Views

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.

hotz
9,194 Views

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

 

snapshot4.png

Aravind
9,194 Views

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

 

Untitled.png

hotz
9,191 Views

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

hotz
9,189 Views

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

Aravind
9,185 Views

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.

 

perms.png

sinhaa
9,198 Views

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.

 

 

 

 

 

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

Aravind
9,137 Views

Hi sinhaa,

 

Thanks a lot for your  help.

i corrected it.now its working fine.

karale
9,234 Views

can you post the query?

Aravind
9,216 Views
select * from dwh_inventory.volume;

hotz
9,159 Views
Sounds like a bug in WFA. Can you reach out to the wfa community?

trentino123
9,123 Views

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.

Aravind
9,116 Views

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.

trentino123
9,110 Views

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.

Aravind
6,895 Views

thanks a lot ,its working fine now.

Aravind
6,824 Views
 

sinhaa
6,886 Views

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

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.
Public