监控数据提取SQL

查询数据的连接方式连接方式：mysql的方式连接名：自定义主机名及IP地址：端口：3306用户名：PerfQueryPassword：

P@ssword1.CPU报警次数set@startdate='2014-11-1000:00:01',@enddate='2014-11-1623:59:59';SELECTca.value_dataname,cca.value_dataip,cct.`name`mon_res_type_name,mra.alert_time,mra.alert_level,mra.alert_msg,mrat.display_name,mrtt.display_namemon_res_displayname,count(cca.value_data)frommon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_ciccONcc.id=mro.owner_idleftJOINcmdb_ci_attrcaONca.owner_id=cc.idANDca.attr_type=0leftJOINcmdb_ci_attrccaONcca.owner_id=cc.idANDcca.attr_type=1148--IP--leftJOINcmdb_ci_attrcccaONccca.owner_id=cc.idANDccca.attr_type=11111--用途--leftJOINcmdb_ci_attrccccaONcccca.owner_id=cc.idANDcccca.attr_type=11111--mysql系统编码leftjoincmdb_ci_typecctoncct.id=cc.ci_typeWHEREmra.alert_level='critical'andcct.`name`in('Windows','Linux','AIX','Solaris','ESX','HPUX')ANDmra.alert_time>=@startdateANDmra.alert_time<@enddateandmrtt.display_namelike'%CPU%'groupbycca.value_data,mon_res_displaynameorderbycount(cca.value_data)desc,mrtt.display_namedesc2.内存报警次数set@startdate='2014-11-1000:00:01',@enddate='2014-11-1623:59:59';SELECTca.value_dataname,cca.value_dataip,cct.`name`mon_res_type_name,mra.alert_time,mra.alert_level,mra.alert_msg,mrat.display_name,mrtt.display_namemon_res_displayname,count(cca.value_data)frommon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_ciccONcc.id=mro.owner_idleftJOINcmdb_ci_attrcaONca.owner_id=cc.idANDca.attr_type=0leftJOINcmdb_ci_attrccaONcca.owner_id=cc.idANDcca.attr_type=1148--IP--leftJOINcmdb_ci_attrcccaONccca.owner_id=cc.idANDccca.attr_type=11111--用途--leftJOINcmdb_ci_attrccccaONcccca.owner_id=cc.idANDcccca.attr_type=11111--mysql系统编码leftjoincmdb_ci_typecctoncct.id=cc.ci_typeWHEREmra.alert_level='critical'andcct.`name`in('Windows','Linux','AIX','Solaris','ESX','HPUX')ANDmra.alert_time>=@startdateANDmra.alert_time<@enddateandmrtt.display_namelike'%内存%'groupbycca.value_data,mon_res_displaynameorderbycount(cca.value_data)desc,mrtt.display_namedesc3.IO报警次数set@startdate='2014-11-1000:00:01',@enddate='2014-11-1623:59:59';SELECTca.value_dataname,cca.value_dataip,cct.`name`mon_res_type_name,mra.alert_time,mra.alert_level,mra.alert_msg,mrat.display_name,mrtt.display_namemon_res_displayname,count(cca.value_data)frommon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_ciccONcc.id=mro.owner_idleftJOINcmdb_ci_attrcaONca.owner_id=cc.idANDca.attr_type=0leftJOINcmdb_ci_attrccaONcca.owner_id=cc.idANDcca.attr_type=1148--IP--leftJOINcmdb_ci_attrcccaONccca.owner_id=cc.idANDccca.attr_type=11111--用途--leftJOINcmdb_ci_attrccccaONcccca.owner_id=cc.idANDcccca.attr_type=11111--mysql系统编码leftjoincmdb_ci_typecctoncct.id=cc.ci_typeWHEREmra.alert_level='critical'andcct.`name`in('Windows','Linux','AIX','Solaris','ESX','HPUX')ANDmra.alert_time>=@startdateANDmra.alert_time<@enddateandmrtt.display_namelike'%磁盘IO%'groupbycca.value_data,mon_res_displaynameorderbycount(cca.value_data)desc,mrtt.display_namedesc4.数据库报警次数set@startdate='2014-10-2000:00:01',@enddate='2014-11-0223:59:59';SELECTca.value_data,cct.`name`mon_res_type_name,mra.alert_time,mra.alert_level,mra.alert_msg,mrat.display_name,mrtt.display_namemon_res_displayname,cca.value_dataxiewuxian,ccca.value_datayouxianji,cccca.value_datasyscodefrommon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_ciccONcc.id=mro.owner_idleftJOINcmdb_ci_attrcaONca.owner_id=cc.idANDca.attr_type=0leftJOINcmdb_ci_attrccaONcca.owner_id=cc.idANDcca.attr_type=1185--oracle业务线leftJOINcmdb_ci_attrcccaONccca.owner_id=cc.idANDccca.attr_type=1182--oracle优先级leftJOINcmdb_ci_attrccccaONcccca.owner_id=cc.idANDcccca.attr_type=1186--oracle系统编码leftjoincmdb_ci_typecctoncct.id=cc.ci_typeWHEREmra.alert_level='critical'andcct.`name`='OracleDB'ANDmra.alert_time>=@startdateANDmra.alert_time<@enddateUNIONSELECTca.value_data,cct.`name`mon_res_type_name,mra.alert_time,mra.alert_level,mra.alert_msg,mrat.display_name,mrtt.display_namemon_res_displayname,cca.value_dataxiewuxian,ccca.value_datayouxianji,cccca.value_datasyscodefrommon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_ciccONcc.id=mro.owner_idleftJOINcmdb_ci_attrcaONca.owner_id=cc.idANDca.attr_type=0leftJOINcmdb_ci_attrccaONcca.owner_id=cc.idANDcca.attr_type=1238--mysql业务线leftJOINcmdb_ci_attrcccaONccca.owner_id=cc.idANDccca.attr_type=1235--mysql优先级leftJOINcmdb_ci_attrccccaONcccca.owner_id=cc.idANDcccca.attr_type=1239--mysql系统编码leftjoincmdb_ci_typecctoncct.id=cc.ci_typeWHEREmra.alert_level='critical'andcct.`name`='MysqlDB'ANDmra.alert_time>=@startdateANDmra.alert_time<@enddate5.业务系统监控模板selectc.*,d.`name`from(SELECTtd.id,a.value_type,a.display_name,td.attr_id,a.res_type,IFNULL(td.threshold,a.threshold)ASthresholdFROMmon_res_template_deftd,mon_res_attraWHEREtd.template_idin(selectidfrommon_res_templatewhereplugin_idin(156,154,158))ANDa.id=td.attr_idAND(td.thresholdISNOTNULLORa.thresholdISNOTNULL))c,mon_res_typedwherec.res_type=d.id6.应用报警次数统计set@startdate='2014-11-1000:00:01',@enddate='2014-11-1623:59:59';SELECT ca.value_data,cct.`name`mon_res_type_name, mra.alert_time, mra.alert_level, mra.alert_msg, mrat.display_name, mrtt.display_namemon_res_displayname, cca.value_dataxiewuxian,ccca.value_datayouxianji,cccca.value_datasyscode,count(cccca.value_data)from mon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_ciccONcc.id=mro.owner_idleftJOINcmdb_ci_attrcaONca.owner_id=cc.idANDca.attr_type=0leftJOINcmdb_ci_attrccaONcca.owner_id=cc.idANDcca.attr_type=1161--mysql业务线leftJOINcmdb_ci_attrcccaONccca.owner_id=cc.idANDccca.attr_type=1163--mysql优先级leftJOINcmdb_ci_attrccccaONcccca.owner_id=cc.idANDcccca.attr_type=1165--mysql系统编码leftjoincmdb_ci_typecctoncct.id=cc.ci_typeWHERE mra.alert_level='critical'andcct.`name`in('weblogic8','JBoss','WebSphereMQ6','WAS')ANDmra.alert_time>=@startdateANDmra.alert_time<@enddategroupbycccca.value_data7．业务监控报警次数统计set@startdate='2014-11-1000:00:01',@enddate='2014-11-1623:59:59';SELECT ca.value_data,cct.`name`mon_res_type_name, mra.alert_time, mra.alert_level, mra.alert_msg, mrat.display_name, mrtt.display_namemon_res_displayname, ccda.value_dataxiewuxian,cccda.value_datayouxianji,cccca.value_datasyscode,count(ca.value_data)from mon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_ciccONcc.id=mro.owner_idleftJOINcmdb_ci_attrcaONca.owner_id=cc.idANDca.attr_type=0leftJOINcmdb_ci_attrccaONcca.owner_id=cc.idANDcca.attr_type=1297--mysql业务线leftjoincmdb_ci_attrccdaonccda.owner_id=cca.value_dataandccda.attr_type=0leftJOINcmdb_ci_attrcccaONccca.owner_id=cc.idANDccca.attr_type=1296--mysql优先级leftjoincmdb_ci_attrcccdaoncccda.owner_id=ccca.value_dataandcccda.attr_type=0leftJOINcmdb_ci_attrccccaONcccca.owner_id=cc.idANDcccca.attr_type=1320--mysql系统编码leftjoincmdb_ci_typecctoncct.id=cc.ci_typeWHERE mra.alert_level='critical'andcct.`name`='业务系统'ANDmra.alert_time>=@startdateANDmra.alert_time<@enddategroupbyca.value_data8.历史报警SQL集合set@startdate='2014-09-2300:00:01',@enddate='2014-09-3023:59:59';SELECT ca.value_data,cct.`name`mon_res_type_name, mra.alert_time, mra.alert_level, mra.alert_msg, mrat.display_name, mrtt.display_namemon_res_displayname, cca.value_dataxiewuxian,ccca.value_datayouxianji,cccca.value_datasyscodefrom mon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_ciccONcc.id=mro.owner_idleftJOINcmdb_ci_attrcaONca.owner_id=cc.idANDca.attr_type=0leftJOINcmdb_ci_attrccaONcca.owner_id=cc.idANDcca.attr_type=1185--oracle业务线leftJOINcmdb_ci_attrcccaONccca.owner_id=cc.idANDccca.attr_type=1182--oracle优先级leftJOINcmdb_ci_attrccccaONcccca.owner_id=cc.idANDcccca.attr_type=1186--oracle系统编码leftjoincmdb_ci_typecctoncct.id=cc.ci_typeWHERE mra.alert_level='critical'andcct.`name`='OracleDB'ANDmra.alert_time>=@startdateANDmra.alert_time<@enddateUNIONSELECT ca.value_data,cct.`name`mon_res_type_name, mra.alert_time, mra.alert_level, mra.alert_msg, mrat.display_name, mrtt.display_namemon_res_displayname, cca.value_dataxiewuxian,ccca.value_datayouxianji,cccca.value_datasyscodefrom mon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_ciccONcc.id=mro.owner_idleftJOINcmdb_ci_attrcaONca.owner_id=cc.idANDca.attr_type=0leftJOINcmdb_ci_attrccaONcca.owner_id=cc.idANDcca.attr_type=1238--mysql业务线leftJOINcmdb_ci_attrcccaONccca.owner_id=cc.idANDccca.attr_type=1235--mysql优先级leftJOINcmdb_ci_attrccccaONcccca.owner_id=cc.idANDcccca.attr_type=1239--mysql系统编码leftjoincmdb_ci_typecctoncct.id=cc.ci_typeWHERE mra.alert_level='critical'andcct.`name`='MysqlDB'ANDmra.alert_time>=@startdateANDmra.alert_time<@enddateUNIONSELECT ca.value_data,cct.`name`mon_res_type_name, mra.alert_time, mra.alert_level, mra.alert_msg, mrat.display_name, mrtt.display_namemon_res_displayname, cca.value_dataxiewuxian,ccca.value_datayouxianji,cccca.value_datasyscodefrom mon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_ciccONcc.id=mro.owner_idleftJOINcmdb_ci_attrcaONca.owner_id=cc.idANDca.attr_type=0leftJOINcmdb_ci_attrccaONcca.owner_id=cc.idANDcca.attr_type=1161--mysql业务线leftJOINcmdb_ci_attrcccaONccca.owner_id=cc.idANDccca.attr_type=1163--mysql优先级leftJOINcmdb_ci_attrccccaONcccca.owner_id=cc.idANDcccca.attr_type=1165--mysql系统编码leftjoincmdb_ci_typecctoncct.id=cc.ci_typeWHERE mra.alert_level='critical'andcct.`name`in('weblogic8','JBoss','WebSphereMQ6','WAS','haproxy','nginx','Tomcat')ANDmra.alert_time>=@startdateANDmra.alert_time<@enddateUNIONSELECT ca.value_data,cct.`name`mon_res_type_name, mra.alert_time, mra.alert_level, mra.alert_msg, mrat.display_name, mrtt.display_namemon_res_displayname, ccda.value_dataxiewuxian,cccda.value_datayouxianji,cccca.value_datasyscodefrom mon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_ciccONcc.id=mro.owner_idleftJOINcmdb_ci_attrcaONca.owner_id=cc.idANDca.attr_type=0leftJOINcmdb_ci_attrccaONcca.owner_id=cc.idANDcca.attr_type=1297--mysql业务线leftjoincmdb_ci_attrccdaonccda.owner_id=cca.value_dataandccda.attr_type=0leftJOINcmdb_ci_attrcccaONccca.owner_id=cc.idANDccca.attr_type=1296--mysql优先级leftjoincmdb_ci_attrcccdaoncccda.owner_id=ccca.value_dataandcccda.attr_type=0leftJOINcmdb_ci_attrccccaONcccca.owner_id=cc.idANDcccca.attr_type=1320--mysql系统编码leftjoincmdb_ci_typecctoncct.id=cc.ci_typeWHERE mra.alert_level='critical'andcct.`name`='业务系统'ANDmra.alert_time>=@startdateANDmra.alert_time<@enddateUNIONSELECT ca.value_data,cct.`name`mon_res_type_name, mra.alert_time, mra.alert_level, mra.alert_msg, mrat.display_name, mrtt.display_namemon_res_displayname, cca.value_dataxiewuxian,ccca.value_datayouxianji,cccca.value_datasyscodefrom mon_res_alert_hismraleftJOINmon_res_metricmrmONmra.metric_id=mrm.idleftJOINmon_res_attrmratONmrat.id=mrm.attr_idleftJOINmon_res_typemrttONmrtt.id=mrat.res_typeleftJOINmon_res_objectmroONmrm.object_id=mro.idleftJOINcmdb_c