Meet us at Cisco Live Las Vegas 2024
Home
>
Blog
>
Integrating IP Fabric into Excel & Power BI

Integrating IP Fabric into Excel & Power BI

4 minute read
Home
>
Blog
>
Integrating IP Fabric into Excel & Power BI
Updated: December 14, 2023
April 11, 2023
Updated: December 14, 2023
4 mins

IP Fabric's GUI provides you with Excel-like tables for filtering and querying data, but sometimes engineers require data to be extracted for more advanced reporting or Power BI dashboards. In this blog, I will demonstrate how to directly connect IP Fabric to Microsoft Excel or Power BI without using CSV exports. This has been simplified into functions stored in template files allowing you to pull any IP Fabric table in seconds!

Documentation and template files are located in the IP Fabric Integrations GitLab.

Prerequisites

You must have web API access to the IP Fabric server (default is port 443). If running these queries from Excel in Office 365 or Power BI in the cloud, your IP Fabric instance must be reachable from the public Internet.

Microsoft Excel and Power BI will only allow you to communicate with websites that have valid and trusted SSL certificates. The easiest solution is to install a trusted certificate on the IP Fabric server that has been signed by a CA in your Trusted Root Certification Authorities. If you are unable to create a signed certificate you are also able to use a self-signed cert and install it on your location machine. This must be done for every user/computer that will be running the Power Query and you will not be allowed to run these queries in the Cloud.

Finally, you will also need to have, or create, an API Token. This token will be saved within the file (or the data source on powerbi.com) so sharing is not recommended. We recommend either removing your personal token before sharing the file (or posting the file to a shared cloud space, i.e. SharePoint) or creating a limited read-only account and API token. If you need more information or assistance with read-only accounts and RBAC please reach out to your Solution Architect.

Configuration

After opening either the Excel or Power BI template file there are two required configurations that must be done prior to accessing data. You may be required to accept some warning messages about communicating with external content prior to being able to continue.

In Excel open the Data Ribbon and click "Queries & Connections" and right-click the IPF_URL query and select edit. This will bring up the Power Query Editor where you are able to input your information. Example configuration:

  • IPF_URL: https://demo1.us.ipfabric.io/
  • IPF_TOKEN: your_ipf_api_token
  • snapshotID: $last
    • Optional: defaults to $last snapshot
    • Will also accept a UUID of a snapshot for instance 12dd8c61-129c-431a-b98b-4c9211571f89

If you are using Power BI when you open the template file you will be prompted with a pop-up to enter these variables. To access the Power Query Editor simply click "Transform data" in the Queries section of the Home ribbon.

Finally, you should be asked about data source settings prior to connecting, if not then select the Snapshots connection and in the Home ribbon in the Power Query Editor select select Refresh Preview. For the Credentials select Anonymous and for Privacy level either check the Ignore Privacy Levels box or select any level and press save. Once completed you should now see a table of the loaded Snapshots.

Querying IP Fabric Tables

The queryIPF function simplifies the pulling of data with only one required parameter, the API endpoint of the table. This can be located by selecting the Table Description or the ? icon on any of the tables and copying the URL under the API Description. For example, I would like to pull my Inventory > Devices table into Excel.

image 4

The reports variable defaults to false, setting to true will include the Intent Checks for the table if any are configured. This will be a new column named colname.severity where severity equals one of the following:

  • -1: No color
  • 0: Green
  • 10: Blue
  • 20: Amber
  • 30: Red

Once configured select Invoke (the epochCols and durationCols can be specified after invoking the function which will be discussed later).

image 5

Now we have the Inventory > Devices table loaded. Changing the Properties > Name will change the Excel Worksheet name so it is recommended to update this value. In this example I specified reports=true and we can see the new configReg.severity column.

Finally, click the Home ribbon Close & Load which will update work workbook or to add more tables simply repeat this process for each table you desire! It is really that simple.

Time Columns

