Reading the the Windows Azure Diagnostics Table

This article is not specific to Sitecore but with Sitecore Azure you have the possibility to store your logs into what they call WAD Tables. To read it you have multiple choices:

• The Azure Storage Explorer but when your logs are too big you will just have a OutOfMemeryException
• The SCLA (Sitecore Log Analyser) you need to uncomment something in the web.config to activate the Azure possibility. But again when you will have big logs it will be not usable anymore.
• Visual Studio. If you go to the server explorer, then on Azure, then Storage, Tables, and click on the WADLogsTable. As a limitation you will not be able to retrieve more than 10000 records.
• This LinqPad with the Azure Table Storage Driver. I will explain this possibility more in details below.

When those table grow up it become just impossible to execute some custom requests on the custom fields. In fact, they are just two fields who stay queryable: PartitionKey and RowKey. But of course for the sitecore logs what you need to retrieve is all the events during a certain time range. The interesting thing about the PartitionKey is the fact that you can match a datetime to this field. You just have to do the following code to retrieve the value of those values:

string.Format("0{0}", (new DateTime(2015, 02, 26)).Ticks)

If you want more details the following article explain it in details: http://gauravmantri.com/2012/02/17/effective-way-of-fetching-diagnostics-data-from-windows-azure-diagnostics-table-hint-use-partitionkey/ By using this trick you are able to build and execute a query on the PartitionKey by using Visual Studio but, as I said, you are limited to 10000 records max.
If you need to retrieve all the records of a single day one of the solution is LinqPad. To be able to query those table you need to:

• Download Azure Table Storage Driver
• Install this driver by:
  • Open LinqPad
  • Click on "Add connection"
  • Click on "View more drivers..."
  • Click on browse and select your lpx file
  • Click on close

If you switch LinqPad in "c# Statement(s)" mode in the language dropdown. you are able to write query like this one:

(from l in WADLogsTable
   where l.PartitionKey.CompareTo(new DateTime(2015, 02, 13, 0,0,0).Ticks.ToString("d19")) > 0
   && l.PartitionKey.CompareTo(new DateTime(2015, 02, 14, 0, 0, 0).Ticks.ToString("d19")) < 0 
   select new
   {
    DateTime = new DateTime(l.EventTickCount.Value),
    l.Message,
    l.Level
   }).ToList().Dump();

But as you see LinqPad is also limited to 1000 records because of the azure storage api. the workaround for it is to execute the request hour by hour in a loop like this:

for(int i = 0; i < 24; i++) {
 (from l in WADLogsTable
   where l.PartitionKey.CompareTo(new DateTime(2015, 02, 13, i,0,0).Ticks.ToString("d19")) > 0
   && l.PartitionKey.CompareTo(new DateTime(2015, 02, 13, i+1, 0, 0).Ticks.ToString("d19")) < 0 
   select new
   {
    DateTime = new DateTime(l.EventTickCount.Value),
    l.Message,
    l.Level
   }).ToList().Dump(); 
}