Index

Overview

Both of Microsoft's Excel and Power BI products use the same method (Power Query) to retrieve and format data from external API's. This method can be used to bring Statseeker configuration and timeseries data into either of these products. Once the Statseeker data has been made available, the graphing and analytics capabilities of the product can be used to build reports as needed.

The process requires basic familiarity with the Statseeker API, see to following for assistance:

The options when specifying the datasource are to either:

  • configure your Statseeker server as a web data resource
  • run a script to retrieve your data from Statseeker, save it to a file, and set the file as the datasource

The Statseeker API returns your data as JSON, unlike Power BI, Excel doesn't include the format as option when importing data from a file. To import the data from a file into Excel, you will need to configure the connection as if you were connecting to a web datasource, and specify the files' save location as the web datasource URL.

[top]

The API Query

There are a few considerations when building a query to retrieve data from Statseeker, the foremost being data size. In some instances you will be manipulating data within Excel/Power BI prior to running analytics and this process is adversely affected by very large data sets. In some instances it may quicker to retrieve smaller data sets, complete your data manipulation on each individually and then merge where necessary.

When retrieving timeseries data from Statseeker you can request that the timestamp of the record be included, this is essential when merging data sets with different start-times or polling intervals.

The query we will be using in our example is a very simple request for inbound and outbound utilization metrics for a single device:

my.statseeker.server/api/v2.1/cdt_port/?fields=cdt_device.name,name,RxUtil,TxUtil&formats=vals,avg,min,max&timefilter=range = start_of_last_week to end_of_last_week&cdt_device.name_filter=IS("Rome-rtr")&value_time=start&timefmt=%d/%m/%y %H:%M&links=none

  • cdt_port - return interface data
  • fields=cdt_device.name,name,RxUtil,TxUtil - device and interface name, as well as inbound/outbound utilization
  • formats=vals,avg,min,max - data formats to return for both RxUtil and TxUtil
  • timefilter=range = start_of_last_week to end_of_last_week - return timeseries data for the previous week
  • cdt_device.name_filter=IS("Rome-rtr") - only return data on the the device named 'Rome-rtr'
  • value_time=start - the query uses the default polling interval so this parameter will return the timestamp for each timeseries value
  • timefmt=%d/%m/%y %H:%M - Power Query doesn't read epoch time, so specify a format for the timestamp
  • links=none - don't return unnecessary data

[top]

Retrieve Data

Connecting to the data source differs slightly between Excel and Power BI.

Excel
  • Select Data tab > New Query > From Other Sources > From Web

If you already have your Statseeker data saved to a file:

  • Set URL to "file:\{filepath to saved API output}", eg. "file:\C:\apiOutput\Rome-rtrLastWeek.json"


If you want to retrieve data directly from Statseeker (Web Resource):

  • Specify the entire API query string as the URL, and click OK
  • Select Basic authentication and specify credentials for a Statseeker User with API access

Note: if you are using the default self-signed certificate on your Statseeker web server, then you may need to modify the hosts file on the local machine to include an entry mapping the server IP to the host name specified on the certificate. You can review the certificate settings in the Admin Tool > Statseeker Administration > Web Server Configuration. This will resolve SSL related errors thrown by Excel/Power BI due to connecting to a source with a self-signed certificate.

Power BI

In our example we are using Power BI Desktop, but the process is identical across the Power BI range.

If you already have your Statseeker data saved to a file:

  • Select Home tab > Get Data > File > JSON and click Connect
  • Browse to the location of the saved API output, select it and click Open

If you want to retrieve data directly from Statseeker (Web Resource):

  • Select Home tab > Get Data > Other > Web and click Connect
  • Specify the entire API query string as the URL, and click OK
  • Select Basic authentication and specify credentials for a Statseeker User with API access

Note: if you are using the default self-signed certificate on your Statseeker web server, then you may need to modify the hosts file on the local machine to include an entry mapping the server IP to the host name specified on the certificate. You can review the certificate settings in the Admin Tool > Statseeker Administration > Web Server Configuration. This will resolve SSL related errors thrown by Excel/Power BI due to connecting to a source with a self-signed certificate.

Format Data

Once the connection to the datasource has been made, the data is retrieved and is available for formating/manipulation prior to running analysis and report generation. This process is identical for Excel and Power BI, we will be using Power BI Desktop in our example.


The first step is navigating the retrieved data object to access the node containing your data.

  • Click through the links to walk the data object until you reach the node containing the response data
  • Click the List link to display the data rows

  • Click To Table

  • No delimiter is needed, simply click OK

  • Click the icon in the column header to separate the records into columns

  • Select the columns to include
Notes: If your API response contains timeseries values (formats=vals) for more than one metric, then only include a single metric in each query. The 'vals' are returned as an array and will need to be separated into individual rows, do this for each metric individually and then merge the resulting tables. This process will be detailed in our example.

The timeseries metrics are imported as a 'record' and need to be expanded into separate columns.

  • Click the icon in the column header to separate the records into columns

  • Select the data formats to bring in and click OK

The timeseries 'vals' are returned as an array and will need to be separated into individual rows.

  • Click the icon in the column header and select Expand to New Rows

The next few steps can be omitted if you are not returning timestamps with your timeseries values, but not including timestamps will restrict your reporting options.

  • Click the icon in the column header and select Extract Values

  • Specify Comma as the delimiter to use

The timeseries values are paired with their timestamp as per the API request, we need to separate those values.

    Select the column and click Split Column > By Delimiter

  • Specify Comma as the delimiter to use

Ensure that the correct data type is set for each column.

  • Select the column header and click Data Type to amend data types as needed
  • Right-click column headers to rename them as needed

  • Right-click the query to rename as needed

If you only have timeseries values for a single metric, then you are done, the data is ready to use in Excel/Power BI:

  • click Close and Apply to complete the process

If you have timeseries values for multiple metrics, then you are going to need to create a new query for the second metric and merge the two data tables. To do this:

  • Return to the query editor, right-click the initial query and select Duplicate

  • In Query panel > Applied Steps delete everything after the Converted to Table step

  • Repeat the process from the initial query, this time selecting a different metric and continue through expanding data into rows, renaming and setting data types until the second query is complete

Once the second query is complete:

  • Right-click in the Queries pane and select New query > Combine > Merge Queries as New

  • Select the query tables and columns to merge and click OK

  • Expand the appended table, selecting only those columns you want to display
  • Click Close & Apply

The data is now available for reporting in Excel/Power BI.

[top]