7 Replies Latest reply on Oct 24, 2016 2:53 PM by njoylif

    Question about using SWQL/SWIS or SQL in Alert Messages

    rupdegraff

      In order to provide more information in the Alert Message for a Volume breaching a threshold I've added a SQL query that uses a Join to pull the Name of the VServer (NetApp) hosting the Volume (bolded below.) This works great when the Volume in question is indeed being hosted under a NetApp Cluster VServer. If the Volume is on a NetApp 7-mode I see the SQL query text after the "VServer: " label. I would like to use some logic that will only list the VServer when applicable.

       

      I know it is preferred by Solarwinds that we use SWQL/SWIS because any queries or logic used there should survive product updates. So my questions;

       

      1. How could the query below be re-written in SWQL/SWIS?
      2. Can I use the "IsNull" or "Case" function to only display the VServer if it is applicable?

       

      Thanks!

       

      ## Alert Message Excerpt Below ##

       

      Timestamp: ${N=Alerting;M=AlertTriggerTime;F=DateTime}.

       

       

      Array: ${SQL: SELECT a.Name

      FROM dbo.[SRM_StorageArrays] a

      WHERE a.StorageArrayID = ${N=SwisEntity;M=StorageArrayID;F=OriginalValue} }

       

      VServer: ${SQL: SELECT svm.Name

      FROM dbo.[SRM_VServers] svm

      INNER JOIN dbo.[SRM_VServerToVolumesMapping] m

      ON svm.VServerID = m.VServerID

      WHERE m.VolumeID = ${N=SwisEntity;M=VolumeID}}

       

       

      Volume ${N=SwisEntity;M=Name} has only ${N=SwisEntity;M=CapacityFreePercentage} (${N=SwisEntity;M=CapacityFree}) free

        • Re: Question about using SWQL/SWIS or SQL in Alert Messages
          njoylif

          ${SQL: SELECT

          --     ISNULL(svm.Name,'') as Name

          --     CASE WHEN svm.Name IS NOT NULL and svm.Name <> '' then svm.name else '' END as Name

          FROM dbo.[SRM_VServers] svm

          INNER JOIN dbo.[SRM_VServerToVolumesMapping] m

          ON svm.VServerID = m.VServerID

          WHERE m.VolumeID = ${N=SwisEntity;M=VolumeID}}

           

          uncomment and test one at a time.  I think ISNULL will work (not in email fields though).

            • Re: Question about using SWQL/SWIS or SQL in Alert Messages
              rupdegraff

              Ok, forgive me, issues grasping the syntax as well as specifying that I'm using true SQL or SWQL is my problem. The options you gave me above may be correct, but I'm not sure what to include and what not.

              • I've tried using exactly what you provided from the $ to the last } but it doesn't work
              • I also tried using either just the ISNUL or CASE without the --

              Neither of these work.

               

              Sorry to be so simplistic, but the syntax of formatting these things in the confines of SWIS/SWQL is where I'm struggling. I'm using this logic in the Message of an Alert. Can you reformat to exactly what will work without the options? If I can get a good working example of using logic I can reverse engineer and experiment from there.

               

              Thanks for your help.

                • Re: Question about using SWQL/SWIS or SQL in Alert Messages
                  Lubomir Krausko

                  Hi rupdegraff,

                  maybe this is what you are looking for:

                   

                  --------------------------------------------------------------------------------------------------------------------------------------------------------------------

                  ## Alert Message Excerpt Below ##

                   

                  Timestamp: ${N=Alerting;M=AlertTriggerTime;F=DateTime}.

                   

                  Array: ${SQL: SELECT a.Name

                  FROM dbo.[SRM_StorageArrays] a

                  WHERE a.StorageArrayID = ${N=SwisEntity;M=StorageArrayID;F=OriginalValue} }

                   

                  ${N=SWQL;M=SELECT CASE WHEN ISNULL(Volumes.VServers.DisplayName, '') = '' THEN '' ELSE ('VServer: ' + Volumes.VServers.DisplayName) END as Name

                  FROM Orion.SRM.Volumes

                  WHERE VolumeID = ${N=SwisEntity;M=VolumeID} }

                   

                  Volume ${N=SwisEntity;M=Name} has only ${N=SwisEntity;M=CapacityFreePercentage} (${N=SwisEntity;M=CapacityFree}) free

                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                   

                  Regards

                  Lubo

                  1 of 1 people found this helpful
                  • Re: Question about using SWQL/SWIS or SQL in Alert Messages
                    njoylif

                    Let us know if LubomirKrausko's code worked.

                     

                    what I suggested is SQL.  if you replace the variable part ${} with a valid volumeID (select * from SRM_VServerToVolumesMapping), then you can test in management studio.

                    swap out 1205 with a valid volume ID to test.  This is what SQL would look like using the case statement for volume 1205.

                     

                    SELECT

                         CASE WHEN svm.Name IS NOT NULL and svm.Name <> '' then svm.name else '' END as Name

                    FROM dbo.[SRM_VServers] svm

                    INNER JOIN dbo.[SRM_VServerToVolumesMapping] m

                          ON svm.VServerID = m.VServerID

                    WHERE m.VolumeID =1205

                  • Re: Question about using SWQL/SWIS or SQL in Alert Messages
                    rupdegraff

                    The responses below have helped a lot and in fact allowed me to add the additional detail to the Alert messages. I also used some of my newfound knowledge in the criteria for alerts and a report. 

                     

                    I am still curious about the "N=", "M=" and "F=" that are necessary in the syntax. I assume they are not just arbitrary letters and gleaning from the examples, N= specifies the provided syntax as either SQL, SWQL or an SwisEntity.

                     

                    Let me ask a question about the SwisEntity. In the example ${N=SwisEntity;M=StorageArrayID;F=OriginalValue} for the Alert that is based on a Volume exceeding a threshold, does the syntax effectively allow pulling of any field of the object the alert is based on?

                     

                    I've read that there are some build in relationships between tables. Are there and where do I find a clear list? Would this allow me to pull the VServer Name or Array Name for a Volume that generated an alert without this need for SQL or SWQL JOINs?

                     

                    I did find my way to the OrionSDK on GitHub @ Release v2.0.50 · solarwinds/OrionSDK · GitHub. Maybe I'm challenged, but I'm still not able to answer some of my questions that I posed above.

                     

                    Where are these things documented?

                     

                    Thanks!