Automate Inbound-Outbound report in csv file
Hello, I use Exchage 2007. I have been using the below script for a while and it has provided me a perfect input about the in/oubound mails. I would like to perform an automatic report and was wondering: 1) If I could automitize the start and end date? I usualy add the start and end date (-Start "06/12/2010 00:00AM" -End "12/12/2010) I need to provide a weekly report and thought about that command $startDate = [DateTime]::Today.AddDays(-7) but I do not maange to implemente it. 2) also I would like to extract the report to the apropriate extension file to use it in a excel document. Can you please help me out with that? Graig NB: I am looking for improving and understand more the scripting part and would be glad to be advice on training or site I should follow. SCRIPT: $domains = get-accepteddomain |% {$_.domainname.smtpdomain} $sent = @{} $recv = @{} $domains |% { $sent.add("$_",0) $recv.add("$_",0) } Get-MessageTrackingLog -ResultSize Unlimited -Start "06/12/2010 00:00AM" -End "12/12/2010 11:59PM" |% { if ($domains -contains $_.sender.split("@")[1]){$sent[$_.sender.split("@")[1]] ++} $_.recipients |%{ if($domains -contains $_.split("@")[1]){$recv[$_.split("@")[1]] ++} } } $stats = @() $domains |% { $stat = ""|select DomainName,Inbound,Outbound $stat.DomainName = $_ $stat.Outbound = $sent["$_"] $stat.Inbound = $recv["$_"] $stats += $stat } $stats | ft -autosize
December 15th, 2010 6:43am

1. If you want to automate it to run weekly, and gather the stats for the previous 7 days: $start=(get-date).adddays(-7).toshortdatestring() $end=(get-date).toshortdatestring() That will get you just the date string, with no time specifier and get-messagetrackinglog will implictly get the logs from midnight to midnight on the start and end days. 2. To save the file as .csv (which can be opened directly in Excel) you can uses export-csv on $stats. You can re-use the $start and $end variables to help create appropriate file names for the csv files: $file = "$($start.replace("/","_"))_to_$($end.replace("/","_")).csv" $stats | export-csv c:\somedir\$file For help in learning PowerShell, I'd start here: http://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
Free Windows Admin Tool Kit Click here and download it now
December 15th, 2010 9:01am

Forgot one thing: You'll want to add the -notypeinformation option to your export-csv, otherwise you'll get an extra header row with type information that's going to make your csv ugly if you open it in Excel. $stats | export-csv c:\somedir\$file -notypeinformation[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
December 15th, 2010 10:00am

Thanks for the link!! Would you also recommend any training as my cmopany is willing to pay for it? The 2 commands line you sent me make sense and I understand that I should run the ps1 at midnight. I have problem only to insert your command in the script :-S I get like: Unexpected token '}' in expression or statement. At C:\Documents and Settings\Administrateur\Bureau\1-TRY.ps1:21 char:2 + } <<<< Unexpected token '_))_to_$($end.replace(/,_)).csv' in expression or statement. At C:\Documents and Settings\Administrateur\Bureau\1-EXT.ps1:26 char:69 + $file = "$($start.replace("/","_"))_to_$($end.replace("/","_")).csv" <<<< --> So I guess I do not insert the start/end date and the export in the right place! Could you please show me how it should look like? Many thanks! Graig
Free Windows Admin Tool Kit Click here and download it now
December 15th, 2010 10:35am

Soemthing like this: $domains = get-accepteddomain |% {$_.domainname.smtpdomain} $sent = @{} $recv = @{} $domains |% { $sent.add("$_",0) $recv.add("$_",0) } $start=(get-date).adddays(-7).toshortdatestring() $end=(get-date).toshortdatestring() Get-MessageTrackingLog -ResultSize Unlimited -Start $start -End $end |% { if ($domains -contains $_.sender.split("@")[1]){$sent[$_.sender.split("@")[1]] ++} $_.recipients |%{ if($domains -contains $_.split("@")[1]){$recv[$_.split("@")[1]] ++} } } $stats = @() $domains |% { $stat = ""|select DomainName,Inbound,Outbound $stat.DomainName = $_ $stat.Outbound = $sent["$_"] $stat.Inbound = $recv["$_"] $stats += $stat } $file = "$($start.replace("/","_"))_to_$($end.replace("/","_")).csv" $stats | export-csv c:\somedir\$file -notypeinformation If you're looking for formal training, I'd post a question to the Powershell forum, with your location information. http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/threads [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
December 15th, 2010 2:14pm

Hello Mjolinor, I have just posted my question on the powershell forum :-) Hope I will get better soon :-D. I still got an error message executing the script though. Would you know what is wrong? ERROR: Unexpected token '_))_to_$($end.replace(/,_)).csv' in expression or statement. At C:\Documents and Settings\Administrateur\Bureau\2.ps1:30 char:69 + $file = "$($start.replace("/","_"))_to_$($end.replace("/","_")).csv" <<<< SCRIPT: $domains = get-accepteddomain |% {$_.domainname.smtpdomain} $sent = @{} $recv = @{} $domains |% { $sent.add("$_",0) $recv.add("$_",0) } $start=(get-date).adddays(-7).toshortdatestring() $end=(get-date).toshortdatestring() Get-MessageTrackingLog -ResultSize Unlimited -Start $start -End $end |% { if ($domains -contains $_.sender.split("@")[1]){$sent[$_.sender.split("@")[1]] ++} $_.recipients |%{ if($domains -contains $_.split("@")[1]){$recv[$_.split("@")[1]] ++} } } $stats = @() $domains |% { $stat = ""|select DomainName,Inbound,Outbound $stat.DomainName = $_ $stat.Outbound = $sent["$_"] $stat.Inbound = $recv["$_"] $stats += $stat } $file = "$($start.replace("/","_"))_to_$($end.replace("/","_")).csv" $stats | export-csv d:\report\$file -notypeinformation
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 6:50am

Replace this line: $file = "$($start.replace("/","_"))_to_$($end.replace("/","_")).csv" with: $file = "$($start.replace('/','_'))_to_$($end.replace('/','_')).csv"[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
December 17th, 2010 8:56am

Thanks Mjolinor, It does work really well. I would just have a question about how to generate the report week without overlaping with next month? Eg: Week 48 we have monday 29 and tuesday 30 th of November Week 49 we have Wednesday Thursday Friday saturday and Sunday of december -->The current report does not separate the week 48 and 49. So when I cumulate the weeks to calculate the indbound outbound for one month, I cannot get the correct amount, exept if I do it manualy. Any way to do it automaticaly? I also would like to take that opportunity to wish you a very happy new year and I would like to thank you and the others for your precious help!! thanks, Graig
Free Windows Admin Tool Kit Click here and download it now
December 31st, 2010 9:53am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics