locked
Exchange Powershell Report RRS feed

  • Question

  • Hello,

    I had some great help on this site regarding a report I needed.  Some clever guys created a script for me to show sent emails from our Exchange 2007 CCR cluster envirnonment hour by hour.  My next question was how can I show sent emails just form sales, but this part didn't work and here is the script based on 2 users I know have sent emails, can you see any issues why it returns no sent info stats in the csv chart apart from the date and time rows:

    $From = Get-Date "08/09/2011"

    $To = $From.AddHours(1)

     

    [Int64] $intSent = 0

    [String] $strSent = $strTotalSent = $null

     

    Do

    {

     If ($From.Hour -eq "0")

    {

    $strTotalSent += "$strSent`n"

    $strTotalRec += "$strRec`n"

     

    $strSent = "$($From.DayOfWeek),$($From.ToShortDateString()),"

    $strRec = "$($From.DayOfWeek),$($From.ToShortDateString()),"

    }

     

    # Sent E-mails

    $intSent = (Get-TransportServer | Get-MessageTrackingLog -ResultSize Unlimited -EventId RECEIVE -Start $From -End $To | ? {$_.Source -eq "STOREDRIVER" -and ($_.Sender -match "gonzo@ourdomain.com" -or $_.Sender -match "gonzo2@ourdomain.com")}).Count

     

    $From = $From.AddHours(1)

    $To = $From.AddHours(1)

    }

    While ($To -lt (Get-Date))

     

    $strTotalSent += "$strSent`n"

     

    "`nSent" >> C:\SentPerHour.csv

    "DayOfWeek,Date,00:00,01:00,02:00,03:00,04:00,05:00,06:00,07:00,08:00,09:00,10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00,18:00,19:00,20:00,21:00,22:00,23:00" >> C:\SentPerHour.csv

    $strTotalSent >> C:\SentPerHour.csv

    Monday, September 19, 2011 2:21 PM

Answers

  • Here's my take on the problem.  Put in the start date, and the addresses you want to check for:

    $start = "09/19/2011"
    $end = ([datetime]$start).adddays(1)
    
    $users = "user1@domain.com","user2@domain.com"
    
    $hash = @{}
    foreach ($user in $users){
    0..23 | foreach {
        $hash["$('{0:D2}' -f $_)"] += @{$user = 0}
        }
    }
    
    $ht_servers = get-exchangeserver |? {$_.serverrole -match "hubtransport"} |% {$_.name}
    
    foreach($ht in $ht_servers){
        get-messagetrackinglog -start $start -end $end -server $ht -eventid "Receive" -resultsize unlimited |
        where {$_.source -eq "StoreDriver" -and $users -contains $_.sender} |
            foreach-object {
             $hash[($_.timestamp).tostring("HH")][$_.sender] ++
    }
    }
    
    $stats = @()
    $hash.keys | sort |
     foreach-object {
     $hash[$_]["Time"] = $_
     $stats += new-object psobject -property $hash[$_]
     }
     
     $stats | ft -auto
    

     


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Tuesday, September 20, 2011 11:06 PM

