Retrieving DQL Data while connecting to PowerBI

[Kris_T]

We are trying to connecting Dynatrace to PowerBI. We want to run DQL Query and have the results in PowerBI, so we can use: DQL queries via API: https://www.dynatrace.com/support/help/shortlink/ba-api-ingest#dql-via-api

But this is a POST Method and not a GET. I have worked with a colleague and we can authenticate with Tokens for the classic platform APIs and use PowerBI/Excel to integrate with the methods mentioned here and in the Dynatrace and PowerBI (YT Video).

I see something similar, but not the same, this is more of RFE (Grail to PowerBI Connector)

How can we connect from PowerBI/Excel to DT, to retrieve DQL Response using the DQL API (POST Method)

[AntonPineiro]

Hi,

Do you know if you token has "Read logs (logs.read)" permission?

I would check Log Monitoring API.

Best regards

[Kris_T]

I have read logs permission 
I have client token for OAuth and Token ID for API V2 when using token ID method for getting logs using Get method i am getting Error Token Authentication failed Token Authentication failed

and got stuck,  Since i am new using API's i don't know how to generate bearer token 

[AntonPineiro]

Hi,

For me, easier way is from here:

You can create your token there and play with Dynatrace API Explorer.

Best regards

[radek_jasinski]

Remember also to select the appropriate scope of such a token:

https://www.dynatrace.com/support/help/shortlink/api-authentication#token-scopes

[gordon_meyer · Accepted solution]

Hi!

Yes it is possible and here is a rudimentary piece of code to help:
Note you would need to create an oauth client on your tenant to be able to use this as described here: Access platform APIs from outside | Dynatrace Developer

then in your Power query connection in Powerbi use the following (this example runs a fetch logs 1000 records DQL query):

let
body = grant_type=client_credentials&client_id=<your oauth client id here>&client_secret=<your oauth secret here>&scope=<your scope required here(e.g. storage:logs:read+storage:buckets:read)>,
Data= Web.Contents("https://sso.dynatrace.com/sso/oauth2/token",
[Content=Text.ToBinary(body),
Headers=[#"Content-Type"="application/x-www-form-urlencoded"]]),
DataRecord = Json.Document(Data) ,
token=DataRecord[access_token],
postBody = <your dql query here, e.g. {"query": "fetch logs | limit 1000","timezone": "UTC","locale": "en_US","requestTimeoutMilliseconds": 60000,"maxResultRecords": 1000}>,
Source = Json.Document(Web.Contents("https://<your DT tenant here>/platform/storage/query/v1/query:execute?enrich=metric-metadata",
[Headers=[#"Authorization"="Bearer "&token, #"Content-Type"="application/json"],
Content=Text.ToBinary(postBody)])),
result = Source[result],
records = result[records],
#"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"timestamp", "content", "container.image.name", "container.name", "dt.entity.container_group", "dt.entity.container_group_instance", "dt.entity.host", "dt.entity.process_group", "dt.entity.process_group_instance", "dt.process.name", "dt.source_entity", "event.type", "host.name", "log.source", "loglevel", "process.technology", "status", "container.id", "log.iostream"}, {"Column1.timestamp", "Column1.content", "Column1.container.image.name", "Column1.container.name", "Column1.dt.entity.container_group", "Column1.dt.entity.container_group_instance", "Column1.dt.entity.host", "Column1.dt.entity.process_group", "Column1.dt.entity.process_group_instance", "Column1.dt.process.name", "Column1.dt.source_entity", "Column1.event.type", "Column1.host.name", "Column1.log.source", "Column1.loglevel", "Column1.process.technology", "Column1.status", "Column1.container.id", "Column1.log.iostream"})
in
#"Expanded Column1"