C# — get data from Azure Log Analytics/Monitor

Everyone knows that we can query data from Log Analytics through Azure Portal. But not everyone knows that we can do the same stuff with a .NET app.

Here is an exemplary KQL code which is our base for this case.

CDBPartitionKeyRUConsumption 
  | where DatabaseName  == 'iowa_db' 
  | summarize RequestCharge = sum(RequestCharge) by OperationName, CollectionName, format_datetime(TimeGenerated, 'yyyy-MM-dd') 
  | project TimeGenerated, CollectionName, OperationName, RequestCharge 
  | order by TimeGenerated desc , CollectionName asc, OperationName asc, RequestCharge desc

And how it looks like at the Azure Portal.

When it comes to C# code, we need to prepare a query and get a proper credential in order to authenticate to Log Analytics. In this case we authorize the app through a Service Principal. Additionally, we need to use two libraries:

• Azure.Monitor.Query,
• Azure.Identity.

using Azure.Monitor.Query;
using Azure.Identity;
// AzureVariables... is a separate custom class where tenantId, clientId, clientSecret are saved as strings
// Create a credential to aunthenticate to Log Analytics, a Service Principal is used in this case
var credential = new ClientSecretCredential(AzureVariables.tenantId, AzureVariables.clientId, AzureVariables.clientSecret);
var logAnalyticsClient = new LogsQueryClient(credential);

string kqlQuery = """
        CDBPartitionKeyRUConsumption 
    | where DatabaseName  == 'iowa_db' 
    | summarize RequestCharge = sum(RequestCharge) by OperationName, CollectionName, format_datetime(TimeGenerated, 'yyyy-MM-dd') 
    | project TimeGenerated, CollectionName, OperationName, RequestCharge 
    | order by TimeGenerated desc , CollectionName asc, OperationName asc, RequestCharge desc
""";

In the first example we collect data into a built-in type — a single, integer column list. Useful if your result has only one column or record.

// Built-in type
Azure.Response<IReadOnlyList<int>> response = await logAnalyticsClient.QueryWorkspaceAsync<int>(
    AzureVariables.logAnalyticsWorkspaceId,
    "CDBControlPlaneRequests | summarize count()",
    new QueryTimeRange(TimeSpan.FromDays(14)));

System.Console.WriteLine("Response value: {0}", response.Value.First());

More complicated structures can be handled in two ways. The first one is based on a class.

    public class RUConsumption
    {
        public string? TimeGenerated { get; set; }
        public string? CollectionName { get; set; }
        public string? OperationName { get; set; }
        public long RequestCharge { get; set; }
    }

...

// Response query at class object
Azure.Response<IReadOnlyList<RUConsumption>> response00 = await logAnalyticsClient.QueryWorkspaceAsync<RUConsumption>(
    AzureVariables.logAnalyticsWorkspaceId,
    kqlQuery,
    new QueryTimeRange(TimeSpan.FromDays(7)));

foreach (var item1 in response00.Value)
{
    System.Console.WriteLine(
        $@"Date: {item1.TimeGenerated.ToString()}, Container: {item1.CollectionName}, Operation: {item1.OperationName}, RU: {item1.RequestCharge}");
}

And the second one is a table object which provides more elasticity. You don’t need to declare any model.

Azure.Response<LogsQueryResult> response02 = await logAnalyticsClient.QueryWorkspaceAsync(
    AzureVariables.logAnalyticsWorkspaceId,
    kqlQuery,
    new QueryTimeRange(TimeSpan.FromDays(7)));

LogsTable table = response02.Value.Table;

foreach (var row in table.Rows)
{
    Console.WriteLine(row["TimeGenerated"] + " " + row["CollectionName"] + " " + row["OperationName"] + " " + row["RequestCharge"]);
}

Here is the code: GitHub

The next article shows how to ingest logs into Azure Monitor: C# — Ingest custom logs into Azure Log Analytics | by Michal Molka | Dec, 2023 | Medium