public void CaptureTransactionsForLastFullDay(GameMonitoringConfig game)
{
DateTime end = DateTime.Now.Date;
DateTime start = end.AddDays(-1);
List<PlayversePurchaseAggregation> purchaseDays = GetPurchaseTransactions(game.Id, start, end);
RecordPurchaseTransactionAggregation(purchaseDays);
}
public void CheckActiveUserDelta(GameMonitoringConfig game)
{
//MoniverseNotification notification = UserNotification.ActiveUserDeltaThreshold(game);
MoniverseNotification notification = UserNotification.ActiveUserDeltaThreshold(game);
List<INotifier> NotifierList;
if (!UserNotification.RunningNotifications.TryGetValue(game.ShortTitle, out NotifierList))
{
NotifierList = new List<INotifier>();
UserNotification.RunningNotifications.TryAdd(game.ShortTitle, NotifierList);
}
if (notification.ShouldSend)
{
if (UserNotification.RunningNotifications.TryGetValue(game.ShortTitle, out NotifierList))
{
if (NotifierList.Exists(x => {
Notifier notifier = (Notifier)x;
return notifier.NotificationId == notification.Id;
}))
{
return;
}
else
{
INotifier OnlineUserCount = new Notifier(game.Id, MessageTopic.Users, notification);
NotifierList.Add(OnlineUserCount);
int counter = 0;
int interval = 1000 * 60 * 5;
int notifyCount = 3;
OnlineUserCount.SendTick(() =>
{
MoniverseNotification ThresholdCheck = UserNotification.CheckOnlineUserCount(game, notifyCount - counter - 1, interval);
OnlineUserCount.setNotification(ThresholdCheck);
if (counter >= notifyCount)
ThresholdCheck.ShouldSend = false;
if (!ThresholdCheck.ShouldSend) {
UserNotification.RunningNotifications.TryGetValue(game.ShortTitle, out NotifierList);
NotifierList.Remove(OnlineUserCount);
}
counter++;
return ThresholdCheck.ShouldSend;
}, interval);
}
}
}
}
public List<PlayversePurchaseAggregation> getTransactionsForGameFromRange(GameMonitoringConfig Game, DateTime start, DateTime end)
{
//GameShortTitle = GameShortTitle.ToUpper();
//GameMonitoringConfig game = Games.Instance.GetMonitoredGames().Where(x => x.ShortTitle == GameShortTitle).FirstOrDefault();
//if (game == null) {
// throw new Exception("No Game by that name");
//}
return GetPurchaseTransactions(Game.Id, start, end);
}
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 void CaptureLatest(GameMonitoringConfig game)
{
List<PlayversePurchaseAggregation> purchaseDays = GetLatestTransactions(game.Id);
RecordLatestTransactions(purchaseDays);
}
public static MoniverseNotification ActiveUserDeltaThreshold(GameMonitoringConfig game)
{
MoniverseNotification notification = new MoniverseNotification()
{
Id = "UserDelta",
ShouldSend = false
};
string subject = "{0} Users Online Dropped {1}";
string message = "[{0} Alert {1}] | Game: {2} - Number of Users Online Dropped by {3} over past {4} minutes which is over the threshold of {5}";
Logger.Instance.Info(String.Format("Check User Delta for {0}", game.Id));
// Get last three user count snapshots
string query = String.Format(
@"SELECT RecordTimestamp,
SUM(GameSessionUsers) AS GameSessionUsers
FROM (
SELECT DATE_SUB(RecordTimestamp, INTERVAL (IFNULL(HOUR(RecordTimestamp) % FLOOR(3 / 60), 0) * 60 * 60) + ((MINUTE(RecordTimestamp) % 3) * 60) + SECOND(RecordTimestamp) SECOND) AS RecordTimestamp,
ROUND(AVG(GameSessionUsers)) AS GameSessionUsers,
RegionName
FROM GameUserActivity
WHERE GameId = '{0}'
AND DATE_SUB(RecordTimestamp, INTERVAL (IFNULL(HOUR(RecordTimestamp) % FLOOR(3 / 60), 0) * 60 * 60) + ((MINUTE(RecordTimestamp) % 3) * 60) + SECOND(RecordTimestamp) SECOND) > UTC_TIMESTAMP() - INTERVAL 10 MINUTE
GROUP BY DATE_SUB(RecordTimestamp, INTERVAL (IFNULL(HOUR(RecordTimestamp) % FLOOR(3 / 60), 0) * 60 * 60) + ((MINUTE(RecordTimestamp) % 3) * 60) + SECOND(RecordTimestamp) SECOND),
RegionName
) USERS
GROUP BY RecordTimestamp
ORDER BY RecordTimestamp DESC
LIMIT 0, 3;",
game.Id);
DataTable queryResults = DBManager.Instance.Query(Datastore.Monitoring, query);
if (!queryResults.HasRows() || queryResults.Rows.Count != 3)
{
if (game.IsNotificationSettingEnabled(NotificationLevel.PVSupport))
{
new Notifier(Games.EMPTYGAMEID, MessageTopic.Error, new MoniverseNotification()
{
Id = "DBERROR",
Message = "Not Enough User Rows returned in Online User Check",
Subject = "Online User Check Failed"
}).SendOnce();
Logger.Instance.Info(String.Format("Not Enough User Rows Returned", game.Id));
}
}
int currentUsers = (int)queryResults.Rows[0].Field<decimal>("GameSessionUsers");
int users_3MinsAgo = (int)queryResults.Rows[1].Field<decimal>("GameSessionUsers");
int users_6MinsAgo = (int)queryResults.Rows[2].Field<decimal>("GameSessionUsers");
string recordTimestamp = queryResults.Rows[0].Field<DateTime>("RecordTimestamp").ToString();
// Check notification thresholds and send notification of delta warning or error is necessary
float sixMinDropPercent = 1.0f - ((float)currentUsers / (float)users_6MinsAgo);
float threeMinDropPercent = 1.0f - ((float)currentUsers / (float)users_3MinsAgo);
string sixMinuteDropF = Math.Abs((decimal)(sixMinDropPercent * 100)).ToString("#.##\\%");
//
if (sixMinDropPercent > game.ActiveUserDeltaThresholdPct_6Min)
{
notification.ShouldSend = true;
notification.Message = String.Format(message, NotificationLevel.Error.ToString(), recordTimestamp, game.Title, sixMinuteDropF, 6, game.ActiveUserDeltaThresholdPct_6Min);
notification.Subject = String.Format(subject, game.ShortTitle, sixMinuteDropF);
}
else if (threeMinDropPercent > game.ActiveUserDeltaThresholdPct_6Min)
{
notification.ShouldSend = true;
notification.Message = String.Format(message, NotificationLevel.Error.ToString(), recordTimestamp, game.Title, sixMinuteDropF, 6, game.ActiveUserDeltaThresholdPct_6Min);
notification.Subject = String.Format(subject, game.ShortTitle, sixMinuteDropF);
}
else if (threeMinDropPercent > game.ActiveUserDeltaThresholdPct_3Min)
{
notification.ShouldSend = true;
notification.Message = String.Format(message, NotificationLevel.Error.ToString(), recordTimestamp, game.Title, sixMinuteDropF, 6, game.ActiveUserDeltaThresholdPct_6Min);
notification.Subject = String.Format(subject, game.ShortTitle, sixMinuteDropF);
}
return notification;
}
public void CaptureGameCreditTransactions(GameMonitoringConfig game)
{
List<PlayverseGameCreditTransaction> data = GetGameCreditTransactions(game.Id);
RecordGameCreditTransactions(data);
}
public void RecordActiveUsers(GameMonitoringConfig game)
{
try
{
if (game == null)
{
throw new Exception("GameInfo cannot be null or empty");
}
// Init the session type specific collections
string[] sessionTypes = game.ActiveUserSessionTypes.ToArray();
string[] sessionTypeNames = new string[8];
for (int i = 0; i < 8; i++)
{
if (i < sessionTypes.Count())
{
sessionTypeNames[i] = sessionTypes[i];
}
else
{
sessionTypeNames[i] = String.Empty;
}
}
// Grab regional counts
DataTable queryResults = DBManager.Instance.Query(Datastore.General, GetSessionTypeUserCountByRegionQueryStr(game.Id, sessionTypes.ToList()));
if (queryResults.HasRows())
{
List<RegionSessionTypeCounts> gameSessionUserResults = new List<RegionSessionTypeCounts>();
foreach (DataRow row in queryResults.Rows)
{
gameSessionUserResults.Add(new RegionSessionTypeCounts()
{
RegionName = row.Field<string>("RegionName"),
SessionType = row.Field<string>("SessionType"),
Count = (int)row.Field<long>("Count")
});
}
// Grab global playverse game counts
int eventListeners = DBManager.Instance.QueryForCount(Datastore.General, GetEventListenersCountQueryStr(game.Id));
int titleScreenUsers = eventListeners - gameSessionUserResults.Sum(x => x.Count);
int otherSessionTypeUsers = 0;
DateTime timestamp = DateTime.UtcNow;
// Grab the region counts and insert the region specific metric
foreach (IGrouping<string, RegionSessionTypeCounts> region in gameSessionUserResults.GroupBy(x => x.RegionName))
{
int gameSessionUsers = region.Sum(x => x.Count);
int[] sessionTypeUserCounts = new int[8];
for (int i = 0; i < 8; i++)
{
if (sessionTypeNames[i] != String.Empty)
{
RegionSessionTypeCounts regionSessionType = region.Where(x => x.SessionType == sessionTypeNames[i]).FirstOrDefault();
if (regionSessionType != null)
{
sessionTypeUserCounts[i] = regionSessionType.Count;
}
}
}
// Grab the other session type region count
RegionSessionTypeCounts otherRegionSessionType = region.Where(x => x.SessionType == "Other").FirstOrDefault();
if (otherRegionSessionType != null)
{
otherSessionTypeUsers = otherRegionSessionType.Count;
}
// Store new game user activity record
string insertQuery = String.Format(
@"INSERT INTO GameUserActivity
(GameId, RegionName, RecordTimestamp, GameSessionUsers, EventListeners, TitleScreenUsers, SessionTypeName_0, SessionTypeUsers_0,
SessionTypeName_1, SessionTypeUsers_1, SessionTypeName_2, SessionTypeUsers_2, SessionTypeName_3, SessionTypeUsers_3,
SessionTypeName_4, SessionTypeUsers_4, SessionTypeName_5, SessionTypeUsers_5, SessionTypeName_6, SessionTypeUsers_6,
SessionTypeName_7, SessionTypeUsers_7, SessionTypeUsers_Other)
VALUES('{0}','{1}','{2}',{3},{4},{5},'{6}',{7},'{8}',{9},'{10}',{11},'{12}',{13},'{14}',{15},'{16}',{17},'{18}',{19},'{20}',{21}, {22})",
game.Id,
region.Key,
//truncated seconds
timestamp.ToString("yyyy-MM-dd HH:mm"),
gameSessionUsers,
eventListeners,
titleScreenUsers,
sessionTypeNames[0],
sessionTypeUserCounts[0],
sessionTypeNames[1],
sessionTypeUserCounts[1],
sessionTypeNames[2],
sessionTypeUserCounts[2],
sessionTypeNames[3],
sessionTypeUserCounts[3],
sessionTypeNames[4],
sessionTypeUserCounts[4],
sessionTypeNames[5],
sessionTypeUserCounts[5],
sessionTypeNames[6],
sessionTypeUserCounts[6],
sessionTypeNames[7],
sessionTypeUserCounts[7],
otherSessionTypeUsers);
try
{
MoniverseResponse response = new MoniverseResponse()
{
Status = "unsent",
TimeStamp = DateTime.UtcNow
};
lock (MoniverseBase.ConsoleWriterLock)
{
Console.ForegroundColor = ConsoleColor.Cyan;
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("Beginning Insert of Active Users");
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("");
}
Users.Service(Service =>
{
response = Service.Insert(new MoniverseRequest()
{
TaskName = "Insert RecordActiveUsers",
Task = insertQuery,
TimeStamp = DateTime.UtcNow
});
});
//int result = DBManager.Instance.Insert(Datastore.Monitoring, insertQuery);
lock (MoniverseBase.ConsoleWriterLock)
{
Console.ForegroundColor = ConsoleColor.Green;
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("Active User Success");
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("");
Logger.Instance.Info("Done inserting Retention Row Data.");
Logger.Instance.Info(String.Format("success!! {0}", response.Status));
Logger.Instance.Info("");
Console.ResetColor();
}
}
catch (Exception e)
{
Console.ForegroundColor = ConsoleColor.Red;
Logger.Instance.Info(e.Message);
Console.ResetColor();
}
}
}
}
catch (Exception ex)
{
Logger.Instance.Exception(String.Format("Game: {0} - {1} | Message: {2}", game.Id, game.Title, ex.Message), ex.StackTrace);
}
}
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;
}
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 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 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 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 int GetPartiesCount(GameMonitoringConfig game)
{
int result = 0;
string query = String.Format(
@"SELECT COUNT(*)
FROM Party
WHERE GameId = '{0}';",
game.Id);
try
{
result = DBManager.Instance.QueryForCount(Datastore.General, query);
}
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 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<GameUserActivity> GetGameUserActivity(GameMonitoringConfig game, int days)
{
List<GameUserActivity> result = new List<GameUserActivity>();
string query = String.Format(
@"SELECT *
FROM GameUserActivity
WHERE GameId = '{0}'
AND RecordTimestamp BETWEEN DATE_SUB(UTC_TIMESTAMP(), INTERVAL {1} DAY) AND UTC_TIMESTAMP()
ORDER BY RecordTimestamp ASC;",
game.Id,
days);
try
{
DataTable queryResult = DBManager.Instance.Query(Datastore.Monitoring, query);
if (queryResult.HasRows())
{
foreach (DataRow row in queryResult.Rows)
{
result.Add(new GameUserActivity()
{
GameId = row.Field<string>("GameId"),
RegionName = row.Field<string>("RegionName"),
RecordTimestamp = row.Field<DateTime>("RecordTimestamp"),
GameSessionUsers = row.Field<int>("GameSessionUsers"),
EventListeners = row.Field<int>("EventListeners"),
TitleScreenUsers = row.Field<int>("TitleScreenUsers"),
SessionTypeName_0 = row.Field<string>("SessionTypeName_0"),
SessionTypeUsers_0 = row.Field<int>("SessionTypeUsers_0"),
SessionTypeName_1 = row.Field<string>("SessionTypeName_1"),
SessionTypeUsers_1 = row.Field<int>("SessionTypeUsers_1"),
SessionTypeName_2 = row.Field<string>("SessionTypeName_2"),
SessionTypeUsers_2 = row.Field<int>("SessionTypeUsers_2"),
SessionTypeName_3 = row.Field<string>("SessionTypeName_3"),
SessionTypeUsers_3 = row.Field<int>("SessionTypeUsers_3"),
SessionTypeName_4 = row.Field<string>("SessionTypeName_4"),
SessionTypeUsers_4 = row.Field<int>("SessionTypeUsers_4"),
SessionTypeName_5 = row.Field<string>("SessionTypeName_5"),
SessionTypeUsers_5 = row.Field<int>("SessionTypeUsers_5"),
SessionTypeName_6 = row.Field<string>("SessionTypeName_6"),
SessionTypeUsers_6 = row.Field<int>("SessionTypeUsers_6"),
SessionTypeName_7 = row.Field<string>("SessionTypeName_7"),
SessionTypeUsers_7 = row.Field<int>("SessionTypeUsers_7"),
SessionTypeUsers_Other = row.Field<int>("SessionTypeUsers_Other")
});
}
}
}
catch (Exception ex)
{
Logger.Instance.Exception(ex.Message, ex.StackTrace);
}
return result;
}
public List<GameSessionUserStats> GetGameSessionUserStats(GameMonitoringConfig game, int days)
{
List<GameSessionUserStats> result = new List<GameSessionUserStats>();
string query = String.Format(
@"SELECT *
FROM GameSessionUserStats
WHERE GameId = '{0}'
AND RecordTimestamp BETWEEN DATE_SUB(UTC_TIMESTAMP(), INTERVAL {1} DAY) AND UTC_TIMESTAMP()
ORDER BY RecordTimestamp ASC;",
game.Id,
days);
try
{
DataTable queryResult = DBManager.Instance.Query(Datastore.Monitoring, query);
if (queryResult.HasRows())
{
foreach (DataRow row in queryResult.Rows)
{
result.Add(new GameSessionUserStats()
{
GameId = row.Field<string>("GameId"),
SessionType = row.Field<string>("SessionType"),
MaxNumPlayers = row.Field<int>("MaxNumPlayers"),
AvgPlayers = row.Field<decimal>("AvgPlayers"),
Sessions = row.Field<int>("Sessions"),
PrivateAvgPlayers = row.Field<decimal>("PrivateAvgPlayers"),
PrivateSessions = row.Field<int>("PrivateSessions"),
TotalAvgPlayers = row.Field<decimal>("TotalAvgPlayers"),
TotalSessions = row.Field<int>("TotalSessions"),
RecordTimestamp = row.Field<DateTime>("RecordTimestamp")
});
}
}
}
catch (Exception ex)
{
Logger.Instance.Exception(ex.Message, ex.StackTrace);
}
return result;
}
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<string> GetPrivateSessionCompareTypes(GameMonitoringConfig game)
{
List<string> result = new List<string>();
string query = String.Format(
@"SELECT SessionType
FROM GameSessionUserStats
WHERE GameId = '{0}'
GROUP BY SessionType
HAVING MAX(Sessions) > 1
AND MAX(PrivateSessions) > 1;"
, game.Id);
try
{
DataTable dt = DBManager.Instance.Query(Datastore.Monitoring, query);
if (dt.HasRows())
{
foreach (DataRow row in dt.Rows)
{
string sessionType = row.Field<string>("SessionType");
if (sessionType != null && !String.IsNullOrEmpty(sessionType))
{
result.Add(sessionType);
}
}
}
}
catch (Exception ex)
{
Logger.Instance.Exception(ex.Message, ex.StackTrace);
}
return result;
}
public List<PVTimeSeries> GetCurrentOnline(TimeInterval interval, DateTime start, DateTime end, 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 (start == DateTime.MinValue || end == DateTime.MinValue || (start >= end))
{
throw new Exception("StartDate and EndDate cannot be null, and StartDate must come before EndDate");
}
start = start.RoundDown(interval).ToUniversalTime();
end = end.RoundDown(interval).ToUniversalTime();
#endregion
string query = String.Format(
@"SELECT RecordTimestamp,
Title,
sum(GameSessionUsers) as GameSessionUsers
FROM (
SELECT RecordTimestamp,
GMC.Title,
GUA.RegionName,
round(avg(GameSessionUsers)) as GameSessionUsers
FROM {0} GUA
INNER JOIN GameMonitoringConfig GMC
ON GUA.GameId = GMC.GameId
WHERE RecordTimestamp BETWEEN '{1}' AND '{2}'
AND GMC.GameId = '{3}'
GROUP BY RecordTimestamp, GMC.Title, GUA.RegionName
) USERS
GROUP BY RecordTimestamp, Title
ORDER BY RecordTimestamp ASC, Title ASC;",
String.Format("GameUserActivity{0}", interval.ToDbTableString()),
start.ToString("yyyy-MM-dd HH:mm:ss"),
end.ToString("yyyy-MM-dd HH:mm:ss"),
game.Id);
List<PVTimeSeries> SeriesList = new List<PVTimeSeries>();
try
{
// Get time series data
DataTable queryResults = DBManager.Instance.Query(Datastore.Monitoring, query);
foreach (DataRow row in queryResults.Rows)
{
PVTimeSeries series = SeriesList.FirstOrDefault(x => x.name == row["Title"].ToString());
if (series == default(PVTimeSeries))
{
series = new PVTimeSeries();
series.name = row["Title"].ToString();
series.data = new List<int>();
series.pointStart = queryResults.Rows[0].Field<DateTime>("RecordTimestamp").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds
series.pointInterval = (int)interval * 60 * 1000; //JS unix timestamp is in milliseconds
series.type = "area";
int zerosCount = ((int)(end - start).TotalMinutes / (int)interval) + 1;
for (int i = 0; i < zerosCount; i++)
{
series.data.Add(0);
}
SeriesList.Add(series);
}
DateTime timeStamp = row.Field<DateTime>("RecordTimestamp");
int index = (int)(timeStamp - start).TotalMinutes / (int)interval;
series.data[index] = Convert.ToInt32(row["GameSessionUsers"].ToString());
}
}
catch (Exception ex)
{
Logger.Instance.Exception(ex.Message, ex.StackTrace);
}
return SeriesList;
}
public void RecordGameSessionUserStats(GameMonitoringConfig game)
{
try
{
if (game == null)
{
throw new Exception("GameInfo cannot be null or empty");
}
foreach (PrivacyCompareSessionTypes privacyCompare in game.PrivacyCompareSessionTypes)
{
DataTable result = DBManager.Instance.Query(Datastore.General, GetGameSessionUserStatsQueryStr(game.Id, privacyCompare.PublicTypes, privacyCompare.PrivateTypes));
if (result.HasRows())
{
string query = String.Format(
@"INSERT INTO GameSessionUserStats
(GameId, SessionType, MaxNumPlayers, AvgPlayers, Sessions, PrivateAvgPlayers
,PrivateSessions, TotalAvgPlayers, TotalSessions, RecordTimestamp)
VALUES ('{0}', '{1}', {2}, {3}, {4}, {5}, {6}, {7}, {8}, '{9}');",
result.Rows[0].Field<string>("GameId"),
result.Rows[0].Field<string>("SessionType"),
result.Rows[0].Field<int>("MaxNumPlayers"),
result.Rows[0].Field<decimal>("AvgPlayers"),
result.Rows[0].Field<long>("Sessions"),
result.Rows[0].Field<decimal>("PrivateAvgPlayers"),
result.Rows[0].Field<long>("PrivateSessions"),
result.Rows[0].Field<decimal>("TotalAvgPlayers"),
result.Rows[0].Field<long>("TotalSessions"),
DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
try
{
MoniverseResponse response = new MoniverseResponse()
{
Status = "unsent",
TimeStamp = DateTime.UtcNow
};
lock (MoniverseBase.ConsoleWriterLock)
{
Console.ForegroundColor = ConsoleColor.Cyan;
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("Beginning Insert of Game Session User Stats");
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("");
}
Users.Service(Service =>
{
response = Service.Insert(new MoniverseRequest()
{
TaskName = "Insert",
Task = query,
TimeStamp = DateTime.UtcNow
});
});
//int res = DBManager.Instance.Insert(Datastore.Monitoring, query);
lock (MoniverseBase.ConsoleWriterLock)
{
Console.ForegroundColor = ConsoleColor.Green;
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("Game Session User Stats success");
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("");
Logger.Instance.Info(String.Format("success!! {0}", response.Status));
Console.ResetColor();
}
}
catch (Exception e)
{
Console.ForegroundColor = ConsoleColor.Red;
Logger.Instance.Info(e.Message);
Console.ResetColor();
}
}
}
}
catch (Exception ex)
{
Logger.Instance.Exception(String.Format("Game: {0} - {1} | Message: {2}", game.Id, game.Title, ex.Message), ex.StackTrace);
}
}
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 List<GameMonitoringConfig> GetMonitoredGames()
{
List<GameMonitoringConfig> games = new List<GameMonitoringConfig>();
// Get game info configuration
string query =
@"SELECT *
FROM GameMonitoringConfig;";
try {
DataTable queryResults = DBManager.Instance.Query(Datastore.Monitoring, query);
// Collect game info configuration from result
if (queryResults.HasRows())
{
foreach (DataRow row in queryResults.Rows)
{
GameMonitoringConfig game = new GameMonitoringConfig();
game.Id = row.Field<string>("GameId");
game.Title = row.Field<string>("Title");
game.ShortTitle = row.Field<string>("ShortTitle");
game.LaunchTime = row.Field<DateTime>("LaunchTime");
game.ActiveUserSessionTypes = row.Field<string>("ActiveUserSessionTypes").Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
game.ActiveUserDeltaThresholdPct_3Min = (float)row.Field<decimal>("ActiveUserDeltaThresholdPct_3Min");
game.ActiveUserDeltaThresholdPct_6Min = (float)row.Field<decimal>("ActiveUserDeltaThresholdPct_6Min");
List<PrivacyCompareSessionTypes> privacyCompares = new List<PrivacyCompareSessionTypes>();
foreach (string privacyCompare in row.Field<string>("PrivacyCompareSessionTypes").Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries).ToList())
{
string[] compares = privacyCompare.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
if (compares.Count() == 2)
{
List<string> publicTypes = compares[0].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
List<string> privateTypes = compares[1].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
if (publicTypes.Any() && privateTypes.Any())
{
privacyCompares.Add(new PrivacyCompareSessionTypes()
{
PublicTypes = publicTypes,
PrivateTypes = privateTypes
});
}
}
}
game.PrivacyCompareSessionTypes = privacyCompares;
game.MaxRunningHostingInstances = row.Field<int>("MaxRunningHostingInstances");
game.NotificationSettings = new NotificationSettings()
{
Info = Convert.ToBoolean(row.Field<ulong>("Notifications_Info")),
Warning = Convert.ToBoolean(row.Field<ulong>("Notifications_Warning")),
Error = Convert.ToBoolean(row.Field<ulong>("Notifications_Error")),
PVSupport = Convert.ToBoolean(row.Field<ulong>("Notifications_PVSupport"))
};
games.Add(game);
}
}
}
catch (Exception e) {
Logger.Instance.Exception(e.Message, e.StackTrace);
}
return games;
}
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 static MoniverseNotification TestNotification(GameMonitoringConfig game)
{
MoniverseNotification notification = new MoniverseNotification()
{
Id = "TestNotification",
Message = "TESTMESSAGE",
Subject = "TEST TEST TEST"
};
return notification;
}
public void RecordDailyActiveUsersByGame(GameMonitoringConfig game)
{
try
{
if (game == null)
{
throw new Exception("GameInfo cannot be null or empty");
}
if (!dau_nextGameRecordTime.ContainsKey(game.Id))
{
dau_nextGameRecordTime.TryAdd(game.Id, DateTime.UtcNow.Date);
}
DateTime nextRecordTime = dau_nextGameRecordTime[game.Id];
if (nextRecordTime <= DateTime.UtcNow)
{
// Check if game DAU record exists for yesterday
string query = CheckStatsRecordExistsQueryStr(game.Id, TYPE_DAU, nextRecordTime.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss"));
int count = DBManager.Instance.QueryForCount(Datastore.Monitoring, query);
if (count == 0)
{
// Get count of game DAU from RecentPlayers
query = GetRecentPlayerCountQueryStr(game.Id, nextRecordTime.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss"), nextRecordTime.AddMinutes(-1).ToString("yyyy-MM-dd HH:mm:ss"));
count = DBManager.Instance.QueryForCount(Datastore.General, query);
// Insert game DAU record into Monitoring datastore
query = InsertStatsRecordQueryStr(game.Id, TYPE_DAU, count, nextRecordTime.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss"));
try
{
MoniverseResponse response = new MoniverseResponse()
{
Status = "unsent",
TimeStamp = DateTime.UtcNow
};
lock (MoniverseBase.ConsoleWriterLock)
{
Console.ForegroundColor = ConsoleColor.Cyan;
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("Beginning Insert of DAU By Game");
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("");
}
Users.Service(Service =>
{
response = Service.Insert(new MoniverseRequest()
{
TaskName = game.ShortTitle + "DAU Insert",
Task = query,
TimeStamp = DateTime.UtcNow
});
});
//int result = DBManager.Instance.Insert(Datastore.Monitoring, query);
lock (MoniverseBase.ConsoleWriterLock)
{
Console.ForegroundColor = ConsoleColor.Green;
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("DAU By Game Success");
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("");
Logger.Instance.Info(String.Format("success!! {0}", response.Status));
Console.ResetColor();
}
}
catch (Exception e)
{
Console.ForegroundColor = ConsoleColor.Red;
Logger.Instance.Info(e.Message);
Console.ResetColor();
}
}
// Increment by a day so the record is only collected once a day after midnight
dau_nextGameRecordTime[game.Id] = nextRecordTime.AddDays(1);
}
}
catch (Exception ex)
{
Logger.Instance.Exception(String.Format("Game: {0} - {1} | Message: {2}", game.Id, game.Title, ex.Message), ex.StackTrace);
}
}
public List<DailyActiveUserSummary> GetDailyActiveUserSummary(GameMonitoringConfig game)
{
List<DailyActiveUserSummary> result = new List<DailyActiveUserSummary>();
string query = String.Format(
@"SELECT RecordTimestamp
,Count
FROM MonitoringStats
WHERE Type = 'DailyActiveUsers'
AND GameId = '{0}'
ORDER BY RecordTimestamp;",
game.Id);
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 static MoniverseNotification CheckOnlineUserCount(GameMonitoringConfig game, int counter, int milliseconds)
{
int count = DBManager.Instance.QueryForCount(Datastore.General, Users.Instance.GetGameSessionUserCountQueryStr(game.Id));
string formatString = "";
if (counter == 0)
{
formatString = "{1} > !ALERT! currently online: {0} notification interval set at {2}";
}
else
{
formatString = "{1} > !ALERT! currently online: {0} notification interval set at {2} ({3} Notifications pending)";
}
string alert = String.Format(formatString, count, game.ShortTitle, TimeSpan.FromMilliseconds(milliseconds).TotalMinutes, counter);
return new MoniverseNotification()
{
Message = alert,
Subject = alert,
Id = "CheckOnlineUserCount",
ShouldSend = true
};
}
public void CheckComputeHostingInstances(GameMonitoringConfig game)
{
DataTable Result = new DataTable();
string query = String.Format(@"SELECT 0 as 'ID'
,'{0}' AS RecordTimestamp
,HI.Id AS 'HostingInstance_Id'
,HI.MachineId AS 'HostingInstance_MachineId'
,HI.IP AS 'HostingInstance_IP'
,HI.CreationTime AS 'HostingInstance_CreationTime'
,HI.StartTime AS 'HostingInstance_StartTime'
,HI.LastUpdateTime AS 'HostingInstance_LastUpdateTime'
,HI.GameId AS 'HostingInstance_GameId'
,RHC.Id AS 'RegionHostingConfiguration_Id'
,RHC.MinimumNumInstances AS 'RegionHostingConfiguration_MinimumNumInstances'
,RHC.MaximumFreeInstances AS 'RegionHostingConfiguration_MaximumFreeInstances'
,HC.Name AS 'HostingConfiguration_Name'
,HR.Name AS 'HostingRegion_Name'
,HI.GameVersionId 'GameVersion_Id'
,GV.Major AS 'GameVersion_Major'
,GV.Minor AS 'GameVersion_Minor'
,GV.Status AS 'GameVersion_Status'
,GV.Label 'GameVersion_Label'
,HI.Status AS 'HostingInstance_Status'
,HI.Health AS 'HostingInstance_Health'
,HI.MaximumComputeUnits AS 'HostingInstance_MaximumComputeUnits'
,HI.TotalComputeUnits AS 'HostingInstance_TotalComputeUnits'
,IFNULL(CALC.TotalComputeUnits, 0) AS 'HostingInstance_CalcTotalComputeUnits'
,HI.ServersCount AS 'HostingInstance_ServersCount'
,IFNULL(CALC.ServersCount, 0) AS 'HostingInstance_CalcServersCount'
,IFNULL(ROUND(CALC.TotalComputeUnits/CALC.ServersCount, 2), 0) AS 'HostingInstance_AvgComputeUnitsAcrossServers'
,IFNULL(CALC.MaxUserCount, 0) AS 'HostingInstance_MaxUserCount'
,IFNULL(CALC.UserCount, 0) AS 'HostingInstance_UserCount'
FROM HostingInstance HI
INNER JOIN RegionHostingConfiguration RHC
ON HI.RegionConfigurationId = RHC.Id
INNER JOIN HostingConfiguration HC
ON RHC.HostingConfigurationId = HC.Id
INNER JOIN HostingRegion HR
ON RHC.RegionId = HR.Id
LEFT JOIN GameVersion GV
ON HI.GameVersionId = GV.Id
LEFT JOIN (
SELECT GSr.InstanceId, SUM(GSr.ComputeUnits) AS 'TotalComputeUnits', COUNT(*) AS 'ServersCount', SUM(GS.MaxNumPlayers) AS 'MaxUserCount', SUM(GS.UserCount) AS 'UserCount'
FROM GameServer GSr
INNER JOIN
(
SELECT GSn.Id, COUNT(*) AS 'UserCount', GST.MaxNumPlayers
FROM GameSession GSn
INNER JOIN GameSessionType GST
ON GSn.SessionTypeId = GST.Id
LEFT JOIN GameSessionUser GSU
ON GSn.Id = GSU.GameSessionId
WHERE GSU.Status = 2
GROUP BY GSn.Id
) GS
ON GSr.GameSessionId = GS.Id
GROUP BY GSr.InstanceId
) CALC
ON HI.Id = CALC.InstanceId
WHERE HI.IsLocallyEmulated = 0
AND HI.IsPhysicallyHosted = 0
AND HI.GameID = '{1}'
ORDER BY HI.GameId, HR.Name, HI.Status, HI.Id;",
DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"), game.Id);
try
{
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("Beginning Hosting Instance Query");
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("");
Result = DBManager.Instance.Query(Datastore.General, query);
Logger.Instance.Info(String.Format("Succesfully retrieved Hosting Instances : {0}", Result.Rows.Count));
if (Result.Rows.Count > 0)
{
List<string> InsertStatements = new List<string>();
foreach (DataRow row in Result.Rows)
{
StringBuilder sb = new StringBuilder();
sb.Append(@"INSERT INTO `HostingInstance_ComputeRaw`
(`ID`,
`RecordTimestamp`,
`HostingInstance_Id`,
`HostingInstance_MachineId`,
`HostingInstance_IP`,
`HostingInstance_CreationTime`,
`HostingInstance_StartTime`,
`HostingInstance_LastUpdateTime`,
`HostingInstance_GameId`,
`RegionHostingConfiguration_Id`,
`RegionHostingConfiguration_MinimumNumInstances`,
`RegionHostingConfiguration_MaximumFreeInstances`,
`HostingConfiguration_Name`,
`HostingRegion_Name`,
`GameVersion_Id`,
`GameVersion_Major`,
`GameVersion_Minor`,
`GameVersion_Status`,
`GameVersion_Label`,
`HostingInstance_Status`,
`HostingInstance_Health`,
`HostingInstance_MaximumComputeUnits`,
`HostingInstance_TotalComputeUnits`,
`HostingInstance_CalcTotalComputeUnits`,
`HostingInstance_ServersCount`,
`HostingInstance_CalcServersCount`,
`HostingInstance_AvgComputeUnitsAcrossServers`,
`HostingInstance_MaxUserCount`,
`HostingInstance_UserCount`) VALUES (");
foreach (var item in row.ItemArray)
{
if (item is DateTime)
{
DateTime itm = (DateTime)item;
sb.Append("'" + itm.ToString("yyyy-MM-dd HH:mm:ss") + "'");
sb.Append(",");
}
else
{
sb.Append("'" + item.ToString() + "'");
sb.Append(",");
}
}
sb.Length--;
sb.Append(");");
string Insert = sb.ToString();
InsertStatements.Add(Insert);
}
try
{
MoniverseResponse response = new MoniverseResponse()
{
Status = "unsent",
TimeStamp = DateTime.UtcNow
};
string shittyWay = string.Join("", InsertStatements);
lock (MoniverseBase.ConsoleWriterLock)
{
Console.ForegroundColor = ConsoleColor.Cyan;
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("Beginning Insert of Retention Row Batch");
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("");
}
HostingInstances.Service(Service => {
Service.Insert(new MoniverseRequest()
{
TaskName = "Insert",
Task = shittyWay,
TimeStamp = DateTime.UtcNow
});
});
//int result = DBManager.Instance.Insert(Datastore.Monitoring, shittyWay);
lock (MoniverseBase.ConsoleWriterLock)
{
Console.ForegroundColor = ConsoleColor.Green;
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("Beginning Insert of Retention Row Batch");
Logger.Instance.Info("--------------------------------------");
Logger.Instance.Info("");
Logger.Instance.Info(String.Format("success!! {0} inserted", InsertStatements.Count));
Console.ResetColor();
}
}
catch (Exception e)
{
Console.ForegroundColor = ConsoleColor.Red;
Logger.Instance.Info(e.Message);
Console.ResetColor();
}
}
}
catch (Exception ex)
{
Logger.Instance.Exception(ex.Message, ex.StackTrace);
}
}