Report Template: Advanced Counter¶

This syslog report template shows how to count different variable values.

Conditions and Goal¶

Each syslog message contains the text Variable=..., where ... is from a set of values, e.g., IP addresses: IP=192.168.1.1. We need to count how many times each value (IP address) occurs.

Report Template¶

Initial step: create a new template.

Initialization (Pre Commands)¶

The initialization (Pre) command creates a table for the values.

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

Data Field¶

Make sure you have the Advanced Settings visible:

Click dropdown box and select Show Advanced Settings

Use regular expression syntax to extract the required field from the message text. The exact expression will be individual for each case. For the example above, {MESSAGE|r/IP=(\S+)/} will work.

Click dropdown box and select Show Advanced Settings

Commands¶

Next, add two commands that apply to all syslog messages (no filter):

INSERT OR IGNORE INTO Vals (Val) VALUES ('{IP|EscSQL}');
UPDATE Vals SET Counter=Counter+1 WHERE Val='{IP|EscSQL}';

The first command creates records for new values (IP addresses), and the second increments the counter. Always add the EscSQL modifier to escape values you extract from message text.

Post SQL Commands¶

All calculations are performed during report generation, so finalizing (Post) commands are not required.

Output Filename¶

The report's creation timestamp will create a unique file name:

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

Output Directory¶

Leaving the target directory empty will prompt you to enter the output directory each time the report is generated.

Output HTML¶

Here is a simple HTML that displays the counters:

<!DOCTYPE html>
<html lang="en">
  <body>
    <table>
      <thead><tr><th>IP</th><th>Counter</th></tr></thead>
      <tbody>
        {{EXECUTE SELECT * FROM Vals ORDER BY Counter DESC;}}
          <tr>
            <td style="border: 1px solid black;">{0}</td>
            <td style="border: 1px solid black; text-align: end;">{1}</td>
          </tr>
        {{END}}
      </tbody>
    </table>
  </body>
</html>

Let's improve the template by including titles, time period, and total number:

<!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_S|%F %T} - {TO_S|%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>

Results¶

You will end up with a template looking like this: Template editor shows the result report template configuration

And a generated report looks similar to this one:

Syslog Report Example: Advanced Counter

Message Filtering¶

For this report type, you will filter syslog messages to analyze only those that contain the required data field. Always add filter by severity level and (if possible) syslog originators.

Tip

Filtering by severity level is very fast and will help you avoid analyzing unnecessary messages.

Report generator windows shows severity filter and complex filter

Download Template¶

You can download this report example (advanced-counter.report.json) and import it into Syslog Watcher Manager.