Create a Lansweeper Power BI Dashboard Template

[sophie]

Learn how to set up the Lansweeper Power BI Dashboard Template to transform your IT asset data into dynamic, visually engaging insights using Microsoft Power BI.

---

This article provides an overall app specification for the Lansweeper Power BI Dashboard template. It contains step-by-step instructions to import, set up & configure the Lansweeper Power BI Dashboard template.

Deployment architecture

Microsoft Power BI platform

Microsoft Power BI is an interactive data visualization software focused on business intelligence. Power BI is a collection of software services, apps, and connectors that combine to turn unrelated data sources into coherent, visually immersive, and interactive insights.

Lansweeper platform​

Lansweeper is the industry leader in device discovery, providing agentless, credential-free and credential-based device recognition, identification, and asset management across all connected IT, OT and IoT.

Lansweeper Power BI Dashboard template​

The Lansweeper Power BI Dashboard template allows you to fetch asset information from Lansweeper and visualize the data with the help of Power BI representations.

System architecture

Prerequisites​

Below is a list of requirements needed to run the dashboard template (v1.0.0) on Power BI:

❗cURL

The following steps require cURL to be installed. cURL is a command line tool that makes API calls. We will use cURL to communicate with Lansweeper Data API. To check if you already have it installed, run the following command in your terminal:

curl --help

If you cannot run this command, you can download it from cURL webpage.

Generate a Personal Access Token on Lansweeper​

An API token is needed to make use of the Lansweeper Data API. This can be generated by following the steps below.

Create a PAT API Client on Lansweeper Sites​

1. Go to Lansweeper Site and log in with your Lansweeper credentials.
2. Select the profile button in the top-right corner, which opens up a dropdown box. Select Developer Tools. Alternatively, if you are inside a site already, select the profile button in the bottom-left corner, then Developer Tools. Click 
3. Select All API Clients > Add new API client.
4. Select Personal Access Token (PAT), fill out the required fields and click Continue.
5. Choose the token expiration time and the sites you would like to grant access to, then Allow.
6. Copy the generated API Token.

⚠️ COPY THE API TOKEN

The API Token won't be visible again, so please store it for future use. For more information, check authorization section from the Data API documentation.

Get Site IDs​

Your Site ID is required to identify a site for which we want to fetch data. This ID can be obtained by making an API call to Lansweeper:

1. Run the following cURL command in your terminal:

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 } } }\"}"​

2. After running the command you will get a response including the list of sites you can access. You can then note the site's ID that you want to use in Power BI.
3. For example, take a look at the response given below:

{
    "data":{
        "authorizedSites":{
            "sites":
            [
                {
                    "id":"401dxxxx-xxxx-xxxx-xxxx-xxxxxxxx48ca",
                    "name":"API-demo-data"
                },
                {
                    "id":"56d4xxxx-xxxx-xxxx-xxxx-xxxxxxxx5c76",
                    "name":"API-demo-data-v2"
                }
            ]
        }
    }
}

You can use 401dxxxx-xxxx-xxxx-xxxx-xxxxxxxx48ca (for the site named API-demo-data) or 56d4xxxx-xxxx-xxxx-xxxx-xxxxxxxx5c76 (for the site named API-demo-data-v2) as site IDs.

Get Export Base URL​

The Export Base URL is required to make it a static parameter. This Base URL will later be used to construct a full download URL. We can get this base URL by simulating an export using cURL:

1. Run the following cURL command in your terminal:

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\":\"mutation export {\r\n  site(id: \\\"<Site ID>\\\") {\r\n exportFilteredAssets {\r\n assetBasicInfo{\r\n name\r\n }\r\n exportId\r\n }\r\n  }\r\n}\",\"variables\":{}}"

2. After running the above code you will get a response containing an exportId. For example take a look at the response given below:

{
    "data":{
        "site":{
            "exportFilteredAssets":
            {
                "assetBasicInfo":null,
                "exportId":"624ee6844e74e48e540351bc"
            }
        }
    }
}

Here, 624ee6844e74e48e540351bc is the export Id.

3. Now run the following cURL command every 30 seconds until you get a response containing an export URL:

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\":\"{\r\n  site(id: \\\"<Site ID>\\\") {\r\n exportStatus(exportId: \\\"<Export ID>\\\") {\r\n exportId\r\n progress\r\n url\r\n }\r\n }\r\n}\",\"variables\":{}}"

 

Take a look at the example response given below containing the export URL:

