/usr/bin/suricata-report-cron in CU 200

Security Intrusion Prevention
1

Since installation of CU 200 no daily report has been sent. For CU199 this worked flawlessly. When analysing this, I found that the report process triggered by fcron /usr/bin/suricata-report-cron is throwing these error messages when starting it from the command line:

/usr/bin/suricata-report-cron daily
Traceback (most recent call last):
File “/usr/bin/suricata-report-generator”, line 922, in
main()
File “/usr/bin/suricata-report-generator”, line 912, in main
generator.email(
File “/usr/bin/suricata-report-generator”, line 790, in email
self.generate(output=f.name, \*\*kwargs)
File “/usr/bin/suricata-report-generator”, line 198, in generate
self.\_make_alerts(elements, date_start, date_end, width=doc.width)
File “/usr/bin/suricata-report-generator”, line 474, in \_make_alerts
self.\_make_alerts_by_date(elements, date, \*\*kwargs)
File “/usr/bin/suricata-report-generator”, line 634, in \_make_alerts_by_date
for query in json.loads(row.dns_queries):
File “/usr/lib/python3.10/json/**init**.py”, line 339, in loads
raise TypeError(f’the JSON object must be str, bytes or bytearray, ’
TypeError: the JSON object must be str, bytes or bytearray, not NoneType

What’s wrong here? Maybe row.dns_queries is not yet populated and initialised wrongly.

Additional background information: Initially I had IPFire DBL enabled as a suricata ruleset. However, I disabled it this morning since it was throwing hundreds of DNS malware e-mails.

2

Hmm, there should not be anything outside of the usual in that row.

Are you able to send me a dump of your database?

3

I have noticed the same thing, no daily report as well. I am looking into it.

PZ

4

Hello Michael, thanks for looking into my issue.

Yes, I can provide a reduced dump of my suricata database.
Is this the correct database:

sqlite3 /var/log/suricata/reporter.db

Which data should I extract from this huge database?

reporter.db is containing just one table ‘alerts’:

sqlite3 /var/log/suricata/reporter.db
SQLite version 3.51.1 2025-11-28 17:28:25
Enter “.help” for usage hints.
sqlite> .tables
alerts
sqlite>

sqlite3 /var/log/suricata/reporter.db ".schema alerts"
CREATE TABLE alerts (
id INTEGER PRIMARY KEY,

-- Store the timestamp
timestamp INTEGER NOT NULL,

-- Store the entire JSON object
event JSONB NOT NULL
);
CREATE INDEX alerts_timestamp ON alerts(timestamp);

Furthermore, the query

sqlite3 /var/log/suricata/reporter.db
“SELECT json_extract(event,‘$.dns’) FROM alerts WHERE json_extract(event,‘$.dns’) IS NOT NULL LIMIT 5;”
{“version”:3,“type”:“request”,“tx_id”:0,“id”:35218,“flags”:“100”,“rd”:true,“opcode”:0,“rcode”:“NOERROR”,“queries”:[{“rrname”:“``relays.syncthing.net``”,“rrtype”:“AAAA”}]}
{“version”:3,“type”:“request”,“tx_id”:0,“id”:28824,“flags”:“100”,“rd”:true,“opcode”:0,“rcode”:“NOERROR”,“queries”:[{“rrname”:“``relays.syncthing.net``”,“rrtype”:“A”}]}
{“version”:3,“type”:“request”,“tx_id”:0,“id”:11505,“flags”:“100”,“rd”:true,“opcode”:0,“rcode”:“NOERROR”,“queries”:[{“rrname”:“``relays.syncthing.net``”,“rrtype”:“A”}]}
{“version”:3,“type”:“request”,“tx_id”:0,“id”:43133,“flags”:“100”,“rd”:true,“opcode”:0,“rcode”:“NOERROR”,“queries”:[{“rrname”:“``relays.syncthing.net``”,“rrtype”:“A”}]}
{“version”:3,“type”:“request”,“tx_id”:0,“id”:61948,“flags”:“100”,“rd”:true,“opcode”:0,“rcode”:“NOERROR”,“queries”:[{“rrname”:“``relays.syncthing.net``”,“rrtype”:“A”}]}

is showing that there are dns entries.

However,

sqlite3 /var/log/suricata/reporter.db "
SELECT
id,
json_extract(event,‘$.timestamp’),
json_extract(event,‘$.src_ip’),
json_extract(event,‘$.dest_ip’),
json_extract(event,‘$.dns’)
FROM alerts
WHERE json_extract(event,‘$.dns.queries’) IS NULL
LIMIT 20;
"
2|2025-10-28T13:15:44.906317+0100|192.168.2.16|141.144.201.57|
...

is showing that there are also queries which return NULL. This seems to trigger my initially reported error messages. Currently 262445 entries are returning NULL:

sqlite3 /var/log/suricata/reporter.db \
"SELECT count(*) FROM alerts WHERE json_extract(event,'$.dns.queries') IS NULL;"
262345

This query

sqlite3 /var/log/suricata/reporter.db "
SELECT count(*)
FROM alerts
WHERE json_extract(event,‘$.dns’) IS NOT NULL;
"
33923

shows that there are many (33923) NOT NULL dns entries in table alerts.

The last NULL dns entry is dated 2026-02-24T13:47:49.622816+0100, i.e. originated by CU199.

How can I upload a bigger file for your debugging?

-rw-r–r-- 1 root root 330080256 Mar 4 23:30 reporter.db

5

You can dump the database like so:

sqlite3 /var/log/suricata/reporter.db .dump

or just send the entire file.

Depending how large your database is, please use nopaste.ipfire.org.

6

An upload with curl is unfortunately failing:

curl -u ewald -T- https://nopaste.ipfire.org <  reporter.db
Enter host password for user ‘ewald’:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

<html><head>
<title>502 Bad Gateway</title>
</head><body>
<h1>Bad Gateway</h1>
<p>The proxy server received an invalid
response from an upstream server.<br />
</p>
</body></html>

The size of reporter.db is 333 MByte.

Same error message when trying to upload the ASCII dump file.

7

The following work-around made ‘/usr/bin/suricata-report-cron daily’ working again:

  1. stop suricata IPS in WUI
  2. ssh into IPFire
  3. cd /var/log/suricata/
  4. mv reporter.db reporter.db.bck
  5. restart suricata IPS in WUI
    6 start in CLI ‘/usr/bin/suricata-report-cron daily’: no error messages, e-mail has been sent

I will check tomorrow morning, if the daily IPS report e-mail will work again ..

8

Thanks for the work-around.
If it is functioning, what I believe, it shows the reason of the errors.
The message is about invalid data type of a field. If this field is generated/populated not early than CU200, then the reporter throws errors for data before this update.

9

Unfortunately, the work-around is not working after the database was populated again. This morning I am getting the same error messages:

/usr/bin/suricata-report-cron daily
Traceback (most recent call last):
File "/usr/bin/suricata-report-generator", line 922, in <module>
main()
File "/usr/bin/suricata-report-generator", line 912, in main
generator.email(
File "/usr/bin/suricata-report-generator", line 790, in email
self.generate(output=f.name, **kwargs)
File "/usr/bin/suricata-report-generator", line 198, in generate
self._make_alerts(elements, date_start, date_end, width=doc.width)
File "/usr/bin/suricata-report-generator", line 474, in _make_alerts
self._make_alerts_by_date(elements, date, **kwargs)
File "/usr/bin/suricata-report-generator", line 634, in _make_alerts_by_date
for query in json.loads(row.dns_queries):
File "/usr/lib/python3.10/json/__init__.py", line 339, in loads
raise TypeError(f'the JSON object must be str, bytes or bytearray, '
TypeError: the JSON object must be str, bytes or bytearray, not NoneType

This time I was able to upload the reporter.db.dump file to https://nopaste.ipfire.org. Please get it from there. The file size is

-rw-r–r-- 1 root 1393634 Mar 9 08:56 reporter.db.dump

10

That link is just the top level ipfire nopaste link.

You need to provide the URL that includes the unique ID that will have been provided when you pasted or uploaded your file via the browser or uploaded the file via curl on the command line.

11

The link is included in my (separate) upload message of https://nopaste.ipfire.org.

12

nopaste.ipfire.org does not support uploads of this kind of size. I suggest to try compressing it or finding another way to submit it, please.

13

I’ve uploaded again to nopaste.ipfire.org - DxH19Ou-

14

Thank you, here is the fix:

I will port it to Core Update 201.

15

Thanks a lot, Michael.
The CLI invocation of ‘/usr/bin/suricata-report-cron daily’ is working again after having patched ‘/usr/bin/suricata-report-generator’ manually with your fix.

16

Hi Michael,

Unfortunately, despite your fix, this morning another similar error message popped up:

Please access reporter.db.dump for your debugging at

/usr/bin/suricata-report-cron daily
Traceback (most recent call last):
File "/usr/bin/suricata-report-generator", line 922, in <module>
main()
File "/usr/bin/suricata-report-generator", line 912, in main
generator.email(
File "/usr/bin/suricata-report-generator", line 790, in email
self.generate(output=f.name, **kwargs)
File "/usr/bin/suricata-report-generator", line 198, in generate
self._make_alerts(elements, date_start, date_end, width=doc.width)
File "/usr/bin/suricata-report-generator", line 474, in _make_alerts
self._make_alerts_by_date(elements, date, **kwargs)
File "/usr/bin/suricata-report-generator", line 647, in _make_alerts_by_date
if row.http_potocol:
AttributeError: 'Row' object has no attribute 'http_potocol'. Did you mean: 'http_protocol'?

17

Did you edit the file.
In my system line 647 of /usr/bin/suricata_report_generator reads
if row.http_protocol:

So the error message of your system tells all:
File "/usr/bin/suricata-report-generator", line 647, in _make_alerts_by_date
if row.http_potocol:
AttributeError: 'Row' object has no attribute 'http_potocol'. Did you mean: 'http_protocol'?

18

Hello Bernhard,

Yes, I edited the file manually.. Therefore, the row numbers may be different. Here the extract of my edit:

                       # Add more information if available for the app proto

                        # DNS
                        if row.app_protocol == "dns":
                                line = []

                                # Append all queries

                                if row.dns_queries:
                                      for query in json.loads(row.dns_queries):
                                               line.append("%s: %s" % (_("Query"), "%(rrname)s (%(rrtype)s)" % query),)

                                      # Append the line to the signature
                                      signature.append(" - ".join(line))

                        # HTTP
                        elif row.app_protocol == "http":
                                line = []

                                # Add protocol
                                if row.http_potocol:
                                        line.append("%s: %s" % (_("Protocol"), row.http_protocol))

                                # Add hostname
                                if row.http_hostname:
                                        line.append("%s: %s" % (_("Hostname"), row.http_hostname))

                                # Add method
                                if row.http_method:
                                        line.append(row.http_method)

                                # Add URL
                                if row.http_url:
                                        line.append("%s: %s" % (_("URL"), row.http_url))

                                # Append the line to the signature
                                signature.append(" - ".join(line))

                        # TLS
                        elif row.app_protocol == "tls":
19

You must have done an unintended typo then.

Here is the section in the patch modified version from @ms
https://git.ipfire.org/?p=suricata-reporter.git;a=blob;f=src/suricata-report-generator.in;h=e4b4db948713a7df1be58362f71ebaf4e2869e35#l645

20

Hello Adolf, Thanks for your hint. I am sorry if this was the case.
How do I get from suricata-report-generator.in to /usr/bin/suricata-report-generator?
Unfortunately, I am not (yet) familiar with the internal generation processes of IPFire.