Derdack

Targeted Alert Notifications – Anywhere Actions

Derdack
  • Use Cases
    • Overview
    • Enterprise IT Alerting
    • IT Managed Services
    • Mobile Alerting in Manufacuring
    • Critical Operations Alerting in Banking & Financial
    • Field Service Dispatching in Energy & Utilities
    • Use Cases in other Industries
  • Products
    • Overview
    • Enterprise Alert®
      • Overview
      • Alert Notifications
      • On-call Duty Scheduling
      • Collaboration
      • Anywhere Remediation
      • Incident Mgmt. App
      • Integrations
      • Technical Support
      • Online Knowledge Base
      • Derdack FAQ
    • SIGNL4® Cloud
    • References & More
  • How to Buy
    • Overview
    • Pricing and Quotes
    • Find a local Partner
    • Azure Marketplace
  • About Derdack
    • About
    • Careers
    • Partner Program
    • Strategic Partners
    • Derdack Podcast
    • Contact Derdack
  • News & Blog
  • Request Demo
    • de de
  • News & Blog

    • Home
    • News & Blog
    • Energy & Utilities
    • Alerts out of your database (SQL, Powershell, REST API)

    Alerts out of your database (SQL, Powershell, REST API)

    • October 7, 2020
    • Energy & UtilitiesManufacturingTechnical
    Alerts out of your database (SQL, Powershell, REST API)

    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.

    Tagged

    DatabasePowerShellREST API

    Share

    Related Posts

    Enterprise Alert 9.4.1 comes with fixes and the revised version of the sentinel connector app

    February 1, 2023

    Critical System Alerts via SIGNL4

    December 29, 2022

    Enterprise Alert 9.4 Update introduces Remote Actions for hybrid scenarios and proxy support for MS Teams

    October 25, 2022

    Upgrade your shopfloor alerting with Derdack

    September 8, 2022

    About

    DERDACK products combine automated alert notification workflows, 24/7 duty scheduling, ad-hoc collaboration and anywhere IT troubleshooting – reducing unexpected IT downtimes at large enterprises and organizations by 60%.

    Most popular

    • Derdack Company Take your ITIL incident management to the next level with Enterprise Alert
    • Mobile alert notifications for HP Service Manager (HPSM)
    • How to forward alerts to Microsoft Teams
    • Oncall Scheduling On-Call Schedule Management with Auto-Rotation
    • checking-mobile Enhancing SCOM alert notifications
    • Announcing Enterprise Alert 2019
    • Even, Alert, Incident, Notification Definition of Event, Alert, Incident and Notification
    • who-is-on-call-sharepoint Add a live “Who is On-Call” Dashboard into Sharepoint and other Tools

    Categories

    • Business (37)
    • Cloud Services (5)
    • Consultancy (1)
    • Customers (18)
    • Energy & Utilities (7)
    • Events (23)
    • Financial & Banking (4)
    • IT Ops (19)
    • Manufacturing (8)
    • News (48)
    • Schools (1)
    • Software (9)
    • Staffing (1)
    • Support (4)
    • Technical (141)
    • Transport & Logistics (5)

    Tags

    alert alert notifications Anywhere Resolution Anywhere Response Azure azure BMC customer reference Database derdack enterprise alert Enterprise Alert Enterprise Alert 2016 Enterprise Alert 2019 Gartner HPE HPE ITSM incident Incident Management Incident resolution incident response Industrie 4.0 Integration IT Alerting IT Operations Maintenance microsoft mobile Mobile App monitoring OMS on-call on-call schedule Operational Alerting rapid response release Remote Action REST API SCOM security SolarWinds NPM System Center update User Group voice

    Follow us

    • Twitter
    • Facebook
    • LinkedIn
    • XING
    • YouTube
    • Vimeo
    • Home
    • News & Blog
    • Energy & Utilities
    • Alerts out of your database (SQL, Powershell, REST API)

    CONTACT US:
    Intl: +49 331 29878-0

    US: +1 (202) 470-0885
    UK: +44 (20) 88167095
    CH: +41 (31) 5391990

    CONTACT VIA EMAIL:
    info@derdack.com

    OFFICES:
    US & Europe

    NEWSLETTER:
    Sign up here

    CAREER:
    Latest job offers

    EVENTS

    • No Upcoming Events
    • Who we help
    • Products
    • How to Buy
    • About Derdack
    • News & Blog
    • Free Trial
    • Twitter
    • LinkedIn
    • XING
    • YouTube
    • Vimeo

     © 2022 Derdack – Imprint, Privacy policy

    • Use Cases
      • Overview
      • Enterprise IT Alerting
      • IT Managed Services
      • Mobile Alerting in Manufacuring
      • Critical Operations Alerting in Banking & Financial
      • Field Service Dispatching in Energy & Utilities
      • Use Cases in other Industries
    • Products
      • Overview
      • Enterprise Alert®
        • Overview
        • Alert Notifications
        • On-call Duty Scheduling
        • Collaboration
        • Anywhere Remediation
        • Incident Mgmt. App
        • Integrations
        • Technical Support
        • Online Knowledge Base
        • Derdack FAQ
      • SIGNL4® Cloud
      • References & More
    • How to Buy
      • Overview
      • Pricing and Quotes
      • Find a local Partner
      • Azure Marketplace
    • About Derdack
      • About
      • Careers
      • Partner Program
      • Strategic Partners
      • Derdack Podcast
      • Contact Derdack
    • News & Blog
    • Request Demo
    Manage Cookie Consent
    We use cookies to optimize our website and our service.
    Functional Always active
    The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
    Preferences
    The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
    Statistics
    The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
    Marketing
    The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
    Manage options Manage services Manage vendors Read more about these purposes
    View preferences
    {title} {title} {title}