5 Replies Latest reply on Jan 7, 2016 8:47 AM by jm_sysadmin

    Help with SWQL Alert

    patrickhorne

      Hi,

       

      I wanted to use the existing "Out of the Box" 'Alert me when memory has less then 60 Days of Capacity" but I want to only alert on a subset of my servers.  I ideally want to use DeviceType = "Domain Controllers"  so I added in Where node.customproperties.devicetype = "Domain Controllers" but when I try to validate it the queries fails.

       

      Please can anyone shed some light for me?

       

      Regards

       

      Patrick

        • Re: Help with SWQL Alert
          jm_sysadmin

          I can't test it right now since I don't have that custom property, but I think you left the S off nodes, and I changed the " to ' around Domain Controllers

           

          LEFT JOIN Orion.Nodes nodes on nodes.NodeID = NodesForecastCapacity.NodeID

            LEFT JOIN Orion.Settings s ON s.SettingID = 'ForecastMinDays'

          WHERE

            NodesForecastCapacity.DaysToCapacityAvg <= 60

            AND NodesForecastCapacity.MetricName = 'Forecast.Metric.PercentMemoryUsed'

            AND DayDiff(NodesForecastCapacity.MinDateTime, NodesForecastCapacity.MaxDateTime) >= ISNULL(s.CurrentValue, 7)

            AND  nodes.Unmanaged = false

            AND  nodes.customproperties.devicetype = 'Domain Controllers'

            • Re: Help with SWQL Alert
              patrickhorne

              Hey thanks for replying but no that doesn't validate either.  Good spot on the syntax errors " instaed of ' but I actually had it right in my query just type it wrong above :/

                • Re: Help with SWQL Alert
                  jm_sysadmin

                  I swapped out your property for one I had, and using like worked. So I have a property called Organization with a value of NCH IS and the alert filtered correctly.

                   

                    AND  nodes.CustomProperties.Organization  like 'NCH IS'

                   

                   

                  There fore I think your line should be

                    AND  nodes.customproperties.devicetype like  'Domain Controllers'

                    • Re: Help with SWQL Alert
                      patrickhorne

                      Ah ok.. as I test I copied and pasted your first example in its entirety and its worked (thats including the  AND  nodes.customproperties.devicetype = 'Domain Controllers')

                       

                      I looked at this is where mine and your differed, right at the top mine says:-

                       

                      LEFT JOIN Orion.Settings s ON s.SettingID = 'ForecastMinDays'

                       

                      But yours has an extra line:-

                       

                      LEFT JOIN Orion.Nodes nodes on nodes.NodeID = NodesForecastCapacity.NodeID

                        LEFT JOIN Orion.Settings s ON s.SettingID = 'ForecastMinDays'

                       

                      Its the top line that makes it work but Im too stupid to see why

                       

                      Thanks for your help though!