3 Replies Latest reply on Mar 30, 2019 12:56 PM by mesverrum

    SQL Query used in an Alert

    mcmillrl

      I created a query to take statistical data from a Solar Wind certain nodes and sum them together to get the total number of VM used on a system. I want to take that total and put it in an alert and alert if the count of users on the system "statistical number" get above a number. Talking to the SW rep Solar Wind does not store that SQL query output any where in the data base. First is this true? Second how can I take this output and feed it to an alert and trigger on it. The SQL Alert feature does not help it is useless. It limits you to the Component Name and Component ID you can only put in the where clause.  The SQL query I wrote gives me exactly what I need, I even use it on a display in reports/ custom table. Seems if it is there it should be in the database. The following script works in the query I want to trigger on the output 101 and test to see if it is >= to 108. if so then sent a email alert. I am good if I can get access to the output in the Solar Wind alert system.

       

       

      Message was edited by: Ro Mac clarifications in the original.

        • Re: SQL Query used in an Alert
          mesverrum

          So when you do the custom sql based alert you have to tie it back to some kind of object to have an alert on it, alerts don't exist in orion on their own, only in context with their relationship to an object.  Sometimes I have had to just come up with placeholder objects to tie my alerts to, for your query you could do something like this to tie it back to your polling engine as the "object" of the alert.

           

           

          SELECT Engines.ServerName, Engines.EngineID FROM Engines

           

          join (SELECT sum(isnull(componentstatisticdata,0)) as number, (select top 1 engineid from engines) as EngineID FROM [dbo].[APM_CurrentStatistics] a

          where applicationname ='service to count the mdse%'

          ) t2 on t2.engineid=engines.engineid

           

           

          where t2.number > 107

           

           

           

           

           

          Another approach that may be more straight forward since you have SAM is just to make this a component on a SAM template with a SQL user experience monitor, put your query in there and use that SUM as the statistic, set your threshold there in the component monitor.  Then just make sure you are alerting on any components that go critical.