Solved

How to find qty of emails sent to an alias

Posted on 2010-08-31
13
624 Views
Last Modified: 2012-06-21
I would like to know how to find out how many emails get sent to an email alias? We have numerous email aliases in our organization and would like to get some statistics on the number of emails sent to those individual emails. Is there an easy way to find this out?
0
Comment
Question by:tparus
  • 7
  • 5
13 Comments
 
LVL 28

Expert Comment

by:sunnyc7
ID: 33570418
Download Log Parser
http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07

Install it

Open Log Parser
Start > programs > Log Parser
run this

logparser -q -i:w3c "SELECT* FROM c:\temp2\ex*.log WHERE Recipient-Address like `user1@company'" > c:\export.txt

Where c:\temp\ex*.log is where your exchange message tracking logs are

Check more here about setting up message tracking logs
http://www.msexchange.org/tutorials/Exchange-2003-Message-Tracking-Logging.html

Ref. log parser
http://msexchangetips.blogspot.com/2006/09/exchange-exporting-and-querying.html
0
 
LVL 6

Expert Comment

by:r3nder
ID: 33571244
The code to filter on items created today would look something like what I have below. this is for outlook
string sFilter = "[CreationTime] > '" + 

    DateTime.Today.ToShortDateString() + " " + 

    DateTime.Today.ToShortTimeString() + "'";

ns = app.GetNamespace("MAPI");

Outlook.MAPIFolder DefaultFolder = ns.GetDefaultFolder(Outlook.olDefaultFolders.olFolderSentMail);

Outlook.Items items = DefaultFolder.Items;

Outlook.Items filteredItems = items.Restrict(sFilter);

 

Outlook.MailItem mail = null;

 

for (int i = 1; i <= filteredItems.Count; i++)

{

    mail = filfteredItems[i];

    MsgBox mail.Subject;

    mail = null;

}

Open in new window

0
 

Author Comment

by:tparus
ID: 33571966
This query is not working correctly. When I run it i get an error stating a Syntax error extra token(s) after query: 'Files\Exchsrvr\Exc01.log\*.log'

the query is below:

logparser -q -i:w3c "SELECT * FROM c:\Program Files\Exchsrvr\Exc01.log\20100831.log WHERE Recipient-Address like `user1@company'" > c:\export.txt

I have tried every different way to write this and still doesn't work. I tried using Message Tracking Center which works but it does not count the emails and I have to do it by hand which wont work either.

Suggestions?
0
 
LVL 28

Expert Comment

by:sunnyc7
ID: 33571976
thats not the log which you should be pulling up :)
We are looking for message tracking logs

which are in
c:\windows\system32\logs\SMTPSVC
0
 
LVL 28

Expert Comment

by:sunnyc7
ID: 33571979
c:\Program Files\Exchsrvr\Exc01.log\20100831.log

>> These are database transaction logs.
Not message tracking logs.
0
 

Author Comment

by:tparus
ID: 33572205
I don't think that is correct. When I go to the location you suggest it does not know the column\field Recipient-Address.

I tried your path which is a little different but it kind of worked but did not give me the results I asked for.

I need to get a count of how many emails a certain email address gets. How can I do this?

I tried using Message Tracking Center but I have to manually count all the emails and that is not going to work since my director wants history for one year. This Logparser seems like it could work but I need the correct query to get the results I need but there is some confusion on where to get this information.

Thank You in advance for all you help you provide.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:sunnyc7
ID: 33572238
Ok. Sorry again :(
It's

C:\WINDOWS\system32\LogFiles\W3SVC1\*.log

@didnt know you want history for one year, the script needs to be modified.
will post back later tomorrow.
0
 

Author Comment

by:tparus
ID: 33631008
I actually just need it to work. Can you help?
0
 
LVL 28

Expert Comment

by:sunnyc7
ID: 33632006
Download Log Parser
http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07

Install it

Open Log Parser
Start > programs > Log Parser
run this

logparser -q -i:w3c "SELECT * FROM C:\WINDOWS\system32\LogFiles\W3SVC1\*.log WHERE Recipient-Address like `user1@company'" > c:\export.txt
0
 

Author Comment

by:tparus
ID: 33632198
OK, I finally got this to work but now I am a bit confused on how to tell the script to pull a date range. When I look at the log file in Excel it shows # Date but when I run it using that column name and a Between statement it returns nothing. How do I write the syntax for a between statement in the logparser?

Also, are you sure about the location of the logs? The Recipient-Address header is in the log files located at c:\Program Files\Exchsrvr\Exc01.log\20100831.log. When I look at the location you suggest it looks like this logs for the IIS and not exchange plus non of those logs have the Recipient-Address header. Inside those files the headers are as follows: Time, c-ip, cs-method, cs-uri-stem, sc-status.

You are the Sage so I don't want to question but it just doesn't seem right.

Thanks again for all your help on this.
0
 
LVL 28

Expert Comment

by:sunnyc7
ID: 33632658
<-- strokes my imaginary beard...

a) Also, are you sure about the location of the logs?
c:\Program Files\Exchsrvr\Exc01.log\20100831.log
>> Yes Very sure.
Those are exchange transaction logs. Whenever exchange receives emails, it will first write to a transaction log and then commit it to the exchange .EDB

We are looking for message tracking logs.
they are located here
C:\WINDOWS\system32\LogFiles\W3SVC1\

b) Date wise.
the command is going to look like this

logparser -q -i:w3c "SELECT * FROM C:\WINDOWS\system32\LogFiles\W3SVC1\*.log WHERE ((Recipient-Address like `user@domain.org') && (to_timestamp(date,time)
between timestamp('2010/02/01 10:11:00', 'yyyy/MM/dd hh:mm:ss') and timestamp('2010/02/22 10:11:00', 'yyyy/MM/dd hh:mm:ss'))"

but I am having trouble executing this - as it errors out in between or something.

quick shortcut.

copy the files from here
C:\WINDOWS\system32\LogFiles\W3SVC1\
to
c:\temp\date1\

and then run the old one.
logparser -q -i:w3c "SELECT * FROM c:\temp\date1\*.log WHERE Recipient-Address like `user@domain.org' > c:\results\date1.txt

and rinse
and repeat

this is a quicker solution. I think I will be able to figure out the date range soon.

0
 

Author Comment

by:tparus
ID: 33648329
I have a related\unrelated question, when I look at the log location I have about 3 years worth of logs, can I copy and remove these files so there is less to search when I am running the log parser?
0
 
LVL 28

Accepted Solution

by:
sunnyc7 earned 125 total points
ID: 33649053
yes you can

OR
You can copy the logs for appropriate date range to this folder.
c:\temp\date1\

and then change the script to search for only that part which is within the date range
ogparser -q -i:w3c "SELECT * FROM c:\temp\date1\*.log WHERE Recipient-Address like `user@domain.org' > c:\results\date1.txt
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
In this video we show how to create a Distribution Group in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Recipients >>…
In this video we show how to create an email address policy in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.:  First we need to log into the Exchange Admin Center. Navigate to the Mail Flow…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now