Copy Exchange Logs and Parse

cd E:\Exch_Logs
"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING(EXTRACT_SUFFIX([#Fields: date-time],0,'T')),0,'.'), 'hh:mm:ss')),3600) AS Hour, COUNT(*) AS [Messages per Hour] INTO E:\Exch_Logs\srv-001_Result_Hourly.csv from E:\Exch_Logs\Tracking_Logs\srv-001\Working\*.log where (event-id='RECEIVE') GROUP BY Hour ORDER BY Hour ASC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING(EXTRACT_SUFFIX([#Fields: date-time],0,'T')),0,'.'), 'hh:mm:ss')),3600) AS Hour, COUNT(*) AS [Messages per Hour] INTO E:\Exch_Logs\srv-002_Result_Hourly.csv from E:\Exch_Logs\Tracking_Logs\srv-002\Working\*.log where (event-id='RECEIVE') GROUP BY Hour ORDER BY Hour ASC" -i:CSV -nSkipLines:4 -o:csv


"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,'T'), 'yyyy-MM-dd')) AS Date,COUNT(*) AS [Daily Email Traffic] INTO E:\Exch_Logs\srv-002_Total_Traffic.csv from E:\Exch_Logs\Tracking_Logs\srv-002\Working\*.log where (event-id='RECEIVE') GROUP BY Date ORDER BY Date ASC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,'T'), 'yyyy-MM-dd')) AS Date,COUNT(*) AS [Daily Email Traffic] INTO E:\Exch_Logs\srv-001_Total_Traffic.csv from E:\Exch_Logs\Tracking_Logs\srv-001\Working\*.log where (event-id='RECEIVE') GROUP BY Date ORDER BY Date ASC" -i:CSV -nSkipLines:4 -o:csv


"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,'T'), 'yyyy-MM-dd')) AS Date,COUNT(*) AS [Daily Email Traffic] INTO E:\Exch_Logs\srv-002_Total_Traffic.csv from E:\Exch_Logs\Tracking_Logs\srv-002\Working\*.log where (event-id='RECEIVE') GROUP BY Date ORDER BY Date ASC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,'T'), 'yyyy-MM-dd')) AS Date,COUNT(*) AS [Daily Email Traffic] INTO E:\Exch_Logs\srv-001_Total_Traffic.csv from E:\Exch_Logs\Tracking_Logs\srv-001\Working\*.log where (event-id='RECEIVE') GROUP BY Date ORDER BY Date ASC" -i:CSV -nSkipLines:4 -o:csv


"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT EXTRACT_PREFIX(remote-endpoint,0,':') as IP,REVERSEDNS(EXTRACT_PREFIX(remote-endpoint,0,':')) as Name,Count(*) as Hits INTO E:\Exch_Logs\srv-001_Sender_Host.csv from E:\Exch_Logs\Protocol_Logs\Receive\srv-001\Working\*.log WHERE data LIKE '%%EHLO%%' GROUP BY IP ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT EXTRACT_PREFIX(remote-endpoint,0,':') as IP,REVERSEDNS(EXTRACT_PREFIX(remote-endpoint,0,':')) as Name,Count(*) as Hits INTO E:\Exch_Logs\srv-002_Sender_Host.csv from E:\Exch_Logs\Protocol_Logs\Receive\srv-002\Working\*.log WHERE data LIKE '%%EHLO%%' GROUP BY IP ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT data,Count(*) as Hits INTO E:\Exch_Logs\srv-001_From_Field.csv from E:\Exch_Logs\Protocol_Logs\Receive\srv-001\Working\*.log WHERE data LIKE '%%Mail From%%' GROUP BY data ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT data,Count(*) as Hits INTO E:\Exch_Logs\srv-002_From_Field.csv from E:\Exch_Logs\Protocol_Logs\Receive\srv-002\Working\*.log WHERE data LIKE '%%Mail From%%' GROUP BY data ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT data,Count(*) as Hits INTO E:\Exch_Logs\srv-001_RCPT_TO.csv from E:\Exch_Logs\Protocol_Logs\Receive\srv-001\Working\*.log WHERE data LIKE '%%RCPT TO%%' GROUP BY data ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT data,Count(*) as Hits INTO E:\Exch_Logs\srv-002_RCPT_TO.csv from E:\Exch_Logs\Protocol_Logs\Receive\srv-002\Working\*.log WHERE data LIKE '%%RCPT TO%%' GROUP BY data ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT connector-id,Count(*) as Hits INTO E:\Exch_Logs\srv-001_Email_Routing.csv from E:\Exch_Logs\Protocol_Logs\Receive\srv-001\Working\*.log WHERE data LIKE '%%EHLO%%' GROUP BY connector-id ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT connector-id,Count(*) as Hits INTO E:\Exch_Logs\srv-002_Email_Routing.csv from E:\Exch_Logs\Protocol_Logs\Receive\srv-002\Working\*.log WHERE data LIKE '%%EHLO%%' GROUP BY connector-id ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT data as [Status Code],Count(*) as Hits INTO E:\Exch_Logs\srv-001_Hard_Soft_Error.csv from E:\Exch_Logs\Protocol_Logs\Receive\srv-001\Working\*.log WHERE data LIKE '5%%' OR data LIKE '4%%' GROUP BY data ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -o:csv

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT data as [Status Code],Count(*) as Hits INTO E:\Exch_Logs\srv-002_Hard_Soft_Error.csv from E:\Exch_Logs\Protocol_Logs\Receive\srv-002\Working\*.log WHERE data LIKE '5%%' OR data LIKE '4%%' GROUP BY data ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -o:csv

