I needed a way to track which Exchange users were remotely retrieving their emails outside of the office on their phones and other email clients, so I pieced together this batch/pseudo VB script that can be ran from the Windows Task Scheduler at midnight. The only dependency/third party app required is the MS Log Parser executable. Also, for the SQL query to filter out the proper internal networks from the log file, you will have to edit the LOCALSUBNET and CHARLENGTH variables.
References:
http://serverfault.com/questions/558215/find-out-if-user-logged-in-to-owa-read-his-emails
http://stackoverflow.com/questions/203090/how-to-get-current-datetime-on-windows-command-line-in-a-suitable-format-for-us
http://stackoverflow.com/questions/2954359/dos-batch-programming-howto-get-and-display-yesterday-date
https://community.spiceworks.com/scripts/show/46-add-send-email-to-any-vbscript
https://blogs.msdn.microsoft.com/carlosag/2010/03/25/analyze-your-iis-log-files-favorite-log-parser-queries/
http://www.paulsadowski.com/wsh/cdo.htm
:: Script to parse yesterday's Exchange IIS log file, export all externally connected
:: mail clients to a csv file, and then email it out using a VBScript.
:: Author: Nathan Thomas
:: Date: 03/28/2016
::
@ECHO OFF
:: Get yesterday's date
set m=%date:~-7,2%
set /A m -= 1
set yesterday=%date:~-10,2%-%m%-%date:~-4,4%
:: Split date into variables
for /f "tokens=1-3 delims=-" %%a in ("%yesterday%") do (
set MM=%%a
set DD=%%b
set YYYY=%%c
)
set YY=%YYYY:~2,2%
set LOGFILENAME="C:\inetpub\logs\LogFiles\W3SVC1\u_ex%YY%%MM%%DD%.log"
:: MS IIS Log Parser Components
set OUTPUT="C:\external_mail_clients.csv"
set APPDIR="C:\Program Files (x86)\Log Parser 2.2"
:: Edit these variables to filter out your internal network from the results
:: Examples
:: For 10.0.0.0/8 Network -> LOCALSUBNET=10. CHARLENGTH=3
:: For 10.1.1.0/24 Network -> LOCALSUBNET=10.1.1. CHARLENGTH=7
:: For 172.16.0.0/16 Network -> LOCALSUBNET=172.16. CHARLENGTH=7
:: For 192.168.10.0/24 Netowrk -> LOCALSUBNET=192.168.10. CHARLENGTH=11
set LOCALSUBNET=10.1.
set CHARLENGTH=5
:: Take your pick
:: Shorter, easier to read report
set SQL1="SELECT date, time, cs-method, s-port, cs-username, c-ip, cs(User-Agent), time-taken INTO %OUTPUT% FROM %LOGFILENAME% WHERE (((NOT SUBSTR(c-ip,0,%CHARLENGTH%)='%LOCALSUBNET%') AND (NOT c-ip='::1')) AND (NOT c-ip='127.0.0.1'))"
:: Longer, slightly harder to read verbose report
set SQL2="SELECT date, time, cs-method, cs-uri-stem, cs-uri-query, s-port, cs-username, c-ip, cs(User-Agent), time-taken INTO %OUTPUT% FROM %LOGFILENAME% WHERE (((NOT SUBSTR(c-ip,0,%CHARLENGTH%)='%LOCALSUBNET%') AND (NOT c-ip='::1')) AND (NOT c-ip='127.0.0.1'))"
:: Execute log parser command
%APPDIR%\logparser.exe -i:iisw3c %SQL2% -o:csv
:: Enter your email server information below
:: Create send authenticated email VBScript
set VBMAIL=C:\send_mail.vbs
@ECHO Set oMessage = CreateObject("CDO.Message") >> %VBMAIL%
@ECHO oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 >> %VBMAIL%
@ECHO oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "server1.fqdn.com" >> %VBMAIL%
@ECHO oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 >> %VBMAIL%
@ECHO oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "bob@server1.fqdn.com" >> %VBMAIL%
@ECHO oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "aabbcc112233" >> %VBMAIL%
@ECHO oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 >> %VBMAIL%
@ECHO oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False >> %VBMAIL%
@ECHO oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60 >> %VBMAIL%
@ECHO oMessage.From = "bob@server1.fqdn.com" >> %VBMAIL%
@ECHO oMessage.To = "admin@server1.fqdn.com" >> %VBMAIL%
@ECHO oMessage.Subject = "Exchange Server External Mail Client Requests" >> %VBMAIL%
@ECHO oMessage.TextBody = "Here is a list of all of the clients that externally connected to the Exchange Mail Server on %yesterday% to retrieve their mail remotely." >> %VBMAIL%
@ECHO oMessage.AddAttachment %OUTPUT% >> %VBMAIL%
@ECHO oMessage.Configuration.Fields.Update >> %VBMAIL%
@ECHO oMessage.Send >> %VBMAIL%
:: Execute send mail VBScript
CScript //nologo %VBMAIL%
:: Cleanup
DEL /F /Q /S %OUTPUT%
DEL /F /Q /S %VBMAIL%