{
    "data":{
        "site":{
            "exportStatus":
            {
                "exportId":"624ee6844e74e48e540351bc",
                "progress":"100%",
                "url":"https://lec-integrations-export-assets-production.s3.eu-west-1.amazonaws.com/asset-exports/401d153d-2a59-45eb-879a-c291390448ca-624ee6844e74e48e540351bc.zip?AWSAccessKeyId=ASIAUBYNJ2KCGNWLTWZM&Expires=1649341601&Signature=JzYlwUVRpSNXJdamTbHT1Seb%2B%2FA%3D&x-amz-security-token=FwoGZXI1rRHrf[...]9Dmtfug72meHs"
            }
        }
    }
}

Export Base URL would be the prefix of the export URL. For example, for the above response, our Export Base URL will be: 

https://lec-integrations-export-assets-production.s3.eu-west-1.amazonaws.com

Create a dashboard in Power BI using Lansweeper Data API​

This section describes how you can import the provided dashboard template into both Power BI Service and Power BI Desktop and how you can create a new dashboard template from scratch.

⚠️ TEMPLATE FILES

We provide template files that can be used to start off.

• pbix file is a report which contains all the visualizations and dummy data prior to importing it. Once you import this you can modify the parameters to fetch the data of a specific site.
• pbit file is a template file which will ask for the parameters at the time of importing it. So, from the start it will contain the data of the site you entered while importing it. You can also change these parameters later on.

Import the dashboard template into Power BI Cloud​

1. Log in to your Power BI service portal.
2. Click on the Workspaces option in the left navigation bar. Select the workspace you would like to import the dashboard template onto.

 

3. Click on the New button and select the Upload a file option.

 

4. Select the provided file: Sample Lansweeper Dashboard Pagination.pbix. Your dashboard template is imported.

5. To configure the provided dashboard template, follow the below steps:

   1. Click on the Data Hub option in the left navigation bar.
   2. For the imported dataset Sample Lansweeper Dashboard Pagination, click on the options button and select the Settings option.

3. Expand Data source credentials. There you should see one item with an Edit credentials option. Click on Edit credentials, set Authentication method to Anonymous, set Privacy level setting for this data source to Public, check the box Skip test connection and click on Sign in.

4. 5. After applying changes, you should refresh both the report and the dataset once.

5. To refresh the dataset:

   1. Click on the Data Hub option in the left navigation bar.
   2. Click on the imported database (i.e. Sample Lansweeper Dashboard Pagination).
   3. In the top bar, click on the Refresh icon and select Refresh now. Wait for the refresh to finish. Now, your dataset is up to date.

To refresh the report:

1. Click on the Workspaces option in the left navigation bar. Select the workspace in which you imported the template.
2. Click on the report (i.e. Sample Lansweeper Dashboard Pagination). Note that the report will have the    icon next to them.

3. This will open up a report. From the top bar, click on Edit.

4. Select Refresh.

Now your report is up to date.

Import a dashboard template into Power BI Desktop​

Importing pbix (Report) file​

1. Launch Power BI Desktop.
2. Select File.

     3. A new navigation bar will open on the left side. From there, select Open Report and then on Browse Reports. This will open up a file selection window.

4. Select the provided file (i.e. Sample Lansweeper Dashboard Pagination.pbix) and click Open.
5. On the right side expand the Fields section if it is not already expanded.

6. Hover over the Lansweeper label there and three dots will appear (More options).

7. Click on them and select Edit query. This will open up a new window.

 

8. On the left select Lansweeper Identity Code (API Token) and enter the appropriate value. Do the same for Lansweeper Site Id.

9. From the top menu bar select Close & Apply.

Once the loading is complete, your report will be ready.

Importing pbit (Template) file​

1. Launch Power BI Desktop.
2. Select File.

3. A new navigation bar will open on the left side. From there click on Import and then on Power BI template. This will open up a file selection window.

     4. Select the provided file (i.e. Sample Lansweeper Dashboard Pagination.pbit) and click Open.

5. Enter appropriate values of the parameters Lansweeper Identity Code and Lansweeper Site Id and click on Load.

Once the loading is complete, your report will be ready.

Create a new dashboard template in Power BI​

In this section we will see how to create a new dashboard template in Power BI from scratch. First, this section will describe how to fetch the data and then it will describe how to create visualizations from the data.

See this for the description of the fields we will be fetching.

To create a new dashboard template, Power BI Desktop is required which can be downloaded from this link. Once Power BI Desktop is downloaded and installed, create a new dashboard template:

1. Launch Power BI Desktop.
2. Select Get data.

3. Select Blank Query > Connect. This will open up the Power Query Editor for your dashboard template.

4. A PROPERTIES section is on the right side of the Query Settings section. From here, you can change the query's name. You should rename the query to Lansweeper so it can be referenced later.

    5. Now we need to create two parameters that will be used in our query.

1. In the Power Query Editor, go to the Home tab and click on the Manage Parameters button.

