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);
}
}
/// <summary>
/// 查询一列值并以List<string>返回
/// </summary>
/// <param name="dal"></param>
/// <param name="sqlText"></param>
/// <returns></returns>
public static List <string> GetOneColumnValue(this DmDAL dal, string sqlText)
{
List <string> ret = new List <string>();
var dt = dal.ExecuteQuery(sqlText);
foreach (DataRow row in dt.Rows)
{
ret.Add(row[0].ToString());
}
return(ret);
}
public ActionResult List(long scId, string user, string objname, string endtime, int page = 1, int limit = 20)
{
JsonResult ret = new JsonResult();
ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
try
{
StringBuilder sbCount = new StringBuilder();
StringBuilder sbSql = new StringBuilder();
string tv = string.Format("{0}.{1}", user, objname);
if (string.IsNullOrWhiteSpace(endtime))
{
endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
sbCount.AppendFormat("SELECT COUNT(*) FROM {0} WHEN TIMESTAMP '{1}'", tv, endtime);
sbSql.AppendFormat("SELECT * FROM {0} WHEN TIMESTAMP '{1}'", tv, endtime);
sbSql.AppendFormat(" LIMIT {0} OFFSET {1}",
limit, (page - 1) * limit);
int count = 0;
DataTable dt = null;
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
count = Convert.ToInt32(dal.ExecuteScalar(sbCount.ToString()));
dt = dal.ExecuteQuery(sbSql.ToString());
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
message = "",
total = count,
data = dt
});
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = "发生异常：" + ex.Message,
total = 0,
data = ""
});
RecordException(ex);
}
return(ret);
}
static void GrabDmData(MonitorManagement mm)
{
//采集SQL
StringBuilder sbSql = new StringBuilder();
sbSql.Append("select HOST_NAME,INSTANCE_NAME,SVR_VERSION AS VERSION,START_TIME AS STARTUP_TIME,STATUS$ AS STATUS,MODE$ AS DATABASE_STATUS from V$INSTANCE");
DataTable dt = null;
string connStr = DataGraber.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())
{
//把上一次数据设成历史数据
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} dm-status-grab {1} items", mm.ID, dt.Rows.Count);
}
}
}
public ActionResult GetColumnName(long scId, string user, string objname)
{
JsonResult ret = new JsonResult();
ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
try
{
List <string> colNames = new List <string>();
if (!string.IsNullOrWhiteSpace(user) && !string.IsNullOrWhiteSpace(objname))
{
string sql = string.Format("SELECT * FROM {0}.{1} LIMIT 1 OFFSET 1", user, objname);
DataTable dt = null;
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
dt = dal.ExecuteQuery(sql);
}
if (dt != null)
{
foreach (DataColumn col in dt.Columns)
{
colNames.Add(col.ColumnName);
}
}
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
message = "",
data = colNames
});;
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = "发生异常：" + ex.Message,
data = ""
});
RecordException(ex);
}
return(ret);
}
public ActionResult List(long scId, string user, string schema, string objname, string type, int page = 1, int limit = 30)
{
JsonResult ret = new JsonResult();
ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
try
{
StringBuilder sbSql = new StringBuilder();
StringBuilder sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(*) FROM ");
sbSql.Append("SELECT * FROM ");
string subTable = @"(SELECT ad.*,
obj1.NAME AS USERNAME,
obj2.NAME AS OBJECTNAME,
(SELECT NAME FROM sysobjects WHERE TYPE$='SCH' AND SUBTYPE$ IS NULL AND ID = (SELECT SCHID FROM sysobjects WHERE ID = ad.TVPID)) AS SCHEMANAME,
(SELECT NAME FROM SYSCOLUMNS WHERE ID = ad.TVPID AND COLID = ad.COLID) AS COLNAME,
(CASE LEVEL WHEN 1 THEN '语句级' WHEN 2 THEN '对象级' ELSE '' END) AS SLEVEL,
(CASE TYPE WHEN 0 THEN 'ALL'
WHEN 12 THEN 'USER'
WHEN 13 THEN 'ROLE'
WHEN 9 THEN 'TABLESPACE'
WHEN 14 THEN 'SCHEMA'
WHEN 15 THEN 'TABLE'
WHEN 16 THEN 'VIEW'
WHEN 17 THEN 'INDEX'
WHEN 18 THEN 'PROCEDURE'
WHEN 19 THEN 'TRIGGER'
WHEN 20 THEN 'SEQUENCE'
WHEN 21 THEN 'CONTEXT'
WHEN 26 THEN 'SYNONYM'
WHEN 22 THEN 'GRANT'
WHEN 23 THEN 'REVOKE'
WHEN 24 THEN 'AUDIT'
WHEN 25 THEN 'NOAUDIT'
WHEN 30 THEN 'INSERT TABLE'
WHEN 33 THEN 'UPDATE TABLE'
WHEN 32 THEN 'DELETE TABLE'
WHEN 31 THEN 'SELECT TABLE'
WHEN 18 THEN 'PROCEDURE'
WHEN 44 THEN 'PACKAGE'
WHEN 45 THEN 'PACKAGE BODY'
WHEN 34 THEN 'MAC POLICY'
WHEN 35 THEN 'MAC LEVEL'
WHEN 36 THEN 'MAC COMPARTMENT'
WHEN 37 THEN 'MAC GROUP'
WHEN 38 THEN 'MAC LABEL'
WHEN 40 THEN 'MAC USER'
WHEN 41 THEN 'MAC TABLE'
WHEN 39 THEN 'MAC SESSION'
WHEN 28 THEN 'CHECKPOINT'
WHEN 75 THEN 'SAVEPOINT'
WHEN 76 THEN 'EXPLAIN'
WHEN 77 THEN 'NOT EXIST'
WHEN 70 THEN 'DATABASE'
WHEN 74 THEN 'CONNECT'
WHEN 72 THEN 'COMMIT'
WHEN 73 THEN 'ROLLBACK'
WHEN 43 THEN 'SET TRANSACTION'
WHEN 50 THEN 'INSERT'
WHEN 53 THEN 'UPDATE'
WHEN 52 THEN 'DELETE'
WHEN 51 THEN 'SELECT'
WHEN 54 THEN 'EXECUTE'
WHEN 56 THEN 'MERGE INTO'
WHEN 55 THEN 'EXECUTE TRIGGER'
WHEN 57 THEN 'LOCK TABLE'
ELSE '' END) AS STYPE,
'' AS SWHENEVER
FROM SYSAUDITOR.SYSAUDIT ad
LEFT OUTER JOIN sysobjects obj1
ON obj1.ID = ad.UID
LEFT OUTER JOIN sysobjects obj2
ON obj2.ID = ad.TVPID)";
sbCount.Append(subTable);
sbSql.Append(subTable);
if (!string.IsNullOrWhiteSpace(user))
{
sbCount.AddCondition(string.Format("USERNAME LIKE '%{0}%'", user.ToUpper()));
sbSql.AddCondition(string.Format("USERNAME LIKE '%{0}%'", user.ToUpper()));
}
if (!string.IsNullOrWhiteSpace(schema))
{
sbCount.AddCondition(string.Format("SCHEMANAME LIKE '%{0}%'", schema.ToUpper()));
sbSql.AddCondition(string.Format("SCHEMANAME LIKE '%{0}%'", schema.ToUpper()));
}
if (!string.IsNullOrWhiteSpace(objname))
{
sbCount.AddCondition(string.Format("OBJECTNAME LIKE '%{0}%'", objname.ToUpper()));
sbSql.AddCondition(string.Format("OBJECTNAME LIKE '%{0}%'", objname.ToUpper()));
}
if (!string.IsNullOrWhiteSpace(type))
{
sbCount.AddCondition(string.Format("STYPE LIKE '%{0}%'", type.ToUpper()));
sbSql.AddCondition(string.Format("STYPE LIKE '%{0}%'", type.ToUpper()));
}
sbSql.AppendFormat(" LIMIT {0} OFFSET {1}",
limit, (page - 1) * limit);
int count = 0;
DataTable dt = null;
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
count = Convert.ToInt32(dal.ExecuteScalar(sbCount.ToString()));
dt = dal.ExecuteQuery(sbSql.ToString());
}
foreach (DataRow dr in dt.Rows)
{
var when = Convert.ToInt32(dr["WHENEVER"]);
string sWhen = "";
switch (when)
{
case 1:
sWhen = "SUCCESSFUL";
break;
case 2:
sWhen = "FAIL";
break;
case 3:
sWhen = "ALL";
break;
}
dr["SWHENEVER"] = sWhen;
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
message = "",
total = count,
data = dt
});
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = "发生异常：" + ex.Message,
total = 0,
data = ""
});
RecordException(ex);
}
return(ret);
}
public ActionResult List(long scId, string user, string schema, string objname, string type, string begtime, string endtime, int page = 1, int limit = 20)
{
JsonResult ret = new JsonResult();
ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
try
{
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
StringBuilder sbSql = new StringBuilder();
StringBuilder sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(*) FROM SYSAUDITOR.V$AUDITRECORDS");
sbSql.Append("SELECT ar.*,'' AS POLICY_NAME FROM SYSAUDITOR.V$AUDITRECORDS ar");
if (!string.IsNullOrWhiteSpace(user))
{
sbSql.AddCondition(string.Format("USERNAME LIKE '%{0}%'", user.ToUpper()));
sbCount.AddCondition(string.Format("USERNAME LIKE '%{0}%'", user.ToUpper()));
}
if (!string.IsNullOrWhiteSpace(schema))
{
sbSql.AddCondition(string.Format("SCHNAME LIKE '%{0}%'", schema.ToUpper()));
sbCount.AddCondition(string.Format("SCHNAME LIKE '%{0}%'", schema.ToUpper()));
}
if (!string.IsNullOrWhiteSpace(objname))
{
sbSql.AddCondition(string.Format("OBJNAME LIKE '%{0}%'", objname.ToUpper()));
sbCount.AddCondition(string.Format("OBJNAME LIKE '%{0}%'", objname.ToUpper()));
}
if (!string.IsNullOrWhiteSpace(type))
{
sbSql.AddCondition(string.Format("OPERATION LIKE '%{0}%'", type.ToUpper()));
sbCount.AddCondition(string.Format("OPERATION LIKE '%{0}%'", type.ToUpper()));
}
if (!string.IsNullOrWhiteSpace(begtime))
{
sbSql.AddCondition(string.Format("OPTIME >= '{0}'", begtime));
sbCount.AddCondition(string.Format("OPTIME >= '{0}'", begtime));
}
if (!string.IsNullOrWhiteSpace(endtime))
{
sbSql.AddCondition(string.Format("OPTIME < '{0}'", endtime));
sbCount.AddCondition(string.Format("OPTIME < '{0}'", endtime));
}
sbSql.AppendFormat(" LIMIT {0} OFFSET {1}",
limit, (page - 1) * limit);
int count = Convert.ToInt32(dal.ExecuteScalar(sbCount.ToString()));
var dt = dal.ExecuteQuery(sbSql.ToString());
var policies = db.AuditPolicy.Where(p => p.SCID == scId).ToList();
if (dt.Rows.Count > 0 && policies.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
var username = row["USERNAME"].ToString();
var schemaname = row["SCHNAME"].ToString();
var obj = row["OBJNAME"].ToString();
var op = row["OPERATION"].ToString();
var sqlUpperCase = row["SQL_TEXT"].ToString().ToUpper();
if (schemaname == "" || obj == "")
{
continue;
}
var policy = policies.Where(p => p.APUser == username && p.APSchema == schemaname && p.APObjectName == obj && p.APStatement == op &&
sqlUpperCase.Contains(p.APCondition)).FirstOrDefault();
/*var po = policies.FirstOrDefault();
* if (po.APUser == username)
* {
* Console.WriteLine("abc");
* }
* if (po.APSchema == schema)
* {
* Console.WriteLine("abc");
* }
* if (po.APObjectName == obj)
* {
* Console.WriteLine("abc");
* }
* if (po.APStatement == op)
* {
* Console.WriteLine("abc");
* }
* if (sqlUpperCase.Contains(po.APCondition))
* {
* Console.WriteLine("");
* }*/
if (policy != null)
{
row["POLICY_NAME"] = policy.APName;
}
}
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
message = "",
total = count,
data = dt
});
}
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = "发生异常：" + ex.Message,
total = 0,
data = ""
});
RecordException(ex);
}
return(ret);
}
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);
}
}
