6 Replies Latest reply on Jun 20, 2016 10:21 AM by john.trumbul

    Using Trigger Variables in Email Alert

    john.trumbul

      I have email alerts going out reporting volume sizes. I would like to include in the email the threshold it passed to trigger the alert.

       

      i.e: Threshold: Freespace <= foo% do variables like this exist? If so is is there a comprehensive list of them somewhere? I looked through what is out there and I cannot seem to find what I am looking for. I can pull in the values free space etc but not the actual percentage value for the trigger condition. all help appreciated.

        • Re: Using Trigger Variables in Email Alert
          mesverrum

          The variables used on the trigger conditions don't get put into the database in a way that would allow for your to reference them unfortunately.  What I generally do for cases like this is instead of using a basic condition like "volume space used over 90%" i will use a double value comparison set up like so:

          Then i just go set my thresholds on the volumes wherever i need them and pull that same volumewarningthreshold variable into the emails.  The nice thing about doing it this way is you can adjust the thresholds easily for those one off exceptions and such.

           

           

          -Marc Netterfield

              Loop1 Systems: SolarWinds Training and Professional Services

          2 of 2 people found this helpful
            • Re: Using Trigger Variables in Email Alert
              john.trumbul

              mesverrum Argh!! so since my threshold warning is variables based on the size of the volume, I would essentially need a custom property for every volume warning? I have 10 for SQL Servers alone, By the time i get to all the other volumes on non sql node types, I am going to end up with who knows how many custom properties. That kind of ties into the next question I was going to post. I want to create a custom property to my current "DB" node group. The group exists with 100 servers currently in it. I understand how to create a custom property and tie it into the "Nodes" parent however is there some easy way on the back end to just hit the DB after the property is created maybe through SSMS and just update the "IsDBNode" bool flag to yes without tagging each individual server in that group as one? If there is an elegant way to do this in the GUI, I am all ears as well. Thanks so much for your help on all of this.

                • Re: Using Trigger Variables in Email Alert
                  mesverrum

                  john.trumbul My mistake, someone in my team made that custom property and I mixed it up with the built in variable I was looking for, the alert condition should actually look like this, note that it is alerting on the volume capacity forecast, not on volumes.

                  As far as adjusting that threshold, they don't make it very easily visible so in my environment I customized my view to make it easier to find. If you go to the Volume Details view for any volume in the environment then customize the view and add the resource "Custom Properties for Volumes" to it.  Once you have that you can click Edit Custom Property Values and go to the screen where you can over ride the warning thresholds.

                   

                  From there you will see this

                   

                  So that's how to edit this on a one by one basis.  To do it in bulk in the GUI is a bit clunky but what I would do it go to Manage nodes, expand the arrow next to the servers that house the disks, check the disks you want to change and hit edit properties. 

                   

                   

                  Honestly I do this kind of work most often inside the database directly since it is pretty easy for me to whip up a query that changes it for the nodes I'm interested in.  I also built a "Custom Query" resource that shows me all the volumes with customized thresholds so I can keep an eye on them and tweak them.

                  In the first box paste this in:

                  Select n.caption as [Node], InstanceCaption as [Volume], WarningThreshold as [Space Warning], CriticalThreshold as [Space Critical], f.DetailsUrl as [_linkfor_Volume], (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning') AS [Global Warning], (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Error') AS [Global Critical],  'Edit' AS [Edit], '/Orion/Nodes/VolumeProperties.aspx?Volumes=' + ToString(InstanceID) AS [_LinkFor_Edit],'/Orion/images/nodemgmt_art/icons/icon_edit.gif' as [_IconFor_Edit] , n.DetailsUrl as [_linkfor_Node], '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]
                  
                  FROM Orion.ForecastCapacity f
                  join orion.nodes  n on n.nodeid=f.nodeid
                  
                  where metricname ='Forecast.Metric.PercentDiskUsed'
                  and (WarningThreshold not like (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning') or CriticalThreshold not like (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Error'))
                  

                   

                  and then check the box to Enable Search and put it in this way to be able to search by volume name or node name:

                   

                  Select n.caption as [Node], InstanceCaption as [Volume], WarningThreshold as [Space Warning], CriticalThreshold as [Space Critical], f.DetailsUrl as [_linkfor_Volume], (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning') AS [Global Warning], (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Error') AS [Global Critical],  'Edit' AS [Edit], '/Orion/Nodes/VolumeProperties.aspx?Volumes=' + ToString(InstanceID) AS [_LinkFor_Edit],'/Orion/images/nodemgmt_art/icons/icon_edit.gif' as [_IconFor_Edit] , n.DetailsUrl as [_linkfor_Node], '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]
                  
                  FROM Orion.ForecastCapacity f
                  join orion.nodes  n on n.nodeid=f.nodeid
                  
                  where metricname ='Forecast.Metric.PercentDiskUsed'
                  and (WarningThreshold not like (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning') or CriticalThreshold not like (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Error'))
                  
                  and (InstanceCaption like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%')
                  
                  

                   

                  As to your second question, you have made a SUPER common mistake for newer Orion users.  I never even begin to touch groups until I have built the custom properties because you can build groups to dynamically pull in nodes that have a custom property, but there is no built in way to reference group membership to populate your custom properties.

                   

                  This is going to be a SQL task, load up the database manager tool on your orion server and connect to the default db, adjust this script for your environment and run it to update a custom property based on being in a group, then I would go back to edit the group and remove all the static entries and use a dynamic query to populate the group with any node that has that custom property set to true.

                   

                  UPDATE Nodes
                  
                  SET Nodes.IsDBNode=1
                  
                  FROM Nodes n
                  JOIN ContainerMemberSnapshots cs ON cs.EntityType=n.EntityType AND n.NodeID=cs.EntityID
                  JOIN Containers c on c.ContainerID=cs.ContainerID
                  WHERE c.Name like '%YourGroupName%'
                  
                  

                   

                  Hopefully all that wasn't too much, once you get used to all the back door stuff you can really bend NPM to you will, even when the GUI doesn't present the options you want.

                   

                  -Marc Netterfield

                      Loop1 Systems: SolarWinds Training and Professional Services

                  1 of 1 people found this helpful
                    • Re: Using Trigger Variables in Email Alert
                      john.trumbul

                      mesverrum The information on the back door stuff is super helpful. As a 17+ yr DB Architect / Admin I would much rather write the queries myself. I didn't really make myself clear when I was talking about having to create a custom property for each threshold. I cannot hard set my percentages as you demonstrated up in your reply. The situation I had to build an alert for was monitoring free space on a volume (over 50,000 volumes just as a fun FYI) BUT the threshold is variable based on the volume size which varies by leaps and bounds. So for example some scenario would be: 

                      IF(volume.volumesize <= 100GB) AND (volume.VolumePercentAvailable <= 30) ALERT
                      IF(volume.volumesize > 100GB AND volume.volumesize <= 1e+11) AND (volume.VolumePercentAvailable <= 20) ALERT

                       

                      So I have this issue where creating a custom property for threshold would not work as I have 10 different sizes to monitor, it would seem using a custom property in this situation would mean creating a separate custom property for each threshold i want to monitor. Since I was only wanting to pull the information into the alert email it hardly seems worth it. I was hoping there was a way to pull the threshold values. Instead I just wrote out the threshold chart in the email. I am providing enough information on the volume as is.

                      One thing i would like to know about the built in variables is:  If I use trigger count in the email to identify problem volumes I am assuming since all of my evaluations are happening within the same Alert that value is going to be the total times the alert triggered overall. Is there a variable I can use that is a counter of sorts for a specific volume or can i create one? Ideally I would love to include a line in the alert email: "Volume: foo, has triggered a critical free space alert $someVariable times." Even better would be that line && "in the last $someVariable weeks" but alas I dream until i learn the back end real well and I can just write these things in the DB (super simple) I am going to be posting ALOT of questions. If you are curious how I setup my variable volume size alert here is what it looks like:

                       

                       


                      When i first submitted the alert you don't know how hard i prayed SW conditional logic supported scientific notation, lol. In query form, which i wish i could have just written to begin with.


                      if ANYONE can help with a good guide to the Orion database please send me a link, most of what I found is very bad and not pragmatic at all. Books, Blogs, ANYTHING. I documented the database on my own with my doc gen tool I just need an outline of the key tables for alerting...

                      Thank you very much on the  info to adding properties to existing groups, I am a total SW noob. All of my past places used Idera or Redgate so this is all new to me. Your help is greatly appreciated.

                        • Re: Using Trigger Variables in Email Alert
                          mesverrum

                          Trigger count will actually pull all the times that alert has fired for as long as the event history is being held, by default that is 60 days if I recall correctly.  There isn't a clean built in way to pull what you want through the GUI, but you have the option to use a custom SQL variable based on checking the alerthistory table and matching the current alert and object going back a specific number of days.  I also have a SWQL report that gives you some visibility into the same things you are asking for, number of times each alert has fired broken down by triggering object for the last 7 days.  You will notice that SWQL is mostly the same as SQL, but what documentation exists is mostly packaged alongside the SDK.  You can get the SDK and see some use examples here GitHub - solarwinds/OrionSDK: SDK for the SolarWinds Orion platform, including tools, documentation, and samples in Powe…

                           

                          Most the people I have known who really get it have learned it from just spending too much time inside SSMS guessing based on table names and hoping for the best.

                           

                          --report on alerts triggered
                          select ac.Name
                          ,ah.Message
                          ,count(ah.message) as [Alert Count]
                          ,EntityCaption as [Trigger Object]
                          ,EntityDetailsUrl as [_linkfor_Trigger Object]
                          ,RelatedNodeCaption as [Parent Node]
                          ,RelatedNodeDetailsUrl as [_linkfor_Parent Node]
                          ,tolocal(max(ah.TimeStamp)) as [Most Recent Trigger]
                          
                          FROM Orion.AlertHistory ah
                          left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
                          left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
                          left join Orion.Actions a on a.actionid=ah.actionid
                          
                          WHERE
                          daydiff(ah.timestamp,GETUTCDATE())<7
                          and eventtype=0
                          
                          group by name, message, [Trigger Object], [Parent Node]
                          order by [Alert Count] desc
                          

                           

                          It is also worth pointing out that the SWQL tables mostly match up with the real SQL views and tables but not always, part of the SDK is a tool called SWQL Studio that you will need to use to really get any work done in SWQL.  Solarwinds has indicated people should write things using SWQL where possible because that way it won't break when the devs move things around the back end between versions.

                           

                          -Marc Netterfield

                              Loop1 Systems: SolarWinds Training and Professional Services

                            • Re: Using Trigger Variables in Email Alert
                              john.trumbul

                              This indeed is my plan.. I have documented the database now, so its mostly digging around. Appreciate all the help though and guidance on the back end. I am sure I will have many more questions about the tables etc.. I have to make this bool property for existing groups (20 of them) so needless to say I will get pretty familiar with that portion! I think if it isn't proposed already, having the ability to add custom properties to existing groups would be a pretty helpful feature..