Macros don't combine correctly

superchunk2000 · 27 March 2019 11:00

Hi All,

I am trying to write some macros to define product vendors. We monitor large ammounts of network devices from different manufacturers. I want to be able to write rules in the rule constructer that can be easily read and provide this information to the alert.

I have tryed many combinations of macro syntax in config.php. They appear correct and apache starts correctly. However when I try to use them in rules they don’t work when used with the macros.device_down.

I have tryed to copy the syntax from the Docs page and searched for examples in the forums.

An example of the first macro i tried;

//Juniper

$config['alert']['macros']['rule']['vendor_juniper'] = 'devices.os LIKE "%junos%" AND 
(devices.sysObjectID LIKE ".1.3.6.1.4.1.2636.%" OR devices.sysObjectID LIKE 
".1.3.6.1.4.1.12532.%")';

I then created the following rule

When I run the rule against a device a get a SQL Syntax error

SQL Error! SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '" AND (devices," OR devices WHERE (devices.device_id = ?) AND (devices.status = ' at line 1 (SQL: SELECT * FROM devices," AND (devices," OR devices WHERE (devices.device_id = 10) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND (devices.os LIKE "%junos" AND (devices.sysObjectID LIKE ".1.3.6.1.4.1.2636." OR devices.sysObjectID LIKE ".1.3.6.1.4.1.12532.%")) = 1) (SQL: SELECT * FROM devices," AND (devices," OR devices WHERE (devices.device_id = 10) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND (devices.os LIKE "%junos" AND (devices.sysObjectID LIKE ".1.3.6.1.4.1.2636." OR devices.sysObjectID LIKE ".1.3.6.1.4.1.12532.%")) = 1)
/opt/librenms/html/includes/output/query.inc.php:50
/opt/librenms/html/ajax_output.php:35

If I look at the SQL generated by the rule it has this odd section where the SQL query isn’t formatted correctly and not how I thought the rule builder logic is laid out. Specifically the ," after devices.

SELECT * FROM devices," AND (devices," OR devices WHERE

I have tryed quite a few different combinations but all result in the same issue when the 2 macros are joined together.

Can anyone help with what I am doing worng please?

Duncan

Chas · 27 March 2019 11:17

I think you ran into a php quote problem, you can fix but im no expert in nesting php quotes

you could try escape all your double quotes like

