public tb_performance_dayreport_model GetStaticMonitor(DbConn PubConn, string day, int serverid, int startid)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("serverid", serverid);
ps.Add("startid", startid);
StringBuilder sbSelect = new StringBuilder();
sbSelect.Append("SELECT serverid ");
sbSelect.Append(",AVG(cpu) avgcpu,MAX(cpu) maxcpu,MIN(cpu) mincpu ");
sbSelect.Append(",AVG(memory) avgmemory,MAX(memory) maxmemory,MIN(memory) minmemory ");
sbSelect.Append(",AVG(networkupload) avgnetworkupload,MAX(networkupload) maxnetworkupload,MIN(networkupload) minnetworkupload ");
sbSelect.Append(",AVG(networkdownload) avgnetworkdownload,MAX(networkdownload) maxnetworkdownload,MIN(networkdownload) minnetworkdownload ");
sbSelect.Append(",AVG(ioread) avgioread,MAX(ioread) maxioread,MIN(ioread) minioread ");
sbSelect.Append(",AVG(iowrite) avgiowrite,MAX(iowrite) maxiowrite,MIN(iowrite) miniowrite ");
sbSelect.Append(",AVG(iisrequest) avgiisrequest,MAX(iisrequest) maxiisrequest,MIN(iisrequest) miniisrequest ");
sbSelect.Append(",MAX(ID) lastmaxid,count(1) count ");
sbSelect.AppendFormat("FROM tb_performance_collect{0} with (nolock) ", day);
sbSelect.AppendFormat("where serverid=@serverid and id>@startid ");
sbSelect.Append("group by serverid ");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sbSelect.ToString(), ps.ToParameters());
if (ds.Tables[0].Rows.Count > 0)
{
tb_performance_dayreport_model model = CreateModel(ds.Tables[0].Rows[0]);
return model;
}
else
{
return null;
}
}));
}
public virtual bool AddStatis(DbConn PubConn, tb_performance_dayreport_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//服务器id
new ProcedureParameter("@serverid", model.serverid),
//平均cpu
new ProcedureParameter("@avgcpu", model.avgcpu),
//最大cpu
new ProcedureParameter("@maxcpu", model.maxcpu),
//
new ProcedureParameter("@mincpu", model.mincpu),
//
new ProcedureParameter("@avgmemory", model.avgmemory),
//
new ProcedureParameter("@maxmemory", model.maxmemory),
//
new ProcedureParameter("@minmemory", model.minmemory),
//
new ProcedureParameter("@avgnetworkupload", model.avgnetworkupload),
//
new ProcedureParameter("@maxnetworkupload", model.maxnetworkupload),
//
new ProcedureParameter("@minnetworkupload", model.minnetworkupload),
//
new ProcedureParameter("@avgnetworkdownload", model.avgnetworkdownload),
//
new ProcedureParameter("@maxnetworkdownload", model.maxnetworkdownload),
//
new ProcedureParameter("@minnetworkdownload", model.minnetworkdownload),
//
new ProcedureParameter("@avgioread", model.avgioread),
//
new ProcedureParameter("@maxioread", model.maxioread),
//
new ProcedureParameter("@minioread", model.minioread),
//
new ProcedureParameter("@avgiowrite", model.avgiowrite),
//
new ProcedureParameter("@maxiowrite", model.maxiowrite),
//
new ProcedureParameter("@miniowrite", model.miniowrite),
//
new ProcedureParameter("@avgiisrequest", model.avgiisrequest),
//
new ProcedureParameter("@maxiisrequest", model.maxiisrequest),
//
new ProcedureParameter("@miniisrequest", model.miniisrequest),
//上次扫描最大id
new ProcedureParameter("@lastmaxid", model.lastmaxid),
//
new ProcedureParameter("@count", model.count)
};
int rev = PubConn.ExecuteSql(@"insert into tb_performance_dayreport(date,serverid,avgcpu,maxcpu,mincpu,avgmemory,maxmemory,minmemory,avgnetworkupload,maxnetworkupload,minnetworkupload,avgnetworkdownload,maxnetworkdownload,minnetworkdownload,avgioread,maxioread,minioread,avgiowrite,maxiowrite,miniowrite,lastmaxid,lastupdatetime,avgiisrequest,maxiisrequest,miniisrequest,count)
values(getdate(),@serverid,@avgcpu,@maxcpu,@mincpu,@avgmemory,@maxmemory,@minmemory,@avgnetworkupload,@maxnetworkupload,@minnetworkupload,@avgnetworkdownload,@maxnetworkdownload,@minnetworkdownload,@avgioread,@maxioread,@minioread,@avgiowrite,@maxiowrite,@miniowrite,@lastmaxid,getdate(),@avgiisrequest,@maxiisrequest,@miniisrequest,@count)" , Par);
return(rev == 1);
}
public Dictionary <int, tb_performance_dayreport_model> GetAutoTaskList(DbConn PubConn, string day)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("day", day);
string sql = "select * from tb_performance_dayreport with (nolock) where date=@day";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
Dictionary <int, tb_performance_dayreport_model> dic = new Dictionary <int, tb_performance_dayreport_model>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_performance_dayreport_model m = CreateModel(dr);
dic.Add(m.serverid, m);
}
return dic;
}));
}
public int Update(DbConn PubConn, tb_performance_dayreport_model model)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("id", model.id);
ps.Add("serverid", model.serverid);
ps.Add("avgcpu", model.avgcpu);
ps.Add("maxcpu", model.maxcpu);
ps.Add("mincpu", model.mincpu);
ps.Add("avgmemory", model.avgmemory);
ps.Add("maxmemory", model.maxmemory);
ps.Add("minmemory", model.minmemory);
ps.Add("avgnetworkupload", model.avgnetworkupload);
ps.Add("maxnetworkupload", model.maxnetworkupload);
ps.Add("minnetworkupload", model.minnetworkupload);
ps.Add("avgnetworkdownload", model.avgnetworkdownload);
ps.Add("maxnetworkdownload", model.maxnetworkdownload);
ps.Add("minnetworkdownload", model.minnetworkdownload);
ps.Add("avgioread", model.avgioread);
ps.Add("maxioread", model.maxioread);
ps.Add("minioread", model.minioread);
ps.Add("avgiowrite", model.avgiowrite);
ps.Add("maxiowrite", model.maxiowrite);
ps.Add("miniowrite", model.miniowrite);
ps.Add("avgiisrequest", model.avgiisrequest);
ps.Add("maxiisrequest", model.maxiisrequest);
ps.Add("miniisrequest", model.miniisrequest);
ps.Add("lastmaxid", model.lastmaxid);
ps.Add("lastupdatetime", model.lastupdatetime);
ps.Add("count", model.count);
string sql = @"update tb_performance_dayreport set
serverid=@serverid,avgcpu=@avgcpu,maxcpu=@maxcpu,
mincpu=@mincpu,avgmemory=@avgmemory,maxmemory=@maxmemory,
minmemory=@minmemory,avgnetworkupload=@avgnetworkupload,
maxnetworkupload=@maxnetworkupload,minnetworkupload=@minnetworkupload,
avgnetworkdownload=@avgnetworkdownload,maxnetworkdownload=@maxnetworkdownload,
minnetworkdownload=@minnetworkdownload,avgioread=@avgioread,maxioread=@maxioread,
minioread=@minioread,avgiowrite=@avgiowrite,maxiowrite=@maxiowrite,miniowrite=@miniowrite,
lastmaxid=@lastmaxid,lastupdatetime=@lastupdatetime,avgiisrequest=@avgiisrequest,
maxiisrequest=@maxiisrequest,miniisrequest=@miniisrequest,count=@count where id=@id";
int rev = PubConn.ExecuteSql(sql, ps.ToParameters());
return rev;
}));
}
public bool StatisServerMinitor()
{
List <tb_cluster_model> dataBaseList = GetEnableServer();
tb_performance_dayreport_dal dal = new tb_performance_dayreport_dal();
tb_performance_dayreport_model staticDayReport = new tb_performance_dayreport_model();
DateTime dateNow = new DateTime();
string day = "";
string date = "";
string ConnectionCluster = "";
int updateMinite = 0;
using (DbConn ManConn = DbConfig.CreateConn(XXF.Common.XXFConfig.MonitorPlatformConnectionString))
{
ManConn.Open();
ConnectionCluster = new tb_database_config_dal().GetDataBaseSqlConn(ManConn, (int)DataBaseType.Cluster);
dateNow = ManConn.GetServerDate();
day = dateNow.ToString("yyyyMMdd");
date = dateNow.ToString("yyyy-MM-dd");
tb_keyvalue_config_model keymodel = new tb_keyvalue_config_dal().Get(ManConn, "SaveServerMonitorIntervalTime");
updateMinite = Convert.ToInt32(keymodel.value);
}
using (DbConn PubConn = DbConfig.CreateConn(ConnectionCluster))
{
PubConn.Open();
if ((dateNow - LastReserverdServerMonitorTime).TotalMinutes > updateMinite)
{
int i = new tb_cluster_monitorinfo_dal().ReseverdServerMonitor(PubConn);
LastReserverdServerMonitorTime = dateNow;
}
Dictionary <int, tb_performance_dayreport_model> dayReportList = new tb_performance_dayreport_dal().GetAutoTaskList(PubConn, date);
foreach (tb_cluster_model m in dataBaseList)
{
if (dayReportList.Keys.Contains(m.id))
{
tb_performance_dayreport_model oldStaticDayReport = dayReportList[m.id];
staticDayReport = dal.GetStaticMonitor(PubConn, day, oldStaticDayReport.serverid, oldStaticDayReport.lastmaxid);
if (staticDayReport != null)
{
tb_performance_dayreport_model newStaticDayReport = new tb_performance_dayreport_model()
{
id = oldStaticDayReport.id,
serverid = m.id,
avgcpu = (oldStaticDayReport.avgcpu * oldStaticDayReport.count + staticDayReport.avgcpu * staticDayReport.count) / (oldStaticDayReport.count + staticDayReport.count),
maxcpu = oldStaticDayReport.maxcpu > staticDayReport.maxcpu ? oldStaticDayReport.maxcpu : staticDayReport.maxcpu,
mincpu = oldStaticDayReport.mincpu <staticDayReport.mincpu?oldStaticDayReport.mincpu : staticDayReport.mincpu,
avgmemory = (oldStaticDayReport.avgmemory *oldStaticDayReport.count + staticDayReport.avgmemory *staticDayReport.count) / (oldStaticDayReport.count + staticDayReport.count),
maxmemory = oldStaticDayReport.maxmemory> staticDayReport.maxmemory ? oldStaticDayReport.maxmemory : staticDayReport.maxmemory,
minmemory = oldStaticDayReport.minmemory <staticDayReport.minmemory?oldStaticDayReport.minmemory : staticDayReport.minmemory,
avgnetworkupload = (oldStaticDayReport.avgnetworkupload *oldStaticDayReport.count + staticDayReport.avgnetworkupload *staticDayReport.count) / (oldStaticDayReport.count + staticDayReport.count),
maxnetworkupload = oldStaticDayReport.maxnetworkupload> staticDayReport.maxnetworkupload ? oldStaticDayReport.maxnetworkupload : staticDayReport.maxnetworkupload,
minnetworkupload = oldStaticDayReport.minnetworkupload <staticDayReport.minnetworkupload?oldStaticDayReport.minnetworkupload : staticDayReport.minnetworkupload,
avgnetworkdownload = (oldStaticDayReport.avgnetworkdownload *oldStaticDayReport.count + staticDayReport.avgnetworkdownload *staticDayReport.count) / (oldStaticDayReport.count + staticDayReport.count),
maxnetworkdownload = oldStaticDayReport.maxnetworkdownload> staticDayReport.maxnetworkdownload ? oldStaticDayReport.maxnetworkdownload : staticDayReport.maxnetworkdownload,
minnetworkdownload = oldStaticDayReport.minnetworkdownload <staticDayReport.minnetworkdownload?oldStaticDayReport.minnetworkdownload : staticDayReport.minnetworkdownload,
avgioread = (oldStaticDayReport.avgioread *oldStaticDayReport.count + staticDayReport.avgioread *staticDayReport.count) / (oldStaticDayReport.count + staticDayReport.count),
maxioread = oldStaticDayReport.maxioread> staticDayReport.maxioread ? oldStaticDayReport.maxioread : staticDayReport.maxioread,
minioread = oldStaticDayReport.minioread <staticDayReport.minioread?oldStaticDayReport.minioread : staticDayReport.minioread,
avgiowrite = (oldStaticDayReport.avgiowrite *oldStaticDayReport.count + staticDayReport.avgiowrite *staticDayReport.count) / (oldStaticDayReport.count + staticDayReport.count),
maxiowrite = oldStaticDayReport.maxiowrite> staticDayReport.maxiowrite ? oldStaticDayReport.maxiowrite : staticDayReport.maxiowrite,
miniowrite = oldStaticDayReport.miniowrite <staticDayReport.miniowrite?oldStaticDayReport.miniowrite : staticDayReport.miniowrite,
avgiisrequest = (oldStaticDayReport.avgiisrequest *oldStaticDayReport.count + staticDayReport.avgiisrequest *staticDayReport.count) / (oldStaticDayReport.count + staticDayReport.count),
maxiisrequest = oldStaticDayReport.maxiisrequest> staticDayReport.maxiisrequest ? oldStaticDayReport.maxiisrequest : staticDayReport.maxiisrequest,
miniisrequest = oldStaticDayReport.miniisrequest < staticDayReport.miniisrequest ? oldStaticDayReport.miniisrequest : staticDayReport.miniisrequest,
lastmaxid = staticDayReport.lastmaxid,
count = oldStaticDayReport.count + staticDayReport.count,
lastupdatetime = dateNow,
};
dal.Update(PubConn, newStaticDayReport);
}
}
else
{
staticDayReport = dal.GetStaticMonitor(PubConn, day, m.id, 0);
if (staticDayReport == null)
{
staticDayReport = new tb_performance_dayreport_model();
}
staticDayReport.serverid = m.id;
staticDayReport.date = dateNow;
dal.AddStatis(PubConn, staticDayReport);
}
}
return(true);
}
}
public virtual bool Edit(DbConn PubConn, tb_performance_dayreport_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//日期
new ProcedureParameter("@date", model.date),
//服务器id
new ProcedureParameter("@serverid", model.serverid),
//平均cpu
new ProcedureParameter("@avgcpu", model.avgcpu),
//最大cpu
new ProcedureParameter("@maxcpu", model.maxcpu),
//
new ProcedureParameter("@mincpu", model.mincpu),
//
new ProcedureParameter("@avgmemory", model.avgmemory),
//
new ProcedureParameter("@maxmemory", model.maxmemory),
//
new ProcedureParameter("@minmemory", model.minmemory),
//
new ProcedureParameter("@avgnetworkupload", model.avgnetworkupload),
//
new ProcedureParameter("@maxnetworkupload", model.maxnetworkupload),
//
new ProcedureParameter("@minnetworkupload", model.minnetworkupload),
//
new ProcedureParameter("@avgnetworkdownload", model.avgnetworkdownload),
//
new ProcedureParameter("@maxnetworkdownload", model.maxnetworkdownload),
//
new ProcedureParameter("@minnetworkdownload", model.minnetworkdownload),
//
new ProcedureParameter("@avgioread", model.avgioread),
//
new ProcedureParameter("@maxioread", model.maxioread),
//
new ProcedureParameter("@minioread", model.minioread),
//
new ProcedureParameter("@avgiowrite", model.avgiowrite),
//
new ProcedureParameter("@maxiowrite", model.maxiowrite),
//写磁盘
new ProcedureParameter("@miniowrite", model.miniowrite),
//
new ProcedureParameter("@avgiisrequest", model.avgiisrequest),
//
new ProcedureParameter("@maxiisrequest", model.maxiisrequest),
//
new ProcedureParameter("@miniisrequest", model.miniisrequest),
//
new ProcedureParameter("@count", model.count),
//上次扫描最大id
new ProcedureParameter("@lastmaxid", model.lastmaxid),
//上次扫描时间
new ProcedureParameter("@lastupdatetime", model.lastupdatetime)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_performance_dayreport set date=@date,serverid=@serverid,avgcpu=@avgcpu,maxcpu=@maxcpu,mincpu=@mincpu,avgmemory=@avgmemory,maxmemory=@maxmemory,minmemory=@minmemory,avgnetworkupload=@avgnetworkupload,maxnetworkupload=@maxnetworkupload,minnetworkupload=@minnetworkupload,avgnetworkdownload=@avgnetworkdownload,maxnetworkdownload=@maxnetworkdownload,minnetworkdownload=@minnetworkdownload,avgioread=@avgioread,maxioread=@maxioread,minioread=@minioread,avgiowrite=@avgiowrite,maxiowrite=@maxiowrite,miniowrite=@miniowrite,avgiisrequest=@avgiisrequest,maxiisrequest=@maxiisrequest,miniisrequest=@miniisrequest,count=@count,lastmaxid=@lastmaxid,lastupdatetime=@lastupdatetime where id=@id", Par);
return(rev == 1);
}
public virtual tb_performance_dayreport_model CreateModel(DataRow dr)
{
var o = new tb_performance_dayreport_model();
//
if (dr.Table.Columns.Contains("id"))
{
o.id = dr["id"].Toint();
}
//日期
if (dr.Table.Columns.Contains("date"))
{
o.date = dr["date"].ToDateTime();
}
//服务器id
if (dr.Table.Columns.Contains("serverid"))
{
o.serverid = dr["serverid"].Toint();
}
//平均cpu
if (dr.Table.Columns.Contains("avgcpu"))
{
o.avgcpu = dr["avgcpu"].Todecimal();
}
//最大cpu
if (dr.Table.Columns.Contains("maxcpu"))
{
o.maxcpu = dr["maxcpu"].Todecimal();
}
//
if (dr.Table.Columns.Contains("mincpu"))
{
o.mincpu = dr["mincpu"].Todecimal();
}
//
if (dr.Table.Columns.Contains("avgmemory"))
{
o.avgmemory = dr["avgmemory"].Todecimal();
}
//
if (dr.Table.Columns.Contains("maxmemory"))
{
o.maxmemory = dr["maxmemory"].Todecimal();
}
//
if (dr.Table.Columns.Contains("minmemory"))
{
o.minmemory = dr["minmemory"].Todecimal();
}
//
if (dr.Table.Columns.Contains("avgnetworkupload"))
{
o.avgnetworkupload = dr["avgnetworkupload"].Todecimal();
}
//
if (dr.Table.Columns.Contains("maxnetworkupload"))
{
o.maxnetworkupload = dr["maxnetworkupload"].Todecimal();
}
//
if (dr.Table.Columns.Contains("minnetworkupload"))
{
o.minnetworkupload = dr["minnetworkupload"].Todecimal();
}
//
if (dr.Table.Columns.Contains("avgnetworkdownload"))
{
o.avgnetworkdownload = dr["avgnetworkdownload"].Todecimal();
}
//
if (dr.Table.Columns.Contains("maxnetworkdownload"))
{
o.maxnetworkdownload = dr["maxnetworkdownload"].Todecimal();
}
//
if (dr.Table.Columns.Contains("minnetworkdownload"))
{
o.minnetworkdownload = dr["minnetworkdownload"].Todecimal();
}
//
if (dr.Table.Columns.Contains("avgioread"))
{
o.avgioread = dr["avgioread"].Todecimal();
}
//
if (dr.Table.Columns.Contains("maxioread"))
{
o.maxioread = dr["maxioread"].Todecimal();
}
//
if (dr.Table.Columns.Contains("minioread"))
{
o.minioread = dr["minioread"].Todecimal();
}
//
if (dr.Table.Columns.Contains("avgiowrite"))
{
o.avgiowrite = dr["avgiowrite"].Todecimal();
}
//
if (dr.Table.Columns.Contains("maxiowrite"))
{
o.maxiowrite = dr["maxiowrite"].Todecimal();
}
//写磁盘
if (dr.Table.Columns.Contains("miniowrite"))
{
o.miniowrite = dr["miniowrite"].Todecimal();
}
//
if (dr.Table.Columns.Contains("avgiisrequest"))
{
o.avgiisrequest = dr["avgiisrequest"].Todecimal();
}
//
if (dr.Table.Columns.Contains("maxiisrequest"))
{
o.maxiisrequest = dr["maxiisrequest"].Todecimal();
}
//
if (dr.Table.Columns.Contains("miniisrequest"))
{
o.miniisrequest = dr["miniisrequest"].Todecimal();
}
//
if (dr.Table.Columns.Contains("count"))
{
o.count = dr["count"].Toint();
}
//上次扫描最大id
if (dr.Table.Columns.Contains("lastmaxid"))
{
o.lastmaxid = dr["lastmaxid"].Toint();
}
//上次扫描时间
if (dr.Table.Columns.Contains("lastupdatetime"))
{
o.lastupdatetime = dr["lastupdatetime"].ToDateTime();
}
return(o);
}
public List <tb_performance_dayreport_model> GetList(DbConn PubConn, string serverip, string serverid, string timebegin, string timeend, string orderby, string datatype, int pagesize, int pageindex, out int count)
{
int _count = 0;
List <tb_performance_dayreport_model> model = new List <tb_performance_dayreport_model>();
DataSet dsList = SqlHelper.Visit <DataSet>(ps =>
{
string sqlwhere = " where 1=1 ";
if (!string.IsNullOrWhiteSpace(serverip))
{
ps.Add("serverip", serverip);
sqlwhere += " and c.serverip=@serverip ";
}
if (!string.IsNullOrWhiteSpace(serverid))
{
ps.Add("serverid", serverid);
sqlwhere += " and d.serverid=@serverid ";
}
DateTime dtimebegin = DateTime.Now;
if (DateTime.TryParse(timebegin, out dtimebegin))
{
ps.Add("dtimebegin", dtimebegin.ToString("yyyy-MM-dd"));
sqlwhere += " and d.date>=@dtimebegin ";
}
DateTime dtimeend = DateTime.Now;
if (DateTime.TryParse(timeend, out dtimeend))
{
ps.Add("timeend", dtimeend.ToString("yyyy-MM-dd"));
sqlwhere += " and d.date<=@timeend ";
}
string orderbywhere = "";
if (!string.IsNullOrWhiteSpace(orderby))
{
orderbywhere = "d." + datatype + orderby;
}
else
{
orderbywhere = "d.date";
}
StringBuilder sql = new StringBuilder();
sql.Append("select ROW_NUMBER() over(order by " + orderbywhere + " desc) as rownum,d.*,c.serverip,c.servername from tb_performance_dayreport " + "d with (nolock) right join dyd_bs_monitor_platform_manage.dbo.tb_cluster c with (nolock) on d.serverid=c.id ");
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_performance_dayreport " + "d with (nolock) right join dyd_bs_monitor_platform_manage.dbo.tb_cluster c with (nolock) on d.serverid=c.id " + sqlwhere, ps.ToParameters()));
DataSet ds = new DataSet();
string sqlSel = "select * from (" + sql + sqlwhere + ") A where rownum between " + ((pageindex - 1) * pagesize + 1) + " and " + pagesize * pageindex;
PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters());
return(ds);
});
foreach (DataRow dr in dsList.Tables[0].Rows)
{
tb_performance_dayreport_model m = CreateModel(dr);
if (dr.Table.Columns.Contains("serverip"))
{
m.serverip = dr["serverip"].Tostring();
}
if (dr.Table.Columns.Contains("servername"))
{
m.servername = dr["servername"].Tostring();
}
model.Add(m);
}
count = _count;
return(model);
}
