private void AddEditGroup_Load(object sender, EventArgs e)
{
if (IsEditMode)
{
this.Text = "Edit Group";
btnSubmit.Text = "Update Group";
txbGroupName.Text = sql.ExecuteScalarACon <string>($"SELECT Name FROM Groups WHERE ID = '{GroupID}'");
}
else
{
this.Text = "Add New Group";
}
// Load all devices and add then to the view
sql.Open();
using (SQLiteDataReader reader = sql.ExecuteQuery("SELECT * FROM Devices LEFT JOIN GroupAssigns ON Devices.MACAddress = GroupAssigns.MACAddress WHERE GroupAssigns.GroupID IS NULL ORDER BY Devices.Name ASC"))
{
while (reader.Read())
{
// Add device to view
GroupViewItem gvi = new GroupViewItem(
Convert.ToString(reader["Name"]),
grvDevices.SmallImageList.Images.IndexOfKey(
Convert.ToString(reader["DeviceType"]) + "_RAW")
);
gvi.Tag = Convert.ToString(reader["MACAddress"]);
grvDevices.GroupViewItems.Add(gvi);
}
}
if (!IsEditMode)
{
// Create new group
sql.ExecuteNonQuery($"INSERT INTO Groups (Name, Description, DeviceType) VALUES ('{newGroupGuid}', '', '{DeviceType.UnknownDevice}')");
GroupID = sql.ExecuteScalar <int>($"SELECT ID FROM Groups WHERE Name = '{newGroupGuid}'");
}
sql.Close();
UpdateGroupDeviceList();
}
private void lbxDeviceSites_SelectedIndexChanged(object sender, EventArgs e)
{
sql.Open();
using (SQLiteDataReader reader = sql.ExecuteQuery($"SELECT * FROM DeviceSites WHERE ID = '{lbxDeviceSites.SelectedValue}'"))
{
while (reader.Read())
{
txbName.Text = Convert.ToString(reader["Name"]);
txbDeviceSiteURL.Text = Convert.ToString(reader["Site"]);
}
}
sql.Close();
EnableInput();
}
private void bgwUpdateEntries_DoWork(object sender, DoWorkEventArgs e)
{
Dictionary <string, bool> DevicePowerState = new Dictionary <string, bool>();
// Get all devices, set powerstate to false
DevicePowerState.Clear();
sql.Open();
using (SQLiteDataReader reader = sql.ExecuteQuery("SELECT * FROM Devices"))
while (reader.Read())
{
DevicePowerState.Add(Convert.ToString(reader["MACAddress"]), false);
}
sql.Close();
Ping ping = new Ping();
StringBuilder sqlSB = new StringBuilder();
if (ipStartParts[0] == ipEndParts[0] && ipStartParts[1] == ipEndParts[1] && ipStartParts[2] == ipEndParts[2])
{
for (int i = Convert.ToInt32(ipStartParts[3]); i <= Convert.ToInt32(ipEndParts[3]); i++)
{
if (bgwUpdateEntries.CancellationPending)
{
return;
}
bgwUpdateEntries.ReportProgress(i);
string currentIP = ipStartParts[0] + "." + ipStartParts[1] + "." + ipStartParts[2] + "." + i.ToString();
PingReply reply = ping.Send(currentIP, 100);
if (reply.Status == IPStatus.Success)
{
string currentMAC = NetExplore.GetMacAddress(currentIP);
if (!string.IsNullOrEmpty(currentMAC))
{
// Update the power-state of the device
if (DevicePowerState.ContainsKey(currentMAC))
{
// Known device, only update values
DevicePowerState[currentMAC] = true;
sqlSB.Append($"UPDATE Devices SET IP4Address = '{currentIP}', LastSeen = '{DateTime.Now:yyyy-MM-dd H:mm:ss}' WHERE MACAddress = '{currentMAC}';");
}
else
{
// New, unknown device, add to DB
DevicePowerState.Add(currentMAC, true);
sqlSB.Append($"INSERT INTO Devices (MACAddress, DeviceType, Name, IP4Address, LastSeen) VALUES ('{currentMAC}','{DeviceType.UnknownDevice}','Device-{currentMAC}','{currentIP}','{DateTime.Now:yyyy-MM-dd H:mm:ss}')");
}
}
}
else
{
continue;
}
}
if (bgwUpdateEntries.CancellationPending)
{
return;
}
// Update IP and LastSeen, add new entries
sql.ExecuteNonQueryACon(sqlSB.ToString());
// Update last power-state
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair <string, bool> entry in DevicePowerState)
{
int state = 0;
if (entry.Value)
{
state = 1;
}
sb.Append($"UPDATE Devices SET LastPowerState = '{state}' WHERE MACAddress = '{entry.Key}';");
}
sql.ExecuteNonQueryACon(sb.ToString());
}
}
private int LoadQDData()
{
// Load local Data
using (WrapSQLite sqlite = new WrapSQLite(QDInfo.ConfigFile))
{
if (!QDLib.ManagedDBOpen(sqlite))
{
QDLib.DBOpenFailed(); return(-1);
}
localConnection = !Convert.ToBoolean(sqlite.ExecuteScalar <short>("SELECT QDValue FROM qd_info WHERE QDKey = ?", QDInfo.DBL.IsOnlineLinked));
promptPassword = Convert.ToBoolean(sqlite.ExecuteScalar <short>("SELECT QDValue FROM qd_info WHERE QDKey = ?", QDInfo.DBL.AlwaysPromptPassword));
Username = sqlite.ExecuteScalar <string>("SELECT QDValue FROM qd_info WHERE QDKey = ?", QDInfo.DBL.DefaultUsername);
Password = sqlite.ExecuteScalar <string>("SELECT QDValue FROM qd_info WHERE QDKey = ?", QDInfo.DBL.DefaultPassword);
dbData.Hostname = Cipher.Decrypt(sqlite.ExecuteScalar <string>("SELECT QDValue FROM qd_info WHERE QDKey = ?", QDInfo.DBL.DBHost), QDInfo.LocalCipherKey);
dbData.Username = Cipher.Decrypt(sqlite.ExecuteScalar <string>("SELECT QDValue FROM qd_info WHERE QDKey = ?", QDInfo.DBL.DBUsername), QDInfo.LocalCipherKey);
dbData.Password = Cipher.Decrypt(sqlite.ExecuteScalar <string>("SELECT QDValue FROM qd_info WHERE QDKey = ?", QDInfo.DBL.DBPassword), QDInfo.LocalCipherKey);
dbData.Database = Cipher.Decrypt(sqlite.ExecuteScalar <string>("SELECT QDValue FROM qd_info WHERE QDKey = ?", QDInfo.DBL.DBName), QDInfo.LocalCipherKey);
sqlite.Close();
if (!string.IsNullOrEmpty(Password))
{
Password = Cipher.Decrypt(Password, QDInfo.LocalCipherKey);
}
}
try
{
using (WrapSQLite sqlite = new WrapSQLite(QDInfo.ConfigFile))
{
sqlite.Open();
sqlite.Close();
}
}
catch { return(3); }
// Load Online Data
if (!localConnection)
{
try
{
using (WrapMySQL mysql = new WrapMySQL(dbData))
{
if (!QDLib.ManagedDBOpen(mysql))
{
QDLib.DBOpenFailed(); return(-1);
}
disconnectAtShutdown = Convert.ToBoolean(mysql.ExecuteScalar <short>("SELECT QDValue FROM qd_info WHERE QDKey = ?", QDInfo.DBO.DisconnectDrivesAtShutdown));
logUserActions = Convert.ToBoolean(mysql.ExecuteScalar <short>("SELECT QDValue FROM qd_info WHERE QDKey = ?", QDInfo.DBO.LogUserActions));
mysql.Close();
}
}
catch { return(2); }
}
if (!promptPassword)
{
QDLib.VerifyPassword(localConnection, Username, Password, out UserID, dbData);
}
return(0);
}
public static void PowerStateCheck(string pIPRangeStart, string pIPRangeEnd, BackgroundWorker pProgressReplyBGW = null)
{
using (WrapSQLite tsql = new WrapSQLite(SurgitManager.SurgitDatabaseLocation, true))
{
Dictionary <string, bool> DevicePowerState = new Dictionary <string, bool>();
// Get all devices, set powerstate to false
DevicePowerState.Clear();
tsql.Open();
using (SQLiteDataReader reader = tsql.ExecuteQuery("SELECT * FROM Devices"))
while (reader.Read())
{
DevicePowerState.Add(Convert.ToString(reader["MACAddress"]), false);
}
tsql.Close();
// Ping all devices in Network Range
Ping ping = new Ping();
StringBuilder sqlSB = new StringBuilder();
if (CheckIPClassCValidity(pIPRangeStart, pIPRangeEnd))
{
string[] ipStartParts = pIPRangeStart.Split('.');
string[] ipEndParts = pIPRangeEnd.Split('.');
for (int i = Convert.ToInt32(ipStartParts[3]); i <= Convert.ToInt32(ipEndParts[3]); i++)
{
if (pProgressReplyBGW.CancellationPending)
{
return;
}
if (pProgressReplyBGW != null)
{
pProgressReplyBGW.ReportProgress(i);
}
string currentIP = ipStartParts[0] + "." + ipStartParts[1] + "." + ipStartParts[2] + "." + i.ToString();
PingReply reply = ping.Send(currentIP, 100);
if (reply.Status == IPStatus.Success)
{
string currentMAC = NetExplore.GetMacAddress(currentIP);
if (!string.IsNullOrEmpty(currentMAC))
{
// Update the power-state of the device
if (DevicePowerState.ContainsKey(currentMAC))
{
// Known device, only update values
DevicePowerState[currentMAC] = true;
sqlSB.Append($"UPDATE Devices SET IP4Address = '{currentIP}', LastSeen = '{DateTime.Now:yyyy-MM-dd H:mm:ss}' WHERE MACAddress = '{currentMAC}';");
}
else
{
// New, unknown device, add to DB
DevicePowerState.Add(currentMAC, true);
sqlSB.Append($"INSERT INTO Devices (MACAddress, DeviceType, Name, IP4Address, LastSeen) VALUES ('{currentMAC}','{DeviceType.UnknownDevice}','Device-{currentMAC}','{currentIP}','{DateTime.Now:yyyy-MM-dd H:mm:ss}')");
}
}
}
else
{
continue;
}
}
// Update IP and LastSeen, add new entries
tsql.ExecuteNonQueryACon(sqlSB.ToString());
// Update last power-state
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair <string, bool> entry in DevicePowerState)
{
sb.Append($"UPDATE Devices SET LastPowerState = '{(entry.Value ? 1 : 0)}' WHERE MACAddress = '{entry.Key}';");
}
tsql.ExecuteNonQueryACon(sb.ToString());
}
else
{
if (pProgressReplyBGW.CancellationPending)
{
return;
}
MessageBox.Show("The given range is not valid. Make sure the IPs are in the same subnet. Note: Only C-Class IP-Adresses are currently supported", "Invalid Range", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}