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:
- a NetPing device that supports the operation of temperature sensors (UniPing server solution v4/SMS, UniPing server solution v3, NetPing 8/PWR-220 v4/SMS, NetPing 4/PWR-220 v4/SMS, NetPing 2/PWR-220 v4/SMS, NetPing 2/PWR-220 v1/SMS, NetPing 2/PWR-220 v3/ETH);
- TS/WT temperature sensors or 1-wire (THS) temperature sensors (depending on NetPing device);
- a PC/a server with a preinstalled OS Windows 7+ and a PowerShell environment;
- Microsoft Office 2010/2013 with an installed superstructure Microsoft Power Query for Excel;
- a local/a corporate network for data transfer
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:
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:
- download a distribution file on the link https://www.microsoft.com/en-US/download/details.aspx?id=39379;
- close Microsoft Office (if windows of a program are opened in the system);
- run a standard installation process of Windows software
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»:
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»:
In the window of a Table Import Wizard, choose «Text File» as a data source and click «Next»:
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»
After a successful import operation, data from a CSV file will be transferred to an Excel file and represented as the table shown below:
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:
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»:
In the window «Create PivotChart» that appeared, there is a need to use a configuration of an external data source:
Choose the connection «Text thermo_data», which was created before:
As a result, an area of a pivot chart and a menu for configuring fields will appear at the sheet of an Excel file:
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:
Afterwards, in the settings «Change Chart type...» change the type from «Bar with grouping» to «Line with Markers» and click the button «OK»:
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»:
In the window «Insert Timelines» that appears, choose a scale «Date» and click the button «OK»:
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»:
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.