2. Click on New and enter the following values:
   • Name: Lansweeper Identity Code
   • Type: Text
   • For Current Value, provide the generated Identity Code (API Token) for your Lansweeper API Client.

3. Similarly, perform the above steps to add the parameter Lansweeper Site Id. Set the Type for this parameter as text and provide an appropriate value in the Current Value field.

6. In the Power Query Editor, select your Query from the left window pane.

7. Go to the Home tab in your top menu bar and click the Advanced Editor button under the Query section. This will open up the Advanced Editor dialog box, and you can edit the query as needed.

 

To get started, you can refer to the below code.

You can directly copy and paste this Power BI query to get the data (after modifying the proper fields according to your use case if needed).

⚠️ INFO

In power query, all code must be covered by let and in, where all our code will reside after let. After in you can mention the variable which you want to access in the Power BI. Each piece of code (this includes all lines and function endings) must be separated by , except the last line before in. For example, running the below query will give the result 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"

7. Once the query is edited, select Done.

8. In the Power Query Editor, go to the Home tab in your top menu bar and select Close & Apply.

Make panels​

Power BI supports making panels using only drag and drop. 

1. From the visualization section, select the visualization you'd prefer. For example, select the pie chart symbol for a pie chart.
2. You can drag and drop values from the table to populate this panel. For example, to make a pie chart for asset types, you can drag and drop the type field in both the Legend and Values section.

You can similarly drag and drop the rest of the fields according to your needs. You can also customizethe  look and feel of your pie chart by selecting:

If needed, you can also provide any conditional formatting over the table created by the data fetched from Lansweeper. For example, in the below steps, we illustrate how to derive a new table from our existing table and create a measure over the newly created table to categorize the data below a threshold value into the Others category for a pie-chart panel:

1. Select Data button in the left navigation bar.
2. Go to the Table Tools tab on the menu bar and under the Calculations section, select New table.
3. Go to the Table Tools tab on the menu bar and under the Calculations section, select New table.

Here we will add a DAX expression to aggregate the counts of asset types that are below the cutoff of 10% coverage and add the aggregated count value to the row with asset type value as Other. Note that here the column name is Lansweeper_type. If you have renamed a column, you should change it in this DAX expression.

Pie Chart Measure for Type =
    VAR cutoff_percentage = 0.1
    VAR current_category = MAX('Asset Type Overview'[Lansweeper_type])
    VAR total_count = CALCULATE(
        SUM('Asset Type Overview'[Count]),
        ALL('Asset Type Overview')
    )
    VAR cutoff_number = total_count * cutoff_percentage
    VAR sum_above_cutoff = SUMX(
        FILTER(
        ALL('Asset Type Overview'),
        'Asset Type Overview'[Count] > cutoff_number),
        ('Asset Type Overview'[Count])
    )
    VAR remainder = total_count-sum_above_cutoff
    VAR filter_points = IF(
        MAX('Asset Type Overview'[Count]) < cutoff_number,
        BLANK(),
        MAX('Asset Type Overview'[Count])
    )
    VAR result = IF(
        current_category = "Other",
        remainder,
        filter_points
    )
    RETURN result

Once you have written the DAX expression for the measure, click on the tick icon on the left side of the expression textbox.

Now, this newly created table and measure can be used for visualization by dropping the DAX expression instead of the column in the Values section.

Save the template​

In the Power BI Desktop window, go to the File option in the top menu bar and click the Save As button. Save the file in .pbix format to your desired location and provide a name for the saved file. This file can be imported into the Power BI Service to create/modify the dashboard template panels.

Dashboard​

The dashboard contains four panels representing the data with chart visualizations:

• Asset Type Overview: The Asset Type Overview panel shows the number of assets belonging to each asset type.
• Asset Manufacturer Overview: The Asset Manufacturer Overview panel shows the number of assets belonging to each asset manufacturer.
• Software Publishers Overview: The Software Publishers Overview panel shows the number of software belonging to each software publisher.
• Windows OS Overview: The Windows OS overview panel shows the number of assets belonging to each OS category.

Troubleshoot​

This section describes the common issues that might happen during the running of the integration and the steps to resolve the issues.

Refresh is not working or it is taking too much time​

If the refresh feature is not working consistently, it is most likely an issue on the Power BI side. If you are using the Power BI desktop, you can try clearing the cache. Follow the below steps to clear the cache.

 In Power BI Desktop navigate to File > Options and Settings > Options > Data Load.

On the right side, scroll to the bottom and select the three Clear cache options individually.

If you are not using the Power BI desktop or the above steps do not resolve the issue, as a workaround, you can use a pagination-based template file (i.e. Sample Lansweeper Dashboard Pagination.pbix).