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");
}
