Whether it be on the administrative side of the house or in a production environment, the digital world is not slowing down.  In fact, it is increasing by the second.  Data is collected from a thousand different sources and often stored in the same number of places.  Automating the collection, analyzing and augmentation of this data can be quite a cumbersome task and very time-consuming. Not to mention the loss in revenue when this is not done.  Crucial time is lost, and critical systems become in jeopardy when there is not a reliable and automated way of filtering through the noise and notifying teams of critical alerts.

The Problem

You have furnaces that must remain in between a certain temperature threshold.  If temps reach above this threshold, valuable resources are wasted due to fuel consumption, costing thousands of dollars.  The temperatures are stored in a database, but you have no way of extracting this data and furthermore, alerting maintenance teams of the potential risk.

The Solution

In two words, Enterprise Alert!  We will use PowerShell and Windows Task Manager to periodically extract information from the external database and then send a REST request to Enterprise Alert.  Once received, the magic of EA takes over, filtering through the data to identify if the conditions are within range and then quickly notifying the Maintenance team of the issue.

1. Create a PowerShell script like the one listed below.  Make sure to update the parameters for DB, REST Endpoint, and SQL query according to your environment and needs. Save the file to a location that is easily accessible like in this case C:/Scripts.

   $sql = "select sensor, location, temp from Datatable_furnace";
   $sqlConnection = new-object System.Data.SqlClient.SqlConnection("Server=sqlserver;User ID=dbread;Password=pw2020;Database=EnterpriseAlert;Integrated Security=true")
   $sqlConnection.open()
   $sqlCommand = $sqlConnection.CreateCommand()
   $sqlCommand.CommandText = $sql
   $DataSet = new-object System.Data.DataTable
   $Adapter = new-object System.Data.SqlClient.SqlDataAdapter($sqlCommand)
   $DataSet = New-Object System.Data.DataSet;
   $Adapter.Fill($DataSet)
   $dbConn.Close();
   $dbConn.Dispose();
   
   $MyResults = $DataSet.Tables[0];
   $MyResults | foreach-object {
       $sensor = $_.sensor;
       $location = $_.location;
       $temp = $_.temp;
   
   	$request = Invoke-RestMethod "<REST_Endpunkt+APIKey>" -Method POST -ContentType "application/json" -Body "{'Sensor':'$sensor','Location':'$location','Temp':'$temp'}"
   
       if ($request.response_type -eq 'error')
       {
           $request.error.extended_error_text;
       }
   }

2. Create a new Task within the Windows Task Scheduler and configure it to trigger at regular intervals. The action should be configured to run PowerShell with a few specific arguments (details below).
   
   

   C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

   -ExecutionPolicy Bypass -File "C:\Scripts\sql_request.ps1"

3. In case everything has been configured correctly you should now receive REST requests matching the DB requests done by the PowerShell script. These show up as Incoming Events within Enterprise Alert. Based on these events you can now start to build your Workflows and Policies.

Summary

In this blog, we just illustrated a very basic Use Case. It would also be possible to set up a DB trigger to send an email to Enterprise Alert, similar to our Teams and Slack blogs, as well as a remote action to request much more specific data. Please let us know if you have ideas on how to make this better or suggestion for other scenarios you might use this with.