Treat IP address as string in custom macro and match

Hello,

I’d like to use a custom macro where I can add all kind of pattern of a syslog.msg.
This is to include or exclude these patterns in various syslog alert rules, whenever they appear in a syslog message.

This is working fine for pure strings but I ran into problems if my string is an IP…

My custom macro (config.php) looks like this:

$config[‘alert’][‘macros’][‘rule’][‘alert_gw2known’] = ‘((%syslog.msg like “%SOMETEXT%” || %syslog.msg like “%8.8.8.8%” || %syslog.msg like “%8.8.4.4%”))’;

In the alert rule, the SQl query will look like this:

SELECT * FROM devices,syslog,8,208 WHERE (devices.device_id = ? AND devices.device_id = syslog.device_id) ((syslog.msg like “%SOMETEXT%” || syslog.msg like “8.8.8.8%” || syslog.msg like “208.67.222.222%”)) = 1

What am I doing wrong?
How can I mask the address and let it treat like a string?

Thank you!

Maybe try wildcard-ing with @ instead of %.

Also, after you change the macro in the config file you may need to remove the conditions with the macro in them and then re-add and save the alert rule before the SQL statement will update.

Maybe check out this thread Groups based in IP addresses

thank you for your suggestion.

I tried it, but it turned out that “%syslog.msg like "@8.8.8.8%@”" was not matching anything at all in the SQL query.

What does the at-sign actually mean as a wildcard? I googled for it… but was not sucessfull.

thank you for your suggestion.

I take it you mean I should mask the dots in my IP with double quotes?
This is what I tried at first, but the outcome is, that thze backslashes are applied the tables-list as well:

SELECT * FROM devices,syslog, *8,208* WHERE (devices.device_id = ? AND devices.device_id = syslog.device_id) ((syslog.msg like “%SOMETEXT%” || syslog.msg like “ 8 \.8\.8\.8%” || syslog.msg like “ 208 \.67\.222\.222%”)) = 1

I believe, the root cause is not the SQL, because “8.8.8.8” is a valid string for a “like”-query.

The problem seems to be the parser which doesn’t like something that looks like a pattern…
Can you confirm this?

I found a workaround for my task:

$config[‘alert’][‘macros’][‘rule’][‘alert_gw2known’] = ‘((%syslog.msg like “%SOMETEXT%” || %syslog.msg like “%8_8_8_8%” || %syslog.msg like “%8_8_4_4%”))’;

will result in a query

SELECT * FROM devices,syslog WHERE (devices.device_id = ? AND devices.device_id = syslog.device_id) ((syslog.msg like “%SOMETEXT%” || syslog.msg like “8_8_8_8%” || syslog.msg like “208_67_222_222%”)) = 1

which is (more or less) what I am looking for.