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