Import your Lansweeper asset data into Microsoft Power BI to build custom dashboards and reports. This article explains how to generate a Lansweeper personal access token (PAT), find your site ID, and use a Power Query script to pull asset data into Power BI Desktop through the Lansweeper Data API.
Prerequisites
Before you start, make sure you have:
- cURL - a command-line tool for making API calls, used here to retrieve your site ID. To check whether it's installed, run
curl --helpin your terminal. If the command isn't recognized, download cURL from the cURL download page. - Power BI Desktop - download it from the Power BI Desktop page.
Generate a personal access token
You need a Lansweeper personal access token (PAT) to authenticate with the Data API.
Create a PAT API client
- Go to app.lansweeper.com and log in with your Lansweeper credentials.
- Select your profile picture in the top-right corner, then select Account settings > Developer Tools.
- Select API clients > Create API client.
- Select Personal Access Token (PAT), fill out the required fields, and select Continue.
- Choose the token expiration time and the sites you want to grant access to, then select Allow.
- Copy the generated API token.
Copy the API token now
The API token is shown only once and can't be retrieved later, so store it somewhere safe. For more information, see the personal access token section of the Data API documentation.
Get your site ID
Your site ID identifies the site you want to pull data from. To retrieve it, call the Data API:
- Run the following cURL command in your terminal, replacing
YOUR_IDENTITY_CODEwith your API token:
curl --location ^
--request POST "https://api.lansweeper.com/api/v2/graphql" ^
--header "Authorization: Token YOUR_IDENTITY_CODE" ^
--header "Content-Type: application/json" ^
--data-raw "{ \"query\": \"{ authorizedSites { sites { id name } } }\"}"
After you run the command, the response lists the sites you can access. Note the ID of the site you want to use in Power BI. For example:
{
"data":{
"authorizedSites":{
"sites":
[
{
"id":"401dxxxx-xxxx-xxxx-xxxx-xxxxxxxx48ca",
"name":"API-demo-data"
},
{
"id":"56d4xxxx-xxxx-xxxx-xxxx-xxxxxxxx5c76",
"name":"API-demo-data-v2"
}
]
}
}
}
In this example, you can use 401dxxxx-xxxx-xxxx-xxxx-xxxxxxxx48ca (site API-demo-data) or 56d4xxxx-xxxx-xxxx-xxxx-xxxxxxxx5c76 (site API-demo-data-v2) as the site ID.
Import Lansweeper data into Power BI Desktop
After you install Power BI Desktop, create a new dashboard:
- Launch Power BI Desktop.
- Select Get data.
- Select Blank Query > Connect to open the Power Query Editor.
- In the Properties section of Query Settings, rename the query to Lansweeper so you can reference it later.
Next, create the two parameters the query uses:
- In the Power Query Editor, on the Home tab, select Manage Parameters.
- Select New and enter:
- Name: Lansweeper Identity Code
- Type: Text
- Current Value: your API token (also called the Identity Code)
- Repeat to add a second parameter named Lansweeper Site Id, with Type set to Text and your site ID as the Current Value.
- Select your query in the left pane.
- On the Home tab, select Advanced Editor to open the query.
Copy and paste the following Power Query script, adjusting the fields for your use case if needed. The script uses the Data API assetResources query; be familiar with the Data API and its restrictions before you change the fields.
In Power Query, all code sits between let and in, and each statement is separated by a comma except the last line before in. For example, this query returns 1:
let
a = "1"
in
a
let
apiCall =
(query as text) =>
let
// Making an actual API call
Source =
Web.Contents(
"https://api.lansweeper.com",
[
RelativePath = "/api/v2/graphql",
Headers = [
#"Method" = "POST",
#"Content-Type" = "application/json",
#"Accept" = "*/*",
#"Authorization" =
Text.Combine(
{
"Token ",
#"Lansweeper Identity Code"
}
)
],
Content = Text.ToBinary(query)
]
),
// Converting response to JSON
#"JSON" = Json.Document(Source)
in
#"JSON",
siteId = #"Lansweeper Site Id",
startCollection =
() =>
let
resp =
// Making an api call for the first page
apiCall(
Text.Format(
"{""query"": ""query getAssetResources { site(id: \""#{0}\"") { assetResources( pagination: { limit: 500, page: FIRST }, fields: [\""assetBasicInfo.upTime\"", \""assetBasicInfo.domain\"", \""assetBasicInfo.name\"", \""assetBasicInfo.userDomain\"", \""assetBasicInfo.userName\"", \""assetBasicInfo.description\"", \""assetBasicInfo.firstSeen\"", \""assetBasicInfo.fqdn\"", \""assetBasicInfo.ipAddress\"", \""assetBasicInfo.lastSeen\"", \""assetBasicInfo.mac\"", \""assetBasicInfo.type\"", \""assetBasicInfo.lastChanged\"", \""assetBasicInfo.lsPushVersion\"", \""assetBasicInfo.lsAgentVersion\"", \""assetBasicInfo.lastActiveScan\"", \""assetBasicInfo.lastIpScan\"", \""assetBasicInfo.lastLsAgent\"", \""assetBasicInfo.lastLsFallBack\"", \""assetBasicInfo.lastLsPush\"", \""assetBasicInfo.lastPerformanceScan\"", \""assetBasicInfo.lastSccmScan\"", \""assetBasicInfo.lastScheduled\"", \""assetBasicInfo.lastTried\"", \""assetBasicInfo.lastTriggered\"", \""assetBasicInfo.lastWorkGroupScan\"", \""assetCustom.manufacturer\"", \""softwares.publisher\"", \""operatingSystem.caption\""] ) { total pagination { limit current next page } items } }}""}",
{siteId}
)
)
in
resp,
getData =
(next as nullable text, currentResults as list) =>
let
data =
// If next is null return the current results
if next = null then
currentResults
// If next is not null call the api again with current cursor
// and combine the results
else
let
resp =
apiCall(
Text.Format(
"{""query"": ""query getAssetResources { site(id: \""#{0}\"") { assetResources( pagination: { limit: 500, cursor:\""#{1}\"", page: NEXT }, fields: [\""assetBasicInfo.upTime\"", \""assetBasicInfo.domain\"", \""assetBasicInfo.name\"", \""assetBasicInfo.userDomain\"", \""assetBasicInfo.userName\"", \""assetBasicInfo.description\"", \""assetBasicInfo.firstSeen\"", \""assetBasicInfo.fqdn\"", \""assetBasicInfo.ipAddress\"", \""assetBasicInfo.lastSeen\"", \""assetBasicInfo.mac\"", \""assetBasicInfo.type\"", \""assetBasicInfo.lastChanged\"", \""assetBasicInfo.lsPushVersion\"", \""assetBasicInfo.lsAgentVersion\"", \""assetBasicInfo.lastActiveScan\"", \""assetBasicInfo.lastIpScan\"", \""assetBasicInfo.lastLsAgent\"", \""assetBasicInfo.lastLsFallBack\"", \""assetBasicInfo.lastLsPush\"", \""assetBasicInfo.lastPerformanceScan\"", \""assetBasicInfo.lastSccmScan\"", \""assetBasicInfo.lastScheduled\"", \""assetBasicInfo.lastTried\"", \""assetBasicInfo.lastTriggered\"", \""assetBasicInfo.lastWorkGroupScan\"", \""assetCustom.manufacturer\"", \""softwares.publisher\"", \""operatingSystem.caption\""] ) { total pagination { limit current next page } items } }}""}",
{
siteId,
next
}
)
),
combinedResults =
List.Combine(
{
currentResults,
@getData(
resp[data][site][assetResources][pagination][next],
resp[data][site][assetResources][items]
)
}
)
in
combinedResults
in
data,
// Initial response
resp = startCollection(),
// Final output
output =
getData(
resp[data][site][assetResources][pagination][next],
resp[data][site][assetResources][items]
),
// Convert to Table
#"Converted to Table" =
Table.FromList(
output,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
// Expanding nested records
#"Expanded Column1" =
Table.ExpandRecordColumn(
#"Converted to Table",
"Column1",
{
"_id",
"assetBasicInfo",
"assetCustom",
"operatingSystem",
"softwares"
},
{
"_id",
"assetBasicInfo",
"assetCustom",
"operatingSystem",
"softwares"
}
),
// Expanding fields contained in assetBasicInfo
#"Expanded assetBasicInfo" =
Table.ExpandRecordColumn(
#"Expanded Column1",
"assetBasicInfo",
{
"domain",
"userDomain",
"fqdn",
"ipAddress",
"name",
"mac",
"firstSeen",
"lastSeen",
"lastTried",
"lastChanged",
"type",
"userName",
"description",
"lsAgentVersion",
"upTime",
"lastIpScan",
"lastSccmScan",
"lastPerformanceScan",
"lastTriggered",
"lastScheduled",
"lastLsPush",
"lsPushVersion"
},
{
"domain",
"userDomain",
"fqdn",
"ipAddress",
"name",
"mac",
"firstSeen",
"lastSeen",
"lastTried",
"lastChanged",
"type",
"userName",
"description",
"lsAgentVersion",
"upTime",
"lastIpScan",
"lastSccmScan",
"lastPerformanceScan",
"lastTriggered",
"lastScheduled",
"lastLsPush",
"lsPushVersion"
}
),
// Expanding fields contained in assetCustom
#"Expanded assetCustom" =
Table.ExpandRecordColumn(
#"Expanded assetBasicInfo",
"assetCustom",
{"manufacturer"},
{"manufacturer"}
),
// Expanding fields contained in operatingSystem
#"Expanded operatingSystem" =
Table.ExpandRecordColumn(
#"Expanded assetCustom",
"operatingSystem",
{"caption"},
{"caption"}
),
// Taking the first element from the OS list.
#"Added Custom" =
Table.AddColumn(
#"Expanded operatingSystem",
"publisher",
each List.First([softwares])
),
// Handling errors occurred during accessing empty list.
#"Replaced Errors" =
Table.ReplaceErrorValues(
#"Added Custom",
{
{
"publisher",
null
}
}
),
// Expanding publisher
#"Expanded publisher" =
Table.ExpandRecordColumn(
#"Replaced Errors",
"publisher",
{"publisher"},
{"publisher"}
)
in
#"Expanded publisher"
- After you finish editing the query, select Done.
- On the Home tab, select Close & Apply.
- If a connection error appears, select Edit Credentials, then Connect.
- Your Lansweeper data is now imported into Power BI, and you can start building dashboards.
Troubleshoot
This section covers common issues and how to resolve them.
Refresh isn't working or is taking too long
If refresh isn't working consistently, it's most likely an issue on the Power BI side. If you're using Power BI Desktop, try clearing the cache:
- In Power BI Desktop, go to File > Options and settings > Options > Data Load.
- Scroll to the bottom and select each of the three Clear cache options individually.