Report of all recipient addresses?

General eFa discussion
Post Reply
fencepost
Posts: 7
Joined: 11 Oct 2017 16:13

Report of all recipient addresses?

Post by fencepost »

I'm wondering if anyone has a good way to get a report of all distinct recipient email addresses out of Mailscanner or Mailwatch. I'm asking because the backend mail host actually accepts multiple formats (initiallastname, initial.lastname, firstname.lastname) and I need to set up forwarding because that backend is going away (along with the bought-out business). Select users are going to have their email forwarded to the new company, but I'd like to simplify my life in terms of what forwarding I have to set up because it's a horrible 1-at-a-time web interface for setting it up. It's not clear if I'm going to be able to keep the EFA server up and running to do that forwarding, particularly over the course of months.

Even something as simple as expanding the "top recipients by volume" from 10 to 100 would be more than enough, I could easily take it from there.

Any suggestions? SSH in and command line is fine, I'm just not informed enough about how Mailscanner stores things to start poking.

Edit: Figured out a way to do what I needed - not graceful, but likely just a one-time thing anyway. Took longer to write this up for future reference than to actually do it.
  • SSH'd in and set a password for root
  • Got the root MySQL/MariaDB password from /etc/EFA-Config
  • Got into Webmin (using the root password)
  • Got into the list of databases using the database password
  • Chose the mailscanner database, maillog table
  • At the bottom, Export as CSV
  • Only field is to_address
  • Display in browser
  • Copy the list of addresses, drop it into Excel
  • On the Data tab, Filter with the column selected
  • Click the dropdown at the right end of to_address, Text Filters, Does Not Contain, excluded commas (to get rid of multi-recpipient entries)
  • Copied the resulting shorter list over a couple of columns
  • Sort the list
  • Turn on Advanced Filter for the new list, with Copy to New Location (yet another couple columns over) and Unique values checked
  • I now had a list of unique single-recipient addresses along with the two working lists.
  • In the cell next to the first data cell in my new unique items list, add a formula "=countif(D:D,G2)" then drag the bottom corner of the cell all the way down to the end of my shortest list. I believe copying the cell and pasting over the entire range would have the same effect. This gave me a count of the number of times each address was the sole recipient of a message.
  • Copy the two columns for this shortest list and paste in once again elsewhere to convert the formula into fixed values. There's another way to do it, but I couldn't be bothered looking it up.
  • Delete all those working columns, all I need is this list of how many messages have come in for each recipient in the last 2 months. Mostly I'm using this to see which aliases are actually in use for setting up forwarding, etc.
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Report of all recipient addresses?

Post by pdwalker »

Code: Select all

mysql -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'` mailscanner \
      -e "select distinct(from_address) from maillog order by 1" > /tmp/from.txt
Then scp the /tmp/from.txt to your computer and you're done.
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Report of all recipient addresses?

Post by pdwalker »

oh, receipient, not sender. Sorry.

Code: Select all

# mysql -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'` mailscanner \
        -e "select to_address from maillog order by 1" > /tmp/to.txt

# sed -e 's/,/\n/g' /tmp/to.txt | sort | uniq > /tmp/to-unique.txt
Now scp the /tmp/to-unique.txt file to your computer.
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Report of all recipient addresses?

Post by pdwalker »

Apparently, I'm having a reading comprehension problem...

Change the last command to:

Code: Select all

# sed -e 's/,/\n/g' /tmp/to.txt | sort | uniq -c | sort -r > /tmp/top-recipients.txt
Thus, you'll get both the top recipients and the number of times they've received a message.
Post Reply