/// <summary>
/// This method polls both the APs and the Clients, and updates the database
/// </summary>
public async Task UpdateDatabase()
{
_writer.WriteLine("Gathering details from SNMP...");
var macAddresses = GatherBulk(Mibs.GetValue(Mibs.Mib.ClientMacAddress), Connection.Host, Connection.Community);
var ipAddresses = GatherBulk(Mibs.GetValue(Mibs.Mib.ClientIpAddress), Connection.Host, Connection.Community);
var usernames = GatherBulk(Mibs.GetValue(Mibs.Mib.ClientUsername), Connection.Host, Connection.Community);
var apMacAddresses = GatherBulk(Mibs.GetValue(Mibs.Mib.ClientApMacAddress), Connection.Host, Connection.Community);
var ssids = GatherBulk(Mibs.GetValue(Mibs.Mib.ClientSsid), Connection.Host, Connection.Community);
var wlanInterfaces = GatherBulk(Mibs.GetValue(Mibs.Mib.ClientWlanInterface), Connection.Host, Connection.Community);
var vlans = GatherBulk(Mibs.GetValue(Mibs.Mib.ClientVlan), Connection.Host, Connection.Community);
var apBaseRadioMac = GatherBulk(Mibs.GetValue(Mibs.Mib.ApBaseRadioMacAddress), Connection.Host, Connection.Community);
var apEthernetMac = GatherBulk(Mibs.GetValue(Mibs.Mib.ApEthernetMacAddress), Connection.Host, Connection.Community);
var apIpAddresses = GatherBulk(Mibs.GetValue(Mibs.Mib.ApIpAddress), Connection.Host, Connection.Community);
var apNames = GatherBulk(Mibs.GetValue(Mibs.Mib.ApName), Connection.Host, Connection.Community);
var apLocations = GatherBulk(Mibs.GetValue(Mibs.Mib.ApLocation), Connection.Host, Connection.Community);
var apModels = GatherBulk(Mibs.GetValue(Mibs.Mib.ApModel), Connection.Host, Connection.Community);
var apSerialNumbers = GatherBulk(Mibs.GetValue(Mibs.Mib.ApSerialNumber), Connection.Host, Connection.Community);
_writer.WriteLine("Finished retrieving SNMP data");
var clients = new List <SnmpClient>();
clients.Update(macAddresses, Mibs.Mib.ClientMacAddress);
clients.Update(ipAddresses, Mibs.Mib.ClientIpAddress);
clients.Update(usernames, Mibs.Mib.ClientUsername);
clients.Update(apMacAddresses, Mibs.Mib.ClientApMacAddress);
clients.Update(ssids, Mibs.Mib.ClientSsid);
clients.Update(wlanInterfaces, Mibs.Mib.ClientWlanInterface);
clients.Update(vlans, Mibs.Mib.ClientVlan);
_writer.WriteLine($"Pre-cleanup: {clients.Count} clients");
Stopwatch sw = new Stopwatch();
sw.Start();
clients.Cleanup();
sw.Stop();
_writer.WriteLine($"Post-cleanup: {clients.Count} clients");
_writer.WriteLine($"SNMP clients updated and cleaned up in {sw.Elapsed}");
var accessPoints = new List <SnmpAccessPoint>();
accessPoints.Update(apBaseRadioMac, Mibs.Mib.ApBaseRadioMacAddress);
accessPoints.Update(apEthernetMac, Mibs.Mib.ApEthernetMacAddress);
accessPoints.Update(apIpAddresses, Mibs.Mib.ApIpAddress);
accessPoints.Update(apNames, Mibs.Mib.ApName);
accessPoints.Update(apLocations, Mibs.Mib.ApLocation);
accessPoints.Update(apModels, Mibs.Mib.ApModel);
accessPoints.Update(apSerialNumbers, Mibs.Mib.ApSerialNumber);
_writer.WriteLine("Database update started");
// Update AP Models
var updateResult = await _database.UpdateAccessPointModels(accessPoints.Select(ap => ap.Model).Distinct().ToList());
_writer.WriteLine(!updateResult.Success
? $"Something went wrong updating access point models.. {updateResult.Message}"
: $"Updated AP Models in {updateResult.TimeTaken}");
// Update SSIDs
updateResult = await _database.UpdateSsids(clients.Select(c => c.Ssid).Distinct().ToList());
_writer.WriteLine(!updateResult.Success
? $"Something went wrong updating SSIDs.. {updateResult.Message}"
: $"Updated SSIDs in {updateResult.TimeTaken}");
// Update VLANs
updateResult = await _database.UpdateVlans(clients.Where(c => !string.IsNullOrEmpty(c.Vlan)).Select(c => c.Vlan).Distinct().ToList());
_writer.WriteLine(!updateResult.Success
? $"Something went wrong updating VLANs.. {updateResult.Message}"
: $"Updated VLANs in {updateResult.TimeTaken}");
// Update interfaces
updateResult = await _database.UpdateWlanInterfaces(clients.Where(c => !string.IsNullOrEmpty(c.Interface)).Select(c => c.Interface).Distinct().ToList());
_writer.WriteLine(!updateResult.Success
? $"Something went wrong updating WLC interfaces.. {updateResult.Message}"
: $"Updated WLC interfaces in {updateResult.TimeTaken}");
// Update clients
updateResult = await _database.UpdateClients(clients.Where(c => !string.IsNullOrEmpty(c.MacAddress)).Select(c => c.MacAddress.Replace(" ", "")).Distinct().ToList());
_writer.WriteLine(!updateResult.Success
? $"Something went wrong updating clients.. {updateResult.Message}"
: $"Updated clients in {updateResult.TimeTaken}");
// Update IP Addresses
updateResult = await _database.UpdateIpAddresses(clients.Where(c => !string.IsNullOrEmpty(c.IpAddress)).Select(c => c.IpAddress).Distinct().ToList());
_writer.WriteLine(!updateResult.Success
? $"Something went wrong updating IP addresses.. {updateResult.Message}"
: $"Updated IP addresses in {updateResult.TimeTaken}");
var dbApModels = await _database.GetApModelsAsync();
// Build the list of access points that the database should contain
var actualAps = accessPoints.Select(accessPoint => new AccessPoint
{
BaseRadioMacAddress = accessPoint.BaseRadioMacAddress,
EthernetMacAddress = accessPoint.EthernetMacAddress,
Location = accessPoint.Location,
Name = accessPoint.Name,
ModelId = dbApModels.First(m => m.Name == accessPoint.Model).Id,
IpAddress = accessPoint.IpAddress
}).ToList();
updateResult = await _database.UpdateAccessPoints(actualAps);
_writer.WriteLine(!updateResult.Success
? $"Something went wrong updating access points.. {updateResult.Message}"
: $"Updated APs in {updateResult.TimeTaken}");
// Return the records so that we can key them properly (i.e. can't insert without FKs)
var apListTask = _database.GetAccessPointsAsync();
var clientListTask = _database.GetClientsAsync();
var ssidListTask = _database.GetSsidsAsync();
var vlanListTask = _database.GetVlansAsync();
var wlanInterfaceListTask = _database.GetWlanInterfacesAsync();
var ipAddressListTask = _database.GetIpAddressesAsync();
await Task.WhenAll(apListTask, clientListTask, ssidListTask, vlanListTask, wlanInterfaceListTask, ipAddressListTask);
var apList = apListTask.Result;
var clientList = clientListTask.Result;
var ssidList = ssidListTask.Result;
var vlanList = vlanListTask.Result;
var wlanInterfaceList = wlanInterfaceListTask.Result;
var ipAddressList = ipAddressListTask.Result;
// Add client tracking records
var clientTrackingList = new List <ClientTracking>();
var batchDate = DateTime.Now;
foreach (var client in clients)
{
try
{
clientTrackingList.Add(new ClientTracking
{
ClientId = clientList.First(c => c.MacAddress == client.MacAddress.Replace(" ", "").ToUpper()).Id,
AccessPointId = apList.First(ap => ap.BaseRadioMacAddress == client.ApMacAddress.Replace(" ", "").ToUpper()).Id,
IpAddressId = ipAddressList.First(ip => ip.Value == client.IpAddress).Id,
SsidId = ssidList.First(s => s.Value == client.Ssid).Id,
Username = client.Username,
VlanId = vlanList.First(v => v.Value == client.Vlan).Id,
WlanInterfaceId = wlanInterfaceList.First(w => w.Value == client.Interface).Id,
BatchDate = batchDate
});
}
catch (NullReferenceException nre)
{
_writer.WriteLine("Hit a null reference exception for the following client...");
_writer.WriteLine(client.ToString());
}
catch (Exception ex)
{
_writer.WriteLine($"Threw an exception for the following client ({ex.Message})");
_writer.WriteLine(client.ToString());
}
}
_writer.WriteLine("Beginning main update block");
updateResult = await _database.AddClientTracking(clientTrackingList);
_writer.WriteLine(!updateResult.Success
? $"Something went wrong adding client tracking.. {updateResult.Message}"
: $"Main update block completed successfully in {updateResult.TimeTaken}");
_writer.WriteLine("Database update complete");
}
