locked
Filtering a particular value via Powershell RRS feed

  • Question

  • Hello Team,

    I am trying to get the datasource details of powerBi reportvia its REST API through the below query:

    $BounGateway=Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($dataset.id)/Default.GetBoundGatewayDataSources" -Method GET | ConvertFrom-Json

    The output for $($BounGateway) is below:

    value          : {@{id=4addff31-d256-4917-a3a9-b61ea6ada3b5; gatewayId=47bc0fc7-ac11-4363-8491-34d48193a4e9; 

                      datasourceType=Sql; 
                     connectionDetails={"server":"x.database.windows.net","database":"ABCD"}}, 
                      @{id=bfff9aec-44e4-429b-a828-850f18e9d120; gatewayId=47bc0fc7-ac11-4363-8491-34d48193a4e9; 
                      datasourceType=AnalysisServices; connectionDetails={"server":"asazure://northeurope.asazure.windows.ne
                      t/x","database":"EDF"}}}

    Now I need the value of the ID for the datasource type=SQL and database=ABCD

    I tried the below scenarios :

    1) $($BounGateway.value.id).where{$($BounGateway.value.datasourcetype).where{$_ -match 'Sql'}} 

    2) Tried splitting the values

    $($BounGateway.value).where{$($BounGateway.value.datasourcetype).where{$_ -match 'Sql'}}

    But didnt get the result.

    So any way by which I can achieve the above result.

    Note:

    I tried using $($BounGateway.value[0]) and it worked to filer out 1 array and the I could directly get the ID.

    But I want to make the logic dynamic wherein whenever I pass a datasource type value , the array should filter it out to that particular Id rather than hard coding. And also the sequence might also differ




    Wednesday, April 29, 2020 4:27 AM

All replies

  • Your screenshot is pretty much unreadable. Please do not post pictures of code. Please format code or sample data or console output or error messages as code. How to Use the Code Feature in a TechNet Forum Post

    So please go back and fix your post.

    Thanks in advance


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Wednesday, April 29, 2020 6:27 AM
  • should be pretty straightforward, but its not easy to decipher your output from the post :D

    According to your note, you should be able to do what you want with something like

    $BounGateway.value | foreach { $_.whatever }

    But we would need your object to be sure. Actually you could post the actual json from your 1st API call.

    Wednesday, April 29, 2020 10:00 AM
  • Hello,
    This is my object:

    value          : {@{id=4addff31-d256-4917-a3a9-b61ea6ada3b5; gatewayId=47bc0fc7-ac11-4363-8491-34d48193a4e9; 

                      datasourceType=Sql; 
                     connectionDetails={"server":"x.database.windows.net","database":"ABCD"}}, 
                      @{id=bfff9aec-44e4-429b-a828-850f18e9d120; gatewayId=47bc0fc7-ac11-4363-8491-34d48193a4e9; 
                      datasourceType=AnalysisServices; connectionDetails={"server":"asazure://northeurope.asazure.windows.ne
                      t/x","database":"EDF"}}}


    I want the ID for the datasourcetype=SQL and database='ABCD'I had used this query:
    | Where-Object {$_.datasourceType -eq "SQL" }

    but not sure how to add the database condition since it is within another tree org.

    Hope this clarifies the use case

    Wednesday, April 29, 2020 10:31 AM
  • This is my object:

    Please format code, sample data, error messages or CONSOLE OUTPUT as code.

    How to Use the Code Feature in a TechNet Forum Post

    Thanks in advance


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    • Edited by BOfH-666 Wednesday, April 29, 2020 11:25 AM
    Wednesday, April 29, 2020 11:24 AM
  • Ok whatever you are posting is neither JSON nor Powershell syntax (or rather, a little bit of both)... Asuming that the "connectionDetails" property was parsed into a powershell object (and not just plain json like what you printed) you should be able to do

    | where-object { $_.datasourceType -like 'Sql' -and $_.connectionDetails.database -like 'ABCD' }

    It would be a lot easier to be sure if you would paste (in a code block, please) the result of

    Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($dataset.id)/Default.GetBoundGatewayDataSources" -Method GET

    • Proposed as answer by Gabq Thursday, April 30, 2020 6:36 AM
    Wednesday, April 29, 2020 12:16 PM
  • Hello,

    Thank you for the input.
    Below is the output of the query which you have provided which I had assigned to a variable $BounGateway :

    @odata.context : http://wabi-west-us-redirect.analysis.windows.net/v1.0/myorg/groups/43ab623f-a70c-43bb-b583-fd653a62cb18/$met
                     adata#gatewayDatasources
    value          : {@{id=4addff31-d256-4917-a3a9-b61ea6ada3b5; gatewayId=47bc0fc7-ac11-4363-8491-34d48193a4e9; 
                     datasourceType=Sql; 
                     connectionDetails={"server":"xyz.database.windows.net","database":"xyz"}}, 
                     @{id=bfff9aec-44e4-429b-a828-850f18e9d120; gatewayId=47bc0fc7-ac11-4363-8491-34d48193a4e9; 
                     datasourceType=AnalysisServices; connectionDetails={"server":"asazure://northeurope.asazure.windows.net/abc","database":"abc"}}}

    So now the need is to get the Id in the value for which the datasourcetype=SQL and database='xyz'.


    I had tried the below query to split the value , but it is not working:

    $BounGateway.value | where-object { $_.datasourceType -like 'Sql' -and $_.connectionDetails.database -like 'xyz' }

    whereas the below code is working which is an incomplete filter condition:

    $BounGateway.value | where-object { $_.datasourceType -like 'Sql'}

    So how to add the filter condition to filter also based on the database name ?


    Thursday, April 30, 2020 6:39 AM