# Power BI integrates with Domotz. Create your custom network reports.

This article will cover how to create helpful custom reports and analytics in Power BI Desktop using the Domotz custom connector. The Power BI integration will allow you to create custom reports on speed tests, RTD information, SNMP sensor charts, network and device status, and more.

Microsoft Power BI enables you to access data from hundreds of sources, such as Dynamics 365, Salesforce, Azure SQL DB, Excel, SharePoint, and more.

With Power BI, you can transform and clean the data into a data model and create charts or graphs to provide visuals of the data. 

You can create dashboards and reports without technical support. A powerful natural language interface and an intuitive graphical designer tool make the platform easy to use.

The Domotz custom connector allows you to gather data from the Domotz public API and create visually rich reports. 

Dashboard Overview of Device Discovery and Counts

1. **Filter Options**: At the top left, options to filter by Agent, Type, Vendor, and Model are available.

2. **Device Statistics**: Below the filters, the total Device Count is displayed as 936, and Important Devices count is shown as 79.

3. **Device First Discovery Chart**: Top right bar graph depicting the count of new devices discovered from 2016 to 2023 across different offices, with each office represented by a distinct color.

4. **Count by Vendor**: Bottom left chart presents device count by vendor, categorized as 'Floating' or 'Vital'. Unknown vendor leads with 212 vital devices.

5. **Count by Model**: Bottom center chart shows device count by model, with Raspberry Pi having the highest importance as either Floating (444) or Vital (39).

6. **Count by Type**: Bottom right chart details device count by type, also divided into Floating and Vital categories. Notebooks and Mobiles are prominently counted.

These charts help in understanding the distribution and prioritization of devices across different categories and time periods.

## How to install the Domotz connector for Power BI integration

First, you need to enable support for custom connectors. 

