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.

Advanced SQL alert help

So I have figured out most of what I need for an advanced SQL alert we have but I have two pieces I haven't figured out yet.

 

My alert needs to match specific volume names on our Linux environment.  The volume names may contain _ which in SQL is a wildcard.  How do I get SQL to match the _ and not use it as everything.

For example I have the following volumes

/a1_test  /a10test  /a11test and several others.  I need the query to match verbatim as

Volume Description Like '/a1_test' without matching the other variations.  I found something about escape characters but I'm not grasping the concept.

The other problem I have (not really a problem but I'm sure there is a simpler way is that in one rule I may specify 20 volumes.  Is there an easier way then listing 1 per line in my query?

VolumeDescription Like '/oracle/admin' or

VolumeDescription Like '/tsmtmp' or

VolumeDescription Like '/tsmalog     

ETC ETC...I have 20 separate lines is there an easier way to do this?

Also I am doing it this method and not the method listed here 

because we have standard volume mappings and set alerts however not all volumes exist on all servers.  This way if I add a new linux server and add all the volumes my rule set is instantly applied regardless if they have 5 or 30 of the 300 potential volumes names we have in our standards.

  • For the way to use the _, it would be [_]  (i.e. like '/a1[_]test'  (that is a [ _ ] with out the spaces)

    I don't how to do a group for 'like', but, why are you using like? Could you use VolumeDescription in ('/a1_test','/tsmtmp','/tsmalog'). I don't see any wildcards, so you don't need to use 'like'.

    Also, you could add a custom property on volumes call Alert1 (or something) and the query could be where (Alert1 <> 'Ignore' or Alert1 is null)  That way you could ignore some but get all the ones that don't have anything filled out.

  • Netlogix thanks as always for your help



    For the way to use the _, it would be [_]  (i.e. like '/a1[_]test'  (that is a [ _ ] with out the spaces)



    I found this as well since I posted which seems to work  'a1&_test' Escape '&'



    I don't how to do a group for 'like', but, why are you using like? Could you use VolumeDescription in ('/a1_test','/tsmtmp','/tsmalog'). I don't see any wildcards, so you don't need to use 'like'.



    In my example I only used a few but I have several that I do have a % in the name.



    Also, you could add a custom property on volumes call Alert1 (or something) and the query could be where (Alert1 <> 'Ignore' or Alert1 is null)  That way you could ignore some but get all the ones that don't have anything filled out.



     

    So here is the problem....first of all I am working with a set of rules that was established with the Openview implementation that existed prior to Solarwinds.  I have a listing of about 260 volume names, the alert thresholds, and the email lists the alerts go to.  One server may have 10 of these names another may have 50.  

    My thought process is that instead of adding a custom property each time I add a server and however many volumes I set I build my alerts based on the custom SQL.  It looks like I'm going to have about 20 alerts which while is a cumbersome task once it's done I don't need to revisit it again.  I've only been working here about 3 months and I have seen linux servers stood up and taken down rather quickly (VM) so I figure it would be more of an administrative nightmare to try to keep up with volume management.  I do have a catch all for any non-defined volume names in case anything new pops up.