Giving back to the team and community: log file database upload

This section contains user-submitted tutorials.
Post Reply
DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-03 21:23

Current version here >> viewtopic.php?f=21&t=35550#p229699


Hi all,

I've been happily using hMailServer for more than 10 years now, and it has accumulates lots of logs. Before archiving them, I wanted to see who is abusing the server and should be added to the firewall blocking rules.
The program that I wrote helped me to put all IP addresses that accessed the server into a database, for further analysis.
I built it in Visual Studio 2015. If you want to use it, you can probably use any version out there. It is built on .NET full framework 4.7.1, but if you want it will likely convert to Core with little effort.
For MySQL, you need MySQL .NET Connector installed. For MS SQL you need SQL client.

Here's how to run it:

Code: Select all

hMailServerLogImporter MsSql "P:\Program Files (x86)\hMailServer\Logs" hmailserver_2018-12-29.log hmailserver_2019-01-01.log
If you want all of your logs processed, you should omit the last two parameters. If you start it without any parameters, it will print help. If you have tonnes of logs, it is probably a good idea to leave it running overnight, if you are uploading to MS SQL because it is slow. MySQL is much faster to upload, and 2-3 years worth of logs may upload in less than 1 hour. In the end, it will print a quick and simple report of every IP that accessed your server more than 1000 times in the last 3 months. Any other analytics can be extracted from the SQL table 'iplog' that the program will create. There is no dupe check, for the sake of best performance, so next time just truncate the table before the import.

I am open to suggestions, so if you have any wishes, leave them in this thread, and I will check it every once in a while.
Last edited by mattg on 2021-05-17 00:26, edited 1 time in total.

palinka
Senior user
Senior user
Posts: 4455
Joined: 2017-09-12 17:57

Re: Giving back to the team and community: log file database upload

Post by palinka » 2020-12-04 20:15

I counted 38 IPs > 1000 hits in the last 3 months. :D

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-05 21:07

Let me guess: some of them probably belong to either of the networks 212.70.149.0 or 45.142.120.0. These are known abusers who brute-force or DDOS SMTP servers around the globe 24/7.

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Giving back to the team and community: log file database upload

Post by SorenR » 2020-12-05 21:25

DrmCa wrote:
2020-12-05 21:07
Let me guess: some of them probably belong to either of the networks 212.70.149.0 or 45.142.120.0. These are known abusers who brute-force or DDOS SMTP servers around the globe 24/7.
Just had a look at my IP Ranges... No 212's and two 45's, one using a NON-RFC greeting and the other GEO-Blocked trying to logon SMTP.

I have some simple tools...

1) If you contact my server more than 2 times within 180 minutes without sending an email you get banned.
2) If your HELO/EHLO greeting is not RFC compliant you get banned.
3) If you try to authenticate outside the Danish Realm you get banned. That also apply to valid accounts - there are alternatives!

"This is the way"
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
mattg
Moderator
Moderator
Posts: 22435
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Giving back to the team and community: log file database upload

Post by mattg » 2020-12-06 01:51

SorenR wrote:
2020-12-05 21:25
I have some simple tools...

1) If you contact my server more than 2 times within 180 minutes without sending an email you get banned.
2) If your HELO/EHLO greeting is not RFC compliant you get banned.
3) If you try to authenticate outside the Danish Realm you get banned. That also apply to valid accounts - there are alternatives!

"This is the way"
Does an IMAP client on a mobile phone in a poor mobile coverage area get banned by #1?
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Giving back to the team and community: log file database upload

Post by SorenR » 2020-12-06 02:57

mattg wrote:
2020-12-06 01:51
SorenR wrote:
2020-12-05 21:25
I have some simple tools...

1) If you contact my server more than 2 times within 180 minutes without sending an email you get banned.
2) If your HELO/EHLO greeting is not RFC compliant you get banned.
3) If you try to authenticate outside the Danish Realm you get banned. That also apply to valid accounts - there are alternatives!

