<!-- Device Inventory Manager — https://wiki.cyberguard.srl/dim/api/power-bi/ -->

# Power BI

Power BI's **Web** connector cannot request an OAuth token on its own. Use a **Power Query** that requests the token and downloads all the pages of data.

---

## Before you start

1. In the portal, open **Tenants → Edit → API** and create a client named `Power BI`.
2. Select only the data the report needs, for example **Devices** and **Compliance**.
3. Copy the **Client ID**, the **Client secret** and the **API address**.

---

## Create the parameters

In Power BI Desktop: **Transform data → Manage Parameters → New Parameter**. Create three text parameters:

| Name | Value |
|------|-------|
| `ApiBaseUrl` | The API address, for example `https://inventory-api.cyberguard.srl` |
| `ApiClientId` | Client ID |
| `ApiClientSecret` | Client secret |

Keeping the secret in a parameter makes it easy to update when you renew it.

---

## Create the query

**Get data → Blank query → Advanced Editor**, then paste:

```text
let
    // 1. Token, valid 60 minutes
    TokenResponse = Json.Document(Web.Contents(ApiBaseUrl, [
        RelativePath = "connect/token",
        Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
        Content = Text.ToBinary(Uri.BuildQueryString([
            grant_type = "client_credentials",
            client_id = ApiClientId,
            client_secret = ApiClientSecret]))
    ])),
    Token = TokenResponse[access_token],

    // 2. One page of data; nextCursor leads to the next page
    GetPage = (cursor as nullable text) => Json.Document(Web.Contents(ApiBaseUrl, [
        RelativePath = "v1/devices",
        Query = [pageSize = "500"] & (if cursor = null then [] else [cursor = cursor]),
        Headers = [Authorization = "Bearer " & Token]
    ])),

    // 3. All pages, until nextCursor is empty
    Pages = List.Generate(
        () => GetPage(null),
        each _ <> null,
        each if _[nextCursor] = null then null else GetPage(_[nextCursor])),
    Devices = Table.FromRecords(List.Combine(List.Transform(Pages, each _[data])))
in
    Devices
```

Name the query `Devices`. For other data, duplicate the query and change `RelativePath`:

| Data | `RelativePath` |
|------|----------------|
| Devices | `v1/devices` |
| Compliance per device | `v1/compliance/devices` |
| Audit log | `v1/audit-log` |

When Power BI asks how to connect to the API address, choose **Anonymous**. The query handles authentication.

---

## Scheduled refresh in the Power BI service

1. Publish the report.
2. In the dataset settings, open **Data source credentials** for the API address.
3. Choose **Anonymous** as authentication method and select **Skip test connection**.
4. Set the privacy level to **Organizational**.
5. Configure the refresh schedule.

Each refresh requests one token per query. With 8 refreshes per day the report stays well within the API limits.

---

## Troubleshooting

| Message | Cause |
|---------|-------|
| `(401) Unauthorized` on `connect/token` | Wrong Client ID or secret, or the client has been revoked. Check the **API** page in the portal |
| `(403) Forbidden` on a data query | The client does not include the scope for that data. Create a client with the right scopes |
| `(429) Too Many Requests` | Rate limit or daily quota reached. Reduce the refresh frequency |
| The service refresh fails while Desktop works | Check that the data source credentials are set to **Anonymous** with **Skip test connection** |