Auvik Rest API is not refreshing on service

[Anonymous]

Hi Experts,
Here is the code from Auvik what changes do I need to make in this so that it can refresh online.

* Description: Fetches CPU Utilization metrics for the specified time range on the sites that the user is authorized on.
Input Parameter:
Report Start Date - Start date of period to fetch CPU Utilization metrics for
Report End Date - End date of period to fetch CPU Utilization metrics for
Output Parameter:
CPU Utilization - Table of CPU Utilization metric data for the specified time range on the sites that the user is authorized on. The results are the Average usage percentage for every hour for the reporting period.
*/
let
/* Description: Takes in a date and converts it to the format needed for the Auvik API.
Input Parameter:
origDate - Date to convert
Output Parameter:
auvikDate - origDate converted to format needed for Auvik API
*/
toAuvikAPIDate =
(origDate as date) as text =>
let
auvikDate = Number.ToText(Date.Year(origDate)) & "-" & Text.PadStart(Number.ToText(Date.Month(origDate)), 2, "0") & "-" & Text.PadStart(Number.ToText(Date.Day(origDate)), 2, "0")
in
auvikDate,

/* Description: Fetches one page of data
url - URL to fetch data
Output Parameter:
ret - record consisting of two elements
retData - data for current page
retNext - URL for next page
*/
getOnePage =
(url) as record =>
let
parsedResults = Json.Document(Web.Contents(url)),
pageData = try parsedResults[data] otherwise null,
next = try parsedResults[links][next] otherwise null,
ret = [retData=pageData, retNext=next]
in
ret,

/* Description: Fetches data for one period
periodStart - start date for the period
periodEnd - end date for the period
Output Parameter:
periodDataList - data for the period
*/
getOnePeriod =
(periodStart as date, periodEnd as date) as list =>
let
auvikPeriodStart = toAuvikAPIDate(periodStart),
auvikPeriodEnd = toAuvikAPIDate(Date.AddDays(periodEnd, 1)),
urlPrefix = "https://auvikapi." & #"Auvik Server Cluster" & ".my.auvik.com/v1/stat/device/cpuUtilization",
timeFilter = "filter[fromTime]=" & auvikPeriodStart & "T00:00:00.000Z" & "&filter[thruTime]=" & auvikPeriodEnd & "T00:00:00.000Z" & "&filter[interval]=hour",
pagination = "page[first]=50",
startUrl = urlPrefix & "?" & timeFilter & "&" & pagination & "&tenants=" & #"TenantID",
periodDataList = List.Generate(
()=> [ret = getOnePage(startUrl)],
each [ret][retData]<>null,
each [ret = getOnePage([ret][retNext])],
each [ret][retData]
)
in
periodDataList,

// Determine how many separate queries we have to make to get the entire desired time range
// API hourly data limit in days
maxHourlyPeriod = 30,
startDate= #"Report Start Date",
endDate = #"Report End Date",
numPeriods = Number.RoundUp( (Duration.Days(Duration.From(endDate - startDate)) + 1 ) / maxHourlyPeriod),
currEndDate = if numPeriods = 1 then endDate else Date.AddDays(startDate, maxHourlyPeriod - 1),

// Iterate through each period and combine the data
statsList = List.Generate(
()=> [
currentPeriod = 1,
currentStartDate = startDate,
currentEndDate = currEndDate,
ret = getOnePeriod(currentStartDate, currentEndDate)
],
each [currentPeriod] <= numPeriods,
each [
currentPeriod=[currentPeriod]+1,
currentStartDate = Date.AddDays([currentStartDate], maxHourlyPeriod),
currentEndDate = if currentPeriod = numPeriods then endDate else Date.AddDays([currentEndDate], maxHourlyPeriod),
ret = getOnePeriod(currentStartDate, currentEndDate)
],
each [ret]
),

Any help would be appriciated. Thanks in advance

[Anonymous · Accepted solution]

Thanks everyone. Here is the working code for device inventory Auvik with Static API

