To get application insight emails on a weekly basis. rename to run.csx when moving into azure functions

run-applicationinsights-weeklyemails.csx

#r "Newtonsoft.Json"

using System.Configuration;
using System.Net.Mail;
using System.Net.Mime;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;

private const string AppInsightsApi = "https://api.applicationinsights.io/beta/apps";


/* README

   Sends out weekly statistics of your application insight powered app. 
   
   [CONFIGURATION_REQUIRED] configure {AI_APP_ID} and {AI_APP_KEY} accordingly in App Settings with values obtained from Application Insights
   [Get your Application ID and API key] https://dev.applicationinsights.io/documentation/Authorization/API-key-and-App-ID
   [Configure Azure Function Application settings] https://docs.microsoft.com/en-us/azure/azure-functions/functions-how-to-use-azure-function-app-settings

   Configuration Manager: For those new to Azure Functions, configuration manager's app settings are one layer below 'functions' at the 'function app'
            level i.e. it's like an IIS app + class/methods with new buzzword labels. Set them up at
            azure portal -> function apps -> Platform features -> Application settings -> scroll to `Application settings` -> Add new setting
            If that fails, you can always just set the strings in code below (unsafe but works)
*/

/////////////////////////////////////////////////////////////////////////////
// START OF USER CONFIGURATION
// Application Insight settings
private static readonly string AiAppId = ConfigurationManager.AppSettings["AI_APP_ID"];
private static readonly string AiAppKey = ConfigurationManager.AppSettings["AI_APP_KEY"];
// Mail related settings
private static readonly string Hostname = ConfigurationManager.AppSettings["SmtpHostName"];
private static readonly int Port = int.Parse(ConfigurationManager.AppSettings["SmtpPort"]);
private static readonly string SmtpUsername = ConfigurationManager.AppSettings["SmtpUsername"];
private static readonly string SmtpPassword = ConfigurationManager.AppSettings["SmtpPassword"];
private static readonly string emailFrom = ConfigurationManager.AppSettings["EmailFrom"];
private static readonly string emailTo = ConfigurationManager.AppSettings["EmailTo"];
private static readonly string appName = ConfigurationManager.AppSettings["AppName"];
// END OF USER CONFIGURATION
/////////////////////////////////////////////////////////////////////////////

public static async Task Run(TimerInfo myTimer, TraceWriter log)
{
    if (myTimer.IsPastDue)
    {
        log.Warning($"[Warning]: Timer is running late! Last ran at: {myTimer.ScheduleStatus.Last}");
    }

    DigestResult result = await ScheduledDigestRun(
        query: GetQueryString(),
        log: log
    );
    log.Verbose($"result={JsonConvert.SerializeObject(result)}");

    DigestResult resultPrevPeriod = await ScheduledDigestRun(
        query: GetQueryStringPreviousPeriod(),
        log: log
    );
    log.Verbose($"result={JsonConvert.SerializeObject(resultPrevPeriod)}");

    var today = DateTime.Today.ToShortDateString();
    var htmlBody = GetHtmlContentValue(appName, today, result, resultPrevPeriod);
    using (var client = new SmtpClient(Hostname, Port))
    {
        client.UseDefaultCredentials = false;
        client.Credentials = new System.Net.NetworkCredential(SmtpUsername, SmtpPassword);
        client.DeliveryMethod = SmtpDeliveryMethod.Network;
        client.EnableSsl = true;

        var emailFromMailAddress = new MailAddress(emailFrom, "Crypteron");
        var emailToMailAddress = new MailAddress(emailTo);

        using (var message = new MailMessage(emailFromMailAddress, emailToMailAddress))
        {

            message.Subject = $"Weekly {appName} performance report for {today}";
            message.IsBodyHtml = false;
            message.Body = GetPlainTextContentValue(appName, today, result, resultPrevPeriod);
            
            if (!string.IsNullOrWhiteSpace(htmlBody))
            {
                message.IsBodyHtml = true;
                AlternateView alternate = AlternateView.CreateAlternateViewFromString(htmlBody, System.Text.Encoding.UTF8, MediaTypeNames.Text.Html);
                message.AlternateViews.Add(alternate);
            }

            client.Send(message);
        }
    }

    log.Info($"Generated and mailed weekly report for {today} at {DateTime.Now}");

    return;
}