$config['alert']['macros']['rule']['vendor_juniper'] = 'devices.os LIKE \"%junos%\" AND 
(devices.sysObjectID LIKE \".1.3.6.1.4.1.2636.%\" OR devices.sysObjectID LIKE 
\".1.3.6.1.4.1.12532.%\")';

Alternatively did you look at using Override SQL alert instead of a MACRO ?
e.g Threshold for traffic alertings

superchunk2000 · 27 March 2019 11:37

Hi Chas,

I did try that and it dosn’t help. The \ are passed into the SQL query.

I have found that it seems to be related to trying to get it to do AND and OR’s inside the macro.

If I reduce the macro to the following;

$config['alert']['macros']['rule']['vendor_juniper'] = 'devices.sysObjectID LIKE ".1.3.6.1.4.1.2636.%"';

It runs correctly
Rule name: P1 – Critical – Network - Juniper Device - Device Down
Alert rule: macros.device_down = 1 AND macros.vendor_juniper = 1
Alert query: SELECT * FROM devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND (devices.sysObjectID LIKE “.1.3.6.1.4.1.2636.%”) = 1
Rule match: no match

If I try and use two SNMP OIDs joined by an OR.

$config['alert']['macros']['rule']['vendor_juniper'] = 'devices.sysObjectID LIKE ".1.3.6.1.4.1.2636.%" OR devices.sysObjectID LIKE ".1.3.6.1.4.1.12532.%"';

It dosn’t work
SQL Error! SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘" OR devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices.d’ at line 1 (SQL: SELECT * FROM devices," OR devices WHERE (devices.device_id = 12) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND (devices.sysObjectID LIKE “.1.3.6.1.4.1.2636.” OR devices.sysObjectID LIKE “.1.3.6.1.4.1.12532.%”) = 1) (SQL: SELECT * FROM devices," OR devices WHERE (devices.device_id = 12) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND (devices.sysObjectID LIKE “.1.3.6.1.4.1.2636.” OR devices.sysObjectID LIKE “.1.3.6.1.4.1.12532.%”) = 1)

I just don’t understand why the begining of the statement gets changed.

SELECT * FROM devices," OR devices WHERE

superchunk2000 · 27 March 2019 11:41

I wanted to use the rule builder so that people using the system can build there own rules without needing to know SQL or the OID strings they need. Using this also breaks how the page is displayed;

Chas · 27 March 2019 12:01

Just looking at https://github.com/librenms/librenms/blob/5f0015ab8b3a06c43b5fececfb5cc947f003822a/misc/macros.json

Maybe you need to enclose them in brackets

$config['alert']['macros']['rule']['vendor_juniper'] = '(devices.sysObjectID LIKE ".1.3.6.1.4.1.2636.%" OR devices.sysObjectID LIKE ".1.3.6.1.4.1.12532.%")';

Not sure otherwise

superchunk2000 · 27 March 2019 12:30

I have tryed brackets around the whole statement.

$config['alert']['macros']['rule']['vendor_juniper'] = '(devices.sysObjectID LIKE ".1.3.6.1.4.1.2636.%" OR devices.sysObjectID LIKE ".1.3.6.1.4.1.12532.%")';

This dosn’t work;

SQL Error! SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘" OR devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices.d’ at line 1 (SQL: SELECT * FROM devices," OR devices WHERE (devices.device_id = 10) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND (devices.sysObjectID LIKE “.1.3.6.1.4.1.2636.” OR devices.sysObjectID LIKE “.1.3.6.1.4.1.12532.%”) = 1) (SQL: SELECT * FROM devices," OR devices WHERE (devices.device_id = 10) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND (devices.sysObjectID LIKE “.1.3.6.1.4.1.2636.” OR devices.sysObjectID LIKE “.1.3.6.1.4.1.12532.%”) = 1)

/opt/librenms/html/includes/output/query.inc.php:50

/opt/librenms/html/ajax_output.php:35

Rule name: P1 – Critical – Network - Juniper Device - Device Down

Alert rule: macros.device_down = 1 AND macros.vendor_juniper = 1

Alert query: SELECT * FROM devices," OR devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND (devices.sysObjectID LIKE ".1.3.6.1.4.1.2636." OR devices.sysObjectID LIKE ".1.3.6.1.4.1.12532.%") = 1

Rule match: matches

I have also tryed brackets around each staement and then brackets around the whole command.

$config['alert']['macros']['rule']['vendor_juniper'] = '((devices.sysObjectID LIKE ".1.3.6.1.4.1.2636.%") OR (devices.sysObjectID LIKE ".1.3.6.1.4.1.12532.%"))';

SQL Error! SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘") OR (devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices’ at line 1 (SQL: SELECT * FROM devices,") OR (devices WHERE (devices.device_id = 10) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND ((devices.sysObjectID LIKE “.1.3.6.1.4.1.2636.”) OR (devices.sysObjectID LIKE “.1.3.6.1.4.1.12532.%”)) = 1) (SQL: SELECT * FROM devices,") OR (devices WHERE (devices.device_id = 10) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND ((devices.sysObjectID LIKE “.1.3.6.1.4.1.2636.”) OR (devices.sysObjectID LIKE “.1.3.6.1.4.1.12532.%”)) = 1)

/opt/librenms/html/includes/output/query.inc.php:50

/opt/librenms/html/ajax_output.php:35

Rule name: P1 – Critical – Network - Juniper Device - Device Down

Alert rule: macros.device_down = 1 AND macros.vendor_juniper = 1

Alert query: SELECT * FROM devices,") OR (devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND ((devices.sysObjectID LIKE ".1.3.6.1.4.1.2636.") OR (devices.sysObjectID LIKE ".1.3.6.1.4.1.12532.%")) = 1

Rule match: matches

superchunk2000 · 27 March 2019 13:07

I have found a way to do this using REGEXP instead of LIKE

$config['alert']['macros']['rule']['vendor_juniper'] = '(devices.sysObjectID REGEXP "(\.1\.3\.6\.1\.4\.1\.2636\.|\.1\.3\.6\.1\.4\.1\.12532\.)")';

I no longer get an error when running the rule.

Rule name: P1 – Critical – Network - Juniper Device - Device Down

Alert rule: macros.device_down = 1 AND macros.vendor_juniper = 1

Alert query: SELECT * FROM devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND (devices.sysObjectID REGEXP "(\.1\.3\.6\.1\.4\.1\.2636\.|\.1\.3\.6\.1\.4\.1\.12532\.)") = 1

Rule match: no match

superchunk2000 · 27 March 2019 13:07

I would still be interested to know why the LIKE dosn’t with the OR operator.

I wonder if its beacuse the other Macro uses && for the operator instead of AND. There fore to join them I have to use Pipes | for OR instead of the OR operator.

Tadpole · 27 March 2019 13:48

Wouldn’t it be easier to create a Device Group for everything Juniper and map your alert to the Device Group? - I believe it will still allow you to provide information in the alert from the erroring device. What am I missing that you are looking for?!?!

superchunk2000 · 27 March 2019 13:59

Thanks for the input tadpole.

We use Device Groups to groups devices based on other criteria. Normally a short code denoting either customer or the site the device is located at.
The Geo Location is populated with the full address. Which can be tricky to read when looking through hundreds of sites.

TheGreatDoc · 27 March 2019 14:01

Just fyi, devices can be in multiple groups. So you can create a group called Juniper - Alerts and map alerts to that group.

superchunk2000 · 27 March 2019 14:11

Hi TheGreatDoc,

Thanks I will look at this.

superchunk2000 · 27 March 2019 14:15

I have now managed to do what I was trying to achieve as follows.

The following in /opt/librenms/config.php

 //Juniper
$config['alert']['macros']['rule']['vendor_juniper_oid'] = '(devices.sysObjectID REGEXP "(\.1\.3\.6\.1\.4\.1\.2636\.|\.1\.3\.6\.1\.4\.1\.12532\.)")';
$config['alert']['macros']['rule']['vendor_juniper_os'] = '(devices.os LIKE "%junos%")';
$config['alert']['macros']['rule']['vendor_juniper'] = '(%macros.vendor_juniper_oid && %macros.vendor_juniper_os)';

I can now create a rule as follows;

It now dosn’t error when run.

Rule name: P1 – Critical – Network - Juniper Device - Device Down
Alert rule: macros.device_down = 1 AND macros.vendor_juniper = 1
Alert query: SELECT * FROM devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND ((devices.sysObjectID REGEXP “(.1.3.6.1.4.1.2636.|.1.3.6.1.4.1.12532.)”) && (devices.os LIKE “%junos%”)) = 1
Rule match: no match

This fits with our environment and how we have setup LibreNMS.

Thanks for the input everyone.