Report Template: Advanced Counter

Count how often each value appears when messages contain text such as IP=192.168.1.1.

Initialization (SQL commands)

CREATE TABLE Vals (Val TEXT PRIMARY KEY, Counter INTEGER DEFAULT 0);

Data Fields

Add a Fields entry:

Field name: IP
Field format: {MESSAGE|r/IP=(\S)/}+

See Regular Expression modifiers.

For Each Message

Two commands, Execute for left blank (all messages that already passed the request filter).

First command:

INSERT OR IGNORE INTO Vals (Val) VALUES ('{IP|EscSQL}');

Second command:

UPDATE Vals SET Counter = Counter + 1 WHERE Val = '{IP|EscSQL}';

Always apply EscSQL to values taken from syslog text.

Finalization is not required.

Filename template

AdvCounter-{GENERATED|%Y-%m-%dT%H-%M-%S}.html

Report file contents

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Syslog report: Advanced Counter</title>
    <style>td { border: 1px solid black; }</style>
  </head>
  <body>
    <h1>Syslog report: Advanced Counter</h1>
    <p>{FROM|%F %T} - {TO|%F %T}</p>
    <table>
      <thead><tr><th>IP</th><th>Counter</th></tr></thead>
      <tbody>
        <tr>
          <td>TOTAL</td>
          <td style="text-align: end;">{{EXECUTE SELECT SUM(Counter) FROM Vals;}}{0}{{END}}</td>
        </tr>
        {{EXECUTE SELECT * FROM Vals ORDER BY Counter DESC;}}
          <tr>
            <td>{0}</td>
            <td style="text-align: end;">{1}</td>
          </tr>
        {{END}}
      </tbody>
    </table>
  </body>
</html>

On the generate or schedule request, narrow Filter by severity and originators so only messages that contain the IP= token are scanned.