CODEWITHSUNDEEP
azureloggingazure-web-app-service
Marcus
Marcus

Reputation: 855

Webserver Logs in Azure -> SQL Server

I'd like to log every Webserver request to SQL instead of to file.

In the Azure Portal I only seem to have the option to save to the Filesystem or Storage.

Is there any way to save to a SQL Database instead?

Upvotes: 0

Views: 135

Answers (1)

Amor
Amor

Reputation: 8491

Is there any way to save to a SQL Database instead?

Azure Web App doesn't provide this feature currently. You could post a feature request on following site.

How can we improve Azure Web Apps?

Web Server Log use W3C Logging format. If we choose an option to save this log to the Storage. Log file name format in Azure Blob Service are like this,

[ServerLogContainerName]>[WebAppName]>[Year]>[Month]>[Day]>[Hour]>[LogFileName].log

Azure Web App will save the logs group by hours. We could create a WebJob and use TimeTrigger to read the log out, parse the log file and save data to SQL Server every hour. Code below is for your reference.

// Runs immediately on startup, then every two hour
public static void SaveLogsToSQLJob(
    [TimerTrigger("0 0 * * * *", RunOnStartup = true)] TimerInfo timerInfo)
{
    // Retrieve storage account from connection string.
    CloudStorageAccount storageAccount = CloudStorageAccount.Parse("connection_string");

    // Create the blob client.
    CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

    // Retrieve a reference to a container.
    CloudBlobContainer container = blobClient.GetContainerReference("ServerLogContainerName");

    var blobs = container.GetDirectoryReference("YourWebAppName").GetDirectoryReference(DateTime.Now.Year.ToString())
         .GetDirectoryReference(DateTime.Now.Month.ToString())
         .GetDirectoryReference((DateTime.Now.Hour == 0 ? DateTime.Now.Day - 1 : DateTime.Now.Day).ToString())
         .GetDirectoryReference((DateTime.Now.Hour == 0 ? 23 : DateTime.Now.Hour - 1).ToString()).ListBlobs();

    foreach (CloudBlockBlob blob in blobs)
    {
        using (Stream stream = blob.OpenRead())
        {
            using (StreamReader sr = new StreamReader(stream))
            {
                string line = null;
                while ((line = sr.ReadLine()) != null)
                {
                    if (!line.StartsWith("#"))
                    {
                        string[] fields = line.Split(' ');
                        //Save these fields to database
                    }
                }
            }
        }

    }
}

According to the log file, we need to create following 16 columns in SQL Table.

date time s-sitename cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Cookie) cs(Referer) cs-host sc-status sc-substatus sc-win32-status

Upvotes: 1

Related Questions