Forum Discussion

MikeyDK1's avatar
MikeyDK1
Frequent Visitor
3 years ago

Dataset connection - howto?

Hey

 

Today I use this powershell to get data from Microsoft. (ATP)

 

(all the sensitive data has been altered)

            $tenantId = '2357c439-58d1-4f7c-b438-df6a82c435df' 
            $appId = '68809f29-1c84-427c-ad3c-b5c39d652ffc' 
            $appSecret = '.7rf5zxMsQrX45FNP__7rf5zxMsQrX45FNP' 
            $resourceAppIdUri = 'https://api.securitycenter.windows.com'
            $oAuthUri = "https://login.windows.net/$TenantId/oauth2/token"

            $authBody = [Ordered] @{
                resource = "$resourceAppIdUri"
                client_id = "$appId"
                client_secret = "$appSecret"
                grant_type = 'client_credentials'
            }

            $authResponse = Invoke-RestMethod -Method Post -Uri $oAuthUri -Body $authBody -ErrorAction Stop
            $token = $authResponse.access_token
            
            $headers = @{
                'Content-Type' = 'application/json'
                Accept = 'application/json'
                Authorization = "Bearer $token"
            }
    
            $url = "https://api.securitycenter.microsoft.com/api/alerts"
            $response = Invoke-WebRequest -Method Get -Uri $url -Headers $headers -ErrorAction Stop

 

How do I create a dataset using this auth method?

 

Thanks in advance.

Need Help

1 Reply

  • mohdasaad94's avatar
    mohdasaad94
    Icon for Helper I rankHelper I
    3 years ago

    To create a dataset in Power BI using the authentication method you are currently using in your PowerShell script, you can follow these steps:

    1. In Power BI, click on the "Get Data" button in the Home tab.

    2. In the "Get Data" window, select "Blank Query" under the "Other" category and click on "Connect".

    3. In the "Query Editor" window, click on the "Advanced Editor" button in the "View" tab.

    4. In the "Advanced Editor" window, paste the following code to create a function that will return the data from your API using the authentication method in your PowerShell script:

    let
    getAlerts = () =>
    let
    tenantId = "2357c439-58d1-4f7c-b438-df6a82c435df",
    appId = "68809f29-1c84-427c-ad3c-b5c39d652ffc",
    appSecret = ".7rf5zxMsQrX45FNP__7rf5zxMsQrX45FNP",
    resourceAppIdUri = "https://api.securitycenter.windows.com",
    oAuthUri = "https://login.windows.net/" & tenantId & "/oauth2/token",
    authBody = [
    resource = resourceAppIdUri,
    client_id = appId,
    client_secret = appSecret,
    grant_type = "client_credentials"
    ],
    authResponse = Web.Contents(oAuthUri, [
    Headers = [
    #"Content-Type" = "application/x-www-form-urlencoded"
    ],
    Content = Text.ToBinary(Uri.BuildQueryString(authBody))
    ]),
    authToken = Json.Document(authResponse)[access_token],
    headers = [
    Accept = "application/json",
    Authorization = "Bearer " & authToken
    ],
    url = "https://api.securitycenter.microsoft.com/api/alerts",
    response = Json.Document(Web.Contents(url, [Headers = headers]))
    in
    response
    in
    getAlerts()

     

     

    1. Click on the "Done" button to close the "Advanced Editor" window.

    2. Click on the "Close & Apply" button in the "Query Editor" window to create the dataset and load the data.

    This should create a dataset in Power BI that uses the authentication method you are currently using in your PowerShell script to get the data from your API. You can then use this dataset to create visualizations in your report.