public List<Total> GetAlertCountsByDay(string system) { string sql = null; string strConn = ""; List<Total> result = new List<Total>(); switch (system) { case "Kaseya": sql = @" SELECT DATEADD(dd, DATEDIFF(dd, 0, a.et), 0) AS date, count(*) AS date_count FROM ( SELECT eventTime AS et, a.reverseName AS groupName FROM [dbo].[AVLogHistory] h INNER JOIN vAgentsAll a ON h.agentGUID = a.AgentGuid -- Filters out non-existant machines WHERE EventType = 1000 -- CodeText = ThreatDetected UNION ALL SELECT t.[TimeStamp] AS et, a.reverseName AS groupName FROM kam.KamThreatIncidents t INNER JOIN vAgentsAll a ON t.agentGUID = a.AgentGuid -- Filters out non-existant machines UNION ALL SELECT DetectedTime AS et, a.reverseName AS groupName FROM [kav].[vThreats] t INNER JOIN vAgentsAll a ON t.agentGUID = a.AgentGuid -- Filters out non-existant machines ) a GROUP BY DATEADD(dd, DATEDIFF(dd, 0, a.et), 0) ORDER BY date DESC"; strConn = ConfigurationManager.ConnectionStrings["Kaseya"].ConnectionString; break; case "Symantec": sql = @" SELECT DATEADD(dd, DATEDIFF(dd, 0, ALERTDATETIME), 0) AS date, count(*) AS date_count FROM V_ALERTS INNER JOIN IDENTITY_MAP ON ID = CLIENTGROUP_IDX GROUP BY DATEADD(dd, DATEDIFF(dd, 0, ALERTDATETIME), 0) ORDER BY date DESC"; strConn = ConfigurationManager.ConnectionStrings["Symantec"].ConnectionString; break; } SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader rdr = null; try { conn = new SqlConnection(strConn); conn.Open(); cmd = new SqlCommand(sql, conn); cmd.CommandType = System.Data.CommandType.Text; rdr = cmd.ExecuteReader(); DateTime dt = DateTime.MaxValue; while (rdr.Read()) { Total t; if (dt != DateTime.MaxValue || (int)rdr[1] != 0) { if (dt == DateTime.MaxValue) { dt = (DateTime)rdr[0]; } while (((DateTime)rdr[0]).Date < (dt = dt.AddDays(-1).Date)) { t = new Total(dt, null, 0); result.Add(t); } t = new Total((DateTime)rdr[0], null, (int)rdr[1]); result.Add(t); dt = dt.AddDays(-1); } if (result.Count > 365) { break; } } result.Reverse(); } catch (Exception e) { throw e; } finally { rdr.Close(); cmd.Dispose(); } return result; }
public List<Total> GetAlertCountsByGroup(string system) { string sql = null; string strConn = ""; List<Total> result = new List<Total>(); switch (system) { case "Kaseya": // BUG -- Doesn't work yet sql = @" SELECT a.groupName AS [group], count(*) AS group_count FROM ( SELECT eventTime AS et, a.reverseName AS groupName FROM [dbo].[AVLogHistory] h INNER JOIN vAgentsAll a ON h.agentGUID = a.AgentGuid -- Filters out non-existant machines WHERE EventType = 1000 -- CodeText = ThreatDetected UNION ALL SELECT t.[TimeStamp] AS et, a.reverseName AS groupName FROM kam.KamThreatIncidents t INNER JOIN vAgentsAll a ON t.agentGUID = a.AgentGuid -- Filters out non-existant machines UNION ALL SELECT DetectedTime AS et, a.reverseName AS groupName FROM [kav].[vThreats] t INNER JOIN vAgentsAll a ON t.agentGUID = a.AgentGuid -- Filters out non-existant machines ) a GROUP BY groupName ORDER BY groupName ASC;"; strConn = ConfigurationManager.ConnectionStrings["Kaseya"].ConnectionString; break; case "Symantec": sql = @" SELECT name AS [group], count(*) AS group_count FROM V_ALERTS INNER JOIN IDENTITY_MAP ON ID = CLIENTGROUP_IDX GROUP BY name ORDER BY name ASC"; strConn = ConfigurationManager.ConnectionStrings["Symantec"].ConnectionString; break; } SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader rdr = null; try { conn = new SqlConnection(strConn); conn.Open(); cmd = new SqlCommand(sql, conn); cmd.CommandType = System.Data.CommandType.Text; rdr = cmd.ExecuteReader(); while (rdr.Read()) { Total t = new Total(null, (string)rdr[0], (int)rdr[1]); result.Add(t); } } catch (Exception e) { throw e; } finally { rdr.Close(); cmd.Dispose(); } return result; }