Times in IP Fabric are stored in two different formats. When you use the API you will see that an integer is returned unlike when you view it in the GUI. Let's take a look at these formats:

  • Time since Epoch/Unix Timestamp: This is an absolute timestamp in milliseconds.
    • Example: End of X dates
    • For more information or examples please see epochconverter.com
    • Returns a new column named: colnameAsDate
  • "Duration" or Time Elapsed: Amount of time in seconds
    • Example: Device and routing protocols uptime
    • Returns a new column named: colnameAsTime

In the Device Inventory, the uptime can be converted into a human-readable format in Excel. Simply change the query by adding a list and the column name.

Original:

= queryIPF("https://demo1.us.ipfabric.io/api/v6.1/tables/inventory/devices", true, null, null)

Modified:

= queryIPF("https://demo1.us.ipfabric.io/api/v6.1/tables/inventory/devices", true, null, {"uptime"})

This then converts 3435180 into 39.18:13:00 (39 days, 18 hours, 13 minutes and 0 seconds.)

Epoch example with the End of Life detail table:

= queryIPF("https://demo1.us.ipfabric.io/api/v6.1/tables/reports/eof/detail", true, {"endSale", "endMaintenance", "endSupport"}, null)

Summary

As valuable as contextualized network data is for your engineering teams, we know how insightful it can be also for adjacent teams, leadership, or third parties. Using our IP Fabric-developed Excel and Power BI template files, you can communicate directly with IP Fabric without exporting CSVs, seamlessly leveraging this data for high-level and detailed reports and presentations.

If you found this interesting and want to implement this in your environment, please look at the following quick videos for a visual demonstration and more advanced information. This includes how to work with nested objects and/or lists as well as joining tables. Reach out to your IP Fabric Solution Architect for more information or assistance!

Want to try out IP Fabric yourself? Sign up for our self-guided demo and see what automated assurance could do for your network teams.

Integrating IP Fabric into Excel & Power BI

IP Fabric's GUI provides you with Excel-like tables for filtering and querying data, but sometimes engineers require data to be extracted for more advanced reporting or Power BI dashboards. In this blog, I will demonstrate how to directly connect IP Fabric to Microsoft Excel or Power BI without using CSV exports. This has been simplified into functions stored in template files allowing you to pull any IP Fabric table in seconds!

Documentation and template files are located in the IP Fabric Integrations GitLab.

Prerequisites

You must have web API access to the IP Fabric server (default is port 443). If running these queries from Excel in Office 365 or Power BI in the cloud, your IP Fabric instance must be reachable from the public Internet.

Microsoft Excel and Power BI will only allow you to communicate with websites that have valid and trusted SSL certificates. The easiest solution is to install a trusted certificate on the IP Fabric server that has been signed by a CA in your Trusted Root Certification Authorities. If you are unable to create a signed certificate you are also able to use a self-signed cert and install it on your location machine. This must be done for every user/computer that will be running the Power Query and you will not be allowed to run these queries in the Cloud.

Finally, you will also need to have, or create, an API Token. This token will be saved within the file (or the data source on powerbi.com) so sharing is not recommended. We recommend either removing your personal token before sharing the file (or posting the file to a shared cloud space, i.e. SharePoint) or creating a limited read-only account and API token. If you need more information or assistance with read-only accounts and RBAC please reach out to your Solution Architect.

Configuration

After opening either the Excel or Power BI template file there are two required configurations that must be done prior to accessing data. You may be required to accept some warning messages about communicating with external content prior to being able to continue.

In Excel open the Data Ribbon and click "Queries & Connections" and right-click the IPF_URL query and select edit. This will bring up the Power Query Editor where you are able to input your information. Example configuration:

  • IPF_URL: https://demo1.us.ipfabric.io/
  • IPF_TOKEN: your_ipf_api_token
  • snapshotID: $last
    • Optional: defaults to $last snapshot
    • Will also accept a UUID of a snapshot for instance 12dd8c61-129c-431a-b98b-4c9211571f89

If you are using Power BI when you open the template file you will be prompted with a pop-up to enter these variables. To access the Power Query Editor simply click "Transform data" in the Queries section of the Home ribbon.