private struct DigestResult
{
    public string TotalRequests;
    public string FailedRequests;
    public string RequestsDuration;
    public string TotalDependencies;
    public string FailedDependencies;
    public string DependenciesDuration;
    public string TotalViews;
    public string TotalExceptions;
    public string OverallAvailability;
    public string AvailabilityDuration;
}

private static async Task<DigestResult> ScheduledDigestRun(string query, TraceWriter log)
{
    log.Info($"Executing scheduled daily digest run at: {DateTime.Now}");

    // generate request ID to allow issue tracking
    string requestId = Guid.NewGuid().ToString();
    log.Verbose($"API request ID is {requestId}");

    try
    {
        using (var httpClient = new HttpClient())
        {
            httpClient.DefaultRequestHeaders.Add("x-api-key", AiAppKey);
            httpClient.DefaultRequestHeaders.Add("x-ms-app", "FunctionTemplate");
            httpClient.DefaultRequestHeaders.Add("x-ms-client-request-id", requestId);
            string apiPath = $"{AppInsightsApi}/{AiAppId}/query?clientId={requestId}&query={query}";
            using (var httpResponse = await httpClient.GetAsync(apiPath))
            {
                // throw exception when unable to determine the metric value
                httpResponse.EnsureSuccessStatusCode();
                var resultJson = await httpResponse.Content.ReadAsAsync<JToken>();
                DigestResult result = new DigestResult
                {
                    TotalRequests = resultJson.SelectToken("Tables[0].Rows[0][0]")?.ToObject<long>().ToString("N0"),
                    FailedRequests = resultJson.SelectToken("Tables[0].Rows[0][1]")?.ToObject<long>().ToString("N0"),
                    RequestsDuration = resultJson.SelectToken("Tables[0].Rows[0][2]")?.ToString(),
                    TotalDependencies = resultJson.SelectToken("Tables[0].Rows[0][3]")?.ToObject<long>().ToString("N0"),
                    FailedDependencies = resultJson.SelectToken("Tables[0].Rows[0][4]")?.ToObject<long>().ToString("N0"),
                    DependenciesDuration = resultJson.SelectToken("Tables[0].Rows[0][5]")?.ToString(),
                    TotalViews = resultJson.SelectToken("Tables[0].Rows[0][6]")?.ToObject<long>().ToString("N0"),
                    TotalExceptions = resultJson.SelectToken("Tables[0].Rows[0][7]")?.ToObject<long>().ToString("N0"),
                    OverallAvailability = resultJson.SelectToken("Tables[0].Rows[0][8]")?.ToString(),
                    AvailabilityDuration = resultJson.SelectToken("Tables[0].Rows[0][9]")?.ToString()
                };
                return result;
            }
        }
    }
    catch (Exception ex)
    {
        log.Error($"[Error]: Client Request ID {requestId}: {ex.Message}");

        // optional - throw to fail the function
        throw;
    }
}

private static string GetQueryString()
{
    // update the query accordingly for your need (be sure to run it against Application Insights Analytics portal first for validation)
    // [Application Insights Analytics] https://docs.microsoft.com/en-us/azure/application-insights/app-insights-analytics
    return @"
let period=7d;    
requests
| where timestamp > ago(period)
| summarize Row = 1, TotalRequests = sum(itemCount), FailedRequests = sum(toint(success == 'False')),
    RequestsDuration = iff(isnan(avg(duration)), '------', tostring(toint(avg(duration) * 100) / 100.0))
| join (
dependencies
| where timestamp > ago(period)
| summarize Row = 1, TotalDependencies = sum(itemCount), FailedDependencies = sum(success == 'False'),
    DependenciesDuration = iff(isnan(avg(duration)), '------', tostring(toint(avg(duration) * 100) / 100.0))
) on Row | join (
pageViews
| where timestamp > ago(period)
| summarize Row = 1, TotalViews = sum(itemCount)
) on Row | join (
exceptions
| where timestamp > ago(period)
| summarize Row = 1, TotalExceptions = sum(itemCount)
) on Row | join (
availabilityResults
| where timestamp > ago(period)
| summarize Row = 1, OverallAvailability = iff(isnan(avg(toint(success))), '------', tostring(toint(avg(toint(success)) * 10000) / 100.0)),
    AvailabilityDuration = iff(isnan(avg(duration)), '------', tostring(toint(avg(duration) * 100) / 100.0))
) on Row
| project TotalRequests, FailedRequests, RequestsDuration, TotalDependencies, FailedDependencies, DependenciesDuration, TotalViews, TotalExceptions, OverallAvailability, AvailabilityDuration
";
}

