public bool Exists(string?awsAccountId, string?awsRegion, string?projectName) { if (string.IsNullOrEmpty(AWSAccountId) || string.IsNullOrEmpty(AWSRegion) || string.IsNullOrEmpty(ProjectName)) { return(false); } if (string.IsNullOrEmpty(awsAccountId) || string.IsNullOrEmpty(awsRegion) || string.IsNullOrEmpty(projectName)) { return(false); } if (AWSAccountId.Equals(awsAccountId) && AWSRegion.Equals(awsRegion) && ProjectName.Equals(projectName)) { return(true); } return(false); }
public JsonResult PrivateVsPublic(string game, AWSRegion region, TimeInterval interval, string start, string end) { DateTime st = Convert.ToDateTime(start); DateTime et = Convert.ToDateTime(end); return JsonResult(GameSessionsModel.Instance.GetPrivateSessionTimeSeries(game, interval, st, et)); }
public JsonResult SessionLengthGraphData(string game, AWSRegion region, TimeInterval interval, string start, string end) { DateTime st = Convert.ToDateTime(start); DateTime et = Convert.ToDateTime(end); return JsonResult(GameSessionsModel.Instance.GetAverageSessionLength(interval, region, st, et, game)); }
public JsonResult UsersOnlineBySessionType(string game, AWSRegion region, TimeInterval interval, string start, string end) { DateTime st = Convert.ToDateTime(start); DateTime et = Convert.ToDateTime(end); return JsonResult(GameSessionsModel.Instance.UsersOnlineBySessionType(game, interval, region, st, et)); }
public List<PVTableRow> AverageSessionLengthChart(string gameShort, TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate) { GameMonitoringConfig game = Games.Instance.GetMonitoredGames().Where(x => x.ShortTitle == gameShort).FirstOrDefault(); List<PVTableRow> DataTableInfo = GetAverageSessionLengthTable(interval, region, startDate, endDate, game); return DataTableInfo; }
public List<PVTimeSeries> GetAverageSessionLength(TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate, string gameShortName) { List<PVTimeSeries> timeSeriesData = new List<PVTimeSeries>(); DataTable queryResults = new DataTable(); GameMonitoringConfig game = Games.Instance.GetMonitoredGames().Where(x => x.ShortTitle == gameShortName).FirstOrDefault(); string query = String.Format( @"select DATE(RecordCreated) as RecordTimeStamp, SessionTypeFriendly as SeriesName, round(avg(minute(timediff(RecordLastUpdateTime, RecordCreated)))) * 60 * 1000 as AverageSessionLength from {0} WHERE GameId = '{1}' AND DATE(RecordCreated) BETWEEN '{2}' and '{3}' AND minute(timediff(RecordLastUpdateTime, RecordCreated)) > 1 group by DATE(RecordCreated), SessionTypeFriendly order by RecordCreated asc;", "GameSessionMeta", game.Id, startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss")); try { queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); timeSeriesData = Charts.Instance.ProcessedTimeSeries(queryResults, interval, startDate, endDate, "RecordTimestamp"); } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return timeSeriesData; }
public JsonResult GetSpendWhales(string game, AWSRegion region, string start, string end, int cohort) { DateTime st = Convert.ToDateTime(start).ToUniversalTime(); DateTime et = Convert.ToDateTime(end).ToUniversalTime(); GameMonitoringConfig gameMonitoringConfig = Games.Instance.GetMoniteredGame(game); return JsonResult( EconomyModel.instance.GetSpendWhaleReport(gameMonitoringConfig, region, st, et, cohort) ); }
public JsonResult GetCoinFlowMacroByCategory(string game, AWSRegion region, string start, string end) { DateTime st = Convert.ToDateTime(start).ToUniversalTime(); DateTime et = Convert.ToDateTime(end).ToUniversalTime(); GameMonitoringConfig gameMonitoringConfig = Games.Instance.GetMoniteredGame(game); return JsonResult( EconomyModel.instance.GetCoinFlowByCat(gameMonitoringConfig, region, st, et) ); }
public JsonResult ReturnersDataTable(string game, AWSRegion region, string interval, string start, string end) { TimeInterval i = (TimeInterval)Enum.Parse(typeof(TimeInterval), interval); DateTime st = Convert.ToDateTime(start); DateTime et = Convert.ToDateTime(end); GameMonitoringConfig gameMonitoringConfig = Games.Instance.GetMoniteredGame(game); return JsonResult( RetentionModel.Instance.ReturnerDataTable(gameMonitoringConfig, i, region, st, et) ); }
public JsonResult DailyActiveUserByGame(string game, AWSRegion region, string interval, string start, string end) { //need to validate these TimeInterval i = (TimeInterval)Enum.Parse(typeof(TimeInterval), interval); DateTime st = Convert.ToDateTime(start); DateTime et = Convert.ToDateTime(end); GameMonitoringConfig gameMonitoringConfig = Games.Instance.GetMoniteredGame(game); return JsonResult(UsersModel.Instance.GetDailyActiveUsersByGame(gameMonitoringConfig, i, region, st, et)); }
public JsonResult DollarCostAveragePerDAU(string game, AWSRegion region, string interval, string start, string end) { DateTime st = Convert.ToDateTime(start).ToUniversalTime(); DateTime et = Convert.ToDateTime(end).ToUniversalTime(); GameMonitoringConfig gameMonitoringConfig = Games.Instance.GetMoniteredGame(game); UsersModel userModel = new UsersModel(); var timeSeries = userModel.GetDollarCostAveragePerDAU(gameMonitoringConfig, region, st, et); return JsonResult( timeSeries ); }
public List<PVTableRow> GetAverageSessionLengthTable(TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate, GameMonitoringConfig game) { #region Validation if (!interval.IsSupportedInterval(TimeInterval.Minute, TimeInterval.Year)) { throw new Exception(String.Format("Chart data only supports an interval between {0} and {1}", TimeInterval.Day, TimeInterval.Year)); } if (startDate == DateTime.MinValue || endDate == DateTime.MinValue || (startDate >= endDate)) { throw new Exception("StartDate and EndDate cannot be null, and StartDate must come before EndDate"); } if (String.IsNullOrEmpty(game.Id)) { throw new Exception("GameID cannot be empty or null"); } #endregion List<PVTableRow> dataTableData = new List<PVTableRow>(); DataTable queryResults = new DataTable(); startDate = startDate.RoundDown(interval); endDate = endDate.RoundDown(interval); string query = String.Format( @"select DATE(RecordCreated) as RecordTimeStamp, SessionTypeFriendly as SeriesName, round(avg(minute(timediff(RecordLastUpdateTime, RecordCreated)))) as AverageSessionLength from {0} WHERE GameId = '{1}' AND DATE(RecordCreated) BETWEEN '{2}' and '{3}' AND minute(timediff(RecordLastUpdateTime, RecordCreated)) > 1 group by DATE(RecordCreated), SessionTypeFriendly order by RecordCreated asc;", "GameSessionMeta", game.Id, startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss")); try { queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); dataTableData = Charts.Instance.ProcessedSessionLengthData(queryResults, interval, startDate, endDate, "RecordTimestamp"); } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return dataTableData; }
public List<PVTimeSeries> AverageSessionLength(string gameShort, TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate) { //List<OnlineBySessionTypeSeries> retVal = new List<OnlineBySessionTypeSeries>(); //need to format this data in the appropriate way as to feed it directly into high charts List<PVTimeSeries> TimeSeriesChart = GetAverageSessionLength(interval, region, startDate, endDate, gameShort); //this is the datetime format with TimeZone encoded in thatjavascript understands // the Z means UTC // and also JS Date has a method to JSON that turns the datetime into this string. // f**k yeah string IEFTFormatForJSON = DateTime.UtcNow.ToString("yyyy-MM-ddTHH:mm:ssZ"); return TimeSeriesChart; }
public JsonResult DailyActiveUser(AWSRegion region, string interval, string start, string end) { //need to validate these TimeInterval i = (TimeInterval)Enum.Parse(typeof(TimeInterval), interval); DateTime st = Convert.ToDateTime(start); DateTime et = Convert.ToDateTime(end); Dictionary<string, List<DailyActiveUserSummary>> counts = new Dictionary<string, List<DailyActiveUserSummary>>(); List<GameMonitoringConfig> games = Games.Instance.GetMonitoredGames(); foreach (GameMonitoringConfig game in games) { counts.Add(game.ShortTitle, UsersModel.Instance.GetDailyActiveUserSummaryById(game.Id, i, region, st, et)); } return JsonResult( counts ); }
public List<PVTimeSeries> AverageSessionLength(string gameShort, TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate) { GameMonitoringConfig game = Games.Instance.GetMonitoredGames().Where(x => x.ShortTitle == gameShort).FirstOrDefault(); List<PVTimeSeries> timeSeriesData = new List<PVTimeSeries>(); DataTable queryResults = new DataTable(); startDate = startDate.RoundDown(interval); endDate = endDate.RoundDown(interval); string query = String.Format( @"SELECT RecordTimestamp, SessionTypeName_0, AverageSessionTypeLength_0, SessionTypeName_1, AverageSessionTypeLength_1, SessionTypeName_2, AverageSessionTypeLength_2, SessionTypeName_3, AverageSessionTypeLength_3, SessionTypeName_4, AverageSessionTypeLength_4, SessionTypeName_5, AverageSessionTypeLength_5, SessionTypeName_6, AverageSessionTypeLength_6, SessionTypeName_7, AverageSessionTypeLength_7 FROM GameAverageSessionLength WHERE GameId = '{1}' GROUP BY RecordTimestamp, SessionTypeName_0, SessionTypeName_1, SessionTypeName_2, SessionTypeName_3, SessionTypeName_4, SessionTypeName_5, SessionTypeName_6, SessionTypeName_7 ORDER BY RecordTimestamp ASC;", "GameAverageSessionLength", game.Id); try { queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); timeSeriesData = Charts.Instance.ProcessedTimeSeries(queryResults, interval, startDate, endDate, "RecordTimestamp"); } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return timeSeriesData; }
public static void SaveConfig() { Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); SetConfigValue(config, "AzureAccountKey", AzureAccountKey); SetConfigValue(config, "AWSAccessKeyID", AWSAccessKeyID); SetConfigValue(config, "AWSSecretAccessKeyID", AWSSecretAccessKeyID); SetConfigValue(config, "SrcAzureAccountKey", SrcAzureAccountKey); SetConfigValue(config, "SrcAWSAccessKeyID", SrcAWSAccessKeyID); SetConfigValue(config, "SrcAWSSecretAccessKeyID", SrcAWSSecretAccessKeyID); SetConfigValue(config, "TargetAzureAccountKey", TargetAzureAccountKey); SetConfigValue(config, "TargetAWSAccessKeyID", TargetAWSAccessKeyID); SetConfigValue(config, "TargetAWSSecretAccessKeyID", TargetAWSSecretAccessKeyID); SetConfigValue(config, "RetryAttemptDelayInSeconds", RetryAttemptDelayInSeconds.ToString()); SetConfigValue(config, "MaxRetryAttempts", MaxRetryAttempts.ToString()); SetConfigValue(config, "DownloadDirectory", DownloadDirectory); SetConfigValue(config, "Verbose", Verbose.ToString()); SetConfigValue(config, "AmDownloading", AmDownloading.ToString()); SetConfigValue(config, "UseBlobCopy", UseBlobCopy.ToString()); SetConfigValue(config, "ListContainer", ListContainer.ToString()); SetConfigValue(config, "MonitorBlobCopy", MonitorBlobCopy.ToString()); SetConfigValue(config, "ParallelFactor", ParallelFactor.ToString()); SetConfigValue(config, "ChunkSizeInMB", ChunkSizeInMB.ToString()); SetConfigValue(config, "DestinationBlobTypeSelected", DestinationBlobTypeSelected.ToString()); SetConfigValue(config, "SkyDriveCode", SkyDriveCode.ToString()); SetConfigValue(config, "SkyDriveRefreshToken", SkyDriveRefreshToken.ToString()); SetConfigValue(config, "DropBoxAPIKey", DropBoxAPIKey.ToString()); SetConfigValue(config, "DropBoxAPISecret", DropBoxAPISecret.ToString()); SetConfigValue(config, "DropBoxUserSecret", DropBoxUserSecret.ToString()); SetConfigValue(config, "DropBoxUserToken", DropBoxUserToken.ToString()); SetConfigValue(config, "SharepointUsername", SharepointUsername.ToString()); SetConfigValue(config, "SharepointPassword", SharepointPassword.ToString()); SetConfigValue(config, "SharedAccessSignatureDurationInSeconds", SharedAccessSignatureDurationInSeconds.ToString()); SetConfigValue(config, "AWSRegion", AWSRegion.ToString()); SetConfigValue(config, "SrcAWSRegion", SrcAWSRegion.ToString()); SetConfigValue(config, "TargetAWSRegion", TargetAWSRegion.ToString()); SetConfigValue(config, "MaxExecutionTimeInMins", MaxExecutionTimeInMins.ToString()); SetConfigValue(config, "MaxServerTimeoutInMins", MaxServerTimeoutInMins.ToString()); SetConfigValue(config, "BlobCopyBatchSize", BlobCopyBatchSize.ToString()); config.Save(ConfigurationSaveMode.Modified); ConfigurationManager.RefreshSection("appSettings"); }
/// <summary> /// Extracts the system name of a region for a bucket from parameter value /// </summary> /// <param name="paramValue"></param> /// <param name="paramName"></param> /// <returns></returns> public static string BucketRegionFromParam(object paramValue, string paramName) { if (paramValue is string) { return((paramValue as string).Trim()); } PSObject bucketRegionObj = paramValue as PSObject; if (bucketRegionObj != null && bucketRegionObj.BaseObject != null) { AWSRegion awsRegion = bucketRegionObj.BaseObject as AWSRegion; if (awsRegion != null) { return(awsRegion.Region); } } throw new ArgumentException(string.Format("Expected string system name or AWSRegion instance for {0} parameter", paramName)); }
public List<PVTimeSeries> ReturnerTimeSeries(GameMonitoringConfig game, TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate) { List<PVTimeSeries> SeriesList = new List<PVTimeSeries>(); string query = String.Format("SELECT Date, NURR, CURR, RURR from Retention as view WHERE view.Date BETWEEN '{0}' AND '{1}' order by view.Date desc", startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss")); int daysDifference = 0; DataTable result = DBManager.Instance.Query(Datastore.Monitoring, query); if (result.HasRows()) { DateTime minDate = startDate.Date; daysDifference = (endDate.Date - startDate.Date).Days; foreach (DataRow row in result.Rows) { DateTime currentDay = DateTime.Parse(row["Date"].ToString()); int daysBetween = (currentDay - minDate).Days; PVTimeSeries NewReturnsSeries = SeriesList.FirstOrDefault(x => x.name == "New Returns"); PVTimeSeries ContinuingReturnsSeries = SeriesList.FirstOrDefault(x => x.name == "Continuing Returns"); PVTimeSeries ReactivatedReturnsSeries = SeriesList.FirstOrDefault(x => x.name == "Reactivated Returns"); if (NewReturnsSeries == default(PVTimeSeries)) { NewReturnsSeries = new PVTimeSeries(); NewReturnsSeries.name = "New Returns"; NewReturnsSeries.data = new List<int>(); NewReturnsSeries.pointStart = result.Rows[0].Field<DateTime>("Date").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds NewReturnsSeries.pointInterval = Convert.ToInt32(TimeSeriesPointInterval.day); NewReturnsSeries.type = "line"; for (int z = 0; z < daysDifference; z++) { NewReturnsSeries.data.Add(0); } SeriesList.Add(NewReturnsSeries); } if (ContinuingReturnsSeries == default(PVTimeSeries)) { ContinuingReturnsSeries = new PVTimeSeries(); ContinuingReturnsSeries.name = "Continuing Returns"; ContinuingReturnsSeries.data = new List<int>(); ContinuingReturnsSeries.pointStart = result.Rows[0].Field<DateTime>("Date").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds ContinuingReturnsSeries.pointInterval = Convert.ToInt32(TimeSeriesPointInterval.day); ContinuingReturnsSeries.type = "line"; for (int z = 0; z < daysDifference; z++) { ContinuingReturnsSeries.data.Add(0); } SeriesList.Add(ContinuingReturnsSeries); } if (ReactivatedReturnsSeries == default(PVTimeSeries)) { ReactivatedReturnsSeries = new PVTimeSeries(); ReactivatedReturnsSeries.name = "Reactivated Returns"; ReactivatedReturnsSeries.data = new List<int>(); ReactivatedReturnsSeries.pointStart = result.Rows[0].Field<DateTime>("Date").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds ReactivatedReturnsSeries.pointInterval = Convert.ToInt32(TimeSeriesPointInterval.day); ReactivatedReturnsSeries.type = "line"; for (int z = 0; z < daysDifference; z++) { ReactivatedReturnsSeries.data.Add(0); } SeriesList.Add(ReactivatedReturnsSeries); } int index = daysBetween; ReactivatedReturnsSeries.data[index] = (int)row.Field<decimal?>("RURR").GetValueOrDefault(-1); ContinuingReturnsSeries.data[index] = (int)row.Field<decimal?>("CURR").GetValueOrDefault(-1); NewReturnsSeries.data[index] = (int)row.Field<decimal?>("NURR").GetValueOrDefault(-1); } } return SeriesList; }
public List<Dictionary<string, object>> ReturnerDataTable(GameMonitoringConfig game, TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate) { List<PVTimeSeries> SeriesList = new List<PVTimeSeries>(); string query = String.Format(@"SELECT `Retention`.`Date`, `Retention`.`WAU`, `Retention`.`NewUserCohort`, `Retention`.`ContinuingUsersCohort`, `Retention`.`ReactivatedUsersCohort`, `Retention`.`NUR`, `Retention`.`CUR`, `Retention`.`RUR`, `Retention`.`NURR`, `Retention`.`CURR`, `Retention`.`RURR` FROM `Moniverse`.`Retention` order by Date desc;", startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss")); DataTable dt = DBManager.Instance.Query(Datastore.Monitoring, query); return JSONFriendifyDataTable(dt); }
public PVPPieChart GetBuyWhaleReport(GameMonitoringConfig game, AWSRegion region, DateTime start, DateTime end, int cohort) { string gameId = game.Id; string thresholdQuery = String.Format( @" # Get threshold for top x% spenders in dollars SET @startDate = '{0}'; SET @endDate = '{1}'; SET @rownum = 0, @prev_val = NULL, @top_percent= {2}; # Calculate threshold for top x% of spenders SELECT IFNULL(min(score),0.0) as threshold, IFNULL(max(row),0) as numUsers FROM ( SELECT @rownum := @rownum + 1 AS row, @prev_val := score AS score, UserId FROM ( select sum(CostAmount) as score, UserId from Moniverse.Economy_GameCreditTransactions where GameId = '{3}' and TransactionType = 0 # addCredits and CostAmount > 0 # money was actually spent and Status = 1 # finalized transaction and CreatedOn between @startDate and @endDate group by UserId order by score desc ) as spending ORDER BY score DESC ) as rankedSpending WHERE row <= ceil((@top_percent/100 * @rownum)); # ceil helps return at least one row for small datasets ", start.ToString("yyyy-MM-dd HH:mm:ss"), end.ToString("yyyy-MM-dd HH:mm:ss"), cohort, gameId); DataTable thresholdDatable = DBManager.Instance.Query(Datastore.Monitoring, thresholdQuery); decimal threshold = thresholdDatable.Rows[0].Field<decimal>("threshold"); long numUsers = thresholdDatable.Rows[0].Field<long>("numUsers"); string query = String.Format( @" # Get people that spent more than threshold for a given date range SET @startDate = '{0}'; SET @endDate = '{1}'; # Grab users that have spent >= threshold DROP TEMPORARY TABLE IF EXISTS topSpenders; CREATE TEMPORARY TABLE IF NOT EXISTS topSpenders (PRIMARY KEY (UserId)) ( select sum(CostAmount) as score, UserId from Moniverse.Economy_GameCreditTransactions where GameId = '{2}' and TransactionType = 0 # addCredits and CostAmount > 0 # money was actually spent and Status = 1 # finalized transaction and CreatedOn between @startDate and @endDate group by UserId having score >= {3} order by score desc ); # Get items bought by the top spenders SELECT UserData as category, Sum(CostAmount) as total, count(*) as count FROM Economy_GameCreditTransactions AS egct INNER JOIN topSpenders AS topSpenders ON egct.UserId = topSpenders.UserId where GameId = '{2}' and TransactionType = 0 # addCredits and CostAmount > 0 # money was actually spent and Status = 1 # finalized transaction and CreatedOn between @startDate and @endDate GROUP BY UserData order by total desc; ", start.ToString("yyyy-MM-dd HH:mm:ss"), end.ToString("yyyy-MM-dd HH:mm:ss"), gameId, threshold); DataTable result = DBManager.Instance.Query(Datastore.Monitoring, query); var categoryData = new List<PVPieChartCategories>(); foreach (DataRow row in result.Rows) { var category = new PVPieChartCategories() { name = row.Field<String>("category"), value = row.Field<Decimal>("total") }; category.metadata.Add("count", row["count"].ToString()); categoryData.Add(category); } var pieChart = new PVPPieChart() { title = cohort + "%", subtitle = (categoryData.Any() ? string.Format("Users: ~{0}<br/>Threshold: >= ${1}", numUsers, threshold) : "No data"), categoryData = categoryData }; return pieChart; }
public FlowDataView GetCoinFlowByCat(GameMonitoringConfig game, AWSRegion region, DateTime start, DateTime end) { FlowDataView chart = new FlowDataView() { Inflows = GetFlowDataByCategory(game.Id, TransactionType.AddCredits, start, end), Outflows = GetFlowDataByCategory(game.Id, TransactionType.Purchase, start, end), StartDate = start.ToUnixTimestamp() * 1000 }; return chart; }
public FlowDataView GetCoinFlow(string game, AWSRegion region, DateTime start, DateTime end) { GameMonitoringConfig GameToGet = Games.Instance.GetMonitoredGames().Where(x => x.ShortTitle == game).FirstOrDefault(); FlowDataView chart = new FlowDataView() { Inflows = GetFlowData(GameToGet.Id, TransactionType.AddCredits, start, end), Outflows = GetFlowData(GameToGet.Id, TransactionType.Purchase, start, end), StartDate = start.ToUnixTimestamp() * 1000 }; return chart; }
public List<PVTimeSeries> UsersOnlineBySessionType(string gameShort, TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate) { GameMonitoringConfig game = Games.Instance.GetMonitoredGames().Where(x => x.ShortTitle == gameShort).FirstOrDefault(); List<PVTimeSeries> timeSeriesData = new List<PVTimeSeries>(); DataTable queryResults = new DataTable(); startDate = startDate.RoundDown(interval); endDate = endDate.RoundDown(interval); string query = String.Format( @"SELECT RecordTimestamp, SessionTypeName_0, SUM(SessionTypeUsers_0) AS SessionTypeUsers_0, SessionTypeName_1, SUM(SessionTypeUsers_1) AS SessionTypeUsers_1, SessionTypeName_2, SUM(SessionTypeUsers_2) AS SessionTypeUsers_2, SessionTypeName_3, SUM(SessionTypeUsers_3) AS SessionTypeUsers_3, SessionTypeName_4, SUM(SessionTypeUsers_4) AS SessionTypeUsers_4, SessionTypeName_5, SUM(SessionTypeUsers_5) AS SessionTypeUsers_5, SessionTypeName_6, SUM(SessionTypeUsers_6) AS SessionTypeUsers_6, SessionTypeName_7, SUM(SessionTypeUsers_7) AS SessionTypeUsers_7, Other, SUM(SessionTypeUsers_Other) AS SessionTypeUsers_Other FROM ( SELECT RecordTimestamp, RegionName, SessionTypeName_0, ROUND(AVG(SessionTypeUsers_0)) AS SessionTypeUsers_0, SessionTypeName_1, ROUND(AVG(SessionTypeUsers_1)) AS SessionTypeUsers_1, SessionTypeName_2, ROUND(AVG(SessionTypeUsers_2)) AS SessionTypeUsers_2, SessionTypeName_3, ROUND(AVG(SessionTypeUsers_3)) AS SessionTypeUsers_3, SessionTypeName_4, ROUND(AVG(SessionTypeUsers_4)) AS SessionTypeUsers_4, SessionTypeName_5, ROUND(AVG(SessionTypeUsers_5)) AS SessionTypeUsers_5, SessionTypeName_6, ROUND(AVG(SessionTypeUsers_6)) AS SessionTypeUsers_6, SessionTypeName_7, ROUND(AVG(SessionTypeUsers_7)) AS SessionTypeUsers_7, 'Other', ROUND(AVG(SessionTypeUsers_Other)) AS SessionTypeUsers_Other FROM {0} WHERE GameId = '{1}' AND RecordTimestamp BETWEEN '{2}' AND '{3}' AND RegionName like '{4}' GROUP BY RecordTimestamp, RegionName, SessionTypeName_0, SessionTypeName_1, SessionTypeName_2, SessionTypeName_3, SessionTypeName_4, SessionTypeName_5, SessionTypeName_6, SessionTypeName_7, 'Other' ) AGGSESSIONS GROUP BY RecordTimestamp, SessionTypeName_0, SessionTypeName_1, SessionTypeName_2, SessionTypeName_3, SessionTypeName_4, SessionTypeName_5, SessionTypeName_6, SessionTypeName_7, Other ORDER BY RecordTimestamp ASC;", String.Format("GameUserActivity{0}", interval.ToDbTableString()), game.Id, startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss"), region.GetDatabaseString()); try { // Get time series data queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); if (queryResults.HasRows()) { foreach (DataRow row in queryResults.Rows) { foreach (DataColumn col in queryResults.Columns) { if ((col.ColumnName.Contains("SessionTypeName") || col.ColumnName == "Other") && !String.IsNullOrEmpty(row[col.ColumnName].ToString())) { int count = Convert.ToInt32(row[col.Ordinal + 1].ToString()); PVTimeSeries series = timeSeriesData.FirstOrDefault(x => x.name == row[col.ColumnName].ToString()); if (series == default(PVTimeSeries)) { series = new PVTimeSeries { name = row[col.ColumnName].ToString(), data = new List<int>(), pointStart = row.Field<DateTime>("RecordTimestamp").ToUnixTimestamp() * 1000, //JS unix timestamp is in milliseconds pointInterval = (int)interval * 60 * 1000, //JS unix timestamp is in milliseconds type = "area" }; int zerosCount = ((int)(endDate - startDate).TotalMinutes / (int)interval) + 1; for (int i = 0; i < zerosCount; i++) { series.data.Add(0); } timeSeriesData.Add(series); } else { DateTime timeStamp = row.Field<DateTime>("RecordTimestamp"); int index = (int)(timeStamp - startDate).TotalMinutes / (int)interval; series.data[index] = count; } } } } } } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return timeSeriesData; }
public List<PVTimeSeries> GetDailyActiveUsersByGame(GameMonitoringConfig game, TimeInterval interval, AWSRegion region, DateTime startdate, DateTime enddate) { DateTime minDate = startdate.Date; int daysDifference = (enddate.Date - startdate.Date).Days + 1; List<PVTimeSeries> result = new List<PVTimeSeries>(); string query = String.Format( @"SELECT RecordTimestamp ,Count FROM MonitoringStats WHERE Type = 'DailyActiveUsers' AND GameId = '{0}' AND DATE(RecordTimestamp) BETWEEN DATE('{1}') and DATE('{2}') ORDER BY RecordTimestamp;", game.Id, startdate.ToString("yyyy-MM-dd HH:mm:ss"), enddate.ToString("yyyy-MM-dd HH:mm:ss")); try { DataTable queryResult = DBManager.Instance.Query(Datastore.Monitoring, query); if (queryResult.HasRows()) { PVTimeSeries series = result.FirstOrDefault(x => x.name == "DailyActiveUsers"); if (series == default(PVTimeSeries)) { series = new PVTimeSeries(); series.name = "Users"; series.data = new List<int>(); series.pointStart = queryResult.Rows[0].Field<DateTime>("RecordTimestamp").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds series.pointInterval = 1440 * 60 * 1000; //JS unix timestamp is in milliseconds series.type = "column"; result.Add(series); for (int i = 0; i < daysDifference; i++) { series.data.Add(0); } } foreach (DataRow row in queryResult.Rows) { int index = (row.Field<DateTime>("RecordTimestamp").Date - minDate).Days; series.data[index] = Convert.ToInt32(row["Count"].ToString()); } } } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return result; }
public List<PVTableRow> GetAverageSessionLengthTable(TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate, GameMonitoringConfig game) { #region Validation if (!interval.IsSupportedInterval(TimeInterval.Minute, TimeInterval.Year)) { throw new Exception(String.Format("Chart data only supports an interval between {0} and {1}", TimeInterval.Day, TimeInterval.Year)); } if (startDate == DateTime.MinValue || endDate == DateTime.MinValue || (startDate >= endDate)) { throw new Exception("StartDate and EndDate cannot be null, and StartDate must come before EndDate"); } if (String.IsNullOrEmpty(game.Id)) { throw new Exception("GameID cannot be empty or null"); } #endregion List<PVTableRow> dataTableData = new List<PVTableRow>(); DataTable queryResults = new DataTable(); startDate = startDate.RoundDown(interval); endDate = endDate.RoundDown(interval); string query = String.Format( @"SELECT RecordTimestamp, SessionTypeName_0, AverageSessionTypeLength_0, SessionTypeName_1, AverageSessionTypeLength_1, SessionTypeName_2, AverageSessionTypeLength_2, SessionTypeName_3, AverageSessionTypeLength_3, SessionTypeName_4, AverageSessionTypeLength_4, SessionTypeName_5, AverageSessionTypeLength_5, SessionTypeName_6, AverageSessionTypeLength_6, SessionTypeName_7, AverageSessionTypeLength_7 FROM GameAverageSessionLength WHERE GameId = '{1}' GROUP BY RecordTimestamp, SessionTypeName_0, SessionTypeName_1, SessionTypeName_2, SessionTypeName_3, SessionTypeName_4, SessionTypeName_5, SessionTypeName_6, SessionTypeName_7 ORDER BY RecordTimestamp DESC;", "GameAverageSessionLength", game.Id); try { queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); dataTableData = Charts.Instance.ProcessedDataTable(queryResults, interval, startDate, endDate, "RecordTimestamp"); } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return dataTableData; }
public List<DailyActiveUserSummary> GetDailyActiveUserSummaryById(string gameId, TimeInterval interval, AWSRegion region, DateTime startdate, DateTime enddate) { List<DailyActiveUserSummary> result = new List<DailyActiveUserSummary>(); string query = String.Format( @"SELECT RecordTimestamp ,Count FROM MonitoringStats WHERE Type = 'DailyActiveUsers' AND GameId = '{0}' AND DATE(RecordTimestamp) BETWEEN DATE('{1}') and DATE('{2}') ORDER BY RecordTimestamp;", gameId, startdate.ToString("yyyy-MM-dd HH:mm:ss"), enddate.ToString("yyyy-MM-dd HH:mm:ss")); try { DataTable queryResult = DBManager.Instance.Query(Datastore.Monitoring, query); if (queryResult.HasRows()) { foreach (DataRow row in queryResult.Rows) { result.Add(new DailyActiveUserSummary() { RecordTimestamp = row.Field<DateTime>("RecordTimestamp"), Count = row.Field<int>("Count") }); } } } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return result; }
public TimeSeriesDataNew GetConcurrentUsersSessionType(TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate, GameMonitoringConfig game) { #region Validation if (!interval.IsSupportedInterval(TimeInterval.Minute, TimeInterval.Year)) { throw new Exception(String.Format("Chart data only supports an interval between {0} and {1}", TimeInterval.Day, TimeInterval.Year)); } //if (region != 0) { // throw new Exception("write check for valid region"); //} if (startDate == DateTime.MinValue || endDate == DateTime.MinValue || (startDate >= endDate)) { throw new Exception("StartDate and EndDate cannot be null, and StartDate must come before EndDate"); } if (String.IsNullOrEmpty(game.Id)) { throw new Exception("GameID cannot be empty or null"); } #endregion TimeSeriesDataNew timeSeriesData = new TimeSeriesDataNew(); DataTable queryResults = new DataTable(); startDate = startDate.RoundDown(interval); endDate = endDate.RoundDown(interval); string query = String.Format( @"SELECT RecordTimestamp, SessionTypeName_0, SUM(SessionTypeUsers_0) AS SessionTypeUsers_0, SessionTypeName_1, SUM(SessionTypeUsers_1) AS SessionTypeUsers_1, SessionTypeName_2, SUM(SessionTypeUsers_2) AS SessionTypeUsers_2, SessionTypeName_3, SUM(SessionTypeUsers_3) AS SessionTypeUsers_3, SessionTypeName_4, SUM(SessionTypeUsers_4) AS SessionTypeUsers_4, SessionTypeName_5, SUM(SessionTypeUsers_5) AS SessionTypeUsers_5, SessionTypeName_6, SUM(SessionTypeUsers_6) AS SessionTypeUsers_6, SessionTypeName_7, SUM(SessionTypeUsers_7) AS SessionTypeUsers_7, Other, SUM(SessionTypeUsers_Other) AS SessionTypeUsers_Other FROM ( SELECT RecordTimestamp, RegionName, SessionTypeName_0, ROUND(AVG(SessionTypeUsers_0)) AS SessionTypeUsers_0, SessionTypeName_1, ROUND(AVG(SessionTypeUsers_1)) AS SessionTypeUsers_1, SessionTypeName_2, ROUND(AVG(SessionTypeUsers_2)) AS SessionTypeUsers_2, SessionTypeName_3, ROUND(AVG(SessionTypeUsers_3)) AS SessionTypeUsers_3, SessionTypeName_4, ROUND(AVG(SessionTypeUsers_4)) AS SessionTypeUsers_4, SessionTypeName_5, ROUND(AVG(SessionTypeUsers_5)) AS SessionTypeUsers_5, SessionTypeName_6, ROUND(AVG(SessionTypeUsers_6)) AS SessionTypeUsers_6, SessionTypeName_7, ROUND(AVG(SessionTypeUsers_7)) AS SessionTypeUsers_7, 'Other', ROUND(AVG(SessionTypeUsers_Other)) AS SessionTypeUsers_Other FROM {0} WHERE GameId = '{1}' AND RecordTimestamp BETWEEN '{2}' AND '{3}' AND RegionName like '{4}' GROUP BY RecordTimestamp, RegionName, SessionTypeName_0, SessionTypeName_1, SessionTypeName_2, SessionTypeName_3, SessionTypeName_4, SessionTypeName_5, SessionTypeName_6, SessionTypeName_7, 'Other' ) AGGSESSIONS GROUP BY RecordTimestamp, SessionTypeName_0, SessionTypeName_1, SessionTypeName_2, SessionTypeName_3, SessionTypeName_4, SessionTypeName_5, SessionTypeName_6, SessionTypeName_7, Other ORDER BY RecordTimestamp ASC;", String.Format("GameUserActivity{0}", interval.ToDbTableString()), game.Id, startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss"), region.GetDatabaseString()); try { // Get time series data queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); timeSeriesData = Charts.Instance.GetTimeSeriesNewData(queryResults, interval, startDate, endDate, "RecordTimestamp"); } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return timeSeriesData; }
public List<PVTimeSeries> GetUsersByRegion(TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate, GameMonitoringConfig game) { #region Validation //if (!interval.IsSupportedInterval(TimeInterval.Minute, TimeInterval.Year)) //{ // throw new Exception(String.Format("Chart data only supports an interval between {0} and {1}", TimeInterval.Day, TimeInterval.Year)); //} //if (startDate == DateTime.MinValue || endDate == DateTime.MinValue || (startDate >= endDate)) //{ // throw new Exception("StartDate and EndDate cannot be null, and StartDate must come before EndDate"); //} //if (String.IsNullOrEmpty(game.Id)) //{ // throw new Exception("GameID cannot be empty or null"); //} #endregion List<PVTimeSeries> timeSeriesData = new List<PVTimeSeries>(); // Init dates startDate = startDate.RoundDown(interval).ToUniversalTime(); endDate = endDate.RoundDown(interval).ToUniversalTime(); string query = String.Format( @"SELECT RecordTimestamp, RegionName, GameSessionUsers FROM {0} WHERE GameId = '{1}' AND RecordTimestamp BETWEEN '{2}' AND '{3}' ORDER BY RecordTimestamp, RegionName ASC;", String.Format("GameUserActivity{0}", interval.ToDbTableString()), game.Id, startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss")); try { // Get time series data DataTable queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); foreach (DataRow row in queryResults.Rows) { PVTimeSeries series = timeSeriesData.FirstOrDefault(x => x.name == row["RegionName"].ToString()); if (series == default(PVTimeSeries)) { series = new PVTimeSeries { name = row["RegionName"].ToString(), data = new List<int>(), pointStart = queryResults.Rows[0].Field<DateTime>("RecordTimestamp").ToUnixTimestamp() * 1000, //JS unix timestamp is in milliseconds pointInterval = (int)interval * 60 * 1000, type = "area" }; int zerosCount = ((int)(endDate - startDate).TotalMinutes / (int)interval) + 1; for (int i = 0; i < zerosCount; i++) { series.data.Add(0); } timeSeriesData.Add(series); } DateTime timeStamp = row.Field<DateTime>("RecordTimestamp"); int index = (int)(timeStamp - startDate).TotalMinutes / (int)interval; series.data[index] = Convert.ToInt32(row["GameSessionUsers"].ToString()); } } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return timeSeriesData; }
public JsonResult GetUsersByRegion(string game, AWSRegion region, TimeInterval interval, string start, string end) { DateTime st = Convert.ToDateTime(start); DateTime et = Convert.ToDateTime(end); GameMonitoringConfig gameMonitoringConfig = Games.Instance.GetMoniteredGame(game); return JsonResult( UsersModel.Instance.GetUsersByRegion(interval, region, st, et, gameMonitoringConfig) ); }
public GeoJsonData GetLoginDenistyMap(TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate, GameMonitoringConfig game) { GeoJsonData returnValue = new GeoJsonData() { type = "FeatureCollection", features = new List<GeoJsonFeature>() }; string query = string.Format(@"select Country, Region, City, Latitude, Longitude, DATE_SUB(LoginTimestamp, INTERVAL (IFNULL(HOUR(LoginTimestamp) % FLOOR(60 / 60), 0) * 60 * 60) + ((MINUTE(LoginTimestamp) % 60) * 60) + SECOND(LoginTimestamp) SECOND) AS Date, COUNT(UserId) as c from UserSessionMeta where DATE(LoginTimestamp) > '2015-07-12' AND LoginTimestamp BETWEEN '{0}' and '{1}' AND Latitude IS NOT NULL AND Longitude IS NOT NULL group by Date, Latitude, Longitude order by c desc, LoginTimestamp desc;", startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss")); DataTable dt = DBManager.Instance.Query(Datastore.Monitoring, query); foreach (DataRow data in dt.Rows) { List<float> floatList = new List<float>(); //cooridinates floatList.Add((data["Longitude"].ToString() == null || data["Longitude"].ToString() == "") ? 0 : float.Parse(data["Longitude"].ToString())); floatList.Add((data["Latitude"].ToString() == null || data["Latitude"].ToString() == "") ? 0 : float.Parse(data["Latitude"].ToString())); //description string desc = string.Format(@" {0}, {1}, {2} - {3}", (data["City"].ToString() == null || data["City"].ToString() == "") ? "" : data["City"].ToString(), (data["Region"].ToString() == null || data["Region"].ToString() == "") ? "" : data["Region"].ToString(), (data["Country"].ToString() == null || data["Country"].ToString() == "") ? "" : data["Country"].ToString(), (data["c"].ToString() == null || data["c"].ToString() == "") ? 0 : Convert.ToInt32(data["c"].ToString())); returnValue.features.Add(new GeoJsonFeature() { type = "Feature", geometry = new GeoJsonGeometry() { type = "Point", coordinates = floatList.ToArray() }, properties = new GeoJsonProperties() { count = (data["c"].ToString() == null || data["c"].ToString() == "") ? 0 : Convert.ToInt32(data["c"].ToString()), timestamp = (data["Country"].ToString() == null || data["Country"].ToString() == "") ? 0 : DateTime.Parse(data["Date"].ToString()).ToUnixTimestamp() * 1000 } }); } return returnValue; }
public List<PVTimeSeries<double>> GetDollarCostAveragePerDAU(GameMonitoringConfig gameMonitoringConfig, AWSRegion awsRegion, DateTime start, DateTime end) { int daysDifference = (end.Date - start.Date).Days + 1; DateTime minDate = start.Date; var summary = (new AWSModel()).FetchNetflixIceData(start, end); var retention = this.GetDailyActiveUsersByGame(gameMonitoringConfig, TimeInterval.Day, AWSRegion.All, start, end); List<PVTimeSeries<double>> allSeries = new List<PVTimeSeries<double>>(); var pvDAU = new PVTimeSeries<double>() { data = new List<double>(), name = "Daily Active Users", pointInterval = 24 * 60 * 60 * 1000, pointStart = minDate.ToUnixTimestamp() * 1000, type = "column" }; for (int i = 0; i < daysDifference; i++) { int dailyActiveUsers = retention[0].data[i]; pvDAU.data.Add(dailyActiveUsers); } allSeries.Add(pvDAU); var pvTimeSeries = new PVTimeSeries<double>() { data = new List<double>(), name = "Dollar Cost per DAU", pointInterval = 24 * 60 * 60 * 1000, pointStart = minDate.ToUnixTimestamp() * 1000, type = "line", yAxis = 1 }; for (int i = 0; i < daysDifference; i++) { double cost = summary.data.aggregated[i]; int dailyActiveUsers = retention[0].data[i]; double dollarCostPerDAU = dailyActiveUsers == 0 ? 0 : cost / dailyActiveUsers; pvTimeSeries.data.Add(dollarCostPerDAU); } allSeries.Add(pvTimeSeries); return allSeries; }