NPM Rules - AND/OR logic

You have to keep in mind that the value you are entering is a string, not an integer.

If I were making the same attempt in SQL, I would be suing some sort of regex.

nicee,

didn't think of doing this type of alerting i just use Custom Properties for this but this actually makes sense if you relay on engineers to update custom properties. Here is an example of checking all nodes that are 'down' and are in an IP address range..

select *
  from nodes n 
  where 
  ((n.status = 2 )) AND
  (IP_address BETWEEN '10.94.255.165' AND '10.94.255.169' 
  OR IP_address BETWEEN '10.92.255.197' AND '10.92.255.200' 
  OR IP_address < '10.92.255.197' 
  OR IP_Address > '10.92.255.198')

perhaps this can be done in the web ui but the above should work for you

thanks

Dan

When I need to do this sort of thing I typically convert the IP Address to a binary value and do operations there.   I haven't tried using bitwise operators to mask the binary, but it should work.   This is what I use:

IP  String to Binary

ALTER FUNCTION [dbo].[fnIPv4toBin](@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END

Binary to IP String
ALTER FUNCTION [dbo].[fnBinToIPV4](@ip AS BINARY(4)) RETURNS VARCHAR(15)
AS
BEGIN
    DECLARE @str AS VARCHAR(15)

    SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) );

    RETURN @str
END;

Wasn't there a database field that was the numeric representation of the IP address?   I might be thinking of a different table or something.  Don't have access to look right now...  :-(

This should have a simple answer, but I tend to agree that a sql/swql solution might be best. Here is a real ugly potential method for using the web gui to do this.. I have not tested it, but it might work. I take no credit (or blame)  for how wrong or silly this is.

But here it is: you could create OR conditions using the STARTS WITH.... Each range need to be listed as a separate OR condition.

I don't see how that could work i_like_eggs since the IP Address field is a string in the Orion database.  I just copied your query and modified the IP's to match ranges in our database and it gives all kinds of crazy results.  SQL Server doesn't have a built-in knowledge on how to work with IP's (thus why they are formatted as string fields in the database and not any kind of numeric field or special field).  You need to use LIKE operators, IN Operators, or Equals operators and treat the addresses as strings, or use some of the other suggestions in this thread like converting the IP to binary.

Having said that and having used the Orion software for many years, I would stay away from writing custom query's like this at all costs.  I only write custom SWQL/SQL (and I always prefer using SWQL to help future-proof my queries) when there is literally no other way to do what I'm doing.  My number one preference is to use Custom Properties to label the Node's, Applications, or Interfaces and then just match on those.  That way if new objects should be included in these results in the future I just label the relevant custom property for those objects and they automatically show up in the results of all the queries that point to those.

SWQL/SQL is fun but it should absolutely be your last resort.  Trust me.  Take extra time now to properly label and organize your Orion environment to save you both time and energy going forward.

Look at it this way:  Spend 20 minutes now designing, creating, and implementing a new custom property or spend between 10 minutes to an hour trying to remember, locate, and edit all the custom queries you created each time you want to add objects in the future.  And then hope that you found all those places you created those custom queries (pro-tip: you'll almost definitely miss some) because it was 8 months ago that you last messed with it and you've pretty much completely forgotten what you did, why you did it, and where you did it...

It's like money, you spend time to save time.

Thank you all for your answers

@ nathatnwilson@bloominbrands.com yes I was assuming that by selecting IP_address that the field would be treated as if it were an integer. thank you for the clarification

@ xtraspecialj I am actually using these alerts to auto populate node custom properties based on address range which is why I cannot use any other custom property field because until this alert runs successfully the only custom property that exists for the node in the specified IP range is the device type category which I enter manually.

@ marcrobinson your suggestion is not an ugly solution, in fact it is what I am currently using. But for large address ranges it becomes a pain to scale up

I was trying to reduce the overall set up time for these alert configurations as I have to create one each time a new site comes online. But for now I think I will continue with my current configuration methods.

Thanks again

Ciaran

Ahh, cool Ciag, so it sounds like you are following some good practices.  I used to automate my custom properties using the alerts too, but the big problem I came across is that it generates a ton of noise in the alerts and events tables, making searching, querying, sorting, and filtering alerts (both active and historical) a real nightmare, not to mention inflating the size of the database dramatically. 

When the edit custom property feature was first introduced with the web alerting feature I was really stoked, but quickly realized that it's probably not the amazing solution I thought it would be.  Now I have a PowerShell script I use that I run as a scheduled task on one of our APE's that automates our properties.  This way all of the logging is controlled by my script (other than the audit log of course which still does track the changes) and my alert and event tables can be kept clean.  Whenever I need to automate a new custom property I now just make a copy of my PS Script, replace the SWQL query it uses with the proper query needed to return the data I'm looking for, make any other tweaks necessary to the script to make it do what I want, and schedule it as a task.  Depending on what I want automatically filled in, I can fully automate a new custom property in anywhere from 10 to 30 minutes. 

For instance, I just recently created a script that performs both a forward and reverse DNS lookup, compares the results to both itself and the DNS and IP Address info Orion has, populates the properties DNS_ReverseLookupResult and DNS_ForwardLookupResult as well as the DNS_ReverseLookupStatus and DNS_ForwardLookupStatus properties I'd created with the proper data, and logs everything to its own log file kept on the server.

I really hope that SolarWinds creates a separate feature for automating Custom Properties.  I don't think the alerting engine is the proper place for updating properties.  Something like the Dynamic Query feature for Groups, except with full and/or logic building features and of course updating properties instead of just putting objects into Groups.

My scripts are highly customized for our environment, so not really something that would be easy to share, but I'd be more than happy to help you put something together for your environment if you'd like to.