Creating Graphical Reports for Exchange 2007 (Part 2)



If you would like to read the other parts in this article series please go to:

Reports using Log Parser with the Message Tracking Logs

Let us now use the Message Tracking Logs. These are the logs to choose if you are looking for information regarding users and total bytes. For this article I used the Message Tracking Logs from an Edge server. In case there aren’t any Exchange Edge servers deployed, the logs from the Internet facing Hub Transport server are also good.

One of the most common questions messaging administrators ask is; “Who are the internal users responsible for sending the most mail?” The answer is easily obtained by running the next piece of logparser code.

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TOP 10 sender-address, Count(*) AS messagesSent FROM C:\Progra~1\Microsoft\Exchan~1\TransportRoles\Logs\MessageTracking\MSG*.log WHERE recipient-status LIKE '250%%' AND connector-id='edgesync - default-first-site-name to internet' GROUP BY sender-address ORDER BY messagesSent DESC" -rtp:-1 -i:CSV -nSkipLines:4 -o:DATAGRID

Notice that I am filtering data by using the connector-id='edgesync - default-first-site-name to internet'. This way I can easily obtain data from the outbound messages or the inbound messages, depending on the connector I choose. Figure 14 shows the top internal senders.

Set featured image
Figure 14: Top internal senders (#messages)

Although the previous datagrid showed the top senders in terms of number of messages sent, one might be interested in the same result, but in terms of volume size of those messages. By analyzing the same logs, logparser can produce the chart depicted in Figure 15.

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TOP 10 sender-address, DIV(Sum(total-bytes),1048576) AS TotalBytes(MB) INTO TopVolumeSenders.gif FROM C:\Progra~1\Microsoft\Exchan~1\TransportRoles\Logs\MessageTracking\MSG*.log WHERE recipient-status LIKE '250%%' AND connector-id='edgesync - default-first-site-name to internet' GROUP BY sender-address ORDER BY TotalBytes(MB) DESC" -chartType:Barclustered3d -i:CSV -nSkipLines:4 -o:CHART


Figure 15: Top internal senders (volume)

We can also combine the previous results, showing both the top senders in terms of number of messages, but also the volume of traffic they generated. The output of the following command is Figure 16.

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TOP 10 sender-address, Count(*) AS messagesSent, DIV(Sum(total-bytes),1048576) AS TotalBytes(MB) INTO TopSendersCombined.gif FROM C:\Progra~1\Microsoft\Exchan~1\TransportRoles\Logs\MessageTracking\MSG*.log WHERE recipient-status LIKE '250%%' AND connector-id='edgesync - default-first-site-name to internet' GROUP BY sender-address ORDER BY messagesSent DESC" -chartType:BarClustered3D -i:CSV -nSkipLines:4 -o:CHART


Figure 16
: Top internal senders combined (#messages, TotalBytes)

The answer to another common question, “Who are the users inside my organization receiving the most mail?” These can be obtained by the combined chart shown in Figure 17, which is the result from the next logparser query.

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TOP 10 recipient-address, Count(*) AS messagesRec, DIV(Sum(total-bytes),1048576) AS TotalBytes(MB) INTO TopReceiversCombined.gif FROM C:\Progra~1\Microsoft\Exchan~1\TransportRoles\Logs\MessageTracking\MSG*.log WHERE recipient-status LIKE '250%%' AND connector-id='edgesync - inbound to default-first-site-name' GROUP BY recipient-address ORDER BY messagesRec DESC" -chartType:BarClustered3d -i:CSV -nSkipLines:4 -o:CHART


Figure 17: Top internal receivers combined (#messages, TotalBytes)

The next query will produce an aggregate view (Figure 18) of all the inbound and outbound traffic in terms of volume bytes (Mbytes, to be more precise).

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT CASE connector-id WHEN 'edgesync - default-first-site-name to internet' THEN 'MB Sent' WHEN 'edgesync - inbound to default-first-site-name' THEN 'MB Received' ELSE connector-id END AS connector-id2, DIV(Sum(total-bytes),1048576) AS TotalBytes(MB) INTO totalbytessendreceive.gif FROM C:\Progra~1\Microsoft\Exchan~1\TransportRoles\Logs\MessageTracking\MSG*.log WHERE recipient-status LIKE '250%%' GROUP BY connector-id2" -i:CSV -nSkipLines:4 -o:CHART  -chartType:Column3D -chartTitle:"Total Bytes Sent/Received (MB)"


Figure 18: Total Bytes Sent/Received (MB)

On the first reports of this section we covered the internal users. If we want to extract similar information, but regarding the external users, we just have to adjust the logparser a little bit. For instance, who are the external users who send most mail to my organization?

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TOP 10 sender-address, Count(*) AS messagesRec FROM C:\Progra~1\Microsoft\Exchan~1\TransportRoles\Logs\MessageTracking\MSG*.log WHERE recipient-status LIKE '250%%' AND connector-id='edgesync - inbound to default-first-site-name' GROUP BY sender-address ORDER BY messagesRec Desc" -rtp:-1 -i:CSV -nSkipLines:4 -o:DATAGRID


Figure 19: Top external senders (#messages)

The MicrosoftExchange329e71ec88ae4615bbc36ab6ce41109e@mycorp.org that appears in the previous datagrid is an internal account responsible for sending Delivery Status Notifications (DSN). If you prefer to filter it out, just add the following condition to the previous query:

AND sender-address<>’ MicrosoftExchange329e71ec88ae4615bbc36ab6ce41109e@mycorp.org’

And to whom are my users sending mail to? What are the top external receivers in terms of number of messages and volume bytes?

The following logparser query will generate the combined chart depicted in Figure 20.

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TOP 10 recipient-address, Count(*) AS messagesSent, DIV(Sum(total-bytes),1048576) AS TotalBytes(MB) INTO TopExternalReceiversCombined.gif from C:\Progra~1\Microsoft\Exchan~1\TransportRoles\Logs\MessageTracking\MSG*.log WHERE recipient-status LIKE '250%%' AND connector-id='edgesync - default-first-site-name to internet' GROUP BY recipient-address ORDER BY messagesSent DESC" -chartType:BarStacked3d -i:CSV -nSkipLines:4 -o:CHART


Figure 20: Top External Receivers Combined (#messages, TotalBytes)

Reports using Log Parser with the IIS Logs

The last Log Parser reports that this article covers will be obtained using the IIS logs. Although the IIS logs are not generated by Exchange Server, they contain invaluable information regarding Outlook Web Access (OWA), Outlook Anywhere, Exchange ActiveSync and even Entourage, in case you have some Macs accessing Exchange Server.

These logs reside on the Exchange CAS servers that users connect to (directly or indirectly), in order to access OWA or to synchronize their mobile devices.

The first query outputs the chart depicted in Figure 21, showing the users who connect the most to the internal Exchange CAS servers, in order to consume OWA.

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TOP 10 cs-username, Count(*) AS OWAHits INTO OWAActivity.gif FROM C:\Inetpub\logs\LogFiles\W3SVC1\u_ex*.log WHERE cs-uri-stem LIKE '/OWA/' AND cs-username IS NOT NULL GROUP BY cs-username ORDER BY OWAHits DESC" -charttype:Bar3D -o:CHART -view:ON


Figure 21: Top OWA users

To obtain a report of the top ActiveSync users, the next logparser command will do the trick. The results are showed in Figure 22 in the form of a datagrid.

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT cs-username, Count(*) AS EASHits FROM C:\Inetpub\logs\LogFiles\W3SVC1\u_ex*.log WHERE cs-uri-stem LIKE '%%Microsoft-Server-ActiveSync%%' AND cs-username IS NOT NULL GROUP BY cs-username ORDER BY EASHits DESC" -rtp:-1 -o:DATAGRID


Figure 22: Top EAS users

The IIS logs not only contain information about the users who consume ActiveSync, but also about what the devices used to connect to Exchange Server are. That particular information is obtained through the field cs(user-agent), used in the following query:

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT cs(user-agent), count(*) AS Devices INTO devices.gif FROM C:\Inetpub\logs\LogFiles\W3SVC1\u_ex*.log WHERE cs-uri-stem LIKE '%%microsoft-server-activesync%%' AND cs-username IS NOT NULL AND cs(User-Agent) IS NOT NULL GROUP BY cs(User-Agent) ORDER BY Devices DESC" -charttype:pieexploded3d -ChartTitle:"Device Activity by Type" -categories:OFF

Apparently, the devices in my logs identify themselves as “generic” devices (MSFT-PPC), as you can confirm in the output picture (Figure 23).


Figure 23: Mobile Devices

Another useful information is the distribution of users accessing OWA during the day. To generate an OWA Hourly Hits chart (Figure 24), we run the next logparser query:

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TO_STRING(time, 'HH') AS Hour, COUNT(*) AS Hits INTO hitPerSecond.jpg FROM C:\Inetpub\logs\LogFiles\W3SVC1\u_ex*.log GROUP BY Hour ORDER BY Hour ASC" -i:IISW3C -o:CHART -chartType:ColumnClustered -chartTitle:"Hourly Hits" -groupSize:420x280


Figure 24: OWA Usage

Time Frames

Until now, the examples I showed all use the available logs to generate the output. I did not worry about narrowing on a specific time frame in the Exchange 2007 logs. But there is a way of doing it, which involves the use of the WHERE clause to restrict the time frame.

In order to use WHERE, there is some string manipulation around the date and time fields from the logs, since the format of these specific fields can vary on the different Exchange logs available.

As an example, the table bellow provides Log Parser conversion expressions to extract date and/or time from the Protocol Logs and Message Tracking Logs (IIS logs are a little bit different).

For the Agent Logs, the date and time field is called Timestamp. To use the same expressions listed in Table 2, replace [#Fields: date-time] by [#Fields: Timestamp].

Extracting

Log Parser expression

Format

Date

EXTRACT_PREFIX([#Fields: date-time],0,'T')

YYYY-MM-DD

Time

EXTRACT_PREFIX(TO_STRING(EXTRACT_SUFFIX([#Fields: date-time],0,'T')),0,'.')

HH:MM:SS

Date & time

TO_TIMESTAMP ( EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,'.') , 'yyyy-mm-ddThh:mm:ss')

TO_TIMESTAMP (Log Parser time stamp type)

Table 2 – Common Exchange logs

Let us now re-run the top internal senders query, but just from the messages sent after ‘2009-09-17 11:30:00’. The command would look like this:

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TOP 10 sender-address, Count(*) AS messagesSent FROM C:\Progra~1\Microsoft\Exchan~1\TransportRoles\Logs\MessageTracking\MSG*.log WHERE recipient-status LIKE '250%%' AND connector-id='edgesync - default-first-site-name to internet' AND TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,'.') , 'yyyy-mm-ddThh:mm:ss') > TO_TIMESTAMP ('2009-09-17 11:30:00','yyyy-mm-dd hh:mm:ss') GROUP BY sender-address ORDER BY messagesSent Desc" -rtp:-1 -i:CSV -nSkipLines:4 -o:DATAGRID

Summary

This concludes part 2 of this series and is also the end of the reports generated with Log Parser. Log Parser is a really powerful tool, I have just unveiled some of its capabilities. For instance, I suggest that you have some more fun by playing around with the –chartType parameter, modifying some of the carts built in part 1 and part 2 of this article.

Related Links

If you would like to read the other parts in this article series please go to:

Rui Silva

Share
Published by
Rui Silva

Recent Posts

What are the potential disadvantages of SSL/TLS?

There’s wide consensus on the benefits of SSL/TLS. However, not as much attention has been given to SSL/TLS disadvantages.

1 day ago

Exploring native software inventory logging in Windows Server

Windows Server has built-software inventory logging that can be very useful. Here’s how to use this little-known feature.

2 days ago

Passwordless authentication: Safer, better, and about time

Passwordless authentication has quickly become one of the primary means by which users access their laptops, phones, and tablets because…

2 days ago

Automated Incident Response in Office 365 ATP simplifies cybersecurity

Microsoft has pumped up Office 365 Advanced Threat Protection with a new feature, Automated Incident Response. Here’s what you need…

2 days ago

IFA 2019: Smart TVs and even smarter wearables unveiled

What will be in your living room or on your wrist this year? It may very likely be one of…

3 days ago

Consider these SD-WAN technologies for faster, more reliable networking

As virtualization becomes a major part of organizations’ infrastructure, these SD-WAN technologies provide faster and more reliable networking solutions.

3 days ago