# Code to copy files 
# File Extension to Copy EveryDay Previous Day Files
$File = (Get-Date).AddDays(-1).ToString('yyyyMMdd') + "*.log"
$Sendfileprefix = "SEND"
$Recvfileprefix = "RECV"
$Mtrackfileprefix = "MSGTRK"
$Send = $Sendfileprefix + $File
$Recv = $Recvfileprefix + $File
$Mtrack = $Mtrackfileprefix + $File

# Complete path Construction to copy necessary files only.
$MTrackSrv001 ="\\srv-001\d$\Program Files\Microsoft\Exchange Server\V15\TransportRoles\Logs\MessageTracking\" + $Mtrack
$MTrackSrv002 ="\\srv-002\c$\Program Files\Microsoft\Exchange Server\V15\TransportRoles\Logs\MessageTracking\" + $Mtrack
$SendSrv001 ="\\srv-001\c$\Program Files\Microsoft\Exchange Server\V15\TransportRoles\Logs\FrontEnd\ProtocolLog\SmtpSend\" + $Send
$SendSrv002 ="\\srv-002\c$\Program Files\Microsoft\Exchange Server\V15\TransportRoles\Logs\FrontEnd\ProtocolLog\SmtpSend\" + $Send
$RecvSrv001 ="\\srv-001\c$\Program Files\Microsoft\Exchange Server\V15\TransportRoles\Logs\FrontEnd\ProtocolLog\SmtpReceive\" + $Recv
$RecvSrv002 ="\\srv-002\c$\Program Files\Microsoft\Exchange Server\V15\TransportRoles\Logs\FrontEnd\ProtocolLog\SmtpReceive\" + $Recv

$MTrackDestSrv001 = "E:\Exch_Logs\Tracking_Logs\srv-001\"
$MTrackDestSrv002 =  "E:\Exch_Logs\Tracking_Logs\srv-002\"
$SendDestSrv001 = "E:\Exch_Logs\Protocol_Logs\Send\srv-001\"
$SendDestSrv002 = "E:\Exch_Logs\Protocol_Logs\Send\srv-002\"
$RecvDestSrv001 = "E:\Exch_Logs\Protocol_Logs\Receive\srv-001\"
$RecvDestSrv002 = "E:\Exch_Logs\Protocol_Logs\Receive\srv-002\"


Get-ChildItem $MTrackSrv001 | copy-item -Destination $MTrackDestSrv001
Get-ChildItem $MTrackSrv002 | copy-item -Destination $MTrackDestSrv002
Get-ChildItem $SendSrv001 | copy-item -Destination $SendDestSrv001
Get-ChildItem $SendSrv002 | copy-item -Destination $SendDestSrv002
Get-ChildItem $RecvSrv001 | copy-item -Destination $RecvDestSrv001
Get-ChildItem $RecvSrv002 | copy-item -Destination $RecvDestSrv002

# end of code to copy files