private static string GetQueryStringPreviousPeriod()
{
    return @"
let period=7d;
let prevPeriod=2*period;
requests
| where timestamp < ago(period) and timestamp > ago(prevPeriod)
| summarize Row = 1, TotalRequests = sum(itemCount), FailedRequests = sum(toint(success == 'False')),
    RequestsDuration = iff(isnan(avg(duration)), '------', tostring(toint(avg(duration) * 100) / 100.0))
| join (
dependencies
| where timestamp < ago(period) and timestamp > ago(prevPeriod)
| summarize Row = 1, TotalDependencies = sum(itemCount), FailedDependencies = sum(success == 'False'),
    DependenciesDuration = iff(isnan(avg(duration)), '------', tostring(toint(avg(duration) * 100) / 100.0))
) on Row | join (
pageViews
| where timestamp < ago(period) and timestamp > ago(prevPeriod)
| summarize Row = 1, TotalViews = sum(itemCount)
) on Row | join (
exceptions
| where timestamp < ago(period) and timestamp > ago(prevPeriod)
| summarize Row = 1, TotalExceptions = sum(itemCount)
) on Row | join (
availabilityResults
| where timestamp < ago(period) and timestamp > ago(prevPeriod)
| summarize Row = 1, OverallAvailability = iff(isnan(avg(toint(success))), '------', tostring(toint(avg(toint(success)) * 10000) / 100.0)),
    AvailabilityDuration = iff(isnan(avg(duration)), '------', tostring(toint(avg(duration) * 100) / 100.0))
) on Row
| project TotalRequests, FailedRequests, RequestsDuration, TotalDependencies, FailedDependencies, DependenciesDuration, TotalViews, TotalExceptions, OverallAvailability, AvailabilityDuration
";
}

private static string GetPlainTextContentValue(string appName, string today, DigestResult result, DigestResult prevResult)
{
    // update the HTML template accordingly for your need
    return $@"
{appName} daily telemetry report: {today} {Environment.NewLine}
The following data shows insights based on telemetry from last 24 hours. {Environment.NewLine}
Total requests ..... : {result.TotalRequests} (previously {prevResult.TotalRequests}) {Environment.NewLine}
Failed requests .... : {result.FailedRequests} (previously {prevResult.FailedRequests}) {Environment.NewLine}
Average response time: {result.RequestsDuration} ms (previously {prevResult.RequestsDuration} ms) {Environment.NewLine}
Total dependencies . : {result.TotalDependencies} (previously {prevResult.TotalDependencies}) {Environment.NewLine}
Failed dependencies  : {result.FailedDependencies} (previously {prevResult.FailedDependencies}) {Environment.NewLine}
Average response time: {result.DependenciesDuration} ms (previously {prevResult.DependenciesDuration}) {Environment.NewLine}
Total views ........ : {result.TotalViews} (previously {prevResult.TotalViews}) {Environment.NewLine}
Total exceptions ... : {result.TotalExceptions} (previously {prevResult.TotalExceptions}) {Environment.NewLine}
Overall Availability : {result.OverallAvailability} % (previously {prevResult.OverallAvailability} %) {Environment.NewLine}
Average response time: {result.AvailabilityDuration} ms (previously {prevResult.AvailabilityDuration} ms) {Environment.NewLine}
";
}

private static string GetHtmlContentValue(string appName, string today, DigestResult result, DigestResult prevResult)
{
    // update the HTML template accordingly for your need
    return $@"
<html><body>
<p style='text-align: center;'><strong>{appName} weekly telemetry report {today}</strong></p>
<p style='text-align: center;'>The following data shows insights based on telemetry from last 7 days.</p>
<table align='center' style='width: 95%; max-width: 480px;'><tbody>
<tr>
<td style='min-width: 150px; text-align: left;'></td>
<td style='min-width: 100px; text-align: right;'>This week</td>
<td style='min-width: 100px; text-align: right;'>Last week</td>
</tr>
<tr>
<td style='min-width: 150px; text-align: left;'><strong>Total requests</strong></td>
<td style='min-width: 100px; text-align: right;'><strong>{result.TotalRequests}</strong></td>
<td style='min-width: 100px; text-align: right;'><strong>{prevResult.TotalRequests}</strong></td>
</tr>
<tr>
<td style='min-width: 120px; padding-left: 5%; text-align: left;'>Failed requests</td>
<td style='min-width: 100px; text-align: right;'>{result.FailedRequests}</td>
<td style='min-width: 100px; text-align: right;'>{prevResult.FailedRequests}</td>
</tr>
<tr>
<td style='min-width: 120px; padding-left: 5%; text-align: left;'>Average response time</td>
<td style='min-width: 100px; text-align: right;'>{result.RequestsDuration} ms</td>
<td style='min-width: 100px; text-align: right;'>{prevResult.RequestsDuration} ms</td>
</tr>
<tr>
<td colspan='3'><hr /></td>
</tr>
<tr>
<td style='min-width: 150px; text-align: left;'><strong>Total dependencies</strong></td>
<td style='min-width: 100px; text-align: right;'><strong>{result.TotalDependencies}</strong></td>
<td style='min-width: 100px; text-align: right;'><strong>{prevResult.TotalDependencies}</strong></td>
</tr>
<tr>
<td style='min-width: 120px; padding-left: 5%; text-align: left;'>Failed dependencies</td>
<td style='min-width: 100px; text-align: right;'>{result.FailedDependencies}</td>
<td style='min-width: 100px; text-align: right;'>{prevResult.FailedDependencies}</td>
</tr>
<tr>
<td style='min-width: 120px; padding-left: 5%; text-align: left;'>Average response time</td>
<td style='min-width: 100px; text-align: right;'>{result.DependenciesDuration} ms</td>
<td style='min-width: 100px; text-align: right;'>{prevResult.DependenciesDuration} ms</td>
</tr>
<tr>
<td colspan='3'><hr /></td>
</tr>
<tr>
<td style='min-width: 150px; text-align: left;'><strong>Total views</strong></td>
<td style='min-width: 100px; text-align: right;'><strong>{result.TotalViews}</strong></td>
<td style='min-width: 100px; text-align: right;'><strong>{prevResult.TotalViews}</strong></td>
</tr>
<tr>
<td style='min-width: 150px; text-align: left;'><strong>Total exceptions</strong></td>
<td style='min-width: 100px; text-align: right;'><strong>{result.TotalExceptions}</strong></td>
<td style='min-width: 100px; text-align: right;'><strong>{prevResult.TotalExceptions}</strong></td>
</tr>
<tr>
<td colspan='3'><hr /></td>
</tr>
<tr>
<td style='min-width: 150px; text-align: left;'><strong>Overall Availability</strong></td>
<td style='min-width: 100px; text-align: right;'><strong>{result.OverallAvailability} %</strong></td>
<td style='min-width: 100px; text-align: right;'><strong>{prevResult.OverallAvailability} %</strong></td>
</tr>
<tr>
<td style='min-width: 120px; padding-left: 5%; text-align: left;'>Average response time</td>
<td style='min-width: 100px; text-align: right;'>{result.AvailabilityDuration} ms</td>
<td style='min-width: 100px; text-align: right;'>{prevResult.AvailabilityDuration} ms</td>
</tr>
</tbody></table>
</body></html>
";
}