Merge pull request #2939 from citrin/clickhouse

[Minor] ClickHouse: Improve things stored

3 files changed, 18 insertions, 12 deletions

diff --git a/lualib/lua_clickhouse.lua b/lualib/lua_clickhouse.lua
index ad5b51dce..4a57afd3f 100644
--- a/lualib/lua_clickhouse.lua
+++ b/lualib/lua_clickhouse.lua
@@ -49,7 +49,7 @@ local function clickhouse_quote(str)
       ['\\'] = [[\\]],
       ['\n'] = [[\n]],
       ['\t'] = [[\t]],
-    }):lower()
+    })
   end
 
   return ''
@@ -503,4 +503,4 @@ exports.generic_sync = function (upstream, settings, params, query)
   end
 end
 
-return exports
\ No newline at end of file
+return exports
diff --git a/src/plugins/lua/asn.lua b/src/plugins/lua/asn.lua
index 5adc2bd7f..7f236ca99 100644
--- a/src/plugins/lua/asn.lua
+++ b/src/plugins/lua/asn.lua
@@ -42,8 +42,12 @@ local function asn_check(task)
     local descr_t = {}
     local mempool = task:get_mempool()
     if asn then
-      mempool:set_variable("asn", asn)
-      table.insert(descr_t, "asn:" .. asn)
+      if tonumber(asn) ~= nil then
+        mempool:set_variable("asn", asn)
+        table.insert(descr_t, "asn:" .. asn)
+      else
+        rspamd_logger.errx(task, 'malformed ASN "%s" for ip %s', asn, task:get_from_ip())
+      end
     end
     if ipnet then
       mempool:set_variable("ipnet", ipnet)
diff --git a/src/plugins/lua/clickhouse.lua b/src/plugins/lua/clickhouse.lua
index ff46d80fe..f62bda2c6 100644
--- a/src/plugins/lua/clickhouse.lua
+++ b/src/plugins/lua/clickhouse.lua
@@ -123,7 +123,7 @@ CREATE TABLE rspamd
     `Urls.Tld` Array(String) COMMENT 'Effective second level domain part of the URL host',
     `Urls.Url` Array(String) COMMENT 'Full URL if `full_urls` module option enabled, host part of URL otherwise',
     Emails Array(String) COMMENT 'List of emails extracted from the message',
-    ASN String COMMENT 'BGP AS number for SMTP client IP (returned by asn.rspamd.com or asn6.rspamd.com)',
+    ASN UInt32 COMMENT 'BGP AS number for SMTP client IP (returned by asn.rspamd.com or asn6.rspamd.com)',
     Country LowCardinality(FixedString(2)) COMMENT 'Country for SMTP client IP (returned by asn.rspamd.com or asn6.rspamd.com)',
     IPNet String,
     `Symbols.Names` Array(LowCardinality(String)) COMMENT 'Symbol name',
@@ -158,7 +158,7 @@ local migrations = {
       ADD COLUMN `Urls.Tld` Array(String) AFTER `Attachments.Digest`,
       ADD COLUMN `Urls.Url` Array(String) AFTER `Urls.Tld`,
       ADD COLUMN Emails Array(String) AFTER `Urls.Url`,
-      ADD COLUMN ASN String AFTER Emails,
+      ADD COLUMN ASN UInt32 AFTER Emails,
       ADD COLUMN Country FixedString(2) AFTER ASN,
       ADD COLUMN IPNet String AFTER Country,
       ADD COLUMN `Symbols.Names` Array(String) AFTER IPNet,
@@ -426,7 +426,7 @@ local function clickhouse_collect(task)
     local from = task:get_from('smtp')[1]
 
     if from then
-      from_domain = from['domain']
+      from_domain = from['domain']:lower()
       from_user = from['user']
     end
 
@@ -446,15 +446,17 @@ local function clickhouse_collect(task)
   if task:has_from('mime') then
     local from = task:get_from({'mime','orig'})[1]
     if from then
-      mime_domain = from['domain']
+      mime_domain = from['domain']:lower()
       mime_user = from['user']
     end
   end
 
   local mime_rcpt = {}
   if task:has_recipients('mime') then
-    local from = task:get_recipients({'mime','orig'})
-    mime_rcpt = fun.totable(fun.map(function (f) return f.addr or '' end, from))
+    local recipients = task:get_recipients({'mime','orig'})
+    for _, rcpt in ipairs(recipients) do
+      table.insert(mime_rcpt, rcpt['user'] .. '@' .. rcpt['domain']:lower())
+    end
   end
 
   local ip_str = 'undefined'
@@ -474,7 +476,7 @@ local function clickhouse_collect(task)
   if task:has_recipients('smtp') then
     local rcpt = task:get_recipients('smtp')[1]
     rcpt_user = rcpt['user']
-    rcpt_domain = rcpt['domain']
+    rcpt_domain = rcpt['domain']:lower()
   end
 
   local list_id = task:get_header('List-Id') or ''
@@ -731,7 +733,7 @@ local function clickhouse_collect(task)
   end
 
   -- ASN information
-  local asn, country, ipnet = '--', '--', '--'
+  local asn, country, ipnet = 0, '--', '--'
   local pool = task:get_mempool()
   ret = pool:get_variable("asn")
   if ret then