unable to query oci dwh database tables from wfa

[Aravind]

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.

[ostiguy]

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

[karale]

can you post the query?

[Aravind]

select * from dwh_inventory.volume;

[Aravind]

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]

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

[Aravind]

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

[hotz]

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

[hotz]

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

[Aravind]

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.

[hotz]

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

[sinhaa · Accepted solution]

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.

[Aravind]

Hi sinhaa,

Thanks a lot for your  help.

i corrected it.now its working fine.

[trentino123]

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]

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]

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.