public Log GetLog(string system, string feeds, string groupName, DateTime startDate, DateTime endDate)
{
Log log = new Log();
string sSQL;
string sConn;
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataReader rdr = null;
DateTime mindt = DateTime.MaxValue;
DateTime maxdt = DateTime.MinValue;
ISystem sys = (ISystem)Activator.CreateInstance(Type.GetType("epidigitalogy.Classes.Systems." + system));
sSQL = sys.getSQLMain(feeds);
sConn = ConfigurationManager.ConnectionStrings[system].ConnectionString;
try {
conn.ConnectionString = sConn;
conn.Open();
cmd.CommandText = sSQL;
cmd.Connection = conn;
if (String.IsNullOrEmpty(groupName)) {
cmd.Parameters.Add(new SqlParameter("@GroupName", SqlDbType.VarChar, 100)).Value = DBNull.Value;
} else {
cmd.Parameters.Add(new SqlParameter("@GroupName", SqlDbType.VarChar, 100)).Value = groupName;
}
cmd.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime)).Value = startDate;
cmd.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime)).Value = endDate;
rdr = cmd.ExecuteReader();
string prevAgentGuid = null;
Host h = new Host();
while (rdr.Read()) {
if (rdr.GetValue(0) == DBNull.Value) {
continue;
}
if ((string)rdr.GetValue(0) != prevAgentGuid) {
h = new Host();
h.id = (string)rdr["id"];
h.hn = (string)rdr["name"];
h.gn = (string)rdr["grp"];
if (rdr["ts"] != DBNull.Value) {
h.lci = (DateTime)rdr["ts"];
}
log.hosts.Add(h);
prevAgentGuid = (string)rdr.GetValue(0);
}
if (rdr["ts"] != DBNull.Value && rdr["et"] != DBNull.Value) {
h.events.Add(new Event((string)rdr["id"], (DateTime)rdr["ts"], (string)rdr["et"]));
}
if (rdr["ts"] != DBNull.Value) {
if ((DateTime)rdr["ts"] < mindt) {
mindt = (DateTime)rdr["ts"];
}
if ((DateTime)rdr["ts"] > maxdt) {
maxdt = (DateTime)rdr["ts"];
}
}
}
} catch (Exception e) {
throw e;
} finally {
rdr.Close();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
log.mindt = mindt;
log.maxdt = maxdt;
return log;
}
public Host getHostInfo(string id)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader rdr = null;
string sql = @"
SELECT a.AGENT_ID,
a.COMPUTER_ID,
a.AGENT_VERSION,
dateadd(second,[LAST_UPDATE_TIME]/1000, '1970-01-01') AS LAST_UPDATE_TIME,
a.FULL_NAME,
a.EMAIL,
a.JOB_TITLE,
a.DEPARTMENT,
a.OFFICE_PHONE,
a.MOBILE_PHONE,
CASE WHEN a.INFECTED = 1 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS INFECTED,
dateadd(second,[LAST_SCAN_TIME]/1000, '1970-01-01') AS LAST_SCAN_TIME,
CASE WHEN LAST_VIRUS_TIME = 0 THEN NULL ELSE dateadd(second,[LAST_VIRUS_TIME]/1000, '1970-01-01') END AS LAST_VIRUS_TIME,
dateadd(second,[LAST_DOWNLOAD_TIME]/1000, '1970-01-01') AS LAST_DOWNLOAD_TIME,
a.LAST_DOWNLOAD_TIME,
CASE WHEN a.AVENGINE_ONOFF = 1 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS AVENGINE_ONOFF,
CASE WHEN a.TAMPER_ONOFF = 1 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS TAMPER_ONOFF,
CASE WHEN a.FIREWALL_ONOFF = 1 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS FIREWALL_ONOFF,
CASE WHEN a.REBOOT_REQUIRED = 1 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS REBOOT_REQUIRED,
a.REBOOT_REASON,
c.COMPUTER_NAME,
c.COMPUTER_DOMAIN_NAME,
c.OPERATION_SYSTEM AS operating_system,
c.SERVICE_PACK,
c.CURRENT_LOGIN_USER,
c.CURRENT_LOGIN_DOMAIN,
c.DNS_SERVER1,
c.DNS_SERVER2,
c.DHCP_SERVER,
c.MAC_ADDR1,
c.IP_ADDR1,
c.GATEWAY1,
c.SUBNET_MASK1
-- c.MAC_ADDR2,
-- c.IP_ADDR2,
-- c.GATEWAY2,
-- c.SUBNET_MASK2,
-- c.MAC_ADDR3,
-- c.IP_ADDR3,
-- c.GATEWAY3,
-- c.SUBNET_MASK3,
-- c.MAC_ADDR4,
-- c.IP_ADDR4,
-- c.GATEWAY4,
-- c.SUBNET_MASK4
FROM SEM_AGENT a
INNER JOIN V_SEM_COMPUTER c ON a.[COMPUTER_ID] = c.[COMPUTER_ID]
WHERE c.COMPUTER_ID = @Id";
conn = new SqlConnection(sqlConnectionString);
conn.Open();
Host host = new Host();
try {
cmd = new SqlCommand(sql, conn);
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.VarChar, 32)).Value = id;
rdr = cmd.ExecuteReader();
if (rdr.Read()) {
host.agent_id = (string)rdr["agent_id"];
host.computer_id = (string)rdr["computer_id"];
host.agent_version = (string)rdr["agent_version"];
host.last_update_time = (DateTime)rdr["last_update_time"];
host.full_name = rdr["full_name"] == DBNull.Value ? "n/a" : (string)rdr["full_name"];
host.email = rdr["email"] == DBNull.Value ? "n/a" : (string)rdr["email"];
host.job_title = rdr["job_title"] == DBNull.Value ? "n/a" : (string)rdr["job_title"];
host.department = rdr["department"] == DBNull.Value ? "n/a" : (string)rdr["department"];
host.office_phone = rdr["office_phone"] == DBNull.Value ? "n/a" : (string)rdr["office_phone"];
host.mobile_phone = rdr["mobile_phone"] == DBNull.Value ? "n/a" : (string)rdr["mobile_phone"];
if (rdr["infected"] != DBNull.Value) {
host.infected = (bool)rdr["infected"];
}
if (rdr["last_scan_time"] != DBNull.Value) {
host.last_scan_time = (DateTime)rdr["last_scan_time"];
}
if (rdr["last_virus_time"] != DBNull.Value) {
host.last_virus_time = (DateTime)rdr["last_virus_time"];
}
if (rdr["last_download_time"] != DBNull.Value) {
host.last_download_time = (DateTime)rdr["last_download_time"];
}
if (rdr["avengine_onoff"] != DBNull.Value) {
host.avengine_onoff = (bool)rdr["avengine_onoff"];
}
if (rdr["tamper_onoff"] != DBNull.Value) {
host.tamper_onoff = (bool)rdr["tamper_onoff"];
}
if (rdr["firewall_onoff"] != DBNull.Value) {
host.firewall_onoff = (bool)rdr["firewall_onoff"];
}
if (rdr["reboot_required"] != DBNull.Value) {
host.reboot_required = (bool)rdr["reboot_required"];
}
host.reboot_reason = rdr["reboot_reason"] == DBNull.Value ? null : (string)rdr["reboot_reason"];
host.computer_name = rdr["computer_name"] == DBNull.Value ? null : (string)rdr["computer_name"];
host.computer_domain_name = rdr["computer_domain_name"] == DBNull.Value ? null : (string)rdr["computer_domain_name"];
host.os = rdr["operating_system"] == DBNull.Value ? null : (string)rdr["operating_system"];
host.service_pack = rdr["service_pack"] == DBNull.Value ? null : (string)rdr["service_pack"];
host.current_login_user = rdr["current_login_user"] == DBNull.Value ? null : (string)rdr["current_login_user"];
host.current_login_domain = rdr["current_login_domain"] == DBNull.Value ? null : (string)rdr["current_login_domain"];
if (rdr["dns_server1"] != DBNull.Value) {
if (Type.GetTypeCode(rdr.GetFieldType(rdr.GetOrdinal("dns_server1"))) == TypeCode.String) {
host.dns_server1 = Util.IP2Long((string)rdr["dns_server1"]);
} else {
host.dns_server1 = (long)rdr["dns_server1"];
}
}
if (rdr["dns_server2"] != DBNull.Value) {
if (Type.GetTypeCode(rdr.GetFieldType(rdr.GetOrdinal("dns_server2"))) == TypeCode.String) {
host.dns_server2 = Util.IP2Long((string)rdr["dns_server2"]);
} else {
host.dns_server2 = (long)rdr["dns_server2"];
}
}
if (rdr["dhcp_server"] != DBNull.Value) {
if (Type.GetTypeCode(rdr.GetFieldType(rdr.GetOrdinal("dhcp_server"))) == TypeCode.String) {
host.dhcp_server = Util.IP2Long((string)rdr["dhcp_server"]);
} else {
host.dhcp_server = (long)rdr["dhcp_server"];
}
}
host.mac_addr1 = rdr["mac_addr1"] == DBNull.Value ? null : (string)rdr["mac_addr1"];
if (rdr["ip_addr1"] != DBNull.Value) {
if (Type.GetTypeCode(rdr.GetFieldType(rdr.GetOrdinal("ip_addr1"))) == TypeCode.String) {
host.ip_addr1 = Util.IP2Long((string)rdr["ip_addr1"]);
} else {
host.ip_addr1 = (long)rdr["ip_addr1"];
}
}
if (rdr["gateway1"] != DBNull.Value) {
if (Type.GetTypeCode(rdr.GetFieldType(rdr.GetOrdinal("gateway1"))) == TypeCode.String) {
host.gateway1 = Util.IP2Long((string)rdr["gateway1"]);
} else {
host.gateway1 = (long)rdr["gateway1"];
}
}
if (rdr["subnet_mask1"] != DBNull.Value) {
if (Type.GetTypeCode(rdr.GetFieldType(rdr.GetOrdinal("subnet_mask1"))) == TypeCode.String) {
host.subnet_mask1 = Util.IP2Long((string)rdr["subnet_mask1"]);
} else {
host.subnet_mask1 = (long)rdr["subnet_mask1"];
}
}
// host.mac_addr2 = rdr["mac_addr2"] == DBNull.Value ? null : (string)rdr["mac_addr2"];
// host.ip_addr2 = (long)rdr["ip_addr2"];
// host.gateway2 = (long)rdr["gateway2"];
// host.subnet_mask2 = (long)rdr["subnet_mask2"];
// host.mac_addr3 = rdr["mac_addr3"] == DBNull.Value ? null : (string)rdr["mac_addr3"];
// host.ip_addr3 = (long)rdr["ip_addr3"];
// host.gateway3 = (long)rdr["gateway3"];
// host.subnet_mask3 = (long)rdr["subnet_mask3"];
// host.mac_addr4 = rdr["mac_addr4"] == DBNull.Value ? null : (string)rdr["mac_addr4"];
// host.ip_addr4 = (long)rdr["ip_addr4"];
// host.gateway4 = (long)rdr["gateway4"];
// host.subnet_mask4 = (long)rdr["subnet_mask4"];
}
} catch (Exception e) {
host.error = e.Message;
} finally {
rdr.Close();
rdr.Dispose();
cmd.Dispose();
}
return host;
}