All replies

  • Try changing the following:

    $intSent = (Get-TransportServer | Get-MessageTrackingLog -ResultSize Unlimited -EventId RECEIVE -Start $From -End $To | ? {$_.Source -eq "STOREDRIVER" -and ($_.Sender -match "gonzo@ourdomain.com" -or $_.Sender -match "gonzo2@ourdomain.com")}).Count

    into

    $intSent = (Get-TransportServer | Get-MessageTrackingLog -ResultSize Unlimited -EventId RECEIVE -Start $From -End $To | ? {$_.Source -eq "STOREDRIVER" -and ($_.Sender -match gonzo@ourdomain.com) -or ($_.Sender -match "gonzo2@ourdomain.com")}).Count

     

    Haven't been able to verify, but should work :-)


    __________________________________
    Michael Van Horenbeeck Check out my blog @ Pro-Exchange (Belgian Usergroup)
    Monday, September 19, 2011 8:41 PM
  • Hi,

    I see in your example 1 email address has the quote mark and the other doesn't, should I use them or not on each email address?

     

    Thanks

    Tuesday, September 20, 2011 9:52 AM
  • It needed them, but when I run the script it returns no value, I just added my email address and a colleagues and the csv table was empty apart from the days and hours :(
    Tuesday, September 20, 2011 10:34 AM
  • As I said, I didn't test the script, but it seems logical to me, because you don't output the value of $intSent anywhere in the script.

     

    try this ( a bit simpler, but it works)

    $From = Get-Date "19/09/2011"
     
    $To = $From.AddHours(1)
     
    [Int64] $intSent = 0
     
    [String] $strSent = $strTotalSent = $null
     
     
    Do
     
    {
     
    # Sent E-mails
     
    $intSent = (Get-TransportServer | Get-MessageTrackingLog -ResultSize Unlimited -EventId RECEIVE -Start $From -End $To | ? {$_.Source -eq "STOREDRIVER" -and ($_.Sender -match "email1@domain.com") -or ($_.Sender -match "email2@domain.com")}).Count
     
    $From = $From.AddHours(1)

    $From >> C:\SentPerHour.csv

    $To = $From.AddHours(1)
    $intSent >> C:\SentPerHour.csv
     
    }
     
    While ($To -lt (Get-Date))
     
     


    __________________________________
    Michael Van Horenbeeck Check out my blog @ Pro-Exchange (Belgian Usergroup)
    Tuesday, September 20, 2011 2:45 PM
  • Hey that return some info!  Can it be put into the original script I first posted as that is how I would like it displayed?

     

    Many thanks for you help so far.

    Tuesday, September 20, 2011 3:47 PM
  • Hello,

    I tried using your new script with the one I posted but it doesn't work as I am useless at powershell.  Your script returned some useful info, but I would love your script to output to a csv like my script at the start of the post does.  Is there any chance you could see if you could use your script with mine? 

    Tuesday, September 20, 2011 8:19 PM
  • I am useless at powershell.  

    Dude, we seriously need to fix that.

    http://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Tuesday, September 20, 2011 8:25 PM
  • Ha! thanks for this I will certainly take a look.  Unfortunately my boss needs this report by Thursday morning, I just hope someone who knows their ps can fix it :)
    Tuesday, September 20, 2011 8:29 PM
  • Hi,

    I'm not quite sure what your output is supposed to look like. The code you provided is pretty unclear.

    Michael

     

    P.S.: take a look at Mike Pfeiffer's book (Exchange 2010 PowerShell Cookbook) if you want to learn more about PowerShell in combination with Exchange 2010.


    __________________________________
    Michael Van Horenbeeck Check out my blog @ Pro-Exchange (Belgian Usergroup)
    Tuesday, September 20, 2011 8:38 PM
  • IIRC, this project started about a week ago, and he needed that afternoon then.  Mine does that, too :).


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Tuesday, September 20, 2011 8:40 PM
  • My bosses upped the game from a week ago, by asking for individuals to be reported on only, I was going to add our sales team smtp addresses, I guess no one knows or it can't be done if it has been a week :)

    MichealVH you have come the closest to be honest.  The script I posted basically showed hour by hour across the top and the date down the side and the logs filled the table with the emails sent.

    I will try and find the original script that worked.

    Thanks

    Tuesday, September 20, 2011 8:52 PM
  • Have you tried Michael's suggestion on the first script? Something like:

    $From = Get-Date "08/09/2011"
    $To = $From.AddHours(1)
    
    [Int64] $intSent = 0
    [String] $strSent = $strTotalSent = $null
    
    Do
    {
    	If ($From.Hour -eq "0")
    	{
    		$strTotalSent += "$strSent`n"
    		$strTotalRec += "$strRec`n"
    
    		$strSent = "$($From.DayOfWeek),$($From.ToShortDateString()),"
    		$strRec = "$($From.DayOfWeek),$($From.ToShortDateString()),"
    	}
    
    	# Sent E-mails
    	$intSent = (Get-TransportServer | Get-MessageTrackingLog -ResultSize Unlimited -EventId RECEIVE -Start $From -End $To | ? {$_.Source -eq "STOREDRIVER" -and ($_.Sender -match "gonzo@ourdomain.com") -or ($_.Sender -match "gonzo2@ourdomain.com")}).Count
    
    	$From = $From.AddHours(1)
    	$To = $From.AddHours(1)
    }
    While ($To -lt (Get-Date))
    
    $strTotalSent += "$strSent`n"
    
    "`nSent" >> C:\SentPerHour.csv
    "DayOfWeek,Date,00:00,01:00,02:00,03:00,04:00,05:00,06:00,07:00,08:00,09:00,10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00,18:00,19:00,20:00,21:00,22:00,23:00" >> C:\SentPerHour.csv
    $strTotalSent >> C:\SentPerHour.csv
    


    http://LetsExchange.blogspot.com
    Tuesday, September 20, 2011 8:52 PM
  • My bosses upped the game from a week ago, by asking for individuals to be reported on only, I was going to add our sales team smtp addresses, I guess no one knows or it can't be done if it has been a week :)

    MichealVH you have come the closest to be honest.  The script I posted basically showed hour by hour across the top and the date down the side and the logs filled the table with the emails sent.

    I will try and find the original script that worked.

    Thanks


    Using the Transport Logs you can do what you want to achieve. It is just a matter of writing the script to match your needs... I have written dozens of scripts to produce reports like that, and they all work fine   :)

    Try my latest suggestion and see if it works.


    http://LetsExchange.blogspot.com
    Tuesday, September 20, 2011 8:55 PM
  • Seems to return a table with the hours and dates, but no stats from those 2 smtp addresses when I know they have sent emails.
    Tuesday, September 20, 2011 9:00 PM
  • My bosses upped the game from a week ago, by asking for individuals to be reported on only, I was going to add our sales team smtp addresses, I guess no one knows or it can't be done if it has been a week :)

    Or maybe they're intentionally not answering the question, hoping you'll start working on it yourself.

    You can't keep coming back here asking other people to write scripts for you.  If your job requries you to do scripting, then you need to learn how. 

    I think you'll find if you at least make an effort and come back with some of your own script you need help with you're more likely to get help with that.

    Asking other people to do your work for you is just rude.

    IMHO


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Tuesday, September 20, 2011 9:07 PM
  • Ok, then try running the following command and see what you get back:

    Get-TransportServer | Get-MessageTrackingLog -ResultSize Unlimited -EventId RECEIVE -Start "09/15/2011" | ? {$_.Source -eq "STOREDRIVER" -and $_.Sender -match "gonzo@ourdomain.com"
    

    Also, in the previous script, make sure you type the $From date correctly and in a period of time you know those senders sent an e-mail at least.

    You really need to learn PowerShell as troubleshooting this without being able to test it with your data is really, really hard...


    http://LetsExchange.blogspot.com
    Tuesday, September 20, 2011 9:08 PM
  • I kinda must agree to this.

    We are here to help you figure out a problem, not to do your job ;-)

    I think that all elements to get your script running are given to get it sorted out.

    Just keep in mind that formatting in a table - at least the way the script is approaching it right now - seems quite difficult somehow. You might be better of putting it all in a hashtable or so.

    Grts,

    Michael


    __________________________________
    Michael Van Horenbeeck Check out my blog @ Pro-Exchange (Belgian Usergroup)
    Tuesday, September 20, 2011 9:21 PM
  • let me try this. Sorry for the lack of input myself, I'm just a Cisco network guy that has to try and help do this as we have no Exchange guys, so script scares me and to be honest I hate Exchange which doesn't help me. I hope to think people don't have to reply to my posts, some do I a way that is dissapointing. I will give this one last go before getting some paid consultancy, maybe I need to find out about a gui based reporting tool, saves troubling you guys. But for those of you who have helped me I really appreciated it you obviously know your stuff.
    Tuesday, September 20, 2011 9:29 PM
  • IMHO, even if you're "just a Cisco network guy", time spent learning the basics of Powershell will be well spent.  Take a look at some of the things Cisco itself is doing with Powershell and UCS.

    Don't be afraid of it because you don't know it, and don't believe for a minute that it's something only an "Exchange guy" needs to know about and can use to automate and report on his stuff.

    Even if you hate Exchange, being "the Powershell guy" can make you a lot more valuable at work very soon.


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Tuesday, September 20, 2011 9:49 PM
  • Here's my take on the problem.  Put in the start date, and the addresses you want to check for:

    $start = "09/19/2011"
    $end = ([datetime]$start).adddays(1)
    
    $users = "user1@domain.com","user2@domain.com"
    
    $hash = @{}
    foreach ($user in $users){
    0..23 | foreach {
        $hash["$('{0:D2}' -f $_)"] += @{$user = 0}
        }
    }
    
    $ht_servers = get-exchangeserver |? {$_.serverrole -match "hubtransport"} |% {$_.name}
    
    foreach($ht in $ht_servers){
        get-messagetrackinglog -start $start -end $end -server $ht -eventid "Receive" -resultsize unlimited |
        where {$_.source -eq "StoreDriver" -and $users -contains $_.sender} |
            foreach-object {
             $hash[($_.timestamp).tostring("HH")][$_.sender] ++
    }
    }
    
    $stats = @()
    $hash.keys | sort |
     foreach-object {
     $hash[$_]["Time"] = $_
     $stats += new-object psobject -property $hash[$_]
     }
     
     $stats | ft -auto
    

     


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Tuesday, September 20, 2011 11:06 PM