public async Task LoadSessionsAsync(DateTime fromdate, DateTime todate)
{
_log.Info($"Loading sessions from {fromdate} to {todate}");
using (var db = _contextFactory.GetContext())
{
var customers = await GetAccountsAsync();
using (var client = await CreateAuthenticatedHttpClientAsync())
{
client.Timeout = TimeSpan.FromMinutes(2);
foreach (var customer in customers)
{
try
{
var url = $"https://openmobile.ipass.com/moservices/rest/api/ipass/{customer.customerId}/mo/reports/central-csv?report=connectionActivity&startdate={fromdate:MM-dd-yyyy}&enddate={todate:MM-dd-yyyy}&sess-type=initiated&pageSize=0";
using (var contentstream = await client.GetStreamAsync(url))
{
using (var connection = _contextFactory.GetSqlConnection())
{
var reader = new StreamReader(contentstream);
var content = await reader.ReadToEndAsync();
content = content.Replace("\"", "");
var stream = new MemoryStream(Encoding.UTF8.GetBytes(content));
var csvreader = new CsvReader(new StreamReader(stream), true, ',');
var bulkcopy = new SqlBulkCopy(connection)
{
DestinationTableName = "Insight.SessionsV2",
BulkCopyTimeout = 60,
BatchSize = 10000
};
bulkcopy.ColumnMappings.Add(0, "AccountId");
bulkcopy.ColumnMappings.Add(1, "[UsernameDomain]");
bulkcopy.ColumnMappings.Add(3, "[ProfileId]");
bulkcopy.ColumnMappings.Add(4, "[SessionId]");
bulkcopy.ColumnMappings.Add(5, "[StartTime]");
bulkcopy.ColumnMappings.Add(6, "[SessionLength]");
bulkcopy.ColumnMappings.Add(7, "[AuthenticationTime]");
bulkcopy.ColumnMappings.Add(8, "[ConnectionType]");
bulkcopy.ColumnMappings.Add(9, "[ConnectionStatus]");
bulkcopy.ColumnMappings.Add(10, "[ConnectionStatusCode]");
bulkcopy.ColumnMappings.Add(11, "[ConnectionStatusCodeType]");
bulkcopy.ColumnMappings.Add(12, "[ConnectionStatusCodeDescription]");
bulkcopy.ColumnMappings.Add(13, "[DisconnectCode]");
bulkcopy.ColumnMappings.Add(14, "[ClientIpAddress]");
bulkcopy.ColumnMappings.Add(15, "[DownloadedMB]");
bulkcopy.ColumnMappings.Add(17, "[UploadedMB]");
bulkcopy.ColumnMappings.Add(18, "[SignalStrength]");
bulkcopy.ColumnMappings.Add(19, "[Country]");
bulkcopy.ColumnMappings.Add(20, "[ClientMacAddress]");
bulkcopy.ColumnMappings.Add(21, "[Ssid]");
bulkcopy.ColumnMappings.Add(22, "[AuthMethod]");
bulkcopy.ColumnMappings.Add(23, "[SecurityMode]");
bulkcopy.ColumnMappings.Add(24, "[DeviceType]");
bulkcopy.ColumnMappings.Add(25, "[DeviceOS]");
bulkcopy.ColumnMappings.Add(26, "[ClientVersion]");
bulkcopy.ColumnMappings.Add(27, "[SdkVersion]");
bulkcopy.ColumnMappings.Add(28, "[ConnectReason]");
connection.Open();
// Veeeewwy veeewwy important! Otherwise decimal parsing from eg 5.53 will fail
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
await bulkcopy.WriteToServerAsync(csvreader);
_log.Info($"Imported sessions for {customer.name}");
}
}
}
catch (Exception ex)
{
_log.Error($"{customer.name} failed to download: ", ex);
}
}
}
_log.Info("Updating nodeids");
var update = @"
BEGIN TRANSACTION
UPDATE Insight.SessionsV2
SET node_id = users.node_id
FROM Insight.SessionsV2 AS i LEFT JOIN users ON CONCAT(users.username, '@', users.realm) = i.UsernameDomain
WHERE CONCAT(users.username, '@', users.realm) = i.UsernameDomain AND i.node_id IS NULL
UPDATE Insight.SessionsV2
SET node_id = (SELECT TOP 1 t1.node_id
FROM ipass_realms AS t1
LEFT JOIN directory ON(t1.node_id = directory.node_id)
WHERE realm = SUBSTRING(UsernameDomain, LEN(usernamedomain) - CHARINDEX('@', REVERSE(UsernameDomain)) + 2, LEN(UsernameDomain))
ORDER BY directory.lft)
FROM Insight.SessionsV2 AS i
WHERE i.node_id IS NULL
UPDATE Insight.SessionsV2
SET node_id = (SELECT TOP 1 node_id
FROM directory
WHERE ipassCustomerID = AccountId
ORDER BY directory.lft)
FROM Insight.SessionsV2 AS i
WHERE i.node_id IS NULL
COMMIT";
await db.Database.ExecuteSqlCommandAsync(update);
_log.Info("Updating summary tables");
var updatesummary = @"
DELETE FROM ipass.CombinedCdrUsers WHERE Month >= @fromdate
INSERT INTO ipass.CombinedCdrUsers (Month, UsernameDomain, NodeId, GiSessions, DsSessions, Sum, Seconds)
SELECT
monthdatetime,
UsernameDomain,
node_id,
SUM(CASE WHEN c.type = 'gi' THEN 1 ELSE 0 END) AS gisessions,
SUM(CASE WHEN c.type = 'ds' THEN 1 ELSE 0 END) AS dssessions,
SUM(sum) AS sum,
SUM(duration) AS seconds
FROM ipass.CdrCombined AS c
WHERE node_id IS NOT NULL AND UsernameDomain IS NOT NULL AND monthdatetime >= @fromdate
GROUP BY monthdatetime, UsernameDomain,node_id
DELETE FROM ipass.combinedcdrgroups WHERE month >= @fromdate
INSERT INTO iPass.CombinedCdrGroups (Month, NodeId, Seconds, Sum, BothUsers, GiUsers, DsUsers, DsSessions, GiSession)
SELECT
u.Month,
u.NodeId,
SUM(u.Seconds),
SUM(u.Sum),
SUM(CASE WHEN u.DsSessions > 0 AND u.GiSessions > 0 THEN 1 ELSE 0 END) AS bothuser,
SUM(CASE WHEN u.DsSessions = 0 AND u.GiSessions > 0 THEN 1 ELSE 0 END) AS giuser,
SUM(CASE WHEN u.DsSessions > 0 AND u.GiSessions = 0 THEN 1 ELSE 0 END) AS dsuser,
SUM(u.DsSessions),
SUM(u.GiSessions)
FROM iPass.CombinedCdrUsers AS u
WHERE Month >= @fromdate
GROUP BY u.Month, u.NodeId";
var summaryfromdate = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.AddMonths(-1).Month, 1);
await db.Database.ExecuteSqlCommandAsync(updatesummary, new SqlParameter("@fromdate", summaryfromdate));
}
_log.Info("Import done");
}
