static void GrabData(MonitorManagement mm, string dbType) { lock (_locker) { _dicWorkState[mm.ID] = "working"; } try { if (dbType == "ORACLE") { GrabOracleData(mm); } else if (dbType == "DM") { GrabDmData(mm); } } catch (Exception ex) { LogHelper.WriteError(ex, string.Format("抓取审计信息,监控管理ID:{0},会话ID:{1}", mm.ID, mm.SCID)); } lock (_locker) { _dicWorkState[mm.ID] = "wait"; } }
static void GrabData(MonitorManagement mm, string dbType) { lock (_lockObj) { _dicWorkState[mm.ID] = "working"; Console.WriteLine("id:{0} working {2}-[{1}]", mm.ID, DateTime.Now, dbType); } try { if (dbType == "ORACLE") { GrabOracleData(mm); } else if (dbType == "DM") { GrabDmData(mm); } } catch { } lock (_lockObj) { _dicWorkState[mm.ID] = "wait"; Console.WriteLine("id:{0} wait", mm.ID); } }
public static void ExecuteGrab(MonitorManagement mm, string dbType) { //定义个工作状态(wait,working) if (_dicWorkState.ContainsKey(mm.ID)) { lock (_locker) { var state = _dicWorkState[mm.ID]; //工作中返回 if (state == "working") { return; } } } else { _dicWorkState.Add(mm.ID, "wait"); } //另开线程来采集 Task.Factory.StartNew(new Action(() => { GrabData(mm, dbType); })); }
static void GrabDmData(MonitorManagement mm) { DateTime dtBeg; DateTime dtEnd; //从上次最大时间开始 dtBeg = DateTime.Parse(mm.MMLastTime); dtEnd = DateTime.Now; //采集SQL StringBuilder sbSql = new StringBuilder(); sbSql.Append("SELECT a.USERNAME, "); sbSql.Append("a.SCHNAME, "); sbSql.Append("a.OBJNAME, "); sbSql.Append("a.OPERATION, "); sbSql.Append("a.SQL_TEXT, "); sbSql.Append(" TO_CHAR(a.OPTIME,'yyyy-mm-dd HH24:MI:SS') TIMESTAMP, "); sbSql.Append("s.SUBTYPE$ OBJTYPE "); sbSql.Append("FROM SYSAUDITOR.V$AUDITRECORDS a "); sbSql.Append("LEFT JOIN sysobjects s "); sbSql.Append("ON a.SCHID = s.SCHID "); sbSql.Append("WHERE SUCC_FLAG = 'Y' "); sbSql.AppendFormat("AND a.OPTIME >= to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtBeg.ToString("yyyy-MM-dd HH:mm:ss")); sbSql.AppendFormat("AND a.OPTIME < to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtEnd.ToString("yyyy-MM-dd HH:mm:ss")); DataTable dt = null; string connStr = GetSessionConnStr(mm.SCID.Value); using (DmDAL dal = new DmDAL(connStr)) { dt = dal.ExecuteQuery(sbSql.ToString()); } var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); using (var ctx = new DbMonitorEntities()) { foreach (DataRow dr in dt.Rows) { ChangeLog log = new ChangeLog(); log.SCID = mm.SCID; log.CLChangeEvent = dr["OPERATION"].ToString(); log.CLContent = ""; log.CLObjectName = dr["OBJNAME"].ToString(); log.CLSchema = dr["SCHNAME"].ToString(); log.CLObjectType = dr["OBJTYPE"].ToString(); log.CLSQL_Text = dr["SQL_TEXT"].ToString(); log.CLOperator = dr["USERNAME"].ToString(); log.CLChangeTime = dr["TIMESTAMP"].ToString(); log.CLGrabTime = grabTime; ctx.ChangeLog.Add(log); } var editMM = ctx.MonitorManagement.Find(mm.ID); editMM.MMLastTime = dtEnd.ToString("yyyy-MM-dd HH:mm:ss"); ctx.SaveChanges(); Console.WriteLine("id:{0} grab {1} items", mm.ID, dt.Rows.Count); } }
static void ExecuteGrab(MonitorManagement mm, string dbType) { //定义个工作状态(wait,working) if (_dicWorkState.ContainsKey(mm.ID)) { lock (_locker) { var state = _dicWorkState[mm.ID]; //工作中返回 if (state == "working") { return; } } } else { _dicWorkState.Add(mm.ID, "wait"); } //设置工作 // _dicWorkState[mm.ID] = "working"; var lastGrab = DateTime.Parse(mm.MMLastTime); DateTime currGrab = lastGrab; switch (mm.MMCycleUnit) { case "s": currGrab = lastGrab.AddSeconds(mm.MMRefreshCycle.Value); break; case "m": currGrab = lastGrab.AddMinutes(mm.MMRefreshCycle.Value); break; case "h": currGrab = lastGrab.AddHours(mm.MMRefreshCycle.Value); break; } if (DateTime.Now >= currGrab) { //另开线程来采集 Task.Factory.StartNew(new Action(() => { GrabData(mm, dbType); })); //状态抓取 //StatusDataGraber.ExecuteGrab(mm, dbType); } }
static void GrabOracleData(MonitorManagement mm) { //采集SQL StringBuilder sbSql = new StringBuilder(); sbSql.Append("SELECT INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,DATABASE_STATUS FROM V$INSTANCE"); DataTable dt = null; string connStr = DataGraber.GetSessionConnStr(mm.SCID.Value); using (OracleDAL dal = new OracleDAL(connStr)) { dt = dal.ExecuteQuery(sbSql.ToString()); } var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); using (var ctx = new DbMonitorEntities()) { //把上一次数据设成历史数据 var ds = ctx.DatabaseStatus.Where(s => s.REALTIME == 1); foreach (var s in ds) { s.REALTIME = 0; s.EditingTime = grabTime; } ctx.SaveChanges(); foreach (DataRow dr in dt.Rows) { DatabaseStatus status = ctx.DatabaseStatus.Create(); status.SCID = mm.SCID; status.INSTANCE_NAME = dr["INSTANCE_NAME"].ToString(); status.HOST_NAME = dr["HOST_NAME"].ToString(); status.VERSION = dr["VERSION"].ToString(); status.STARTUP_TIME = dr["STARTUP_TIME"].ToString(); status.STATUS = dr["STATUS"].ToString(); status.DATABASE_STATUS = dr["DATABASE_STATUS"].ToString(); status.REALTIME = 1; status.CreationTime = grabTime; ctx.DatabaseStatus.Add(status); } ctx.SaveChanges(); Console.WriteLine("id:{0} oracle-status-grab {1} items", mm.ID, dt.Rows.Count); } }
static void GrabOracleData(MonitorManagement mm) { DateTime dtBeg; DateTime dtEnd; //首次采集 //if (string.IsNullOrWhiteSpace(mm.MMLastTime)) //{ // //从现在开始 // dtBeg = DateTime.Now; //} //else //{ // //从上次最大时间开始 // dtBeg = DateTime.Parse(mm.MMLastTime); //} ////采集范围 //dtEnd = dtBeg.AddMinutes(mm.MMTimeRange.Value); //if(dtEnd > DateTime.Now) //{ // dtEnd = DateTime.Now; //} //从上次最大时间开始 dtBeg = DateTime.Parse(mm.MMLastTime); dtEnd = DateTime.Now; //采集SQL StringBuilder sbSql = new StringBuilder(); sbSql.Append("SELECT t.DB_USER,t.OBJECT_SCHEMA,t.OBJECT_NAME,t.STATEMENT_TYPE,t.SQL_TEXT,to_char(t.EXTENDED_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') TIMESTAMP,o.OBJECT_TYPE "); sbSql.Append("FROM DBA_COMMON_AUDIT_TRAIL t "); sbSql.Append("LEFT JOIN dba_objects o "); sbSql.Append("ON t.OBJECT_SCHEMA = o.OWNER AND t.OBJECT_NAME = o.OBJECT_NAME "); sbSql.Append("WHERE t.OBJECT_NAME is not null AND object_type is not null and returncode = 0 "); sbSql.AppendFormat("and t.STATEMENT_TYPE not in('SELECT') "); sbSql.AppendFormat("and t.EXTENDED_TIMESTAMP >= to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", dtBeg.ToString("yyyy-MM-dd HH:mm:ss")); sbSql.AppendFormat("and t.EXTENDED_TIMESTAMP < to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", dtEnd.ToString("yyyy-MM-dd HH:mm:ss")); DataTable dt = null; string connStr = GetSessionConnStr(mm.SCID.Value); using (OracleDAL dal = new OracleDAL(connStr)) { dt = dal.ExecuteQuery(sbSql.ToString()); } var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); using (var ctx = new DbMonitorEntities()) { foreach (DataRow dr in dt.Rows) { ChangeLog log = new ChangeLog(); log.SCID = mm.SCID; log.CLChangeEvent = dr["STATEMENT_TYPE"].ToString(); log.CLContent = ""; log.CLObjectName = dr["OBJECT_NAME"].ToString(); log.CLSchema = dr["OBJECT_SCHEMA"].ToString(); log.CLObjectType = dr["OBJECT_TYPE"].ToString(); log.CLSQL_Text = dr["SQL_TEXT"].ToString(); log.CLOperator = dr["DB_USER"].ToString(); log.CLChangeTime = dr["TIMESTAMP"].ToString(); log.CLGrabTime = grabTime; ctx.ChangeLog.Add(log); } var editMM = ctx.MonitorManagement.Find(mm.ID); editMM.MMLastTime = dtEnd.ToString("yyyy-MM-dd HH:mm:ss"); ctx.SaveChanges(); Console.WriteLine("id:{0} grab {1} items", mm.ID, dt.Rows.Count); } }
static void GrabDmData(MonitorManagement mm) { DateTime dtBeg; DateTime dtEnd; //从上次最大时间开始 dtBeg = DateTime.Parse(mm.MMLastTime); dtEnd = DateTime.Now; //采集SQL StringBuilder sbSql = new StringBuilder(); sbSql.Append("SELECT a.USERNAME, "); sbSql.Append("a.SCHNAME, "); sbSql.Append("a.OBJNAME, "); sbSql.Append("a.OPERATION, "); sbSql.Append("a.SQL_TEXT, "); sbSql.Append(" TO_CHAR(a.OPTIME,'yyyy-mm-dd HH24:MI:SS') TIMESTAMP, "); sbSql.Append("'' AS OBJTYPE "); sbSql.Append("FROM SYSAUDITOR.V$AUDITRECORDS a "); //sbSql.Append("LEFT JOIN sysobjects s "); //sbSql.Append("ON a.SCHID = s.SCHID "); //sbSql.Append("WHERE SUCC_FLAG = 'Y' "); sbSql.Append("WHERE OPERATION NOT IN ('SELECT') "); sbSql.AppendFormat("AND a.OPTIME >= to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtBeg.ToString("yyyy-MM-dd HH:mm:ss")); sbSql.AppendFormat("AND a.OPTIME < to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtEnd.ToString("yyyy-MM-dd HH:mm:ss")); DataTable dt = null; string connStr = GetSessionConnStr(mm.SCID.Value); using (DmDAL dal = new DmDAL(connStr)) { dt = dal.ExecuteQuery(sbSql.ToString()); } var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); bool bAdd; if (dt.Rows.Count > 0) { using (var ctx = new DbMonitorEntities()) { foreach (DataRow dr in dt.Rows) { bAdd = true; ChangeLog log = new ChangeLog(); log.SCID = mm.SCID; log.CLChangeEvent = dr["OPERATION"].ToString(); log.CLContent = ""; log.CLObjectName = dr["OBJNAME"].ToString(); log.CLSchema = dr["SCHNAME"].ToString(); log.CLObjectType = dr["OBJTYPE"].ToString(); log.CLSQL_Text = dr["SQL_TEXT"].ToString(); log.CLOperator = dr["USERNAME"].ToString(); log.CLChangeTime = dr["TIMESTAMP"].ToString(); log.CLGrabTime = grabTime; var sql_Upper = log.CLSQL_Text.ToUpper(); if (_dicDmDDL.ContainsKey(log.CLChangeEvent)) { var objName = GetObjectNameFormDDL(sql_Upper, log.CLChangeEvent); if (log.CLChangeEvent.Contains("CREATE")) { log.CLOldData = ""; log.CLNewData = objName; } else if (log.CLChangeEvent.Contains("ALTER")) { var pos = sql_Upper.IndexOf(objName); log.CLNewData = sql_Upper.Substring(pos + objName.Length); } else if (log.CLChangeEvent.Contains("DROP")) { log.CLOldData = objName; log.CLNewData = ""; } log.CLChangeType = _dicDmDDL[log.CLChangeEvent]; switch (log.CLChangeEvent) { case "CREATE TABLE": case "ALTER TABLE": case "DROP TABLE": case "TRUNCATE TABLE": log.CLObjectType = "UTAB"; break; case "CREATE VIEW": case "DROP VIEW": log.CLObjectType = "VIEW"; break; case "CREATE PROCEDURE": case "DROP PROCEDURE": log.CLObjectType = "PROC"; break; case "CREATE TRIGGER": case "DROP TRIGGER": log.CLObjectType = "TRIG"; break; case "CREATE INDEX": case "DROP INDEX": log.CLObjectType = "INDEX"; break; } } //数据操纵 else if (log.CLChangeEvent == "INSERT") { var pos = sql_Upper.IndexOf("VALUES") + 6; var lastPos = sql_Upper.LastIndexOf(")"); log.CLOldData = ""; log.CLNewData = sql_Upper.Substring(pos, lastPos - pos); log.CLChangeType = "插入数据"; log.CLObjectType = "UTAB"; } else if (log.CLChangeEvent == "UPDATE") { var pos = sql_Upper.IndexOf("SET"); log.CLOldData = ""; log.CLNewData = sql_Upper.Substring(pos); log.CLChangeType = "更新数据"; log.CLObjectType = "UTAB"; } else if (log.CLChangeEvent == "DELETE") { var pos = sql_Upper.IndexOf(log.CLObjectName); log.CLOldData = ""; log.CLNewData = sql_Upper.Substring(pos + log.CLObjectName.Length); log.CLChangeType = "删除数据"; log.CLObjectType = "UTAB"; } else { bAdd = false; } if (bAdd) { ctx.ChangeLog.Add(log); } //有数据才更新数据库 var editMM = ctx.MonitorManagement.Find(mm.ID); editMM.MMLastTime = dtEnd.ToString("yyyy-MM-dd HH:mm:ss"); ctx.SaveChanges(); } } Console.WriteLine("id:{0} grab {1} items", mm.ID, dt.Rows.Count); } }
static void GrabOracleData(MonitorManagement mm) { DateTime dtBeg; DateTime dtEnd; //从上次最大时间开始 dtBeg = DateTime.Parse(mm.MMLastTime); dtEnd = DateTime.Now; //采集SQL /*StringBuilder sbSql = new StringBuilder(); * sbSql.Append("SELECT t.DB_USER,t.OBJECT_SCHEMA,t.OBJECT_NAME,t.STATEMENT_TYPE,t.SQL_TEXT,to_char(t.EXTENDED_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') TIMESTAMP,o.OBJECT_TYPE "); * sbSql.Append("FROM DBA_COMMON_AUDIT_TRAIL t "); * sbSql.Append("LEFT JOIN dba_objects o "); * sbSql.Append("ON t.OBJECT_SCHEMA = o.OWNER AND t.OBJECT_NAME = o.OBJECT_NAME "); * sbSql.Append("WHERE t.OBJECT_NAME is not null AND object_type is not null and returncode = 0 "); * sbSql.AppendFormat("and t.STATEMENT_TYPE not in('SELECT') "); * sbSql.AppendFormat("and t.EXTENDED_TIMESTAMP >= to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", dtBeg.ToString("yyyy-MM-dd HH:mm:ss")); * sbSql.AppendFormat("and t.EXTENDED_TIMESTAMP < to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", dtEnd.ToString("yyyy-MM-dd HH:mm:ss")); */ DataTable dt = null; string connStr = GetSessionConnStr(mm.SCID.Value); using (OracleDAL dal = new OracleDAL(connStr)) { //dt = dal.ExecuteQuery(sbSql.ToString()); dt = dal.ExecuteProcedureQuery("DB_MONITOR.P_GetChangeLog", new OracleParameter("begtime", OracleDbType.Varchar2, dtBeg.ToString("yyyy-MM-dd HH:mm:ss"), ParameterDirection.Input), new OracleParameter("endtime", OracleDbType.Varchar2, dtEnd.ToString("yyyy-MM-dd HH:mm:ss"), ParameterDirection.Input), new OracleParameter("out_data", OracleDbType.RefCursor, ParameterDirection.Output) ); } var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); using (var ctx = new DbMonitorEntities()) { foreach (DataRow dr in dt.Rows) { ChangeLog log = new ChangeLog(); log.SCID = mm.SCID; log.CLChangeEvent = dr["STATEMENT_TYPE"].ToString(); log.CLContent = ""; log.CLObjectName = dr["OBJECT_NAME"].ToString(); log.CLSchema = dr["OBJECT_SCHEMA"].ToString(); log.CLObjectType = dr["OBJECT_TYPE"].ToString(); log.CLSQL_Text = dr["SQL_TEXT"].ToString(); log.CLOperator = dr["DB_USER"].ToString(); log.CLChangeTime = dr["OP_TIME"].ToString(); log.CLGrabTime = grabTime; log.CLOldData = dr["OLD_DATA"].ToString(); log.CLNewData = dr["NEW_DATA"].ToString(); log.CLChangeType = dr["CHANGE_TYPE"].ToString(); ctx.ChangeLog.Add(log); var editMM = ctx.MonitorManagement.Find(mm.ID); editMM.MMLastTime = dtEnd.ToString("yyyy-MM-dd HH:mm:ss"); ctx.SaveChanges(); } Console.WriteLine("id:{0} grab {1} items", mm.ID, dt.Rows.Count); } }