"This is the way"
Does an IMAP client on a mobile phone in a poor mobile coverage area get banned by #1?
No, #3 is the one to look out for here...

Should have mentioned #1 is unauthenticated SMTP only.
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

palinka
Senior user
Senior user
Posts: 4455
Joined: 2017-09-12 17:57

Re: Giving back to the team and community: log file database upload

Post by palinka » 2020-12-06 14:58

SorenR wrote:
2020-12-06 02:57
Should have mentioned #1 is unauthenticated SMTP only.
Hmmm... I run everything through IDS except connections from localhost, local LAN and local WAN.

IP gets removed if authenticated logon -OR- message fully processed (received from outside server).

I never had an issue with mobile imap clients. Maybe because mobile clients prefer activesync, which gets excluded from testing by virtue of connecting to hmailserver via localhost. ¯\_ (ツ)_/¯

This IDS is perfection in simplicity. Send a message, no problem. Logon, no problem. Everyone else - PROBLEM... :D

palinka
Senior user
Senior user
Posts: 4455
Joined: 2017-09-12 17:57

Re: Giving back to the team and community: log file database upload

Post by palinka » 2020-12-06 15:09

DrmCa wrote:
2020-12-05 21:07
Let me guess: some of them probably belong to either of the networks 212.70.149.0 or 45.142.120.0. These are known abusers who brute-force or DDOS SMTP servers around the globe 24/7.
Here's the list: Firewall banned IPs having > 999 drops in the last 3 months.

Code: Select all

 HITS   IP ADDRESS
====== ===============
38,153 136.147.183.133
35,294 136.147.183.131
27,409 136.147.183.129
21,688 136.147.183.132
21,664 136.147.183.130
19,599 212.70.149.4
15,174 198.245.86.78
12,428 95.216.194.37
 9,233 198.245.86.56
 7,388 3.227.148.255
 6,910 148.251.241.12
 6,551 66.249.69.50
 6,381 72.166.182.50
 4,814 66.249.69.82
 3,750 144.76.68.124
 3,722 144.76.151.45
 3,719 63.239.204.50
 2,329 199.7.202.150
 2,317 66.249.66.152
 1,825 223.25.77.84
 1,760 85.208.96.65
 1,674 85.208.96.68
 1,654 66.249.66.154
 1,511 85.208.96.67
 1,510 66.249.64.142
 1,264 143.110.230.225
 1,171 72.52.250.24
 1,164 66.249.64.30
 1,158 85.208.96.66
 1,129 66.249.74.3
 1,087 66.249.74.26
 1,084 185.244.41.7
 1,067 66.249.64.19
This isn't a product of your app, which looks really cool. I have my own firewall ban project. I scrape the firewall log for dropped connections. The statistics gleaned from that are pretty informative.

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Giving back to the team and community: log file database upload

Post by SorenR » 2020-12-06 15:26

palinka wrote:
2020-12-06 14:58
SorenR wrote:
2020-12-06 02:57
Should have mentioned #1 is unauthenticated SMTP only.
Hmmm... I run everything through IDS except connections from localhost, local LAN and local WAN.

IP gets removed if authenticated logon -OR- message fully processed (received from outside server).

I never had an issue with mobile imap clients. Maybe because mobile clients prefer activesync, which gets excluded from testing by virtue of connecting to hmailserver via localhost. ¯\_ (ツ)_/¯

This IDS is perfection in simplicity. Send a message, no problem. Logon, no problem. Everyone else - PROBLEM... :D
What we do cannot be done easily with the standard 5.x hMailServer as it is missing two triggers; OnHELO and OnClientLogon. You need RvdH's version of 5.6.8 B2505 or 5.7-64bit.
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

palinka
Senior user
Senior user
Posts: 4455
Joined: 2017-09-12 17:57

Re: Giving back to the team and community: log file database upload

Post by palinka » 2020-12-06 15:29

SorenR wrote:
2020-12-06 15:26
What we do cannot be done easily with the standard 5.x hMailServer as it is missing two triggers; OnHELO and OnClientLogon. You need RvdH's version of 5.6.8 B2505 or 5.7-64bit.
Does that make us jedi masters?

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Giving back to the team and community: log file database upload