Finally, you should be asked about data source settings prior to connecting, if not then select the Snapshots connection and in the Home ribbon in the Power Query Editor select select Refresh Preview. For the Credentials select Anonymous and for Privacy level either check the Ignore Privacy Levels box or select any level and press save. Once completed you should now see a table of the loaded Snapshots.

Querying IP Fabric Tables

The queryIPF function simplifies the pulling of data with only one required parameter, the API endpoint of the table. This can be located by selecting the Table Description or the ? icon on any of the tables and copying the URL under the API Description. For example, I would like to pull my Inventory > Devices table into Excel.

image 4

The reports variable defaults to false, setting to true will include the Intent Checks for the table if any are configured. This will be a new column named colname.severity where severity equals one of the following:

  • -1: No color
  • 0: Green
  • 10: Blue
  • 20: Amber
  • 30: Red

Once configured select Invoke (the epochCols and durationCols can be specified after invoking the function which will be discussed later).

image 5

Now we have the Inventory > Devices table loaded. Changing the Properties > Name will change the Excel Worksheet name so it is recommended to update this value. In this example I specified reports=true and we can see the new configReg.severity column.

Finally, click the Home ribbon Close & Load which will update work workbook or to add more tables simply repeat this process for each table you desire! It is really that simple.

Time Columns

Times in IP Fabric are stored in two different formats. When you use the API you will see that an integer is returned unlike when you view it in the GUI. Let's take a look at these formats:

  • Time since Epoch/Unix Timestamp: This is an absolute timestamp in milliseconds.
    • Example: End of X dates
    • For more information or examples please see epochconverter.com
    • Returns a new column named: colnameAsDate
  • "Duration" or Time Elapsed: Amount of time in seconds
    • Example: Device and routing protocols uptime
    • Returns a new column named: colnameAsTime

In the Device Inventory, the uptime can be converted into a human-readable format in Excel. Simply change the query by adding a list and the column name.

Original:

= queryIPF("https://demo1.us.ipfabric.io/api/v6.1/tables/inventory/devices", true, null, null)

Modified:

= queryIPF("https://demo1.us.ipfabric.io/api/v6.1/tables/inventory/devices", true, null, {"uptime"})

This then converts 3435180 into 39.18:13:00 (39 days, 18 hours, 13 minutes and 0 seconds.)

Epoch example with the End of Life detail table:

= queryIPF("https://demo1.us.ipfabric.io/api/v6.1/tables/reports/eof/detail", true, {"endSale", "endMaintenance", "endSupport"}, null)

Summary

As valuable as contextualized network data is for your engineering teams, we know how insightful it can be also for adjacent teams, leadership, or third parties. Using our IP Fabric-developed Excel and Power BI template files, you can communicate directly with IP Fabric without exporting CSVs, seamlessly leveraging this data for high-level and detailed reports and presentations.

If you found this interesting and want to implement this in your environment, please look at the following quick videos for a visual demonstration and more advanced information. This includes how to work with nested objects and/or lists as well as joining tables. Reach out to your IP Fabric Solution Architect for more information or assistance!

Want to try out IP Fabric yourself? Sign up for our self-guided demo and see what automated assurance could do for your network teams.

SHARE
Demo

Try out the platform

Test out IP Fabric’s automated network assurance platform yourself and be inspired by the endless possibilities.

What would this change for your network teams?
Start live demo
 
 
 
 
 
We're Hiring!
Join the Team and be part of the Future of Network Automation
Available Positions
IP Fabric, Inc.
115 BROADWAY, 5th Floor
NEW YORK NY, 10006
United States
This is a block of text. Double-click this text to edit it.
Phone : +1 617-821-3639
IP Fabric s.r.o.
Kateřinská 466/40
Praha 2 - Nové Město, 120 00
Czech Republic
This is a block of text. Double-click this text to edit it.
Phone : +420 720 022 997
IP Fabric UK Limited
Gateley Legal, 1 Paternoster Square, London,
England EC4M 7DX
This is a block of text. Double-click this text to edit it.
Phone : +420 720 022 997
IP Fabric, Inc. © 2024 All Rights Reserved