4f885e7e-d8bb-427c-a54e-8e3e1c3137a7 true IOAK-SQL-LOG-01.sdt.local jazz log prod true <ProgramFilesX64>\Microsoft SDKs\Azure\.NET SDK\v2.9\bin\plugins\Diagnostics\Newtonsoft.Json.dll Amazon.Lambda.S3Events AWSSDK.Core AWSSDK.S3 Amazon Amazon.Auth.AccessControlPolicy Amazon.Auth.AccessControlPolicy.ActionIdentifiers Amazon.Internal Amazon.Lambda.S3Events Amazon.MissingTypes Amazon.Runtime Amazon.Runtime.CredentialManagement Amazon.Runtime.CredentialManagement.Internal Amazon.Runtime.EventStreams Amazon.Runtime.EventStreams.Internal Amazon.Runtime.Internal Amazon.Runtime.Internal.Auth Amazon.Runtime.Internal.Settings Amazon.Runtime.Internal.Transform Amazon.Runtime.Internal.Util Amazon.Runtime.SharedInterfaces Amazon.Runtime.SharedInterfaces.Internal Amazon.S3 Amazon.S3.Encryption Amazon.S3.Encryption.Internal Amazon.S3.Internal Amazon.S3.IO Amazon.S3.Model Amazon.S3.Model.Internal.MarshallTransformations Amazon.S3.Transfer Amazon.S3.Util Amazon.Util Amazon.Util.Internal Amazon.Util.Internal.PlatformServices Newtonsoft.Json Newtonsoft.Json.Converters Newtonsoft.Json.Linq Newtonsoft.Json.Schema Newtonsoft.Json.Serialization System System.Diagnostics System.Diagnostics.Tracing System.IO.Compression ThirdParty.BouncyCastle.Asn1 ThirdParty.BouncyCastle.Asn1.Utilities ThirdParty.BouncyCastle.Math ThirdParty.BouncyCastle.OpenSsl ThirdParty.BouncyCastle.Utilities.IO.Pem ThirdParty.Ionic.Zlib ThirdParty.Json.LitJson ThirdParty.MD5 public static string PrimaryUrl => "Opportunities.aspx"; public static string UrlGroup => "/CCI"; public static DateTime cutoff { get; set; } = DateTime.Now.AddDays((((int)DateTime.Now.DayOfWeek) * -1) + 1).Date; void Main() { RunTimings(); } public void RunTimings() { cutoff = DateTime.Now.AddDays(-30); var timings = GetTimings(cutoff) .Where(t => t.Key.Group == 0 && t.Data.Any()); var outliers = timings.SelectMany(t => t.Data.Where(d => d.ClientTimeData / 1000.0d > 4.0d)); outliers.Dump(nameof(outliers)); timings .OrderByDescending(t => t.Key.ClientTimeMax > 4 ? t.Key.ClientTimeMax : 0) .ThenBy(t => t.Key.Group) .ThenBy(t => t.Key.Url.Contains(PrimaryUrl) ? "!" : t.Key.Url) .ThenBy(t => t.Key.Application) .ThenByDescending(t => t.Key.Date) .ThenBy(t => t.Key.Browser.StartsWith("IE") ? 0 : 1) .Dump(); } public IEnumerable GetTimings(DateTime cutoff) { return from log in Archive_PageLogs where (log.Url.StartsWith(UrlGroup)) && log.DateTimeStamp > cutoff group log by new { Group = (log.UserName.StartsWith("sdt\\") ? 1 : 0), log.Url, log.Browser, Date = log.DateTimeStamp.Value.Date, log.Application } into grp //join dbs in ViewActiveDatabases on grp.FirstOrDefault().DatabaseGuid equals dbs.DatabaseGUID orderby grp.Key.Group, (grp.FirstOrDefault().ClientTime / 1000) descending, grp.Key.Date descending select new MetricData { Key = new MetricKey { Application = grp.Key.Application, Group = grp.Key.Group, Url = grp.Key.Url, Browser = grp.Key.Browser, Date = grp.Key.Date, ClientTimeAvg = grp.ToList().Average(g => (g.ClientTime / 1000.0d)) ?? 0.0d, ClientTimeMin = grp.ToList().Min(g1 => (g1.ClientTime / 1000.0d)) ?? 0.0d, ClientTimeMax = grp.ToList().Max(g1 => (g1.ClientTime / 1000.0d)) ?? 0.0d, Count = grp.ToList().Count }, Data = grp .Select(g => new Metric(g.DatabaseGuid.Value, ViewActiveDatabases) { UrlParams = g.UrlParams, UserName = g.UserName, WebServer = g.WebServer, ServerTimeData = g.ServerTime ?? 0, ClientTimeData = g.ClientTime ?? 0, DatabaseName = g.DatabaseName, ClientTime = g.ClientTime ?? 0 / 1000.0d, ClientTimeAvg = grp.ToList().Average(g1 => g1.ClientTime ?? 0.0d / 1000.0d), ClientTimeMin = grp.ToList().Min(g1 => g1.ClientTime ?? 0.0d / 1000.0d), ClientTimeMax = grp.ToList().Max(g1 => g1.ClientTime ?? 0.0d / 1000.0d) }).Where(x => (x.ClientTimeData / 1000.0d) > x.ClientTimeAvg || ((x.ClientTimeData / 1000.0d) > 4.0d)) }; } // Define other methods and classes here public class Metric { public double ClientTime { get; set; } public double ClientTimeData { get; set; } public double ClientTimeAvg { get; set; } public double ClientTimeMin { get; set; } public double ClientTimeMax { get; set; } public string WebServer { get; set; } public string PhysicalName { get; set; } public Guid DatabaseGuid { get; set; } public string DatabaseName { get; set; } public string UserName { get; set; } public string UrlParams { get; set; } public int ServerTimeData { get; set; } public string OrgPin { get; set; } public Metric() { } public Metric(Guid databaseGuid, Table activeDatabases) { DatabaseGuid = databaseGuid; var activeDatabase = activeDatabases.SingleOrDefault(vad => vad.DatabaseGUID == databaseGuid); PhysicalName = activeDatabase?.PhysicalName ?? string.Empty; OrgPin = activeDatabase?.OrgPin ?? string.Empty; } object ToDump() => new { ClientTimeMax = new XElement("LINQPad.HTML", new XElement("div", new XAttribute("align", "right"), new XAttribute("style", $"background-color:{(ClientTimeMax > 4.0d ? "red" : (ClientTimeAvg < (ClientTimeData / 1000.0d)) ? "violet" : "white")};"), $"{ClientTimeMax:n}".PadLeft(10))), ClientTimeAvg, ClientTimeMin, WebServer, OrgPin, DatabaseName, PhysicalName, UserName, UrlParams, ServerTime = $"{ServerTimeData:n0}".PadLeft(10) }; } public class MetricKey { public string Application { get; set; } public int Group { get; set; } public string Browser { get; set; } public string Url { get; set; } //public int UrlLength { get; set; } public DateTime Date { get; set; } public double ClientTimeAvg { get; set; } public double ClientTimeMin { get; set; } public double ClientTimeMax { get; set; } public int Count { get; set; } object ToDump() => new { Application, GroupName = Group == 0 ? "Clients" : "Strata", Browser, URL = Url, //.PadRight(UrlLength), OnDate = Date.ToShortDateString(), Avg_ClientTime = $"{ClientTimeAvg:N2}", Min_ClientTime = $"{ClientTimeMin:N2}", Max_ClientTime = $"{ClientTimeMax:N2}", Count }; } public class MetricData { public MetricKey Key { get; set; } public IEnumerable Data { get; set; } object ToDump() => new { Group = Key, Statistics = Data .OrderBy(d => d.WebServer) .ThenBy(d => d.DatabaseName) .ThenBy(d => d.UserName) .ThenBy(d => d.ClientTimeData) }; }