SQL filter on node category?

I'm trying to filter 'All Nodes - Tree' resource based on node category. For example, Filter only network nodes, filter server nodes, etc.

Is there a way to filter nodes by 'node category'? Either I can't find proper filtering for the node category or I can't get the syntax correct.

I tried using NodesData_Category and EffectiveCategory, but no luck

Find more posts tagged with

Accepted answers

xtraspecialj

Ahh!! That is a brand new feature of NPM 12. We have one of our Orion environments upgraded to the newest version but I've not worked in it much, so I wasn't aware of this new feature. I'll have to take a deeper look into this, I'd not heard about it. Still, what I said above holds true regarding custom properties. This is a nice built-in feature, but to truly organize your monitoring environment a good set of custom properties with rigid processes and procedures on how you and your team name them and fill them out is the best way. Also, if you have a CMDB containing all of the devices in your enterprise then I recommend making that the "master" source of inventory information and use scripts to pull that info into Orion to make sure things are labelled uniformly throughout your inventory, monitoring, and ticketing environments.

In the meantime, I'm seeing in the database that the NodesData table, which is where all of the, you guessed it, Nodes data is held, the Category field is just an ID number that references a separate table called NodeCategories that maps the ID number in the NodesData table to a category description, like Network or Server. It also looks like you can create custom categories somehow. Also, in the SQL View table simply called "Nodes" (the Nodes view contains a combination of data from several tables like NodesData, NodeSettings, NodeCustomProperties, and now NodeCategories), the columns NodeData_Category and EffectiveCategory are also both integer fields.

What all of this means is that you must know what the category ID's are to be able to know what values to put in your filter statement.

Here are the out-of-the-box ID mappings:

If you want to filter your All Nodes resource to just show servers, you would put NodesData_Category = 2. To filter it just to Network devices you would replace that 2 with a 1. How to use the CustomCategory or EffectiveCategory fields I have no clue, but this should at least get you started.

All comments

xtraspecialj

Not sure where you got the "NodesData_Category" and "EffectiveCategory" properties from, but there are no default properties like that for nodes in Orion. To be able to filter, sort, and just in general organize nodes, volumes, interfaces, and application monitors in Orion you need to use Custom Properties. Create a Node custom property and name it whatever makes sense to you. You could name it NodeCategory or DeviceCategory or whatever you want, make sure it is a "Text" type custom property (text will be the default), then, I recommend selecting the box called "Create a drop-down list of values for this property. Admins can add to this list" so that you have a uniform set of values for this property, otherwise other people (and maybe even yourself) may type in similar, but different, values later on when they mean the same thing. Even if you select the drop-down option for a custom property like this you can still easily add new values "on-the-fly" as long as you have admin rights in Orion, so it really is the best way to go.

Once you've created the property you can then easily filter your All Nodes resource (and many, many other items throughout Orion including the Manage Nodes table by using the "Group by" option, the Custom Property Editor, and nearly every other screen where there is a "Group by", filter, or limit option available).

I wouldn't stop here either. After DeviceCategory you could then create another property to filter by Type, and then another by Role, and then another by Owner or Responsible party. You can create properties for Location (State, City, Site, Building, Room, Rack) or by application (ApplicationGroup, ApplicationName, ApplicationOwner) and all kinds of stuff. With the ability to fill in custom property values using the Custom Property Editor (which is pretty darn good) or by exporting a blank spreadsheet of your newly created properties from the Manage Custom Properties screen, filling out the values in Excel, then import the spreadsheet with its new values back into Orion. The capabilities are pretty darn cool

asong

I got those properties from 'Show Filter Examples' -> 'Show List of Node Properties' below your highlighted section.

If you go 'Edit Node' in any individual node, there is 'Category' dropdown list where you can select Network, Server, Other or Auto-detected.

Since it already has category in nodes, I was wondering I could use that property to filter nodes.

xtraspecialj

What all of this means is that you must know what the category ID's are to be able to know what values to put in your filter statement.

Here are the out-of-the-box ID mappings:

asong

Sorry I forgot to mention the version number of NPM.

This is exactly what I wanted to do. I was able to filter nodes by nodes category. Thank you so much for detailed explanation!

I think creating custom properties will be more powerful in the long run and I will take your advice and start looking into adding custom properties, but for now, filtering by node category should do what I wanted to do.

I really appreciate your help!