diff options
| -rw-r--r-- | README.md | 1 | ||||
| -rwxr-xr-x | main.py | 8 | ||||
| -rw-r--r-- | report.py | 7 |
3 files changed, 10 insertions, 6 deletions
@@ -1,4 +1,5 @@ # ejabberd mod_spam_filter ingest +[](https://www.codefactor.io/repository/github/mightybroccoli/spamfilter-ingest) ## installation Python 3 virtual environment @@ -55,7 +55,8 @@ class AbuseReport: for domain in self.domain: sql_query = self.conn.execute('''SELECT COUNT(*) AS messages,COUNT(DISTINCT user) AS bots,domain, MIN(ts) - AS first,MAX(ts) AS last FROM spam WHERE domain = :domain;''',{"domain": domain}).fetchall() + AS first,MAX(ts) AS last FROM spam WHERE ts BETWEEN DATE('now','-1 months') AND DATE('now') + and domain = :domain;''',{"domain": domain}).fetchall() # if specified domain is not listed yet, the resulting table will not show the domain name # this ugly tuple 2 list swap prevents this @@ -76,8 +77,9 @@ class AbuseReport: if self.config.get_at("top10_view"): result = self.conn.execute('''SELECT * FROM "top10"''').fetchall() else: - result = self.conn.execute('''SELECT COUNT(*) AS messages,COUNT(DISTINCT user) AS bots,domain AS domain - FROM spam GROUP BY domain ORDER BY 1 DESC LIMIT 10''').fetchall() + result = self.conn.execute('''SELECT COUNT(*) AS messages, COUNT(DISTINCT user) AS bots, domain AS + domain FROM spam WHERE ts BETWEEN DATE('now','-14 days') AND DATE('now') GROUP BY domain + ORDER BY 1 DESC LIMIT 10;''').fetchall() # tabelize data spam_table = tabulate.tabulate(result, headers=["messages", "bots", "domain", "first seen", "last seen"], @@ -38,8 +38,8 @@ class ReportDomain: :return: formatted result string """ - jids = self.conn.execute('''SELECT user || '@' || domain as jid FROM spam WHERE domain=:domain GROUP BY user - ORDER BY 1;''', {"domain": domain}).fetchall() + jids = self.conn.execute('''SELECT user || '@' || domain AS jid FROM spam WHERE ts BETWEEN DATE('now','-14 days') + AND DATE('now') AND domain=:domain GROUP BY user ORDER BY 1;''', {"domain": domain}).fetchall() return tabulate.tabulate(jids, tablefmt="plain") @@ -52,7 +52,8 @@ class ReportDomain: logs = self.conn.execute('''SELECT CHAR(10) || MIN(ts) || ' - ' || MAX(ts) || char(10) || COUNT(*) || 'messages:' || char(10) ||'========================================================================' || char(10) || message || char(10) || '========================================================================' - FROM spam WHERE domain=:domain GROUP BY message ORDER BY COUNT(*) DESC LIMIT 10;''', {"domain": domain}).fetchall() + FROM spam WHERE ts BETWEEN DATE('now','-14 days') AND DATE('now') AND domain=:domain GROUP BY message ORDER + BY COUNT(*) DESC LIMIT 10;''', {"domain": domain}).fetchall() return tabulate.tabulate(logs, tablefmt="plain") |