public MetricService(IConfiguration config, MetricDbContext context) { this._context = context; // var client = new MongoClient(config.GetConnectionString("BookstoreDb")); // var database = client.GetDatabase("BookstoreDb"); // _books = database.GetCollection<Book>("Books"); }
public async Task <string> GetHighUsageJob(int gauge) { // Get active users of version per month const string getUsagePerVersion = @"SELECT N.Month, N.Year, COUNT(N.InstallId) as Count FROM( SELECT M.Year, M.Month, SUM(M.Total) as Count, M.InstallId FROM( SELECT a.InstallId, DATEPART(year, a.Timestamp) as [Year], DATEPART(month, a.Timestamp) as [Month], DATEPART(day, a.Timestamp) as [Day], RANK() OVER (PARTITION BY a.InstallId, DATEPART(year, a.Timestamp), DATEPART(month, a.Timestamp) ORDER BY DATEPART(year, a.Timestamp), DATEPART(month, a.Timestamp), a.InstallId) as Total FROM [MetricEvents] as a, [MetricEventDefinitions] as b WHERE a.MetricId = b.MetricId and b.MetricName = 'OpenApplication' AND a.AppId = {0} GROUP BY a.InstallId, DATEPART(year, a.Timestamp), DATEPART(month, a.Timestamp), DATEPART(day, a.Timestamp) )M GROUP BY M.Year, M.Month, M.InstallId HAVING SUM(M.Total) >= {1} )N GROUP BY N.Year, N.Month ORDER BY N.Year, N.Month"; var result = await SqlToList <AggregationPerMonth>(getUsagePerVersion, MetricDbContext.AppEditorId, gauge); var cacheQuery = "UPDATE [MetricCache] SET JsonData = '" + JsonConvert.SerializeObject(result) + "' WHERE Type = 'get-high-usage-job'"; using (var db = new MetricDbContext()) { await db.Database.ExecuteSqlCommandAsync(cacheQuery); } return("Done"); }
public IHttpActionResult Push(NewMetricMessage newMetric) { // If special guid, then don't store anything var ipAddress = GetIPAddress(); if (newMetric.SpecialId == ByPassSpecialGuid) //filter out SSKK by default { Trace.TraceInformation("/api/push-metric (origin: {0}) with SpecialGuid not saved {1}", ipAddress, JsonConvert.SerializeObject(newMetric)); return(Ok()); } // TODO: Disable pooling for now to make sure that we don't starve global ThreadPool as it is used also by ASP.net // Put this immediately in a worker thread //ThreadPool.QueueUserWorkItem(state => //{ var clock = Stopwatch.StartNew(); MetricEvent result; using (var db = new MetricDbContext()) { result = db.SaveNewMetric(newMetric, ipAddress); } Trace.TraceInformation("/api/push-metric New metric saved in {0}ms: {1}", clock.ElapsedMilliseconds, JsonConvert.SerializeObject(result)); //}); return(Ok()); }
public async Task <List <int> > GetActiveUsersLastDays() { const string getActiveUsersLastDays = @"SELECT COUNT(*) as [Count] FROM ( SELECT 1 as [Count] FROM [MetricEvents] as a, [MetricEventDefinitions] as b WHERE a.MetricId = b.MetricId and b.MetricName = 'OpenApplication' AND a.AppId = {{0}} {0} GROUP BY a.InstallId HAVING COUNT(*) > 5 ) XX"; using (var db = new MetricDbContext()) { return(new List <int>() { // In the past 30 days await db.Database.SqlQuery <int>(string.Format(getActiveUsersLastDays, "AND a.Timestamp > {1}"), MetricDbContext.AppEditorId, date30Days).FirstOrDefaultAsync(), // In the past [-60, -30] days await db.Database.SqlQuery <int>(string.Format(getActiveUsersLastDays, "AND a.Timestamp > {1} AND a.Timestamp <= {2}"), MetricDbContext.AppEditorId, date60Days, date30Days).FirstOrDefaultAsync(), }); } }
protected async Task <List <T> > SqlToList <T>(string sqlQuery, params object[] parameters) { using (var db = new MetricDbContext()) { return(await db.Database.SqlQuery <T>(sqlQuery, parameters).ToListAsync()); } }
public static void UseMetricServer(this IAppBuilder metricsApp, string authorityServer = null) { //authorityServer = authorityServer ?? "http://localhost:44300/"; BundleConfig.RegisterBundles(BundleTable.Bundles); MetricDbContext.Initialize(); //metricsApp.UseIdentityServerBearerTokenAuthentication(new IdentityServerBearerTokenAuthenticationOptions //{ // Authority = authorityServer, // RequiredScopes = new[] // { // "metrics.write" // }, //}); var config = new HttpConfiguration(); // Enable this to view webapi exception in details. Only for debug, not for production! //config.IncludeErrorDetailPolicy = IncludeErrorDetailPolicy.Always; // Remove XML and use #if !DEBUG config.Filters.Add(new RequireHttpsAttribute()); #endif config.Formatters.Remove(config.Formatters.XmlFormatter); config.Formatters.JsonFormatter.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver(); config.Services.Replace(typeof(IHttpControllerTypeResolver), new HttpControllerTypeResolver()); config.MapHttpAttributeRoutes(); metricsApp.UseWebApi(config); }
public async Task <string> GetProjectsUsersJob() { var res = await SqlToList <ProjectsUsersAggregation>(projectsUsersScraper); var cacheQuery = "UPDATE [MetricCache] SET JsonData = '" + JsonConvert.SerializeObject(res) + "' WHERE Type = 'get-projects-users-job'"; using (var db = new MetricDbContext()) { await db.Database.ExecuteSqlCommandAsync(cacheQuery); } return("Done"); }
public async Task <string> GetCountriesJob(int daysInPast) { var query = daysInPast == 0 ? getUsersCountriesTotal : getUsersCountriesLastMonth; var res = await SqlToList <AggregationPerValue>(query, MetricDbContext.AppEditorId, daysInPast); var cacheQuery = "UPDATE [MetricCache] SET JsonData = '" + JsonConvert.SerializeObject(res) + $"' WHERE Type = 'get-countries-job/{daysInPast}'"; using (var db = new MetricDbContext()) { await db.Database.ExecuteSqlCommandAsync(cacheQuery); } return("Done"); }
public async Task <List <int> > GetInstallsCount() { using (var db = new MetricDbContext()) { const string getInstallsCount = @"SELECT COUNT(*) as Count FROM [MetricInstalls] a"; // Get total installs count return(new List <int>() { await db.Database.SqlQuery <int>(getInstallsCount).FirstOrDefaultAsync(), await db.Database.SqlQuery <int>(getInstallsCount + " WHERE a.[Created] < {0}", date30Days) .FirstOrDefaultAsync() }); } }
public static int GetMetricId(this MetricDbContext context, Guid metricGuid) { int id; lock (MetricIds) { if (!MetricIds.TryGetValue(metricGuid, out id)) { id = -1; var metricDef = context.MetricDefinitions.FirstOrDefault(t => t.MetricGuid == metricGuid); if (metricDef != null) { id = metricDef.MetricId; MetricIds.Add(metricGuid, id); } } } return(id); }
public static int GetApplicationId(this MetricDbContext context, Guid appGuid) { int id; lock (AppIds) { if (!AppIds.TryGetValue(appGuid, out id)) { id = -1; var app = context.Apps.FirstOrDefault(t => t.AppGuid == appGuid); if (app != null) { id = app.AppId; AppIds.Add(appGuid, id); } } } return(id); }
public static int GetOrCreateInstallId(this MetricDbContext context, Guid installGuid) { int id; lock (InstallIds) { if (!InstallIds.TryGetValue(installGuid, out id)) { // Create automatically install id on first encounter var install = context.Installs.FirstOrDefault(t => t.InstallGuid == installGuid); if (install == null) { install = new MetricInstall(installGuid); context.Installs.Add(install); context.SaveChanges(); } id = install.InstallId; InstallIds.Add(installGuid, id); } } return(id); }
public async Task <string> GetActiveUsersJob() { const string getActiveUsers = @" SELECT MONTH(Timestamp) as Month, YEAR(Timestamp) as Year, SUM(SessionTime) as Time, COUNT(InstallId) as Sessions, COUNT(DISTINCT InstallId) as Users FROM ( SELECT DISTINCT InstallId, SessionId, TRY_CAST(MetricValue AS DECIMAL(18,2)) as SessionTime, Timestamp, RANK () OVER (PARTITION BY InstallId, SessionId ORDER BY TRY_CAST(MetricValue AS DECIMAL(18,2)) DESC, EventId) as N FROM [MetricEvents] AS a, [MetricEventDefinitions] AS b WHERE a.MetricId = b.MetricId and b.MetricName = 'SessionHeartbeat2' or a.MetricId = b.MetricId and b.MetricName = 'CloseSession2')M WHERE N = 1 AND SessionTime > 0 GROUP BY MONTH(Timestamp), YEAR(Timestamp), N ORDER BY YEAR(Timestamp), MONTH(Timestamp) "; var result = await SqlToList <ActiveUsersView>(getActiveUsers); var cacheQuery = "UPDATE [MetricCache] SET JsonData = '" + JsonConvert.SerializeObject(result) + "' WHERE Type = 'get-active-users-job'"; using (var db = new MetricDbContext()) { await db.Database.ExecuteSqlCommandAsync(cacheQuery); } return("Done"); }
public async Task <string> GetCrashesPerVersionJob() { const string getCrashesPerVersion = @" SELECT sq.InstallId, sq.SessionId, sq.MetricSent, MetricValue as Version FROM [MetricEvents] AS a, [MetricEventDefinitions] AS b, (SELECT InstallId, SessionId, MetricValue as MetricSent FROM [MetricEvents] AS a, [MetricEventDefinitions] AS b WHERE a.MetricId = b.MetricId and b.MetricName = 'CrashedSession') as sq WHERE a.MetricId = b.MetricId and b.MetricName = 'OpenApplication' and sq.InstallId = a.InstallId and sq.SessionId = a.SessionId AND a.AppId = {0} AND DATEDIFF(day ,a.Timestamp, CURRENT_TIMESTAMP) < 30 GROUP BY sq.InstallId, sq.SessionId, MetricValue, sq.MetricSent ORDER BY Version "; const string getActivityData = @" SELECT MetricValue as Version, SUM(Time) as Time FROM [MetricEvents] AS a, [MetricEventDefinitions] AS b, (SELECT InstallId, SessionId, MONTH(Timestamp) as Month, YEAR(Timestamp) as Year, SUM(SessionTime) as Time FROM ( SELECT DISTINCT a.InstallId, a.SessionId, TRY_CAST(MetricValue AS DECIMAL(18,2)) as SessionTime, Timestamp, RANK () OVER (PARTITION BY InstallId, SessionId ORDER BY TRY_CAST(MetricValue AS DECIMAL(18,2)) DESC, EventId) as N FROM [MetricEvents] AS a, [MetricEventDefinitions] AS b WHERE a.MetricId = b.MetricId and b.MetricName = 'SessionHeartbeat2' or a.MetricId = b.MetricId and b.MetricName = 'CloseSession2' AND DATEDIFF(day ,a.Timestamp, CURRENT_TIMESTAMP) < 30) as M WHERE N = 1 AND SessionTime > 0 GROUP BY MONTH(Timestamp), YEAR(Timestamp), InstallId, SessionId) as Activity WHERE a.MetricId = b.MetricId and b.MetricName = 'OpenApplication' and Activity.SessionId = a.SessionId and Activity.InstallId = a.InstallId and a.AppId = {0} GROUP BY MetricValue ORDER BY Version "; var versionAndCrashes = new Dictionary <string, int>(); var mining = await SqlToList <CrashAggregation>(getCrashesPerVersion, MetricDbContext.AppEditorId); foreach (var crashAggregation in mining) { if (crashAggregation.Version.StartsWith("1.20")) { continue; } if (crashAggregation.Version.Contains("alpha")) { continue; } if (versionAndCrashes.ContainsKey(crashAggregation.Version)) { var current = versionAndCrashes[crashAggregation.Version]; versionAndCrashes[crashAggregation.Version] = current + 1; } else { versionAndCrashes.Add(crashAggregation.Version, 1); } } var versionActivity = new Dictionary <string, decimal>(); var activity = await SqlToList <ActivityData>(getActivityData, MetricDbContext.AppEditorId); foreach (var activityData in activity) { versionActivity.Add(activityData.Version, activityData.Time); } var res = new List <CrashAggregationResult>(); foreach (var versionAndCrash in versionAndCrashes) { decimal time; if (versionActivity.TryGetValue(versionAndCrash.Key, out time)) { var ratio = versionAndCrash.Value / ((double)time / 60.0 / 60.0); res.Add(new CrashAggregationResult { Version = versionAndCrash.Key, Ratio = ratio }); } } var cacheQuery = "UPDATE [MetricCache] SET JsonData = '" + JsonConvert.SerializeObject(res) + "' WHERE Type = 'get-crashes-per-version-job'"; using (var db = new MetricDbContext()) { await db.Database.ExecuteSqlCommandAsync(cacheQuery); } return("Done"); }
public async Task <string> GetQuittingCountJob() { // Get active users per month const string getActiveUsersPerMonth = @" SELECT [Year], [Month], InstallId FROM ( SELECT DATEPART(year, a.Timestamp) as [Year], DATEPART(month, a.Timestamp) as [Month], InstallId FROM [MetricEvents] as a, [MetricEventDefinitions] as b WHERE a.MetricId = b.MetricId and b.MetricName = 'OpenApplication' AND a.AppId = {0} GROUP BY DATEPART(year, a.Timestamp), DATEPART(month, a.Timestamp), a.InstallId HAVING COUNT(*) > 0 ) N GROUP BY [Year], [Month], InstallId ORDER BY [Year], [Month] "; var installations = await SqlToList <AggregationPerInstall>(getActiveUsersPerMonth, MetricDbContext.AppEditorId); var installationsCopy = installations.ToList(); var result = new List <AggregationPerMonth>(); foreach (var aggregationPerInstall in installations) { var date = new DateTime(aggregationPerInstall.Year, aggregationPerInstall.Month, 1); if (result.Count == 0) { result.Add(new AggregationPerMonth { Count = 0, Year = aggregationPerInstall.Year, Month = aggregationPerInstall.Month }); } var future = date.AddMonths(1); if (future > new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)) { continue; } var persists = installationsCopy.Any( x => aggregationPerInstall.InstallId == x.InstallId && new DateTime(x.Year, x.Month, 1) == future); if (persists) { continue; } { var resultMonth = result.FirstOrDefault(x => x.Year == future.Year && x.Month == future.Month); if (resultMonth == null) { resultMonth = new AggregationPerMonth { Month = future.Month, Year = future.Year }; result.Add(resultMonth); } resultMonth.Count++; } } var cacheQuery = "UPDATE [MetricCache] SET JsonData = '" + JsonConvert.SerializeObject(result) + $"' WHERE Type = 'get-quitting-count-job'"; using (var db = new MetricDbContext()) { await db.Database.ExecuteSqlCommandAsync(cacheQuery); } return("Done"); }