7 Replies Latest reply on Mar 31, 2016 9:52 AM by Jan Pelousek

    Going NUTS trying to Learn DB Field Content

    mistervapor

      I completely understand the desire to NOT publicly share the full ERD of Solarwinds but I'm going NUTS trying to write SQL strings using values I see in the GUIs.  For example, when you escalate an alert, there is a value to wait X number of minutes, hours, etc.  When that escalation occurs, I would like to send an email that includes the "wait" value so I can say something similar to "This alert has been in an extend alert state (Greater than X minutes).  Please escalate to have investigated immediately."  But for the life of me, I cannot find where that value is stored as I go scrolling through the DB Manager.

       

      Can someone point me in the right direction to see some sort of list that shows what values in the GUI correspond to which table.field in the database?  I would be eternally grateful.  If the Solarwinds team is worried about the proprietary information, perhaps they can create a Solarwinds-hosted, web-based tool that allows a user to enter a field from the Solarwinds GUI and have the tool return the field and table where the value is stored.

       

      Please help.

       

      Thanks,

      Mike - Noob

        • Re: Going NUTS trying to Learn DB Field Content
          designerfx

          Have you looked at the SDK forum? There is a solarwinds API that has a lot of SQL calls and a corresponding schema. See: Orion SDK   ?  You can actually make direct SQL calls to this REST API via powershell/python/whatever the heck you want as long as you can authenticate with it, and they'll even give you everything in JSON.

           

          Keep in mind that most tables in the DB are named according to their original tool name, so you'll see SEUM if you're looking for tablespace for WPM.

          • Re: Going NUTS trying to Learn DB Field Content
            blsanner

            There is not a built in variable for that field.  However, you could use the following as a variable in your alert message to pull that data from the database:

             

            ${SQL:SELECT TriggerSustained FROM AlertDefinitions WHERE AlertName = '${N=Alerting;M=AlertName}'}

             

            Note that this field in the database is in seconds.

             

            Brian Sanner

            Loop1 Systems

            • Re: Going NUTS trying to Learn DB Field Content
              Jan Pelousek

              Escalation level of action on alert is stored in the 'ActionsProperties' DB table.

              This is related to the AlertConfigurations (definitions for alerting service v2) table via ActionsAssignments and Actions tables.

              Following query shows escalation level (in minutes) for all actions on all alerts.

              It should give you a hint how to get value you need.

              SELECT        a.AlertID, a.Name, ac.ActionTypeID,ap.PropertyValue AS EscalationLevel
              FROM            AlertConfigurations AS a LEFT OUTER JOIN
                                       ActionsAssignments AS acas ON acas.ParentID = a.AlertID LEFT OUTER JOIN
                                       Actions AS ac ON ac.ActionID = acas.ActionID LEFT OUTER JOIN
                                       ActionsProperties AS ap ON ap.ActionID = acas.ActionID
              WHERE        (ap.PropertyName = 'EscalationLevel')
              

               

              Regards,

              Honza