14 Replies Latest reply on Feb 7, 2018 9:13 AM by cahunt

    Confused about inconsistent SQL Filters

    xpowels

      I've recently been working on some dashboards, and some of the easy requests turn out to be much harder than I thought.

       

      For example, customer wants a list of all volumes listed by server. I tried the Top Volumes and while that gives the volumes, it doesn't sort things well. A server can be listed multiple times because the Resource wants to sort by the space used per volume. I use a filter like this:

       

      Nodes.Caption like 'ServerName%' and Volumes.Caption not Like '%C:\*%' and Volumes.Caption not Like '%memory%'

       

      Then I try the "All Volumes" Resource, figure this is perfect. Except I cannot get the SQL Filter to work. Many of the examples fail. Trying to filter on the Nodes.Caption seems to be the issue.

       

      So does anyone have an idea of what would work? And why is the syntax different?

        • Re: Confused about inconsistent SQL Filters
          jeilers

          Have you tried  custom table rather than using the built ins and then using a filter? I have had similar issues and found it easier to create my own query in some cases.

          1 of 1 people found this helpful
            • Re: Confused about inconsistent SQL Filters
              xpowels

              Thanks for steering me in a good direction. Table worked well, expect I'm going to need to figure out how to filter the data out as the standard method only let's me filter on Nodes or Volumes, not both. Off to learn SQL or SWQL!

                • Re: Confused about inconsistent SQL Filters
                  jeilers

                  SQL and SWQL are very helpful in a lot of different ways. I am still learning about both myself. If you ever get close but can't quite figure out how to get your queries exactly right don't be afraid to post more specifics for help. Side thought and I could be mistaken about this but the filters are a lot like your where statement just without being able to see the select or from in the query. Good luck and glad I could help steer you towards what seems like the right direction!

              • Re: Confused about inconsistent SQL Filters
                deverts

                Is it a requirement to be placed on the dashboard? There is a built-in report "All Disk Volumes Inventory Report" that already does this for you. You could even schedule it to be e-mailed to the person or team daily/monthly.

                 

                D

                • Re: Confused about inconsistent SQL Filters
                  David Smith

                  Something like this maybe?

                  If this looks like the sort of thing your after I can share the SQL.

                  1 of 1 people found this helpful
                    • Re: Confused about inconsistent SQL Filters
                      xpowels

                      Yes, that's great. More info than is needed, but I'm a lot better at removing it than getting anything to work. I'd be thrilled to see the code!

                        • Re: Confused about inconsistent SQL Filters
                          David Smith

                          Hi xpowels

                           

                          Try this:

                           

                          SELECT n.Caption AS 'Host Name','<a href="/Orion/View.aspx?View=NodeDetails&NetObject=V:' + CAST(v.VolumeID AS varchar(256)) + '" style="font-size:100%">' + v.caption + '</a>' AS 'Volume'

                          ,CONVERT(NVARCHAR(50), ROUND(v.VolumeSpaceUsed/1024/1024/1024,2)) + ' GB' AS 'Used'

                          ,CONVERT(NVARCHAR(50), ROUND(v.VolumeSpaceAvailable/1024/1024/1024,2)) + ' GB' AS 'Available'

                          ,CONVERT(NVARCHAR(50), ROUND(v.VolumeSize/1024/1024/1024,2)) + ' GB' AS 'Size'

                          ,CONVERT(NVARCHAR(50), cast(v.VolumePercentUsed as decimal (4,2))) + '%' as 'PercentUsed'

                          ,v.VolumeType AS 'Volume Type'

                          FROM [dbo].Volumes v

                          INNER JOIN [dbo].Nodes n on n.NodeID = v.NodeID

                          WHERE

                          (n.Vendor LIKE 'Windows') AND (v.VolumeType LIKE 'Fixed%') AND (v.Caption NOT LIKE 'C:%') --Change the filters here to your own

                          ORDER BY (n.Caption) asc, (v.Caption) asc

                           

                          Set the Volume column to allow HTML Tags or the Hyperlink won't work - If you don't want the hyperlinks then you can remove the CAST option in the Caption and just use v.Caption AS 'Volume' up to you. Then obviously you need to change the WHERE Statements to apply your own specific filters.

                           

                          Hope it helps Shout if you get stuck.

                          2 of 2 people found this helpful
                      • Re: Confused about inconsistent SQL Filters
                        cahunt

                        With so many filter options, you might have a easier time setting up some custom properties either on the nodes, and or volumes to help provide an easier filter/where clause that may not have so many wildcards.

                          • Re: Confused about inconsistent SQL Filters
                            xpowels

                            That would be another way to handle it, and probably should have. My original question was more about why the syntax is different when they are just supposed to be SQL filters, which no one can answer, and that's fine (really only a Solarwinder could). I often am just looking to see if others have run into the same difficulty, and I have learned there are several ways to get this done (and I think that's great), so folks here aren't dwelling on any issue, they work around it!

                             

                             

                            One reason I like the filtered SQL is that they are more dynamic. If someone creates a new server which would likely have a similar name, it will automatically be included in the dashboard, where with a customer property, it will only be included if someone enters the correct info when the node is added (or later).

                              • Re: Confused about inconsistent SQL Filters
                                cahunt

                                Maybe I need more coffee today, but I am not sure what you are noting as different syntax. Any extra detail or example you have might help....

                                 

                                With your query;

                                 

                                Nodes.Caption like 'ServerName%' and Volumes.Caption not Like '%C:\*%' and Volumes.Caption not Like '%memory%'

                                 

                                if you need more iterations with this you might consider other properties like volume type, and possibly build this as inclusive instead of exclusive with that property.

                                 

                                  For Example

                                Nodes.Caption like 'ServerName%' and Caption not Like '%C:\%' and (VolumeType = 'FixedDisk' OR VolumeType = 'Fixed Disk')

                                 

                                With SQL, when you use Wild Cards (%) then you need to use the LIKE or NOT LIKE, if your query is exact then use =, <>, etc.

                                Also if you are using a volume module, then you should be able to reference the volume caption with just caption, and the node caption as nodes.caption or nodesdata.caption. 

                                 

                                Also, use either the % or * for wildcard to make it easy to read.  SQL Wildcard is % (so use this if you want to copy and paste from the where clause in reports), and the Asterisk (*) is recognized due to the web/windows functionality... but only recognized in certain places on the web console.

                                 

                                 

                                  • Re: Confused about inconsistent SQL Filters
                                    xpowels

                                    Ah, I kinda figured this out. I did not READ the title of the filter options I was working with. If you use a "Resource like All Volumes" or "Volumes with High Percent Usage", you get a "Filter Nodes (SQL) option. However, if you use the "Top XX Volumes by Disk Space" Resource, the filter option is titled "Filter Volumes", so one says Nodes, the other says Volumes, and latter doesn't mention SQL.

                                     

                                    So, if you use a filter like Nodes.Caption like 'orion%' (or Nodes.Caption like 'orion*)', it will work only in the "Top XX Volumes by Disk Space" Resource, not the the first two mentioned. Actually, I can't get any Caption filter to work on Volumes with High Percent Usage. I copy and paste in the example(Caption Like 'AX3-*', and I get "Custom SQL filter is incorrectly formatted."

                                      • Re: Confused about inconsistent SQL Filters
                                        cahunt

                                        Where nodes.Property does not work try nodesdata.Property, I have been frustrated by the same thing before. In modules/places where the node property is the only property of that name (i.e. no same named volume property) you might be able to call that property just by name, without the nodes/nodesdata.property