This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Confused about inconsistent SQL Filters

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?

  • 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.

  • 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!

  • 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!

  • 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

  • Something like this maybe?

    pastedImage_1.png

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

  • 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!

  • Hi xpowels

    Try this:

    SELECT n.Caption AS 'Host Name','<a href="thwack.solarwinds.com/.../View.aspx + 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 emoticons_wink.png Shout if you get stuck.

  • Perfect. It took a little for me to figure out the syntax for changes, but I'm all set. Thanks much!

  • More than welcome xpowels emoticons_wink.png - If you could mark my response as helpful it sure would be appreciated gives me some nice THWACK Points - Need me some socks!

  • 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.