Ejemplo n.º 1
0
 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");
 }
Ejemplo n.º 2
0
        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");
        }
Ejemplo n.º 3
0
        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());
        }
Ejemplo n.º 4
0
        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(),
                });
            }
        }
Ejemplo n.º 5
0
 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());
     }
 }
Ejemplo n.º 6
0
        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);
        }
Ejemplo n.º 7
0
        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");
        }
Ejemplo n.º 8
0
        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");
        }
Ejemplo n.º 9
0
 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()
         });
     }
 }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 11
0
        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);
        }
Ejemplo n.º 12
0
        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);
        }
Ejemplo n.º 13
0
        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");
        }
Ejemplo n.º 14
0
        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");
        }
Ejemplo n.º 15
0
        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");
        }