How can I match spaces in interface description when creating an alert rule please?
SELECT * FROM devices,ports WHERE (devices.device_id = ? AND devices.device_id = ports.device_id) AND ports.port_descr_descr = " BL "
Also,
How can I make my match criteria case sensitive please?
I also tried REGEX but there’s nothing in documentation around what “style” of REGEX is supported by LibreNMS and there aren’t any examples of REGEX option use cases either in the documentation so it’s just shooting in the dark.
What you are doing there are pure mysql queries, but regex is also there afaik if you just select something else than equal. In general I think mysql is case sensitive so if you click on the advanced you can see which query it has built.
The top of my post shows the generated SQL query - when I clicked on advanced.
And it can’t match the space before and after the characters BL.
Regarding the case sensitivity,
As a matter of fact my testing suggests that the sql queries are not case sensitive cause a query like this:
SELECT * FROM devices,ports WHERE (devices.device_id = ? AND devices.device_id = ports.device_id) AND ports.port_descr_descr = “blahblah”
Will I fact match a an interface description of BlahBlah (i.e. blahblah matches BlahBlah), hence my question on how to make queries case sensitive.
But most importantly I’d like to find out how to match an empty space in interface description.
Do I need to use REGEX option for that or any special break character to indicate a blank space, or asci code, etc… in the match criteria?
Any pointers to how I can accomplish matching empty space in interface descriptions are much appreciated.
To make varchar field case sensitive in your query you would first have to cast it to binary. I see that type varchar would also ignore trailing space so likely it’s not saved into the database and hence you can’t query with it.
Just a last minute update, I just changed from “ports.port_descr_descr” to “ports.ifAlias” -and using “ports.ifAlias” I can indeed match for strings containing spaces (haven’t tested for trailing space match though)
Might be helpful if there was a description for each of the options somewhere in the documentation:
I.E. to document what are all these about:
ports.port_descr_type CORE:???
ports.port_descr_descr -god knows what???
ports.port_descr_circuit -matches stuff in {} brackets -works (not case sensitive)
ports.port_descr_speed -matches stuff in [] brackets -works (not case sensitive)
ports.port_descr_notes -matches stuff in () brackets -works (not case sensitive)
ports.ifDescr – this is interface description -one can match spaces e.g. (blah blah blah) (not case sensitive)
ports.ifName
ports.portName
ports.ifMtu – interface MTU
ports.ifAlias
ports.ifSpeed
ports.ifAdminStatus
ports.ifOperStatus
ports_ifInUcastPkts/_prev/_delta/_rate -the “rate” thing can math pps rate -but god knows if it’s matching ingress/egress/sum of both???
ports_ifOutUcastPkts/_prev/_delta/_rate
ports_ifInErrors/_prev/_delta/_rate
ports_ifOutErrors/_prev/_delta/_rate
ports_ifInOctets/_prev/_delta/_rate
ports_ifOutOctets/_prev/_delta/_rate
ports_statistics.ifInNUcastPkts/_prev/_delta/_rate
ports_statistics.ifOutNUcastPkts/_prev/_delta/_rate
Regarding ports_ifInUcastPkts etc that is only ingress, Out variant is egress. The pps value is the current so with periodical polling it won’t be as useful afaik. So it has current counter packets, previous packets and supposedly delta of those 2 and a momentary pps. I’m guessing these: ifInNUcastPkts are non-unicast so BUM counters.
