How would you create a data-center-only NPM resource to show ports with errors on various Machine Types, without using custom properties?

Are you as averse to using groups here as you are to using custom properties here?

"Averse"?  No.  Interested?  Maybe!

Please say on!  I'm imaging just creating a group with all the devices in the data center, and then using some SQL filtering to display port errors on only that group.  Is that how you'd do it?

Use dynamically defined groups creatively.  Nesting where appropriate.  Leverage the group to define what should appear on the page to do most of your filtering.  Should make it easier to display interfaces from what remains.

Do keep in mind group status rollup functionality, which is where custom properties and groups differ greatly.  It's functionality that groups have (and can be set best case/ worst case/ mixed).  You probably won't need it for anything you are doing now, but be aware of how group status is affected by these settings (and from the groups that may be contained in the group).  It's more of a reason to use groups over custom properties (well, besides nesting).

Group=

Various models of Cisco ASA firewalls

Cisco Nexus 9000 ACI devices

Cisco Nexus 7009's Cisco Nexus 5548's

Cisco 3850's

None of this stuff:(whatever you know you don't want--loopback, VoIP, Null interfaces, )

Selection method to use for report objects: Specific Objects, Dynamic Query Builder or SQL SWQL - SolarWinds Worldwide, …

Selection method to use for report objects: Specific Objects, Dynamic Query Builder or SQL SWQL

Overview

This article provides brief information on methods to use for report objects.

Specific objects

Dynamic Query Builer

SQL/SWQL

Environment

All Orion Platform products 2013.1.1 and later

Steps

If you want to create a chart for a new, custom group of objects, select Create new object selection, and then select the objects you want to chart using one of the following Selection methods:

Select Specific Objects (static selection) if you know precisely which objects you want to chart.     Note: This is the most straightforward selection method, and it is preferred both for newer users and for relatively permanent network objects.    

Select Dynamic Query Builder to select objects based on object properties.     Note: This is the preferred selection method for groups of objects of a specified type that may change over time. "All Cisco nodes in Austin" is an example of a group best defined using the Dynamic Query Builder.   

Select Advanced DataBase Query (SQL, SWQL) only if you are comfortable querying your SolarWinds database directly, using SQL or SWQL.

This was easy.  Thanks.

Now that I have a group of nodes I'm interested, what method would you use to display errors or discards on the interfaces within those nodes? Preferably scaling with the highest count of port errors or discards at the top.

I'm imagining adding an Available Widget like " . . . Errors & Discards Today", but I'm not clear on how (or whether it's possible) to use the newly-created group as part of a filter.  Is this possible?

Is there an option in SQL to say:  'Group = Data Center'  ?   Assuming I named the Group "Data Center."     I only see a "GroupStatus" option in the SQL example for filtering this Widget.

Am I going down the wrong rabbit hole?

Create a view and limit the view via your group.  And then add widgets like you mentioned.

If you want to use a SWQL query against your group, here is an example for inspiration: https://thwack.solarwinds.com/thread/71929

I would treat SWQL and SQL queries as separate efforts from this view as I don't think those queries 'care' (think of them as honey badgers) about the other criteria defined.  They stand on their own.  Up to you which is better for you.

I think you will see that SQL and SWQL queries ignore view filters on the page.  Which actually comes in handy sometimes.  Just not for this use case. 

Rick-

I think I get what you are doing, and I like it. But, let's make sure I understanding this correctly. You want a tabular result listing node, interface, and a number representing Errors and Discards for each interface, and list it only for interfaces with a number in Errors and Discards.

I have created this little table using SQL, but I'm sure you can do this with Dynamic Query Builder -- I still haven't learned the DQB since I like SQL -- LOL! Anyway, based upon your suggestions, I created this table. I created a column (EDOrder) and added the 4 columns Errors and Discards In and Out. I later used that to organized in descending order -- that way the top Quantities are at the top. You could add 'TOP 10' right after the word SELECT to limit to the top 10 results. Of course, You don't need to show all columns in your report. Heck, you could hide the 4 Error/Discards/In/Out columns and just show the ED column as a relative indicator of interfaces with problems.  Pop this into an Advanced Database Query for SQL, and let me know if you like it.

A few notes on Caption vs SystemName. System name may have DNS name. System name should also update if you change automatically the device name, whereas Caption doesn't seem to do so automatically. Description is polled via SNMP, and may be a description of the OS running on the device. I don't like seeing DNS names, so I generally use Caption.

SELECT n.NodeID
  , n.Caption
  , n.IP_Address
  , i.InterfaceName
  , i.InDiscardsToday
  , i.InErrorsToday
  , i.OutDiscardsToday
  , i.OutErrorsToday
  , n.MachineType
  , EDOrder = (i.InDiscardsToday+i.InErrorsToday+i.OutDiscardsToday+ i.OutErrorsToday)
FROM Nodes n,  Interfaces i
WHERE n.NodeID = i.NodeID 
AND
  ( i.InDiscardsToday > 0 
    OR i.InErrorsToday > 0
    OR i.OutDiscardsToday > 0
    OR i.OutErrorsToday > 0 ) 
AND
  ( n.MachineType LIKE '%55%' 
    OR n.MachineType LIKE '%n9000%'
    OR n.MachineType LIKE '%700%' 
    OR n.MachineType LIKE '%5548%' 
    OR n.MachineType LIKE '%38%')
AND
    n.Vendor LIKE '%Cisco%'
ORDER BY EDOrder DESC

On the note of the n.MachineType fields %5548% is included in %55% -- so consider deleting the %5548% line. Or consider revising to %55[01249][04689]% -- which should match any Nexus 55xx model currently. Also, %38% may yield 3845 or 3825 if you still have any of those routers in Orion. Perhaps consider using %38[x5]% -- which should match 38xx for stacks and 3850 for individual switches, but not 3845 or 3825. Maybe use %70[01][04689]% for the n7000 series. I also added another AND in the WHERE section for n.Vendor LIKE '%Cisco%' to eliminate HP7000 chassis from making their way into the results.

Regards,

Eric

I added the above query, with the changed LIKE statements, and I added a WHERE statement AND (i.InDiscardsToday+i.InErrorsToday+i.OutDiscardsToday+ i.OutErrorsToday)>9 because I didn't want a few 1 or 2 errors/discards making the list.

I added this to our summary page by adding a Custom Table, and then pasting the SQL query into the Select Data Source field. A little massaging of the columns and it looks great. Thanks for asking your question. I learned a bit, and improved our Network Summary Page.

So which of the ways discussed most closely meets your requirements with respect to one-offs/ being cookie-cutter/ intuitive?  Your goal:

//////////////////////////////////////////////////////////////////////////////////////////////

Is there a better way to build the SQL filter--preferably without using Custom Properties? I've done Custom Properties with good results in the past.  But now I'm trying to get away from one-offs like Custom Properties, to make management more cookie-cutter and intuitive.  Custom Properties is a useful tool, but is . . . uh . . .  too . . .  "customized" to fit the goal of reducing one-offs that require more management time and more thought.

///////////////////////////////////////////////////////////////////////////////////////////////

I think if you experiment with groups you will appreciate how extensible they can be and how they can reduce complexity of SWQL and SQL queries.  I have found that to be the case.  I am a SWQL and SQL script kiddie, and usually make minor tweaks to queries I find on thwack.  But I always wonder if they are too complex for co-workers or whoever inherits the environment after me.  So I have been using groups more and have been combining them with Orion native functionality.