How to filter Azure logs, or WCF Data Services filters for Dummies

This query should be in the format:

Timestamp gt datetime'2011-04-20T00:00:00'

Remembering to put that datetime in there is the important bit.

This trips me up every time, so I use the OData overview for reference.

Adding to knightffhor's response, you can certainly write a query which filters by Timstamp but this is not recommended approach as querying on "Timestamp" attribute will lead to full table scan. Instead query this table on PartitionKey attribute. I'm copying my response from other thread here (Can I capture Performance Counters for an Azure Web/Worker Role remotely...?):

"One of the key thing here is to understand how to effectively query this table (and other diagnostics table). One of the things we would want from the diagnostics table is to fetch the data for a certain period of time. Our natural instinct would be to query this table on Timestamp attribute. However that's a BAD DESIGN choice because you know in an Azure table the data is indexed on PartitionKey and RowKey. Querying on any other attribute will result in full table scan which will create a problem when your table contains a lot of data.The good thing about these logs table is that PartitionKey value in a way represents the date/time when the data point was collected. Basically PartitionKey is created by using higher order bits of DateTime.Ticks (in UTC). So if you were to fetch the data for a certain date/time range, first you would need to calculate the Ticks for your range (in UTC) and then prepend a "0" in front of it and use those values in your query.If you're querying using REST API, you would use syntax like:PartitionKey ge '0<from date/time ticks in UTC>' and PartitionKey le '0<to date/time in UTC>'."

I've written a blog post about how to write WCF queries against table storage which you may find useful: http://blog.cerebrata.com/specifying-filter-criteria-when-querying-azure-table-storage-using-rest-api/

Also if you're looking for a 3rd party tool for viewing and managing diagnostics data, may I suggest that you take a look at our product Azure Diagnostics Manager: /Products/AzureDiagnosticsManager. This tool is built specifically for surfacing and managing Windows Azure Diagnostics data.

The answer I accepted helped me immensely in directly querying the table through Visual Studio. Eventually, however, I needed a more robust solution. I used the tips I gained here to develop some classes in C# that let me use LINQ to query the tables. In case it is useful to others viewing this question, here is roughly how I now query my Azure logs.

Create a class that inherits from Microsoft.WindowsAzure.StorageClient.TableServiceEntity to represent all the data in the "WADLogsTable" table:

public class AzureDiagnosticEntry : TableServiceEntity{    public long EventTickCount { get; set; }    public string DeploymentId { get; set; }    public string Role { get; set; }    public string RoleInstance { get; set; }    public int EventId { get; set; }    public int Level { get; set; }    public int Pid { get; set; }    public int Tid { get; set; }    public string Message { get; set; }    public DateTime EventDateTime    {        get        {            return new DateTime(EventTickCount, DateTimeKind.Utc);        }    }}

Create a class that inherits from Microsoft.WindowsAzure.StorageClient.TableServiceContext and references the newly defined data object class:

public class AzureDiagnosticContext : TableServiceContext{    public AzureDiagnosticContext(string baseAddress, StorageCredentials credentials)        : base(baseAddress, credentials)    {        this.ResolveType = s => typeof(AzureDiagnosticEntry);    }    public AzureDiagnosticContext(CloudStorageAccount storage)        : this(storage.TableEndpoint.ToString(), storage.Credentials) { }    // Helper method to get an IQueryable.  Hard code "WADLogsTable" for this class    public IQueryable<AzureDiagnosticEntry> Logs    {        get        {            return CreateQuery<AzureDiagnosticEntry>("WADLogsTable");        }    }}

I have a helper method that creates a CloudStorageAccount from configuration settings:

public CloudStorageAccount GetStorageAccount(){    CloudStorageAccount.SetConfigurationSettingPublisher(        (name, setter) => setter(RoleEnvironment.GetConfigurationSettingValue(name)));    string configKey = "Microsoft.WindowsAzure.Plugins.Diagnostics.ConnectionString";    return CloudStorageAccount.FromConfigurationSetting(configKey);}

I create an AzureDiagnosticContext from the CloudStorageAccount and use that to query my logs:

public IEnumerable<AzureDiagnosticEntry> GetAzureLog(DateTime start, DateTime end){    CloudStorageAccount storage = GetStorageAccount();    AzureDiagnosticContext context = new AzureDiagnosticContext(storage);    string startTicks = "0" + start.Ticks;    string endTicks = "0" + end.Ticks;    IQueryable<AzureDiagnosticEntry> query = context.Logs.Where(        e => e.PartitionKey.CompareTo(startTicks) > 0 &&             e.PartitionKey.CompareTo(endTicks) < 0);    CloudTableQuery<AzureDiagnosticEntry> tableQuery = query.AsTableServiceQuery();    IEnumerable<AzureDiagnosticEntry> results = tableQuery.Execute();    return results;}

This method takes advantage of the performance tip in Gaurav's answer to filter on PartitionKey rather than Timestamp.

If you wanted to filter the results by more than just date, you could filter the returned IEnumerable. But, you'd probably get better performance by filtering the IQueryable. You could add a filter parameter to your method and call it within the IQueryable.Where(). Eg,

public IEnumerable<AzureDiagnosticEntry> GetAzureLog(    DateTime start, DateTime end, Func<AzureDiagnosticEntry, bool> filter){    ...    IQueryable<AzureDiagnosticEntry> query = context.Logs.Where(        e => e.PartitionKey.CompareTo(startTicks) > 0 &&             e.PartitionKey.CompareTo(endTicks) < 0 &&             filter(e));    ...}

In the end, I actually further abstracted most of these classes into base classes in order to reuse the functionality for querying other tables, such as the one storing the Windows Event Log.