/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

1 Like
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 ..

1 Like
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.