Post by SorenR » 2020-12-06 15:33

palinka wrote:
2020-12-06 15:29
SorenR wrote:
2020-12-06 15:26
What we do cannot be done easily with the standard 5.x hMailServer as it is missing two triggers; OnHELO and OnClientLogon. You need RvdH's version of 5.6.8 B2505 or 5.7-64bit.
Does that make us jedi masters?
Nah... Homes Simpson would call us SMRT ... :mrgreen:
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

palinka
Senior user
Senior user
Posts: 4455
Joined: 2017-09-12 17:57

Re: Giving back to the team and community: log file database upload

Post by palinka » 2020-12-06 15:42

SorenR wrote:
2020-12-06 15:33
palinka wrote:
2020-12-06 15:29
SorenR wrote:
2020-12-06 15:26
What we do cannot be done easily with the standard 5.x hMailServer as it is missing two triggers; OnHELO and OnClientLogon. You need RvdH's version of 5.6.8 B2505 or 5.7-64bit.
Does that make us jedi masters?
Nah... Homes Simpson would call us SMRT ... :mrgreen:
Wicked smaahht!

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-07 00:08

palinka wrote:
2020-12-06 15:09

Code: Select all

19,599 212.70.149.4
This is the network I get most hits from. Needless to say, added them to DROP rule on the FW.

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-18 15:51

hMailServerLogImporter1001.7z
Log importer version 1.0.0.1
(1.49 MiB) Downloaded 389 times
Fixed an embarrassing bug in MySql report query and added some optimizations.
For MsSQL, I am trying to do everything in tempdb which on my system is slightly quicker.
For MySql, moving away from an index that took a long time to be created, seems to speed things up too.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Giving back to the team and community: log file database upload

Post by gotspatel » 2020-12-21 13:25

getting error as attached image any help please
Attachments
error.png

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-23 02:39

gotspatel wrote:
2020-12-21 13:25
getting error as attached image any help please
Do you get this error with the latest update that I've posted right before your comment? It should have addressed the date format error that arises on Dec 1st which seems to be your case, judging by the log file date.
Try the latest version and post back if any issues.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Giving back to the team and community: log file database upload

Post by gotspatel » 2020-12-23 06:16

[/quote]

Do you get this error with the latest update that I've posted right before your comment? It should have addressed the date format error that arises on Dec 1st which seems to be your case, judging by the log file date.
Try the latest version and post back if any issues.
[/quote]

yes i used your latest version and still getting this error

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-23 17:20

Where does the value "40777" come from? Somehow, the last file is different from all of the successfully loaded ones, in terms of the log entry date that contains this value.
The software only looks at the lines that match this log entry:

Code: Select all

"DEBUG"	6816	"2020-12-03 00:26:50.199"	"Client connection from X.X.X.X was not accepted. Blocked either by IP range or by connection limit."
The attempt date is parsed in the above format. The IP address X.X.X.X goes into the IP address column, and the date goes into the date column.
If you have something different in the date field, post it here and I'll try to accommodate a different format.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Giving back to the team and community: log file database upload

Post by gotspatel » 2020-12-23 17:44

DrmCa wrote:
2020-12-23 17:20
Where does the value "40777" come from? Somehow, the last file is different from all of the successfully loaded ones, in terms of the log entry date that contains this value.
The software only looks at the lines that match this log entry:

Code: Select all

"DEBUG"	6816	"2020-12-03 00:26:50.199"	"Client connection from X.X.X.X was not accepted. Blocked either by IP range or by connection limit."
The attempt date is parsed in the above format. The IP address X.X.X.X goes into the IP address column, and the date goes into the date column.
If you have something different in the date field, post it here and I'll try to accommodate a different format.
40777 is the session number of imap

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-23 18:55

Since I do not use IMAP, I have no way of knowing the format of the log record that it creates. Could you post a sample of the whole record? I'll see if I can accommodate it.
I have time today and am adding PostgreSQL import, so if you post it soon, I may have a fix before the end of the day.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Giving back to the team and community: log file database upload