To do so, you need to download and put the [domotz.mez](https://github.com/domotz/power_bi_domotz/raw/main/bin/Debug/domotz.mez) file in this location (create a folder with the following path, if it does not already exist):

**C:\Users\$username\OneDrive...\Documents\Microsoft Power BI Desktop\Custom Connectors**

If you do not use OneDrive, put it in:

**C:\Users\$username\Documents\Power BI Desktop\Custom Connectors** 

Start using Power BI Desktop, and from_File  → Options and Settings →Options → Security → Data Extensions_, select “_Allow any extension to load without validation or warning_” and click OK.
Then go to_File → Get data -> Get data to get started_:

Accessing Data in Power BI.

1. In the Power BI interface, open the left sidebar menu and select "Get data."
2. A list of data sources appears, including options like Excel workbook, Power BI datasets, SQL Server database, and more.
3. Click "Get data to get started" to proceed with importing data using your selected source.

Search for the Domotz extension, select it,  and click connect:

Connecting to Domotz in the Get Data window.

1. In the search box, type "domotz" to filter the options.
2. Select "domotz" from the list on the right.
3. Click the "Connect" button to proceed with the connection.

To complete the configuration, you must provide the **API Endpoint** and **API Key** (please see here [https://help.domotz.com/admin-global-features/domotz-api/](https://help.domotz.com/admin-global-features/domotz-api/):

Configuring Domotz Public API Settings

1. Navigate to the Domotz section on the left sidebar.
2. Select "Domotz Public API" to access the API configuration settings.
3. In the "API Endpoint" field, input the endpoint URL: https://api-eu-west-1-cell-1.domotz.com/
4. Enter your API Key in the provided field.
5. Click "Save" to apply the changes or "Cancel" to discard them.

When the configuration is complete, you will have access to the Domotz Public API endpoints. Remember that some data might require transformation (expanding nested objects, data conversion) before being usable from Power BI visualization tools.

Navigator Interface for Data Selection and Loading

1. On the left panel, under "Display Options," a list of available data queries is presented. Select "getDeviceRTDHistory" to access historical device data.

2. The right panel contains input fields for filtering data:
   - Enter an "agent_id."
   - Enter a "device_id."
   - Optionally, specify "from" and "to" dates to narrow data by date range.

3. Click "Apply" to process the query or "Clear" to reset the fields.

4. The bottom section displays a table labeled "getDeviceRTDHistory." It contains columns for "timestamp," "min," "median," "max," and "lost_packet_count," showing device performance metrics.

5. Use the buttons at the bottom right of the interface:
   - "Load" to import the selected data.
   - "Transform Data" for further data manipulation.
   - "Cancel" to exit without loading data.

Now the connector is ready to be used.

## Run Domotz Power BI templates

Please download the Domotz template here: [https://github.com/domotz/power_bi_domotz/blob/main/templates/Domotz.pbit](https://github.com/domotz/power_bi_domotz/raw/main/templates/Domotz.pbit) 

And double click on it.

When opening the template, a popup message will ask for the time interval. This will be important to retrieve all the historical data you need:

Domotz Power BI data import form.

1. Set the date range by entering the start date in the "Date From" field and the end date in the "Date To" field. The period should be at most 30 days; if left blank, the last week will be selected automatically.
2. Decide whether to load TCP Ports by selecting "Yes" or "No" from the dropdown.
3. Choose whether to load SNMP data. Options are "Yes" or "Yes, with history."
4. For devices uptime, choose "Yes" or "No" as needed.
5. Select if you want to load device variables. Options include "Yes" or "Yes, with history."
6. Choose to load device applications by selecting "Yes" or "No."
7. Once selections are made, click "Load" to proceed or "Cancel" to stop the process.

You can simply click the “Load” button leaving empty the form, and you access the historical data of the last 7 days. Otherwise, when specifying the time interval, please take care of respecting the following limits:

- Size of the time interval at most 30 days
- “Date From” not before 2 months ago

The date format to use is “MM/DD/YYYY” or “DD/MM/YYYY” depending on the settings of the system used.

The import and processing of the data should now start, and you will see the following screen:

Refresh dialog box for loading data.

1. The dialog box displays a list of models with their loading status. Models include "Agents," "Agents Speed Test," "Agents Status History," "Agents Uptime," and "Devices."
2. Each model is currently in the process of loading, as indicated by the message "Loading data to model…" next to each item.
3. A scrollbar on the right suggests more items may be available to view.
4. A "Cancel" button is located at the bottom right to stop the loading process if needed.

Load time (as well as API consumption) depends on the number of agents and sensors.

## Domotz Power BI templates

When the data import is finished, you will be able to see Domotz Power BI Dashboards.

Each dashboard contains interactive reports and filters: you can act on the filters or click a specific item in a report to filter all the information in the dashboard.

- [Network Highlights](#network-highlights)
- [Network Inventory](#network-inventory)
- [Device Performance](#device-performance)
- [SNMP/TCP](#snmp-tcp)
- [Variables](#variables)
- [Applications](#applications)

### Network Highlights

Network Monitoring Dashboard Overview

1. The dashboard displays filters at the top for Agent, API Status, and Licence Status, allowing selection of specific criteria.

2. The world map indicates agent status locations with color-coded markers: red for offline and green for online agents.

3. In the "My Networks" table, details include Agent Name, ID, Uptime, Agent Status, and Last Status Change with dates and times.

4. The "Agent Downtime" graph shows a bar indicating an 11.32440% downtime for the period 6/5/2023 to 6/12/2023.

5. The "Agent Event History" chart reflects event counts for connection losses and recoveries, labeled by event type, with six occurrences each for Domotz Office.

6. The "Agent Speed Test" graph compares the average download and upload speeds (in bps) for different offices, showing the highest download average of 0.63bn bps at Draper Office.

“Network Highlights” reports data about your Agents: their current status and location, their downtime during the selected period, the average speed test of their networks (AGENT SPEED TEST), and the events registered (AGENT EVENT HISTORY). 

In AGENT DOWNTIME, AGENT SPEED TEST, and AGENT EVENT HISTORY, if you right-click on one agent data, you can drill down by time to visualize the downtime events or the historical trend:

Context menu options for analyzing chart data in a dashboard.

1. Right-click on a bar within the "Agent Speed Test" chart to open the context menu.
2. Choose "Drill down" to view more detailed data related to the selected item.
3. Select "Show data point as a table" to see the data point in a tabular format.
4. Opt for "Show as a table" to convert the entire chart data into a table.
5. Use "Show next level" or "Expand to next level" to explore hierarchical data further.
6. Choose "Include" or "Exclude" to filter the chart data based on selected criteria.
7. Click "Analyze" to perform advanced data analysis.
8. Use "Copy" to duplicate the data point for use elsewhere.

Agent Speed Test Results for April to May 2021

1. The chart displays average download and upload speeds in Mbps over a month.
2. Light blue bars represent the average download speed, consistently at 94 Mbps except for the penultimate and last entries, which decrease to 93 Mbps.
3. Dark blue bars represent the average upload speed, maintaining a regular pattern with slight fluctuations.
4. X-axis labels are uniformly marked "Dr. Corp," representing different test intervals or locations.
5. The y-axis indicates speed in bits per second, ranging from 0M to 100M. Blue and dark blue legends identify the color representation for download and upload speeds, respectively.

Remember that speed test and event history data are limited to the time period selected before the data load. 

### Network Inventory

Dashboard Overview of Device Discovery and Counts

1. **Filter Options**: At the top left, options to filter by Agent, Type, Vendor, and Model are available.

2. **Device Statistics**: Below the filters, the total Device Count is displayed as 936, and Important Devices count is shown as 79.

3. **Device First Discovery Chart**: Top right bar graph depicting the count of new devices discovered from 2016 to 2023 across different offices, with each office represented by a distinct color.

4. **Count by Vendor**: Bottom left chart presents device count by vendor, categorized as 'Floating' or 'Vital'. Unknown vendor leads with 212 vital devices.

5. **Count by Model**: Bottom center chart shows device count by model, with Raspberry Pi having the highest importance as either Floating (444) or Vital (39).

6. **Count by Type**: Bottom right chart details device count by type, also divided into Floating and Vital categories. Notebooks and Mobiles are prominently counted.

These charts help in understanding the distribution and prioritization of devices across different categories and time periods.

In “Network Inventory”, you see the composition of your networks by device vendor (COUNT BY VENDOR), model (COUNT BY MODEL), and type (COUNT BY TYPE) distinguished by importance (VITAL or FLOATING).

The “device first discovery” graph reports how many devices were discovered over time, with the possibility of drilling down over time.

### Device Performance

Network Performance Metrics Dashboard

1. **Filters**: The top of the dashboard includes dropdown filters for Agent, Importance, Type, Vendor, and Model. Use these to refine the data displayed.

2. **RTD Average Median Section**: Displays a bar graph of device median round-trip delay times in milliseconds (ms). Key devices include "HUAWEI_P30_Pro" at 176 ms and "RedPanda DNA" at 27 ms.

3. **Latest Lost Packet Percentage**: Shows the percentage of lost packets for each device. Notable entries are "RedPanda DNA" with 45.00% and "HUAWEI_P30_Pro" with 20.67%.

4. **Important Device Downtime**: A bar chart highlighting downtime percentages from 6/5/2023 to 6/12/2023. "Pisa Ufficio 2 DN" has 95.83% downtime, while "ni-team Hyper-v" has 14.29%.

5. **Device Table**: Lists devices with details such as Agent name, Device, IP Addresses, Type, Vendor, and Model. Also includes Importance, Agent Uptime, Device Uptime, and RTD (Round Trip Delay) metrics. Key entries include "HUAWEI_P30_Pro" and "RedPanda DNA" marked as VITAL with varying uptime and downtime percentages.

Device Performance may help you find which of your devices is slow regarding RTD (RTD AVERAGE MEDIAN) and which has the highest packet loss (LATEST LOST PACKET PERCENTAGE). It also reports the downtime of important devices.

If you want to troubleshoot your device in Domotz WebApp, click on the Device field in the table, which will link you to its page.

### SNMP/TCP

Network Monitoring Dashboard Overview

1. Filters at the top allow selection by Agent, Device, SNMP Sensor Name, SNMP Sensor Category, and date range. Use these to refine the data displayed below.

2. A table lists network devices with columns for Agent, Device, Sensor Name, SNMP Sensor OID, Sensor Category, and Sensor Value Type. Use this table for detailed device information.

3. The "Sensors with Numeric Value Type" section includes a graph displaying sensor values over time. Track trends and changes in sensor data here.

4. The "TCP Ports Status" section shows port statuses with bars representing Open, Closed, and Filtered states. Two tables show details of each port’s status by Agent, Device, TCP Port, and Last Update. Use this to monitor network port activity.

SNMP/TCP retrieves data from your sensors. The latest values are reported in the tables, while in the chart, you see the historical data over time of the SNMP sensors with a numerical value type.

### Variables

RAM Usage Data Visualization Interface

1. Use the dropdown menus at the top to select the "Agent," "Device," "Variable Path," "Variable Label," "Metric," and "Unit." Currently, all are set to "All" except for "Metric," which is set to "ram_usage."

2. In the "Variable by Device and Path" chart, two bars indicate RAM usage. "Server01" (value 75) and "DC01" (value 78) under "system/ram/usage" show usage levels.

3. The "Variables with Numeric Value Type" line chart displays RAM usage over time, with data points fluctuating around the 70 to 80 range.

4. The table below lists details for each device, including "Agent Name," "Device Name," "Variable Id," "Path," "Label," "Unit," "Metric," "Value," and "Value Update Time," which gives specific RAM usage percentages and timestamps for "Server01" and "DC01."

Use this interface to analyze RAM usage across different devices and time periods, adjusting filters as needed for detailed insights.

If you use Pre-Configured SNMP Sensors, Custom Integrations, or TCP Sensors, this dashboard allows you to access this information in a single place. Just filtering by “Metric” you will be able to see at a glance the free space of all your disks or the RAM usage of all devices along with their historical values. Alternatively, you can filter by “Unit” to see, for example, any temperature sensor or select the specific sensors you are interested in by their path or label.

### Applications

Dashboard Displaying Application Usage by Device

1. The top section provides filters for narrowing down data, including options for "Agent," "Device," "Application Name," and "First Time Seen."
2. "Application Count" shows a total of 17 applications.
3. "Application First Seen" bar graph displays usage for "Hyper-v-agent-Linux-snap" agent, with 17 applications first seen in the year 2023.
4. "Count of Applications by Device" bar graph shows applications by the "Hyper-v-agent-Linux-snap" agent, with 10 for device "DC01" and 8 for "Server01."
5. A table lists details such as "Agent Name," "Device Name," "Application Name," "Version," "Publisher," and "Info" for various software, including applications like Google Chrome and Microsoft Visual C++.

If you unlock the OS monitoring on any device, here you can see the list of all the applications in your devices, with a visual of when they were discovered. You can drill down in this view to determine when and where you installed the new applications.

## Customizing your Power BI templates

Every Power BI template, even the Domotz one, can be customized and adapted to fit your needs better.

You can set a global filter at the page or template level using the “Filters” section or create a visualization leveraging the list of fields available in the “Fields” section.

Adjusting Data Filters and Visualizations in a Software Interface

1. To filter data, locate the "Filters" panel on the left. Use the search box to find specific filters or directly click "Add data fields here" under "Filters on this page" or "Filters on all pages."

2. In the "Visualizations" panel, explore various chart types and visuals. This section allows you to choose the appropriate visual representation for your data. Click on the icons to apply your desired visualization style. You can also add fields under "Values" and "Drill through" options for more detailed filtering.

3. On the right, in the "Fields" panel, a list of data fields is shown. Use the search box to quickly locate fields. Drag relevant fields into the visualizations and filters sections to customize the data display.

4. The graphs on the left provide a visual representation of sensor data over time. Adjust the date range using the top selectors to focus on specific periods.

For example, add/remove some fields from the tabular views of the Domotz template or create a chart view containing only a specific set of sensors (e.g., all the printer supply levels).

When you finish your customization, you can save it as a .pbit or .pbix file:

- .pbix is a file that contains the structure (data sources, data manipulations, the data model, reports) along with the actual data
- .pbit is a template file that contains the structure like a .pbix file but without data

## What is and why use Power BI gateway?

Power BI gateway is software required to access data in an on-premises network. The gateway manages the request if anyone needs to access on-premises data from a cloud or web-based app. 

The on-premises Power BI gateway provides quick and secure data transfer between data not in the cloud, called on-premises data, and several Microsoft cloud services.

Power BI gateway allows you to schedule an automatic refresh of your reports or an on-demand refresh.

## How to get started with the Power BI gateway

Get ready to use the data gateway in Power BI by following these five simple steps:

1: [Download the standard mode](https://powerbi.microsoft.com/en-us/gateway/) of the Power BI gateway and install it.

2: Start it and login in with your credentials. 

3: Create a new Power BI gateway with a custom name, for example, “_myDomotzGateway”_. 

4: Open the connectors settings tab and copy the _domotz.mez_ file in the default directory proposed by the interface, in this example, “_C:\Windows\ServiceProfiles\PBIEgwService\Documents\Power BI Desktop\Custom Connectors”_. If the directory does not exist, create it.  

Step 5: See the Domotz connector loaded after you restart your gateway service:

On-premises data gateway configuration for custom data connectors

1. In the On-premises data gateway interface, select "Connectors" from the left-hand menu to access the custom data connectors settings.
2. Under "Custom data connectors," you can see a list of existing connectors. In this example, "domotz" is displayed as a connector name.
3. Below, find the field labeled "Load custom data connectors from folder." This shows the directory path where the connectors are stored: `C:\Windows\ServiceProfiles\PBIgwService\Documents\Power BI`.
4. Use the ellipsis button next to this field to browse and select a different folder if needed.
5. Click "Close" at the bottom of the window to exit the configuration.

## How to configure the Power BI service to use the Power BI gateway

Firstly, open the_“Manage Gateways”_ section in the Power BI service:

Navigating the Software Menu

1. Locate the top navigation bar with icons including a search icon, notification bell, settings gear, download symbol, help question mark, and user profile.

2. Click on the settings gear icon to open a dropdown menu.

3. In the dropdown menu, options listed sequentially are: Manage personal storage, View content pack, Admin portal, Manage gateways, Settings, and Manage embed codes.

4. Select "Manage gateways" for tasks related to managing system gateways.

Secondly, select _“Allow user’s custom data connectors to refresh through this gateway cluster”_:

Gateway Cluster Settings Configuration

1. Ensure the "myDomotzGateway" cluster is selected from the "GATEWAY CLUSTERS" list on the left.
2. Confirm status reads “Online: You are good to go” to verify connection.
3. In the "Gateway Cluster Name" field, enter or confirm the name, such as "myDomotzGateway."
4. Enter relevant details in the "Department" and "Description" fields as necessary.
5. Provide contact email information in the "Contact Information" field, such as "@domotz.com."
6. Check the box to allow cloud data sources to refresh through this gateway cluster if needed.
7. Optionally, check the box to allow custom data connectors to refresh. This is a preview feature.
8. Choose whether to distribute requests across all active gateways in this cluster.
9. Click "Apply" to save changes or "Discard" to cancel.

Then add the Domotz data source:

Gateway Cluster Settings Menu

1. Confirm the gateway is online with the message "Online: You are good to go."
2. Click on the link labeled "Add data sources to use the gateway" to proceed with adding data sources.

The last step is to provide your Domotz Public API credentials – use the API endpoint as Username and the API Key as Password:

Gateway Cluster Settings Configuration

1. Ensure the "myDomotzGateway" cluster is selected from the "GATEWAY CLUSTERS" list on the left.
2. Confirm status reads “Online: You are good to go” to verify connection.
3. In the "Gateway Cluster Name" field, enter or confirm the name, such as "myDomotzGateway."
4. Enter relevant details in the "Department" and "Description" fields as necessary.
5. Provide contact email information in the "Contact Information" field, such as "@domotz.com."
6. Check the box to allow cloud data sources to refresh through this gateway cluster if needed.
7. Optionally, check the box to allow custom data connectors to refresh. This is a preview feature.
8. Choose whether to distribute requests across all active gateways in this cluster.
9. Click "Apply" to save changes or "Discard" to cancel.

## How to use the data source to refresh the data

Create your report starting from the [Domotz template for Power BI](https://github.com/domotz/power_bi_domotz/tree/main/templates). Do not select a time frame when opening the template. 

Publish your report when you want. 

As a result, you’ll see both the report and the dataset in the Power BI service workspace:

Workspace Overview in a Software Application

1. At the top left, there is a section titled "My workspace" with a "+ New" button underneath, allowing users to create new items.
2. A notification bar announces updates to the workspace interface with an option to take a tour.
3. Below, there's a tabbed interface with options labeled "All," "Content," and "Datasets + dataflows" for navigating different sections.
4. The table displays a list of items with columns: "Name," "Type," "Owner," "Refreshed," "Next refresh," "Endorsement," and "Sensitivity."
5. Two entries are listed in the table:
   - The first is a "Report" named "publisheddomotz," owned by Andrea Azzara, with a refresh date of 28/05/21, 12:25:01.
   - The second is a "Dataset" with the same name and owner, also refreshed on 28/05/21, 12:25:01. The next refresh is marked "N/A."

Proceed with opening the _“Settings”_ of the datasets from the menu to open _“Gateway Connection”_:

Power BI Workspace Menu Navigation

1. Access your workspace by selecting "My workspace" from the sidebar.
2. Click on "+ New" to create new content, such as reports or datasets.
3. Use the main menu to create or manage reports. Options include "Analyze in Excel," "Create report," "Create paginated report," "Delete," and more.
4. In the content list, you can see the names of datasets or reports, their types, owners, last refreshed times, and any endorsements.
5. To take actions on a specific dataset or report, click the ellipsis (three-dot) icon next to it to view options like "Settings," "Manage permissions," and "View lineage."
6. Use the options above the list to change the view, apply filters, or search for specific content.

As a result, you can now refresh the dataset by assigning the newly-created data source:

Settings for Published Dataset Using a Gateway Connection

1. Navigate to the "Datasets" tab in the main menu and select the dataset named "publisheddomotz" from the list on the left.

2. View the dataset settings that indicate the last refresh succeeded. A link to "Refresh history" is available for detailed logs.

3. Under "Gateway connection," ensure the option to use an "On-premises or VNet data gateway" is turned on.

4. In the "Gateway" section, verify that "myDomotzGateway" is selected.

5. Check that data sources for the gateway are correctly included and that the mapping is set to "domotz."

6. Confirm the contact information is displayed and that the status shows as "Running on WINDEV2004EVAL."

7. For any changes, use the "Apply" or "Discard" buttons below the gateway options.

In addition, on the same settings page, you can also configure the automatically scheduled refresh:

Settings for a published data gateway and scheduled refresh.

1. The page heading is "Settings for publisheddomotz" with a last refresh success message and timestamp.
2. Under "Gateway connection," users can toggle the use of an On-premises or VNet data gateway. The toggle is currently set to "Off."
3. A listed gateway, "myDomotzGateway," includes a data source with the extension and source path specified, mapped to "domotz."
4. Options to "Apply" or "Discard" changes are available but not activated.
5. "Data source credentials" and "Parameters" sections are listed but not expanded.
6. Under "Scheduled refresh," the refresh option is "On." The refresh frequency is set to "Daily" and the time zone is "(UTC+01:00) Brussels, Copenhagen, ..."
7. There is an option to "Add another time" for scheduling.

## I am not able to see the geo map in PowerBI

It may happen that the Geo Map is not visible in your PowerBI Domotz Template. This may be due to PowerBI configurations:

Power BI Desktop User Interface with Disabled Visuals Notification

1. The Power BI Desktop interface displays the toolbar at the top with tabs labeled File, Home, Insert, Modeling, and others.

2. A notification in the main workspace indicates that "Map and filled map visuals are disabled." To enable these, users are instructed to go to File > Options and settings > Options > Global > Security.

3. On the right side, there are various charts under sections labeled "Agent Event History" and "Agent Speed Test," showing data trends and event types.

4. Below the main notification, a table labeled "My Networks" lists columns like Agent Name, Agent Id, Uptime, and Agent Status, displaying network data.

5. The bottom section includes tabs such as Network Highlights, Network Inventory, and others, suggesting different data views or categories available for selection.

Under File -> Options and Settings -> Optiona -> Global -> Security, you may check the following checkbox to enable it:

Power BI Options Configuration

1. In the Power BI Options window, select "Security" from the left-hand menu under the "GLOBAL" section.
2. Navigate to the "Map and Filled Map visuals" section.
3. Check the box next to "Use Map and Filled Map visuals" to enable this feature.
4. If required, review other settings such as "Data Extensions" and "Authentication Browser" to configure how extensions and authentication are handled.
5. Confirm by clicking the "OK" button at the bottom of the window to apply the settings or select "Cancel" to disregard changes.