mysql sql开发

来源：互联网发布：淘宝商城曼妮芬内衣编辑：程序博客网时间：2024/06/06 09:40

1.查看不等于'-1d'所有任务的时间偏移量(mja.arguments_elt)情况
select mt.name,mt.url_host,mj.name,mja.arguments_idx,mja.arguments_elt from managed_element mt,me_job mj,me_job_arguments mja where mt.id=mj.managed_element_id and
mj.id=mja.arguments and mja.arguments_idx='timedelta' and mja.arguments_elt<>'-1d' order by mja.arguments_elt ;
name                    url_host        name    arguments_idx   arguments_elt
AAA统计系统             136.160.13.153  AAA05   timedelta       -14d
AAA统计系统补采2        136.160.13.153  AAA06   timedelta       -14d
AAA统计系统补采2        136.160.13.153  AAA04   timedelta       -14d
AAA统计系统             136.160.13.153  AAA01   timedelta       -15d
AAA统计系统             136.160.13.153  AAA03   timedelta       -15d
AAA统计系统             136.160.13.153  AAA29   timedelta       -15d
AAA统计系统补采2        136.160.13.153  AAA02   timedelta       -15d
AAA统计系统补采2        136.160.13.153  AAA28   timedelta       -15d
AAA统计系统补采2        136.160.13.153  AAA30   timedelta       -15d
AAA统计系统补采2        136.160.13.153  AAA26   timedelta       -16d
############################################################################
2.查看每日的备份任务成功数
select count(jh.summary) '任务成功数' from managed_element mt,me_job mj,job_history jh
where mt.id=mj.managed_element_id and mj.id=jh.job_id and jh.summary='successful' and jh.start like concat(date_format(curdate(),'%Y-%m-%d'),'%');
任务成功数
133
############################################################################
3.查看每日的备份任务失败情况
select mt.name,mt.url_host,mj.name,jh.start,jh.finish,jh.bz2size,jh.status,jh.summary from managed_element mt,me_job mj,job_history jh
where mt.id=mj.managed_element_id and mj.id=jh.job_id and (jh.summary<>'successful' or jh.summary is null) and jh.start like concat(date_format(curdate(),'%Y-%m-%d'),'%') order by jh.summary;
name                         url_host        name            start                  finish               bz2size status          summary
长春大区SS计费_1             192.1.1.1       jfbill-CCCC08   2014-03-29 01:20:19     NULL                NULL    processing      NULL
长春大区SS计费_1             192.1.1.1       jfbill-YJCC08   2014-03-29 03:40:18     NULL                NULL    processing      NULL
长春大区SS计费_2             192.1.1.1       jfcenter-NGN    2014-03-29 04:40:17     NULL                NULL    processing      NULL
松原MGW7_系统数据            136.164.13.2    system_data     2014-03-29 05:52:16     2014-03-29 05:52:31 NULL    failed          backup error,please see the worker.log话单   136.160.30.180 in01_bak        2014-03-29 00:52:20     2014-03-29 00:52:25     NULL    successful      no fileschanged

0 0