# start of code to copy file to working directory so that logparser bat file can run.

# Working Directory Path
$workMTrackDestSrv001 = $MTrackDestSrv001 + "Working\"
$workMTrackDestSrv002 =  $MTrackDestSrv002  + "Working\"
$workSendDestSrv001 = $SendDestSrv001  + "Working\"
$workSendDestSrv002 = $SendDestSrv002 + "Working\"
$workRecvDestSrv001 = $RecvDestSrv001  + "Working\"
$workRecvDestSrv002 = $RecvDestSrv002 + "Working\"

#Remove Old Files from working directory, this is a temp folder for processing.
remove-item $workMTrackDestSrv001 -force -recurse
remove-item $workMTrackDestSrv002 -force -recurse 
remove-item $workSendDestSrv001 -force -recurse
remove-item $workSendDestSrv002 -force -recurse
remove-item $workRecvDestSrv001 -force -recurse
remove-item $workRecvDestSrv002 -force -recurse

# Force Creation of Working Directory
New-Item -Name "Working" -ItemType "directory" -Path $MTrackDestSrv001
New-Item -Name "Working" -ItemType "directory" -Path $MTrackDestSrv002
New-Item -Name "Working" -ItemType "directory" -Path $SendDestSrv001
New-Item -Name "Working" -ItemType "directory" -Path $SendDestSrv002
New-Item -Name "Working" -ItemType "directory" -Path $RecvDestSrv001
New-Item -Name "Working" -ItemType "directory" -Path $RecvDestSrv002

# Source Directory path to copy files of previous day for processing.

$srcMTrackSrv001 = $MTrackDestSrv001 + $Mtrack
$srcMTrackSrv002 =$MTrackDestSrv002 + $Mtrack
$srcSendSrv001 = $SendDestSrv001 + $Send
$srcSendSrv002 = $SendDestSrv002 + $Send
$srcRecvSrv001 = $RecvDestSrv001  + $Recv
$srcRecvSrv002 = $RecvDestSrv002  + $Recv

# Copy files.

Get-Item $srcMTrackSrv001 | copy-item -Destination $workMTrackDestSrv001
Get-Item $srcMTrackSrv002 | copy-item -Destination $workMTrackDestSrv002 
Get-Item $srcSendSrv001| copy-item -Destination  $workSendDestSrv001
Get-Item $srcSendSrv002| copy-item -Destination $workSendDestSrv002
Get-Item $srcRecvSrv001 | copy-item -Destination $workRecvDestSrv001
Get-Item $srcRecvSrv002 | copy-item -Destination $workRecvDestSrv002

# Call Batch File to process entries, keep adding new queries and new destination csv to get stats
cd C:\Script\Exchange_LogParser
.\parser.bat

# Rename newly generated CSV files and move to to Reports Folder
cd E:\Exch_Logs
$Yesterday = "_" + (Get-Date).AddDays(-1).ToString('yyyyMMdd') + ".csv"
Get-ChildItem *.csv | Rename-Item -NewName { $_.Name -replace '\.csv',$yesterday }
Move-Item -Path E:\Exch_Logs\*.csv -Destination E:\Exch_Logs\Reports\

# End of Script

cd C:\Script\Exchange_LogParser
$d = (Get-Date).ToString('yyyy-MM-dd')
$logFile = "C:\Script\Exchange_LogParser\Log_" + $d + ".txt"
.\exchange_parser.ps1 3>&1 2>&1 | Out-file -filePath $logFile
$smtpserver = "email.learntechfuture.com"
$msg = New-Object Net.Mail.MailMessage
$smtp = New-Object Net.Mail.SmtpClient($smtpserver)
$msg.To.Add("sourabh@learntechfuture.com,sourabh2@learntechfuture.com")
$msg.From = "Exchange Report <exch_report@learntechfuture.com>"
$a = (Get-Date).ToString('MM-dd-yyyy')
if (!$error)
{
$msg.Subject = "Daily Exchange Log Parser Run(" + $date + ") - Success"
$smtp.Send($msg)
}
else
{
    $msg.Subject = "Daily Exchange Log Parser Run(" + $date + ") - Fail"
    $msg.Body += "Daily Exchange Log Parser Script threw an exception : "+$a+":"+$error
    $smtp.Send($msg)
}