We will use a PowerShell script run by the SQL Server Agent 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 new Rest Endpoint in Enterprise Alert as described in our Integration article (https://www.derdack.com/portfolio_item/webhook-rest-api-mobile-app-text-push-voice-scheduling/)
2. Open the SQL Studio and connect to your Database go to the SQL Server Agent and start if not already started.
   
3. Create a new Job by right-clicking on Jobs under SQL Server Agent
   
4. Name the new Job appropriately like “SQL Connector” and provide a description
   
5. Create a new step within the Job, give it a name, select PowerShell as the Type and paste in the PowerShell Script.
   • Copy the below PowerShell Script

     $sql = "select sensor, location, temp from Datatable_furnace";
     $dbConn = New-Object Data.SqlClient.SqlConnection;
     $dbConn.ConnectionString = "Data Source=mydbserver;User ID=someuserid;Password=mypassword;Initial Catalog=mydatabase";
     $dbConn.Open();
     $QueryCmd = $dbConn();
     $QueryCmd.CommandText = $sql;
     $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
     $QueryCmd.Connection = $dbConn;
     $SqlAdapter.SelectCommand = $QueryCmd;
     $DataSet = New-Object System.Data.DataSet;
     $SqlAdapter.Fill($DataSet)
     $dbConn.Close();
     $dbConn.Dispose();
     
     $MyResults = $DataSet.Tables[0];
     $MyResults | foreach-object {
     $param.sensor = $_.sensor;
     $param.location = $_.location;
     $param.temp = $_.temp;
     
     $request = Invoke-RestMethod "" -Method POST -ContentType "application/json" -Body "{'Sensor':'$Param.sensor','Location':'$Param.location','Temp':'$Param.temp'}"
     
     if ($request.response_type -eq 'error')
     {
     $request.error.extended_error_text;
     }
     }

   • Insert the script code.
     
   • Make sure to replace the place holders in the Connection String and in the URL with actual values.
   • Optional: Adapt the SQL query to match your needs
6. Go to Schedules and create a new schedule the setup below will trigger daily at 5 pm
   
7. In Enterprise Alert create new policies applying to the data being forwarded.

Notifications within the Enterprise Alert mobile app

Enterprise Alert comes complete with a mobile app allowing for all critical alert details to be seen from anywhere.  Users can acknowledge and take ownership of alerts and with remote actions, even resolve them.  Team members collaborate with their peers so that even if an on-call person is not the SME, that person is easily within reach via voice call, text or email.

A centralized Alert Center can be viewed from the web portal.  All alerts, whether active, overdue or closed are visible to EA admins.  Ownership and even delegation is as easy as drag-and-dropping the alert to the person/team.

Scheduling

Maintenance Windows

Anywhere Remediation