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 [email protected] 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<>’ [email protected]

 

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:420×280

 


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:

 

 

About The Author

Leave a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Scroll to Top