public ActionResult List(long scId, int page = 1, int limit = 20, string user = "", string option = "")
{
JsonResult ret = new JsonResult();
ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
try
{
StringBuilder sbCount = new StringBuilder();
StringBuilder sbSql = new StringBuilder();
string tv = "DBA_PRIV_AUDIT_OPTS";
sbCount.AppendFormat("SELECT COUNT(1) FROM {0} ", tv);
sbSql.AppendFormat("SELECT * FROM (SELECT ROWNUM AS ROWNO, t.* FROM {0} t WHERE ", tv);
//筛选条件
if (!string.IsNullOrWhiteSpace(user))
{
sbSql.AppendFormat("USER_NAME LIKE '%{0}%' AND ", user);
sbCount.AddCondition(string.Format("USER_NAME LIKE '%{0}%'", user));
}
if (!string.IsNullOrWhiteSpace(option))
{
sbSql.AppendFormat("PRIVILEGE LIKE '%{0}%' AND ", option.ToUpper());
sbCount.AddCondition(string.Format("PRIVILEGE LIKE '%{0}%'", option.ToUpper()));
}
sbSql.AppendFormat("ROWNUM <= {0}) table_alias WHERE table_alias.ROWNO > {1}",
page * limit, (page - 1) * limit);
int count = 0;
DataTable dt = null;
//string connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));Persist Security Info=True;User ID=sys;Password=sys;DBA Privilege=SYSDBA;";
string connStr = GetSessionConnStr(scId);
using (OracleDAL dal = new OracleDAL(connStr))
{
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);
}
/// <summary>
/// 查询一列值并以List<string>返回
/// </summary>
/// <param name="dal"></param>
/// <param name="sqlText"></param>
/// <returns></returns>
public static List <string> GetOneColumnValue(this OracleDAL 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);
}
//SELECT * FROM STUDENT AS OF TIMESTAMP TO_TIMESTAMP('2018-12-19 16:00:16','YYYY-MM-DD hh24:mi:ss')
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(1) FROM {0} AS OF TIMESTAMP TO_TIMESTAMP('{1}','YYYY-MM-DD hh24:mi:ss')", tv, endtime);
sbSql.AppendFormat("SELECT * FROM (SELECT ROWNUM AS ROWNO, t.* FROM {0} AS OF TIMESTAMP TO_TIMESTAMP('{1}','YYYY-MM-DD hh24:mi:ss') t WHERE ", tv, endtime);
sbSql.AppendFormat("ROWNUM <= {0}) table_alias WHERE table_alias.ROWNO > {1}",
page * limit, (page - 1) * limit);
int count = 0;
DataTable dt = null;
using (OracleDAL dal = new OracleDAL(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);
}
public ActionResult List(long scId, int page = 1, int limit = 20, string username = "")
{
JsonResult ret = new JsonResult();
ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
try
{
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat(@"select 'Statement' ITEM, count(1) ALL_COUNT,count(1) ENABLE_COUNT,0 DISABLE_COUNT from DBA_STMT_AUDIT_OPTS
UNION ALL
select 'Object' Item, count(1) ALL_COUNT,count(1) ENABLE_COUNT,0 DISABLE_COUNT from DBA_OBJ_AUDIT_OPTS
UNION ALL
select 'Privilege' Item, count(1) ALL_COUNT,count(1) ENABLE_COUNT,0 DISABLE_COUNT from DBA_PRIV_AUDIT_OPTS
UNION ALL
Select * from
(select 'FGA' Item, count(1) ALL_COUNT from DBA_AUDIT_POLICIES
),
(select COUNT(1) ENABLE_COUNT from DBA_AUDIT_POLICIES WHERE ENABLED = 'YES'),
(select COUNT(1) DISABLE_COUNT from DBA_AUDIT_POLICIES WHERE ENABLED = 'NO')");
int count = 4;
DataTable dt = null;
//string connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));Persist Security Info=True;User ID=sys;Password=sys;DBA Privilege=SYSDBA;";
string connStr = GetSessionConnStr(scId);
using (OracleDAL dal = new OracleDAL(connStr))
{
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 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);
}
}
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} WHERE ROWNUM <= 1", user, objname);
DataTable dt = null;
using (var dal = new OracleDAL(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 GetColumnName(long scId, string user, string objname)
{
JsonResult ret = new JsonResult();
try
{
List <string> objs = new List <string>();
//查询语句结尾不要逗号，否则报错:ORA-00911: 无效字符
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("select column_name from dba_tab_columns where owner='{0}' and TABLE_NAME = '{1}'",
user, objname);
using (OracleDAL dal = new OracleDAL(GetSessionConnStr(scId)))
{
DataTable dt = dal.ExecuteQuery(sbSql.ToString());
foreach (DataRow row in dt.Rows)
{
var u = row.ItemArray[0].ToString();
objs.Add(u);
}
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
data = objs
});
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = ex.Message
});
RecordException(ex);
}
return(ret);
}
public ActionResult List(long scId, string schema, string user, string objname, string type, string begtime, 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 = "DBA_COMMON_AUDIT_TRAIL";
sbCount.AppendFormat("SELECT COUNT(1) FROM {0}", tv);
sbSql.AppendFormat("SELECT * FROM (SELECT ROWNUM AS ROWNO, t.* FROM {0} t WHERE ", tv);
//筛选条件
if (!string.IsNullOrWhiteSpace(schema))
{
sbSql.AppendFormat("OBJECT_SCHEMA LIKE '%{0}%' AND ", schema.ToUpper());
sbCount.AddCondition(string.Format("OBJECT_SCHEMA LIKE '%{0}%'", schema.ToUpper()));
}
if (!string.IsNullOrWhiteSpace(user))
{
sbSql.AppendFormat("DB_USER LIKE '%{0}%' AND ", user.ToUpper());
sbCount.AddCondition(string.Format("DB_USER LIKE '%{0}%'", user.ToUpper()));
}
if (!string.IsNullOrWhiteSpace(objname))
{
sbSql.AppendFormat("OBJECT_NAME LIKE '%{0}%' AND ", objname.ToUpper());
sbCount.AddCondition(string.Format("OBJECT_NAME LIKE '%{0}%'", objname.ToUpper()));
}
if (!string.IsNullOrWhiteSpace(type))
{
sbSql.AppendFormat("STATEMENT_TYPE LIKE '%{0}%' AND ", type.ToUpper());
sbCount.AddCondition(string.Format("STATEMENT_TYPE LIKE '%{0}%'", type.ToUpper()));
}
if (!string.IsNullOrWhiteSpace(begtime))
{
sbSql.AppendFormat("extended_timestamp >= to_date('{0}','yyyy-MM-dd HH24:mi:ss') AND ", begtime);
sbCount.AddCondition(string.Format("extended_timestamp >= to_date('{0}','yyyy-MM-dd HH24:mi:ss')", begtime));
}
if (!string.IsNullOrWhiteSpace(endtime))
{
sbSql.AppendFormat("extended_timestamp <= to_date('{0}','yyyy-MM-dd HH24:mi:ss') AND ", endtime);
sbCount.AddCondition(string.Format("extended_timestamp <= to_date('{0}','yyyy-MM-dd HH24:mi:ss')", endtime));
}
sbSql.AppendFormat("ROWNUM <= {0}) table_alias WHERE table_alias.ROWNO > {1}",
page * limit, (page - 1) * limit);
int count = 0;
DataTable dt = null;
//string connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));Persist Security Info=True;User ID=sys;Password=sys;DBA Privilege=SYSDBA;";
string connStr = GetSessionConnStr(scId);
using (OracleDAL dal = new OracleDAL(connStr))
{
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);
}
public ActionResult List(long scId, int page = 1, int limit = 20, string user = "", string objtype = "", string objname = "")
{
JsonResult ret = new JsonResult();
ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
try
{
StringBuilder sbCount = new StringBuilder();
StringBuilder sbSql = new StringBuilder();
string tv = "DBA_OBJ_AUDIT_OPTS";
sbCount.AppendFormat("SELECT COUNT(1) FROM {0} ", tv);
sbSql.AppendFormat("SELECT * FROM (SELECT ROWNUM AS ROWNO, t.* FROM {0} t WHERE ", tv);
//筛选条件
if (!string.IsNullOrWhiteSpace(user))
{
sbSql.AppendFormat("OWNER LIKE '%{0}%' AND ", user);
sbCount.AddCondition(string.Format("OWNER LIKE '%{0}%'", user));
}
if (!string.IsNullOrWhiteSpace(objtype))
{
sbSql.AppendFormat("OBJECT_TYPE LIKE '%{0}%' AND ", objtype);
sbCount.AddCondition(string.Format("OBJECT_TYPE LIKE '%{0}%'", objtype));
}
if (!string.IsNullOrWhiteSpace(objname))
{
sbSql.AppendFormat("OBJECT_NAME LIKE '%{0}%' AND ", objname);
sbCount.AddCondition(string.Format("OBJECT_NAME LIKE '%{0}%'", objname));
}
sbSql.AppendFormat("ROWNUM <= {0}) table_alias WHERE table_alias.ROWNO > {1}",
page * limit, (page - 1) * limit);
int count = 0;
DataTable dt = null;
string connStr = GetSessionConnStr(scId);
using (OracleDAL dal = new OracleDAL(connStr))
{
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 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);
}
}