Remix!! Using Powershell to parse ESE Transaction Logs …



Let me preface this post by saying this: I’m a tad lazy.  However, the newest addition to our team, Brad Hughes, is not.  Far from it.  That being said, he took it upon himself to rewrite my “Rough & Tough” approach to parsing ESE logs in Powershell.  Enjoy …


 


1.    Download & install Powershell


2.  Download & install strings.exe; make sure strings.exe is in your path


3.    Place all your transaction logs into a temp directory (i.e. D:\templogs)


4.    Fire up Powershell


5.    Run the following command:


 


strings.exe -q -n 16 D:\templogs\*.log | foreach-object { ($_.Split(“:”.ToCharArray(),3)[2]) }| group-object | select-object count,name | sort count | export-csv C:\temp\output.csv


 


 


What this is doing:


 


·         Identifies all strings in the logs greater than 16 chars


·         Removes the D:\templogs\E00xxxx.log: from the output


·         Sorts the output


·         Finds all duplicate records, and retains a count


·         Sorts the final output (ending with the largest # of occurrences)


·         Writes all the output to D:\templogs\output.csv



 


As before, the output will be sorted from the least number of repeating occurences to greatest, but now it’s in a nifty csv format that you use Excel to do all sorts of fancy sorting.


 



Note: this post will probably be obsolote in the next 15 minutes, as Brad will likely re-write this in assembly next.


 


Update: you’ll have to put the output.csv file into a different directory from the logs that you’re trying to parse.  Otherwise, you’ll get into an endless loop where we try to parse the output.csv file as well.


 


strings.exe -q -n 16 D:\templogs\*.log | foreach-object { ($_.Split(“:”.ToCharArray(),3)[2]) }| group-object | select-object count,name | sort count | export-csv C:\temp\output.csv

Comments (22)

  1. Karl says:

    Hi, when trying this I get strings.exe is not recognized as a cmdlet – any ideas?

  2. scottos says:

    You’ll need strings.exe in your path; you can download it from:

    http://live.sysinternals.com/strings.exe.

    Hope this helps,

    Scott

  3. jamec says:

    This rocks, thanks for the update.  You don;t have to put strings in the path, you can simply do this:

    .strings.exe -q -n 16 D:templogs*.log | foreach-object { ($_.Split(":".ToCharArray(),3)[2]) }| group-object | select-object count,name | sort count | export-csv C:tempoutput.csv

    Powershell will run that command (atleast it did on Win7)

    james

  4. Leo says:

    I have a need to search all 30 Transaction Logs for any emails in or out bound to 4 domain names and dumping it into a CSV file.  Will Strings and Powershell be able to do this? And if so what is the code?

    Thanks

    Leo

  5. Scott says:

    Unfortunately, parsing transaction logs won’t get you the specifics you’re after (as once the data has made it to the ESE layer, it’s no longer "mail" … it’s simply insertions of data into the data store).  However, using the approach above will reveal any strings (and potentially domain names if you’re lucky) that are being written into the database.

    Scott

  6. Sachin Arora says:

    Thanks James. That helps; even on XP!

  7. Monty says:

    Hello,

    When I try this command it seems to run forever. I tried running it against a sample of 50 logs (over night) and even just one log (for about 30 minutes). It creates the output.csv file but it is 0 bytes. I’ve tried it on both Windows server 2008 and Windows xp SP2.

    My log file is in c:templogs and I’m outputting the command to c:temp

    I’m running the command from c:templogs as follows:

    PS C:templogs> strings.exe -q -n 16 C:templogs*.log | foreach-object { ($_.Split(":".ToCharArray(),3)[2]) }| group-ob

    ject | select-object count,name | sort count | export-csv C:tempoutput.csv

    If I take out the sorting part of the command it outputs to the .csv just fine and it ends up being about 200k for one log file. Of course this file makes no sense since it hasn’t been split/sorted.

    I’m just wondering if I need to do something else to get the command to actually finish.

    Thanks.

  8. scottos says:

    While I’d love to say the Powershell one has been tried & true, we’ve had mixed results (at best), while the "native" one (using the Win32 ports of *nix utilities) has stood the test of time: http://j.mp/3Arn9U

    Thanks,

    Scott Oseychik

  9. Monty says:

    Ah ok. I’ll give the nix tools a try.

    Thanks for the quick reply Scott!

  10. Sid says:

    Hello Scott,

    i did try to run the command and did get the output for the same in CSV file. need more help from you in analyzing the logs from the oputput. if there is any specific method to read them. if you can share it with us will be great help !!!

  11. Sid says:

    Hello Scott,

    i did try to run the command and did get the output for the same in CSV file. need more help from you in analyzing the logs from the output. if there is any specific method to read them. if you can share it with us will be great help !!!

  12. Scott Oseychik says:

    Hi Sid,

    Try using the same approach using the WIn32 versions of the Unix utilities (previous post) instead.  Unfortunately, I've never had 100% success using this approach with Powershell.

    Regards,

    Scott Oseychik

  13. Satyendra says:

    This is amazing… Thanks Scott!

    I ran this against 200 logs and 1 user flashed with 38L entries, this clearly determines something is wrong with this users (mailbox / addins / rules / corruption) just wondering if there is a way we can tweak the command parameter which can point to any specific email/calendar item?

    -Satyendra

  14. Hi Satyendra,

    I'm pleased you found this useful!  As you're seeing, the data is subject to interpretation, and the output is only as good as your ability to make correlations between the data patterns & the symptoms being encountered.  Wish I had better news for you 🙂

    Regards,

    Scott Oseychik

  15. Cool tool says:

    Is it possible to only show results greater than a number?  Like only show strings that repeated 10 times?

  16. scottos says:

    *Anything* is possible with Powershell 🙂 … Let us know what you come up with!

    Regards,

    Scott Oseychik

  17. Dave Paoli says:

    I can get the script to run, however I only get two lines of output:

    Count: 1

    Name:  tempstrins.exs [-a] [-f offset] [-b bytes] [-n length] [-o] [-q] [-s] [-u] <file or directory>

    No other output, what am I missing?

  18. scottos says:

    Hi Dave,

    I believe you have a syntax error in your example; change 'strins' to 'strings', and you should be good to go.

    Regards,

    Scott Oseychik

  19. Dave Paoli says:

    Scott, I believe I Strings.exe spelled correctly, however my question is was this designed for Exchange 2007 logs, as we are running 2010.  My memory fails me as to when MS moved from 512kb logs to 1024?  Do I need to put any values into the -b or -n fields?  Thanks for the assistance and quick response.  

  20. scottos says:

    Hi Dave,

    I was inferring the syntax error based on your original comment.  Also the size of the logfile shouldn't be an issue here (we still use this approach against Exchange 20130).

    I guess my next recommendation would be to give the "tried & true" method a spin:

    blogs.msdn.com/…/rough-and-tough-guide-to-identifying-patterns-in-ese-transaction-log-files.aspx

    This will rule out anything .NET and/or PowerShell related.

    Hope this helps!

    Scott Oseychik

  21. Zeke Smith says:

    what do asterisks in a transaction log indicate? encrypted data perhaps?

  22. scottos says:

    Hi Zeke,

    Simply means that there were asterisks contained in the transaction logs.  Any encrypted info will be just that: encrypted (not obfuscated/redacted via asterisks).

    In my experience, I've often found asterisks as part of a meeting request (or a meeting request acceptance) that was converted to plain text.

    Hope this helps,

    Scott Oseychik