let
// Build the URL for the API call
// startUrl = "https://auvikapi." & #"Auvik Server Cluster" & ".my.auvik.com/v1/inventory/device/info?page[first]=50" & "&tenants=" & #"TenantID",
mainURL = "https://auvikapi." & #"Auvik Server Cluster" & ".my.auvik.com/",
  startQuery = [#"page[first]" = "50", tenants = TenantID],

/* Description: Fetches one page of data
url - URL to fetch data
Output Parameter:
ret - record consisting of two elements
retData - data for current page
retNext - URL for next page
*/
// Function to fetch one page of data
  getOnePage = (query) as record =>
        let
            devicePage = Json.Document(
                Web.Contents(
                    mainURL,
                    [
                        RelativePath = "v1/inventory/device/info?",
                        Query = query,
                        Headers = [
                            //#"Authorization" = "Basic " & #"Header",
                            #"Accept" = "application/json"
                        ]
                    ]
                )
            ),
            // Print the entire JSON response for debugging
            debugResponse = devicePage,

deviceData = try devicePage[data] otherwise null,
            next = try devicePage[links][next] otherwise null,
            ret = [retData = deviceData, retNext = next, debugResponse = debugResponse]
        in
            ret,

    // Fetch each page until there are no more pages
    deviceList = List.Generate(
        () => [ret = getOnePage(startQuery)],
// Stop when there is no more data
each try [ret][retData] <> null otherwise false,
// Get the next page using the next link - numbers in the query parameters must be formatted as text, hence the gymnastics
each [
ret = getOnePage(
Record.FromTable(
Table.TransformColumnTypes(
Record.ToTable(Uri.Parts([ret][retNext])[Query]),
{{"Value", type text}}
)
)
)
],
// Return only the data
each [ret][retData]
),

// Convert the list into a table
deviceListTable = Table.FromList(deviceList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Expand the first column into a separate row for each list item
deviceTable = Table.ExpandListColumn(deviceListTable, "Column1"),

// Expand the API response into a column for each first level field
#"Expanded Column1" = Table.ExpandRecordColumn(deviceTable, "Column1", {"id", "attributes", "relationships"}, {"Unique ID of the Device", "Column1.attributes", "Column1.relationships"}),
// Expand each field in the attributes column into a separate column
#"Expanded Column1.attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.attributes", {"ipAddresses", "deviceName", "deviceType", "makeModel", "vendorName", "softwareVersion", "serialNumber", "description", "firmwareVersion", "lastModified", "lastSeenTime", "onlineStatus"}, {"LAN IP Address(es)", "Device Name", "Device Type", "Device Model", "Device Vendor", "Device Software Version", "Device Serial Number", "Device Description", "Device Firmware Version", "Last Time Device Attributes Were Modified", "Last Time the Device Was Seen", "Device Status"}),

// Convert the IP addresses list into a single string
#"Extracted Values" = Table.TransformColumns(#"Expanded Column1.attributes", {"LAN IP Address(es)", each Text.Combine(List.Transform(_, Text.From), ","), type text}),

// Expand the data field in the relationships column
#"Expanded Column1.relationships" = Table.ExpandRecordColumn(#"Extracted Values", "Column1.relationships", {"tenant"}, {"Column1.relationships.tenant"}),
// Expand the data field in the relationships.tenant column
#"Expanded Column1.relationships.tenant" = Table.ExpandRecordColumn(#"Expanded Column1.relationships", "Column1.relationships.tenant", {"data"}, {"Column1.relationships.tenant.data"}),
// Expand the relationships.tenant.data column into separate columns for each field
#"Expanded Column1.relationships.tenant.data" = Table.ExpandRecordColumn(#"Expanded Column1.relationships.tenant", "Column1.relationships.tenant.data", {"id", "attributes"}, {"Site Unique ID", "Column1.relationships.tenant.data.attributes"}),
// Expand the domainPrefix field in the relationships.tenant.data.attributes column
#"Expanded Column1.relationships.tenant.data.attributes" = Table.ExpandRecordColumn(#"Expanded Column1.relationships.tenant.data", "Column1.relationships.tenant.data.attributes", {"domainPrefix"}, {"Domain Prefix"}),

// Creating a new column that generates distinct names by combining the Device Name and the IP addresses of each device
formattedDeviceInventoryTable = Table.AddColumn(#"Expanded Column1.relationships.tenant.data.attributes", "Unique Device Name (DeviceName & LAN IP(s)", each [Device Name] &"@"& [#"LAN IP Address(es)"])
in
formattedDeviceInventoryTable

[Anonymous]

If I hide #"Authorization" then it returns 100 records. How to make it return all records.

[Anonymous]

Hi @Anonymous,

Thank you for reaching out in Microsoft Community Forum.

Thank you @johnbasha33  for the helpful response.

Please follow below steps to return all records.

1.Ensure your API token is valid and correctly passed in the "Authorization" header:

Headers = [
#"Authorization" = "Bearer " & #"Auvik API Token",
#"Accept" = "application/json"
]

2.Make sure the token has access to all records. If it's limited in scope, generate a new token with broader permissions and pagination is implemented properly to fetch all records. Use page[after] for the next set of data if needed.

3.Add debugResponse to check the full API response for error messages or missing pagination links:

4.Wrap the Web.Contents call in a try...otherwise block to capture any errors:

devicePage = try Json.Document(Web.Contents(...)) otherwise null

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[Anonymous]

@johnbasha33 
I dont know what could be the reason. But I am not able to fetch a single record.

[johnbasha33]

Hi @Anonymous 

To make your Power Query (M) script refresh properly in the Power BI Service, you'll need to modify your use of Web.Contents and ensure that the API call is compatible with the Service's data privacy and authentication model.

The most common reason your Auvik API query works in Desktop but fails in Power BI Service refresh is that Web.Contents is using a dynamic URL or missing required authentication headers, which prevents the Service from caching and managing credentials properly.

What You Need to Fix

🔧 Step 1: Use Web.Contents with RelativePath and Query options

Power BI Service cannot refresh when the full URL is dynamically constructed. You must break your URL into components using Web.Contents's structured form:

Change this:
parsedResults = Json.Document(Web.Contents(url))
To this structure:
parsedResults = Json.Document(
Web.Contents(
"https://auvikapi." & #"Auvik Server Cluster" & ".my.auvik.com",
[
RelativePath = "v1/stat/device/cpuUtilization",
Query = [
#"filter[fromTime]" = auvikPeriodStart & "T00:00:00.000Z",
#"filter[thruTime]" = auvikPeriodEnd & "T00:00:00.000Z",
#"filter[interval]" = "hour",
#"page[first]" = "50",
tenants = #"TenantID"
],
Headers = [
#"Authorization" = "Bearer " & #"Auvik API Token"
]
]
)
)

Why This Matters

• Using RelativePath and Query allows Power BI to identify the base domain, cache credentials properly, and enable scheduled refresh.

You must move Authorization into the Headers section as shown above.

Step 2: Authentication Setup

Make sure you store your Auvik API Token in Power BI Parameters and use that in your header.

In Power BI Desktop:

1. Create a new parameter called Auvik API Token.

2. Use "Bearer " & AuvikTokenParameter in the Authorization header.

In the Service, go to Settings > Data source credentials, and confirm it's set as anonymous or web API key, depending on how Auvik authenticates.

Step 3: Privacy Levels

• Go to File > Options > Privacy > Ignore Privacy Levels (for dev testing).

In Power BI Service, you may need to ensure all your sources are in the same privacy group or use Organizational privacy level.

Optional (but Helpful)

If you're not sure which call is breaking:

• Use Diagnostics.Trace to log URL and errors.

• Or just wrap Web.Contents in a try...otherwise to surface issues during refresh.
  
  

  Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Anonymous]

This code return return me a blank list

mainURL = "https://auvikapi." & AuvikServerCluster & ".my.auvik.com",
    startQuery = [#"page[first]" = "1000", tenants = TenantID],
   
    // Function to fetch one page of data
    getOnePage = (query) as record =>
        let
            devicePage = Json.Document(
                Web.Contents(
                    mainURL,
                    [
                        RelativePath = "v1/inventory/device/info?",
                        Query = query,
                        Headers = [
                            #"Authorization" = "Bearer " & #"Header",
                            #"Accept" = "application/json"
                        ]
                    ]
                )
            ),
            // Print the entire JSON response for debugging
            debugResponse = devicePage,

deviceData = try devicePage[data] otherwise null,
            next = try devicePage[links][next] otherwise null,
            ret = [retData = deviceData, retNext = next, debugResponse = debugResponse]
        in
            ret,

    // Fetch each page until there are no more pages
    deviceList = List.Generate(
        () => [ret = getOnePage("page[first] = 1000")],
        each [ret][retData] <> null,
        each [ret = getOnePage([#"page[after]" = [ret][retNext]])],
each [ret][retData]
    ),
// Convert the list into a table
deviceListTable = Table.FromList(deviceList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),