Post by gotspatel » 2020-12-24 06:26

DrmCa wrote:
2020-12-23 18:55
Since I do not use IMAP, I have no way of knowing the format of the log record that it creates. Could you post a sample of the whole record? I'll see if I can accommodate it.
I have time today and am adding PostgreSQL import, so if you post it soon, I may have a fix before the end of the day.

Here is a portion if you need full i can upload and share link for you to research.

Code: Select all


"DEBUG"	3764	"2020-11-27 10:06:22.404"	"Creating session 40777"
"TCPIP"	3764	"2020-11-27 10:06:22.404"	"TCP - 43.241.136.150 connected to 192.168.111.253:143."
"DEBUG"	3764	"2020-11-27 10:06:22.404"	"TCP connection started for session 40776"
"IMAPD"	3764	40776	"2020-11-27 10:06:22.420"	"43.241.136.150"	"SENT: * OK IMAPrev1"
"IMAPD"	4592	40776	"2020-11-27 10:06:22.451"	"43.241.136.150"	"RECEIVED: 1.1 CAPABILITY"
"IMAPD"	4592	40776	"2020-11-27 10:06:22.451"	"43.241.136.150"	"SENT: * CAPABILITY IMAP4 IMAP4rev1 CHILDREN IDLE QUOTA SORT ACL NAMESPACE RIGHTS=texk[nl]1.1 OK CAPABILITY completed"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.483"	"43.241.136.150"	"RECEIVED: 2.1 LOGIN mail@mydomain.com ***"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.483"	"43.241.136.150"	"SENT: 2.1 OK LOGIN completed"
"IMAPD"	4592	40776	"2020-11-27 10:06:22.514"	"43.241.136.150"	"RECEIVED: 3.1 CAPABILITY"
"IMAPD"	4592	40776	"2020-11-27 10:06:22.514"	"43.241.136.150"	"SENT: * CAPABILITY IMAP4 IMAP4rev1 CHILDREN IDLE QUOTA SORT ACL NAMESPACE RIGHTS=texk[nl]3.1 OK CAPABILITY completed"
"IMAPD"	3764	40776	"2020-11-27 10:06:22.633"	"43.241.136.150"	"RECEIVED: 4.1 LIST "" """
"IMAPD"	3764	40776	"2020-11-27 10:06:22.633"	"43.241.136.150"	"SENT: * LIST (\Noselect) "." ""[nl]4.1 OK LIST completed"
"IMAPD"	4592	40776	"2020-11-27 10:06:22.662"	"43.241.136.150"	"RECEIVED: 5.1 SELECT INBOX"
"IMAPD"	4592	40776	"2020-11-27 10:06:22.662"	"43.241.136.150"	"SENT: * 1172 EXISTS[nl]* 24 RECENT[nl]* FLAGS (\Deleted \Seen \Draft \Answered \Flagged)[nl]* OK [UIDVALIDITY 1602502611] current uidvalidity[nl]* OK [UNSEEN 1149] unseen messages[nl]* OK [UIDNEXT 1173] next uid[nl]* OK [PERMANENTFLAGS (\Deleted \Seen \Draft \Answered \Flagged)] limited[nl]5.1 OK [READ-WRITE] SELECT completed"
"DEBUG"	3764	"2020-11-27 10:06:22.708"	"Creating session 40778"
"TCPIP"	3764	"2020-11-27 10:06:22.708"	"TCP - 43.241.136.150 connected to 192.168.111.253:143."
"DEBUG"	3764	"2020-11-27 10:06:22.708"	"TCP connection started for session 40777"
"IMAPD"	3764	40777	"2020-11-27 10:06:22.708"	"43.241.136.150"	"SENT: * OK IMAPrev1"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.708"	"43.241.136.150"	"RECEIVED: 6.1 FETCH 1141:1172 (INTERNALDATE UID RFC822.SIZE FLAGS BODY.PEEK[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)])"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.724"	"43.241.136.150"	"SENT: * 1141 FETCH (UID 1141 RFC822.SIZE 76764 FLAGS (\Seen) INTERNALDATE "26-Nov-2020 14:34:07 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {483}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.724"	"43.241.136.150"	"SENT: )"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.740"	"43.241.136.150"	"SENT: * 1142 FETCH (UID 1142 RFC822.SIZE 76764 FLAGS (\Seen) INTERNALDATE "26-Nov-2020 14:34:07 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {483}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.740"	"43.241.136.150"	"SENT: )"
"IMAPD"	4592	40777	"2020-11-27 10:06:22.740"	"43.241.136.150"	"RECEIVED: 1.2 CAPABILITY"
"IMAPD"	4592	40777	"2020-11-27 10:06:22.740"	"43.241.136.150"	"SENT: * CAPABILITY IMAP4 IMAP4rev1 CHILDREN IDLE QUOTA SORT ACL NAMESPACE RIGHTS=texk[nl]1.2 OK CAPABILITY completed"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.740"	"43.241.136.150"	"SENT: * 1143 FETCH (UID 1143 RFC822.SIZE 76944 FLAGS (\Seen) INTERNALDATE "26-Nov-2020 15:09:30 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {429}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.740"	"43.241.136.150"	"SENT: )"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.755"	"43.241.136.150"	"SENT: * 1144 FETCH (UID 1144 RFC822.SIZE 76928 FLAGS (\Seen) INTERNALDATE "26-Nov-2020 15:09:30 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {429}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.755"	"43.241.136.150"	"SENT: )"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.771"	"43.241.136.150"	"SENT: * 1145 FETCH (UID 1145 RFC822.SIZE 76944 FLAGS (\Seen) INTERNALDATE "26-Nov-2020 15:09:30 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {429}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.771"	"43.241.136.150"	"SENT: )"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.771"	"43.241.136.150"	"SENT: * 1146 FETCH (UID 1146 RFC822.SIZE 76928 FLAGS (\Seen) INTERNALDATE "26-Nov-2020 15:09:30 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {429}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.771"	"43.241.136.150"	"SENT: )"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.787"	"43.241.136.150"	"SENT: * 1147 FETCH (UID 1147 RFC822.SIZE 76928 FLAGS (\Seen) INTERNALDATE "26-Nov-2020 15:09:30 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {429}"
"IMAPD"	4404	40777	"2020-11-27 10:06:22.787"	"43.241.136.150"	"RECEIVED: 2.2 LOGIN mail@mydomain.com ***"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.787"	"43.241.136.150"	"SENT: )"
"IMAPD"	4404	40777	"2020-11-27 10:06:22.787"	"43.241.136.150"	"SENT: 2.2 OK LOGIN completed"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.787"	"43.241.136.150"	"SENT: * 1148 FETCH (UID 1148 RFC822.SIZE 76928 FLAGS (\Seen) INTERNALDATE "26-Nov-2020 15:09:30 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {429}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.787"	"43.241.136.150"	"SENT: )"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.802"	"43.241.136.150"	"SENT: * 1149 FETCH (UID 1149 RFC822.SIZE 21544 FLAGS () INTERNALDATE "26-Nov-2020 16:00:27 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {415}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.802"	"43.241.136.150"	"SENT: )"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.802"	"43.241.136.150"	"SENT: * 1150 FETCH (UID 1150 RFC822.SIZE 21544 FLAGS () INTERNALDATE "26-Nov-2020 16:00:27 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {415}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.802"	"43.241.136.150"	"SENT: )"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.818"	"43.241.136.150"	"SENT: * 1151 FETCH (UID 1151 RFC822.SIZE 38009 FLAGS () INTERNALDATE "26-Nov-2020 16:55:40 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {443}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.818"	"43.241.136.150"	"SENT: )"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.818"	"43.241.136.150"	"SENT: * 1152 FETCH (UID 1152 RFC822.SIZE 38009 FLAGS () INTERNALDATE "26-Nov-2020 16:55:40 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {443}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.818"	"43.241.136.150"	"SENT: )"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.818"	"43.241.136.150"	"SENT: * 1153 FETCH (UID 1153 RFC822.SIZE 10042 FLAGS () INTERNALDATE "26-Nov-2020 17:06:05 +0530" BODY[HEADER.FIELDS (date subject from to cc message-id in-reply-to references content-type x-priority x-uniform-type-identifier x-universally-unique-identifier list-id list-unsubscribe)] {445}"
"IMAPD"	3240	40776	"2020-11-27 10:06:22.818"	"43.241.136.150"	"SENT: )"


DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-24 22:22

gotspatel wrote:
2020-12-24 06:26
Here is a portion if you need full i can upload and share link for you to research.
I only need to see one line that says "Client connection from X.X.X.X was not accepted. Blocked either by IP range or by connection limit."
It was not included in your portion.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Giving back to the team and community: log file database upload

Post by gotspatel » 2020-12-25 06:52

DrmCa wrote:
2020-12-24 22:22
gotspatel wrote:
2020-12-24 06:26
Here is a portion if you need full i can upload and share link for you to research.
I only need to see one line that says "Client connection from X.X.X.X was not accepted. Blocked either by IP range or by connection limit."
It was not included in your portion.
There is no Line for blocked connection in whole log

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-25 20:11

This is strange. If you like, you can upload the failing log to somewhere and PM me the link.

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-27 04:08

hMailServerLogImporter1002.7z
(1.74 MiB) Downloaded 427 times
Here is the new version that implements PostgreSQL.
It is refactored properly from the quick and dirty prototype, to allow it to be truly extendable for any DB back end.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Giving back to the team and community: log file database upload

Post by gotspatel » 2020-12-27 06:12

DrmCa wrote:
2020-12-27 04:08
hMailServerLogImporter1002.7z
Here is the new version that implements PostgreSQL.
It is refactored properly from the quick and dirty prototype, to allow it to be truly extendable for any DB back end.
Hi

I downloaded it, Deleted the old Database and tried running this version but something is broken. It is not creating the Database.

If I run it on old script database it throws error of multiple Primary keys.

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-27 15:53

I am not really sure what is happening here. Without seeing those errors I am blind and cannot troubleshoot. This program does not create a database. The database has to exist. It only creates 2x tables in the current version and 1x table in the old version. In MS SQL the old version used Tempdb, but the new version uses the database in the connection string.

First of all, what is your DB back end?
Does the user that you log in with have rights to create tables and insert records into them?
Once I see the errors, I can devise a plan to help you out.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Giving back to the team and community: log file database upload

Post by gotspatel » 2020-12-29 11:42

DrmCa wrote:
2020-12-27 15:53
I am not really sure what is happening here. Without seeing those errors I am blind and cannot troubleshoot. This program does not create a database. The database has to exist. It only creates 2x tables in the current version and 1x table in the old version. In MS SQL the old version used Tempdb, but the new version uses the database in the connection string.

First of all, what is your DB back end?
Does the user that you log in with have rights to create tables and insert records into them?
Once I see the errors, I can devise a plan to help you out.

My backend is MySQL. Yes the user is the Administrator user with complete access.

Please check the error.
Attachments
error.png

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Giving back to the team and community: log file database upload

Post by gotspatel » 2020-12-29 12:27

Try no 2 still no success.
Attachments
error no 2.png

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2020-12-30 16:21

Great, thanks for testing! It was a bug in MySQL. Here is a fix.
Attachments
hMailServerLogImporter1003.7z
MySQL duplicate primary key fix
(1.74 MiB) Downloaded 345 times

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Giving back to the team and community: log file database upload

Post by gotspatel » 2021-01-01 11:50

DrmCa wrote:
2020-12-30 16:21
Great, thanks for testing! It was a bug in MySQL. Here is a fix.
I scrapped my old logs, but tested and here are the results.

Error in open file log processing, Any solution.
error in direct.png

It works fine if dates specified.
Perfect Logging.png

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2021-01-01 22:16

This is by design. It tells me that the last file is being written to.

palinka
Senior user
Senior user
Posts: 4455
Joined: 2017-09-12 17:57

Re: Giving back to the team and community: log file database upload

Post by palinka » 2021-01-02 04:09

DrmCa wrote:
2021-01-01 22:16
This is by design. It tells me that the last file is being written to.
You should try to trap the error and return a message instead. 👍

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2021-01-02 22:02

This is free, open-source software that I wrote on the side of my day work. If you need any more functionality, you can add it to the source.

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

Re: Giving back to the team and community: log file database upload

Post by DrmCa » 2021-01-08 04:31

hMailServerLogImporter1004.7z
(1.75 MiB) Downloaded 356 times
Here is an almost complete re-write.
What has changed:
* The bug with sharing violation reported by gotspatel is fixed.
+ PostgreSQL implemented
+ Now users can create their own reports. 3x sample JSON definitions can be found under .\Reports\ Any JSON file placed there will be treated as a report definition. I do not have schema validation and probably never will, so the format is crucial. The order is dictated by the file name, hence the numbering.
+ Command-line switch /R added, to run reports only, no import
+ In App.Config, users can turn imports on and off, and report parameter defaults can be overridden as well
- The old report generation is gone

User avatar
SorenR
Senior user
Senior user
Posts: 6308
Joined: 2006-08-21 15:38
Location: Denmark

Re: Giving back to the team and community: log file database upload

Post by SorenR » 2021-01-08 06:03

DrmCa wrote:
2021-01-08 04:31
hMailServerLogImporter1004.7z
Here is an almost complete re-write.
What has changed:
* The bug with sharing violation reported by gotspatel is fixed.
+ PostgreSQL implemented
+ Now users can create their own reports. 3x sample JSON definitions can be found under .\Reports\ Any JSON file placed there will be treated as a report definition. I do not have schema validation and probably never will, so the format is crucial. The order is dictated by the file name, hence the numbering.
+ Command-line switch /R added, to run reports only, no import
+ In App.Config, users can turn imports on and off, and report parameter defaults can be overridden as well
- The old report generation is gone
Why not SQLite? Easy, lightweight, embedded and would work across all the different installs of hMailServer databases without having to install new software.

I did actually fiddle a bit with SQLite for my real-time IDS SPAM filters and it works pretty good - I also tried XML as a database ... before choosing the MySQL I already used for hMailServer as I discovered I could reduce the code footprint by using the hMailServer API to write to the database. 8)
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: Giving back to the team and community: log file database upload

Post by gotspatel » 2021-01-09 10:51

DrmCa wrote:
2021-01-08 04:31
hMailServerLogImporter1004.7z
Here is an almost complete re-write.
What has changed:
* The bug with sharing violation reported by gotspatel is fixed.
+ PostgreSQL implemented
+ Now users can create their own reports. 3x sample JSON definitions can be found under .\Reports\ Any JSON file placed there will be treated as a report definition. I do not have schema validation and probably never will, so the format is crucial. The order is dictated by the file name, hence the numbering.
+ Command-line switch /R added, to run reports only, no import
+ In App.Config, users can turn imports on and off, and report parameter defaults can be overridden as well
- The old report generation is gone
SUPER :D

DrmCa
Normal user
Normal user
Posts: 172
Joined: 2011-02-14 21:30

New version uploaded

Post by DrmCa » 2021-05-16 22:38

hMailServerLogImporter1010.7z

What has changed:
* Massive performance tuning. The import is now 6 times faster, per 1 CPU core. On my 2x-way test machine, it is 9x times faster which is probably limited by 1 Gbps LAN and MySQL server.
+ Spamhaus import and report implemented
+ In JSON report definitions, users can now set Active attribute to 0, to skip the report. The default is 1.
Attachments
hMailServerLogImporter.1010.7z
Version 1.0.1.0
(1.76 MiB) Downloaded 275 times

Post Reply