Koozali.org: home of the SME Server

statistiche email inviate per user

Offline studydata

  • 3
  • +0/-0
statistiche email inviate per user
« on: April 27, 2021, 12:29:20 PM »
Buongiorno avrei necessità di avere una statistica giornaliera che mi rappresenti per ogni singola riga:
Utente sme   ----- numero email inviate verso remote --- numero di email verso local

ho bisogno di capire se qualcuno sta inviando spam all'esterno ( non è detto ma voglio prevenire)

Ho leggo tonnellate di documentazione su
es https://wiki.koozali.org/Mail_log_file_analysis

ma non ho trovato semplicemente il modo di generare un report solo delle email in uscita per utente.

Potete aiutarmi ?
Grazie Alessio

Offline Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: statistiche email inviate per user
« Reply #1 on: April 28, 2021, 04:07:03 AM »
Hello
sorry for the English

https://wiki.koozali.org/Email_Statistics is a résumé of all you can have as addons to get what you want.

but I know one that could do soemthing like you want, and it is not therE: isoqlog : https://wiki.koozali.org/Isoqlog

alternative would be to write a script to parse qmail log

Offline mmccarn

  • *
  • 2,626
  • +10/-0
Re: statistiche email inviate per user
« Reply #2 on: April 28, 2021, 03:36:32 PM »
Count queued emails (queued for local or remote delivery) by date, IP address, sender, and "local" (recipient domain matches SME domain) or "remote" (recipient domain does not match SME domain).

I've included IP address, as the qpsmtpd logterse entries do not appear to include the user account used to authenticate for SMTP, only the email address in the 'From' field of the email.

Code: [Select]
export LC_ALL=C;  \
myDomain=$(config get DomainName); \
printf "count\tdate\tIP_Address\tSender\tDestination\n"
grep -h 'logterse.*queued[^$]' $(find /var/log/qpsmtpd /var/log/sqpsmtpd/ -type f -name "@*" -o -name current) \
| tai64nlocal \
| sed -e 's/[<>]//g' \
| awk -v d=$myDomain -F"[\t ]" ' \
  {split ($11,rdomain,"@"); \
  rdomain[2] == d ? dest="local" : dest="remote"; \
  msg=$1 "\t" $7 "\t" $10 "\t" dest; \
  count[msg]++; \
  } \
  END \
  {                                \
  for (j in count)                 \
  print count[j] "\t" j;           \
  }' \
  | grep '@'${myDomain} \
  | sort -rn

Quote from: Explanation
# I'm not sure, but I needed this for other one-line sme commands...
export LC_ALL=C;  \

# get the local SME domain, for use determining if the email destination is local or remote
myDomain=$(config get DomainName); \

# print the header row for the output
printf "count\tdate\tIP_Address\tSender\tDestination\n"

# get the 'logterse' entries from /var/log/qpsmtpd and /var/log/sqpsmtpd where 'queued' is not the last word on the line
# (The only time queued is the last word on the line is when the line represents an smtp connection that was "denied before queued")
grep -h 'logterse.*queued[^$]' $(find /var/log/qpsmtpd /var/log/sqpsmtpd/ -type f -name "@*" -o -name current) \

# convert the tai64n date to human readable
| tai64nlocal \

# remove the "<" and ">" surrounding email addresses
| sed -e 's/[<>]//g' \

# set the awk var "d" to "$myDomain" and parse the input data on either a tab or a space
| awk -v d=$myDomain -F"[\t ]" ' \

# split the 11th field (recipient email) on '@' and put the results in the array 'rdomain'
  {split ($11,rdomain,"@"); \

# if the recipient domain is the local SME domain, assign dest=local.  Otherwise assign dest=remote
  rdomain[2] == d ? dest="local" : dest="remote"; \

# set a variable to <date ($1)> <tab> <ip address($7)> <tab> <recipient email ($10)> <tab> <local or remote>
# make changes to this line to change the output
# for example -
# remove $1 to ignore the date
# remove $7 to ignore the IP address,
# remove $10 to ignore the sender IP address
# replace "dest" with "rdomain[2]" to see the recipient domain instead of "local" or "remote"
# If you change this line you probably want to change the header row, too
  msg=$1 "\t" $7 "\t" $10 "\t" dest; \

# create an array named "count" with the name (msg) of each value from above. increment it each time we get the same value
  count[msg]++; \
  } \

# process the entire input
  END \
  {                                \

# Step through the array we created above
  for (j in count)                 \

# output the count, then the value (the message summary from above)
  print count[j] "\t" j;           \
  }' \

# the output up to here will include both in-bound and outbound email.  This line looks for a sender who is using your local SME domain
# (note: if you remove $10 above, this filter will not find anything and the output will be empty)
  | grep '@'${myDomain} \

# sort the results in descending order of the count
  | sort -rn

Offline Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: statistiche email inviate per user
« Reply #3 on: April 28, 2021, 06:34:00 PM »
only limit by checking on qpsmtpd log is you miss all the daemon users. So all php app that are not configured to use a smtp will send using php mail function and are intercepted by qmail not qpsmtpd.

one way to do is to use the smeserver-sendmail-wrapper contrib to use qpsmtpd in place of qmail for mail function. 

Offline studydata

  • 3
  • +0/-0
Re: statistiche email inviate per user
« Reply #4 on: April 29, 2021, 12:15:24 PM »
I've included IP address, as the qpsmtpd logterse entries do not appear to include the user account used to authenticate for SMTP, only the email address in the 'From' field of the email.

Thanks, that's exactly my problem.
I can't get a report where I have a user, number of mails sent to local and to remote.
can I modify the logterse to add the user AUTH (nobody sends from the app or from an unauthenticated user)?



Offline studydata

  • 3
  • +0/-0
Re: statistiche email inviate per user
« Reply #5 on: April 29, 2021, 02:44:07 PM »


but I know one that could do soemthing like you want, and it is not therE: isoqlog : https://wiki.koozali.org/Isoqlog

I try it, perfect !

thanks all for help !

Offline Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: statistiche email inviate per user
« Reply #6 on: April 29, 2021, 06:28:21 PM »
Quote
(nobody sends from the app or from an unauthenticated user)?


unless you have no website or php is deactivated on all ibays, you do not want to just assume no mails come from webapps. That is one of the first sources of spam : compromised php webapp  with compromised client. 

i let mmccarn answer for the AUTH