• Main
  • >
  • Blog
  • >
  • Tutorial
  • >
  • Example of Collecting Information from NetPing Devices in a Form of an Excel Table
Tutorial
12/29/2015

Example of Collecting Information from NetPing Devices in a Form of an Excel Table

Example of Collecting Information from NetPing Devices in a Form of an Excel Table

On the basis of a developed functionality of sensor monitoring devices and remote controlling of NetPing power supply, it is possible to implement collecting information in different systems of monitoring and accounting. First of all, it is necessary to watch important parameters in the field of interest, as well as building various reports and charts for a specified period of time.

Receiving the information from NetPing devices can be flexible when based on the support of an SNMP protocol and specialized HTTP API commands. Everything necessary to do is to have a NetPing device with connected sensors, certain software or a script and a little time for configuring.

In this article, an example of semi-automated collecting of information from eight temperature sensors of a NetPing device in a form of an Excel table with building illustrative temperature charts and a possibility to view a history of temperatures in different periods of time will be examined.

To collect the information from a NetPing device, a PowerShell script is used, which uses HTTP API commands for receiving data from temperature sensors as the basis. A script is run by Windows Task Scheduler with a specified period of time and stores received values in a text file of a CSV format. An Excel file reads data from a CSV file with the help of a Microsoft Power Query superstructure in the moment when a user clicks a button «Refresh». 

Requirements

To collect the information from eight temperature sensors of a NetPing device in a form of an Excel table, there is a need to have:

Configuring a NetPing Device

To collect the information from temperature sensors, there is a need to connect temperature sensors to a NetPing device and configure network parameters of a device to access it via a network. More detailed information about it can be read in the firmware description and in a user guide for a corresponding NetPing device. All necessary documentation is located at the official website at the page of a description of a device in the section «Documentation and files».

As a result, after a simple configuration, a NetPing device must be available on the network, and readings of a temperature must be displayed  at the page «Temperature sensors» in a device web interface.

Configuration of Collecting the Information in a Form of an Excel Table with Building Illustrative Temperature Charts

1. An example of a PowerShell script for collecting the information from temperature sensors in a file of a CSV format

Below there is a PowerShell script, which must be copied into a text file. Save it with the extension «.ps1» or download a ready script file on the link: thermo_data.ps1.

$Dev_url='http://192.168.0.100/'
$Dev_login='visor'
$Dev_pass='ping'
$Number_of_sensors=8
$webclient = New-Object System.Net.WebClient
$webclient.Credentials = New-Object System.Net.NetworkCredential($Dev_login,$Dev_pass)

for ($i=1;$i -le $Number_of_sensors;$i++)

{

      $temp=New-Object PSObject -Property @{

                                                                      Date=get-date;

                                                                      Label="Thermosensor$i"

                                                                      Tmp=[regex]::Matches($webclient.DownloadString($Dev_url+'thermo.cgi?t'+$i),",\s?(.*),").groups[1].value

                                                                 }

      $temp | Export-Csv C:\temp\thermo_data.csv -NoTypeInformation -Append -Delimiter ","

}

where:

$Dev_url – is a variable where URL-address is specified that contains a domain name or an IP address of a NetPing device;
$Dev_login – is a variable where a username is specified for authorization at a NetPing device; 
$Dev_pass – is a variable where a password is specified for authorization at a NetPing device;
$Number_of_sensors – is a variable where the amount of requested temperature sensors of a NetPing device is specified

On default, performing PowerShell scenarios in the system is prohibited because scripts may contain a malicious code, which is able to damage a system. It is possible to disable verification of compliance for own scripts. To do this, there is a need to run a PowerShell console from a name of a system administrator and enter a command:

Set-ExecutionPolicy RemoteSigned

Kindly read more detailed information about a script execution policy in a PowerShell environment in the article «Using the Set-ExecutionPolicy Cmdlet».

After a file of a PowerShell script is saved in a file system of a computer, there is a need to configure its automatic start using a Windows Task Scheduler. A recommended launch interval is 5 minutes. To configure a task scheduler, it is possible to use the article «How to schedule a PowerShell Script to auto run on a Windows Server» at the Microsoft website or any similar one from other sources on the Internet.

At a successful launch of a PowerShell script, a CSV file of the following appearance will be created:

Create a CSV file based on PowerShell script

2. Configuring a Microsoft Power Query Superstructure for Excel

In this example, the Power Query tool for independent business data analysis and processing is used to receive data into Excel from a CSV file. Using Power Query, it is possible to receive data from external sources quickly and easily, and on this basis, it is possible to build different spreadsheets, reports and charts.

To install Microsoft Power Query for Excel, there is a need to:

3. Configuring an Excel File for Reading Data from a CSV File to Build Temperature Charts

To configure reading data from a CSV file, there is a need to create a new Excel file. Then, there is a need to indicate an external source for receiving data to read data, i.e. configure a connection with a CSV file. To do this, there is a need to go to the tab «POWERPIVOT» and click the button «Manage»:

POWERPIVOT tab in Excel

Afterwards, a new window «PowerPivot for Excel» will be opened, in which there is a need to choose an external data source by clicking menu items «Get External Data» -> «From Other Sources»:

Setting obtaining external data in Excel

In the window of a Table Import Wizard, choose «Text File» as a data source and click «Next»:

Import Wizard in Excel spreadsheets

When configuring a connection with unstructured file, specify a path to a CSV file, where data from temperature sensors of a NetPing device are located, check a checkbox «Use first row as column headers» and click the button «Finish»

Configuring the connection with unstructured file in Excel

After a successful import operation, data from a CSV file will be transferred to an Excel file and represented as the table shown below:

The imported data into Excel from CSV

At the same window, there is a need to configure a data type and a format of table columns, as shown at the screenshots below:

Setting the data type and format of the Excel spreadsheet column

Setting the data type and format of the Excel spreadsheet column

Setting the data type and format of the Excel spreadsheet column

Now, on the basis of a table formed by data from temperature sensors, there is a need to build a temperature chart and use a timeline filter. To do this, there is a need to go to the tab «INSERT» and choose the menu item «PivotChart»:

Setting a pivot chart in Excel

In the window «Create PivotChart» that appeared, there is a need to use a configuration of an external data source:

Application of an external data source to configure a pivot chart in Excel

Choose the connection «Text thermo_data», which was created before:

Selection of connection to an external data source into Excel

As a result, an area of a pivot chart and a menu for configuring fields will appear at the sheet of an Excel file:

Setting a pivot chart in Excel

To configure a pivot chart for representing a temperature from temperature sensors of a NetPing device, there is a need to move into a necessary area the fields «Date», «Tmp» and «Label», as shown at the screenshot below: 

Setting fields in the summary Excel charts

Afterwards, in the settings «Change Chart type...» change the type from «Bar with groupping» to «Line with Markers» and click the button «ОК»:

Selecting the graphics with markers in Excel

When done, a configuration of a temperature chart from temperature sensors of a NetPing device is completed. It is left only to add a filter for browsing a chart into different periods of time. To configure this filter, there is a need to go to the tab «INSERT», select an area of a temperature chart and click the menu item «Timeline»:

Setting Timeline in Excel

In the window «Insert Timelines» that appears, choose a scale «Date» and click the button «ОК»:

Insert timelines in Excel

4. Updating Data in the Excel File

In this example, data in the Excel file are not refreshed automatically. Therefore, to get actual readings, there is a need to select the area of a configured temperature chart, go to the tab «ANALYZE» and click the button «Refresh»:

Updating data in an Excel file

Result

As a result of a configuration described above, actual temperature charts were received from temperature sensors of a NetPing device with a possibility to browse readings in different periods of time using a timeline.

Actual temperature graph of temperature sensors on the device NetPing in Excel

 


  • UniPing server solution v3/SMS
  • UniPing server solution v3
  • NetPing IO v2
  • NetPing 8/PWR-220 v3/SMS
  • NetPing 4/PWR-220 v3/SMS
  • NetPing 2/PWR-220 v2/SMS
  • NetPing 2/PWR-220 v1/SMS
  • NetPing 2/PWR-220 v3/ETH

Main tags


All devices
All NetPing power
NetPing SMS
NetPing mini-UPS
NetPing 2/PWR-220 v1/SMS
UniPing server solution v3/SMS
UniPing server solution v3
NetPing /PWR-220 v3/ETH
NetPing IO v2
NetPing 4/PWR-220 v3/SMS
UniPing v3
Uniping server solution
NetPing 8/PWR-220 v3/SMS
NetPing 2/PWR-220 v2/SMS
NetPing 2/PWR-220 v3/ETH
NP-SM4
Temperature sensor T811, 2m
Temperature 1-wire, (THS), 2m
▼ All tags
New devices
Temperature sensor T811, 2m
Temperature sensor for measuring an ambient air temperature indoors from -40°C to +125°C. Avoid water details...

Price: 19 usd

UniPing server solution v3
A device for a remote monitoring of sensors via the Ethernet/Internet network. It allows to receive details...

Price: 180 usd

Humidity sensor WS-2
A relative humidity sensor for measuring relative humidity in % and a built-in temperature sensor.Plastic details...

Price: 42 usd

-+ usd usd
Subtotal usd

Your Order