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