6 Replies Latest reply on Dec 5, 2018 10:25 AM by fabian.s

    SQL for a group of nodes.

    trilobite rex

      for subplots for a bunch of servers.  On each pane of glass they was different site

       

       

      subplot = 'Prod Environment Cleveland' AND Volumes.Caption <>'cached memory' and Volumes.VolumeType='Fixed Disk' and VolumePercentUsed >= 90

      subplot = 'Prod Environment Parma' AND Volumes.Caption <>'cached memory' and Volumes.VolumeType='Fixed Disk' and VolumePercentUsed >= 90

      subplot = 'Test Environment - Canton' AND Volumes.Caption <>'cached memory' and Volumes.VolumeType='Fixed Disk' and VolumePercentUsed >= 95

      subplot = 'Test Environment UCRC' and Volumes.Caption <>'cached memory' and Volumes.VolumeType = 'Fixed Disk' and VolumePercentUsed >=40

       

       

      I cannot get anything to appear in the top one no matter what I change that last number to.  There are nodes in that "subplot" I copied it from the custom properties of one of the nodes in there.

       

      Why is it not displaying anything (it doesn't indicate an error either.

       

      Is there a better why to write the sql query?

        • Re: SQL for a group of nodes.
          mesverrum

          I've never seen the subplot command used in a solarwinds resource, are you sure it is supported within the filters?  Seems like it would be cleaner to just add  4 separate resources, one for each environment. 

            • Re: SQL for a group of nodes.
              trilobite rex

              Subplot is just a custom property.  I needed or a way to sort certain

              groups of servers.

               

              On Fri, Apr 20, 2018 at 8:20 PM, mesverrum

                • Re: SQL for a group of nodes.
                  mesverrum

                  ok that makes sense, for some reason I thought you were trying to use all 4 of those filters in the same resource.  So your problem is when you apply the top filter nothing matches, but you are expecting something.  So I'm not seeing anything obviously glitchy in the syntax, I'd just break the filters down until you understands why the volumes are expecting aren't show up.  just try

                   

                  subplot = 'Prod Environment Cleveland'

                  and make sure those are correct, then add in

                  and Volumes.Caption <>'cached memory'

                  Make sure the results still seem correct, then add in

                  and Volumes.VolumeType='Fixed Disk'

                  and make sure those are correct, then add in

                  and VolumePercentUsed >= 90

                   

                   

                  As you step through these one by one you should be ale to pinpoint where it is falling apart.  One thing that comes to mind is the volumetype, sometimes devices will be "fixed disk" and other show up as "fixeddisk" without the space. Otherwise I just expect something is wrong values in the subplot property.

                    • Re: SQL for a group of nodes.
                      trilobite rex

                      So I started down the path and just had items that were in that "subplot".  All items that appeared were memory related.  Beyond that I can get nothing to appear.

                        • Re: SQL for a group of nodes.
                          mesverrum

                          Sounds like you didn't discover any fixed disks on the nodes in that suplot, need to check that out and maybe redo your discovery.

                          • Re: SQL for a group of nodes.
                            fabian.s

                            https://thwack.solarwinds.com/people/trilobite%20rex  wrote:

                             

                            So I started down the path and just had items that were in that "subplot".  All items that appeared were memory related.  Beyond that I can get nothing to appear.

                             

                            I'm not sure how you have tried to find the root cause but below is an idea. Just let me know if you did that already.

                             

                            What do you get if you run the following SQL statement against the Orion database:

                            Change 'n.Applications' to your customproperty name, most likely n.subplot

                             

                            SELECT  v.Caption as volume_name

                            ,n.Caption as node_name

                            ,v.VolumeType

                            ,v.VolumePercentUsed

                            ,n.Applications -- CUSTOMERPROPERTIES NAME. Please adjust

                            from Volumes v

                            JOIN Nodes n ON n.nodeid = v.NodeID

                            WHERE n.Applications ='Prod Environment Cleveland' -- CUSTOMERPROPERTIES NAME. Please adjust

                            order by n.Caption, v.Caption

                             

                             

                            You can then compare the result of the SQL statement above with the filer ' subplot = 'Prod Environment Cleveland' AND Volumes.Caption <>'cached memory' and Volumes.VolumeType='Fixed Disk' and VolumePercentUsed >= 90 ' .

                             

                            Of course you can also expand the SQL exactly with your filter but I personally like always to start simple.

                             

                            regards