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; }