前提
- 我采用的是XXL-JOB+JAVA语言扫库+http调用短信接口
并不是必须跟我一样，只要满足能扫库+能通知就行
代码
- 修改jdbcUrl
- 修改driverClassName
- 修改userName
- 修改password
- 修改smsAlertUrl，以及调整发通知的方式
package com.xxl.job.executor.service.jobhandler;
import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.toolkit.MapUtils;
import com.baomidou.mybatisplus.toolkit.StringEscape;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.collect.Lists;
import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.handler.IJobHandler;
import com.xxl.job.core.log.XxlJobLogger;
import org.springframework.http.*;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StopWatch;
import org.springframework.web.client.RestTemplate;
import java.io.Serializable;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* @author hjp
* @since 2022年6月9日 11:37:41
* kettle执行监控
*/
public class KettleExecuteMonitorJobHandler extends IJobHandler {
public static final ReturnT<String> SUCCESS = new ReturnT<>(null);
public static final ReturnT<String> FAIL = new ReturnT<>(ReturnT.FAIL_CODE, null);
private static final String jdbcUrl = "xxxJdbcUrl";
private static final String driverClassName = "xxxDriver";
private static final String userName = "xxxUserName";
private static final String password = "xxxPassword";
private DruidDataSource dataSource = null;
private JdbcTemplate jdbcTemplate = null;
private int initialSize = 1;
private int minIdle = 1;
private int maxActive = 1;
private int queryTimeout = 3;// seconds
private int maxWait = 12 * 1000;
private Long timeBetweenEvictionRunsMillis = 60 * 1000L;
private Long minEvictableIdleTimeMillis = 5 * 60 * 1000L;
private Boolean useUnfairLock = true;
private Boolean poolPreparedStatements = true;
private Integer maxOpenPreparedStatements = 200;
private String validationQuery = "select 1 from dual;";
private Boolean testOnBorrow = false;
private Boolean testOnReturn = false;
private Boolean testWhileIdle = true;
private String filters = "wall";
private Boolean logAbandoned = true;
//查找错误的步骤日志
private static final String stepQuerySql = "select t.TRANSNAME,t.STEPNAME,t.CHANNEL_ID,to_char(t.log_date,'yyyy-MM-dd HH24:mi:ss') as LOGDATE from t_kettle_step_log t where t.errors >?";
//查询作业通道日志（包含步骤），用作跳板，二次查询转换日志、作业日志
private static final String jobChannelQuerySql = "select t.ROOT_CHANNEL_ID,t.PARENT_CHANNEL_ID from t_kettle_job_channel_log t where t.channel_id=?";
//查询转换日志
private static final String transformationQuerySql = "select t.LOG_FIELD from t_kettle_transformation_log t where t.channel_id=?";
//查询作业日志
private static final String jobQuerySql = "select t.JOBNAME,t.START_JOB_ENTRY,t.EXECUTING_SERVER,t.EXECUTING_USER,t.CLIENT from t_kettle_job_log t where t.channel_id=?";
private static final String smsAlertUrl = "xxxSmsAlertUrl";
private final RestTemplate restTemplate = new RestTemplate();
private final ObjectMapper jackson = new ObjectMapper();
private StopWatch stopWatch = null;
@Override
public ReturnT<String> execute(String param) throws Exception {
List<NoticeEntity> errorLogList = null;
try {
stopWatch = new StopWatch(String.format("%n----------------开始扫描错误日志"));
dataSource = buildDbSource();
jdbcTemplate = new JdbcTemplate(dataSource);
//1、获取存在错误的步骤日志
List<Map<String, Object>> errorStepList =
jdbcTemplate.queryForList(stepQuerySql, 0);
if (CollectionUtils.isEmpty(errorStepList)) {
stopWatch.start("无报错的作业或转换");
stopWatch.stop();
return SUCCESS;
}
errorLogList = Lists.newArrayList();
for (Map<String, Object> errorStep : errorStepList) {
NoticeEntity noticeEntity = NoticeEntity.builder();
String stepChannelId = (String) errorStep.get("CHANNEL_ID");
Map<String, Object> jobChannelLogMap = jdbcTemplate.queryForMap(jobChannelQuerySql, stepChannelId);
//判断是不是通过作业or转换执行；转换执行的话，是无法获取到详细日志，只能知道是哪个转换报错
boolean isJobExecute = false;//是否通过作业执行
if (MapUtils.isNotEmpty(jobChannelLogMap)) {
isJobExecute = true;
/*
* 转换日志信息
*/
String transformationChannelId = (String) jobChannelLogMap.get("PARENT_CHANNEL_ID");
Map<String, Object> transformationLogMap = jdbcTemplate.queryForMap(transformationQuerySql, transformationChannelId);
/*
* 作业日志信息
*/
String jobChannelId = (String) jobChannelLogMap.get("ROOT_CHANNEL_ID");
Map<String, Object> jobLogMap = jdbcTemplate.queryForMap(jobQuerySql, jobChannelId);
noticeEntity
.通过作业或转换执行("作业")
.所属作业((String) jobLogMap.get("JOBNAME"))
.所属转换((String) errorStep.get("TRANSNAME"))
.所属步骤((String) errorStep.get("STEPNAME"))
.作业面板的起始步骤((String) jobLogMap.get("START_JOB_ENTRY"))
.所属步骤的日志记录时间((String) errorStep.get("LOGDATE"))
.日志详细内容(transformationLogMap.get("LOG_FIELD"))
.主机名((String) jobLogMap.get("EXECUTING_SERVER"))
.用户((String) jobLogMap.get("EXECUTING_USER"))
.客户端方式((String) jobLogMap.get("CLIENT"))
.作业的ID(jobChannelId)
.转换的ID(transformationChannelId);
} else {
noticeEntity
.通过作业或转换执行("转换")
.所属转换((String) errorStep.get("TRANSNAME"))
.所属步骤((String) errorStep.get("STEPNAME"))
.所属步骤的日志记录时间((String) errorStep.get("LOGDATE"));
}
stopWatch.start(String.format("%n----------------异常转换步骤日志：%s", noticeEntity.build()));
stopWatch.stop();
/*
* 发送告警信息
*/
//因为短信长度有限制、过长的异常信息影响视觉效果、http json对格式严格，因此将日志详细信息去除
noticeEntity.日志详细内容(null);
String alertMessageBody = String.format("{\"msg\":\"【kettle报错】%s\"}"
, StringEscape.escapeString(noticeEntity.toString()));
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_JSON_UTF8);
HttpEntity<String> httpEntity = new HttpEntity<>(alertMessageBody, headers);
ResponseEntity<String> responseEntity = restTemplate.exchange(smsAlertUrl,
HttpMethod.POST, httpEntity, String.class);
}
return SUCCESS;
} catch (Exception e) {
e.printStackTrace();
ReturnT<String> returnT = FAIL;
returnT.setMsg("异常==" + e);
return returnT;
} finally {
if (dataSource != null) {
dataSource.close();
}
if (stopWatch.isRunning()) {
stopWatch.stop();
}
XxlJobLogger.log(stopWatch.prettyPrint());
}
}
private DruidDataSource buildDbSource() throws SQLException {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(driverClassName);
druidDataSource.setUrl(jdbcUrl);
druidDataSource.setUsername(userName);
druidDataSource.setPassword(password);
druidDataSource.setInitialSize(initialSize);
druidDataSource.setMinIdle(minIdle);
druidDataSource.setMaxActive(maxActive);
druidDataSource.setQueryTimeout(queryTimeout);// 请求超时时间
druidDataSource.setMaxWait(maxWait);// 连接最大等待时间
druidDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); // 检测空闲连接时间间隔
druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); // 线程最小生存时间
druidDataSource.setUseUnfairLock(useUnfairLock); // 使用非公平锁
druidDataSource.setPoolPreparedStatements(poolPreparedStatements);
druidDataSource.setMaxOpenPreparedStatements(maxOpenPreparedStatements);// 缓存preparedStatement
druidDataSource.setValidationQuery(validationQuery);
druidDataSource.setTestOnBorrow(testOnBorrow);
druidDataSource.setTestOnReturn(testOnReturn);
druidDataSource.setTestWhileIdle(testWhileIdle);
druidDataSource.setFilters(filters); // sql注入防御插件
druidDataSource.setLogAbandoned(logAbandoned);// 将当前关闭动作记录到日志
return druidDataSource;
}
static class NoticeEntity implements Serializable {
private String 通过作业或转换执行;
private String 所属作业;
private String 所属转换;
private String 所属步骤;
private String 作业面板的起始步骤;
private String 所属步骤的日志记录时间;
private Object 日志详细内容;
private String 主机名;
private String 用户;
private String 客户端方式;
private String 作业的ID;
private String 转换的ID;
public static NoticeEntity builder() {
return new NoticeEntity();
}
public NoticeEntity build() {
return this;
}
public String get通过作业或转换执行() {
return 通过作业或转换执行;
}
public NoticeEntity 通过作业或转换执行(String 通过作业或转换执行) {
this.通过作业或转换执行 = 通过作业或转换执行;
return this;
}
public String get所属作业() {
return 所属作业;
}
public NoticeEntity 所属作业(String 所属作业) {
this.所属作业 = 所属作业;
return this;
}
public String get所属转换() {
return 所属转换;
}
public NoticeEntity 所属转换(String 所属转换) {
this.所属转换 = 所属转换;
return this;
}
public String get所属步骤() {
return 所属步骤;
}
public NoticeEntity 所属步骤(String 所属步骤) {
this.所属步骤 = 所属步骤;
return this;
}
public String get作业面板的起始步骤() {
return 作业面板的起始步骤;
}
public NoticeEntity 作业面板的起始步骤(String 作业面板的起始步骤) {
this.作业面板的起始步骤 = 作业面板的起始步骤;
return this;
}
public String get所属步骤的日志记录时间() {
return 所属步骤的日志记录时间;
}
public NoticeEntity 所属步骤的日志记录时间(String 所属步骤的日志记录时间) {
this.所属步骤的日志记录时间 = 所属步骤的日志记录时间;
return this;
}
public Object get日志详细内容() {
return 日志详细内容;
}
public NoticeEntity 日志详细内容(Object 日志详细内容) {
this.日志详细内容 = 日志详细内容;
return this;
}
public String get主机名() {
return 主机名;
}
public NoticeEntity 主机名(String 主机名) {
this.主机名 = 主机名;
return this;
}
public String get用户() {
return 用户;
}
public NoticeEntity 用户(String 用户) {
this.用户 = 用户;
return this;
}
public String get客户端方式() {
return 客户端方式;
}
public NoticeEntity 客户端方式(String 客户端方式) {
this.客户端方式 = 客户端方式;
return this;
}
public String get作业的ID() {
return 作业的ID;
}
public NoticeEntity 作业的ID(String 作业的ID) {
this.作业的ID = 作业的ID;
return this;
}
public String get转换的ID() {
return 转换的ID;
}
public NoticeEntity 转换的ID(String 转换的ID) {
this.转换的ID = 转换的ID;
return this;
}
@Override
public String toString() {
return "" +
"通过作业或转换执行=" + 通过作业或转换执行 +
", 所属作业=" + 所属作业 +
", 所属转换=" + 所属转换 +
", 所属步骤=" + 所属步骤 +
", 作业面板的起始步骤=" + 作业面板的起始步骤 +
", 所属步骤的日志记录时间=" + 所属步骤的日志记录时间 +
", 日志详细内容=" + 日志详细内容 +
", 主机名=" + 主机名 +
", 用户=" + 用户 +
", 客户端方式=" + 客户端方式 +
", 作业的ID=" + 作业的ID +
", 转换的ID=" + 转换的ID;
}
}
}
配置定时任务
- 采用XXL-JOB的GLUE(Java)方式
- 编辑代码
报警内容
因为日志内容包含特殊字符，http无法接收，短信对内容长度也有限制，因此详细内容要去到XXL-JOB的调度日志查看
日志表查询和关联关系
- 表
select t.*,rowid from t_kettle_job_log t;--作业日志
select t.*,rowid from t_kettle_job_channel_log t;--作业日志通道
select t.*,rowid from t_kettle_transformation_log t;--转换日志
select t.*,rowid from t_kettle_step_log t;--转换步骤日志
- 关联关系
-
查询是否有异常的步骤
select t.TRANSNAME,t.STEPNAME,t.CHANNEL_ID,to_char(t.log_date,'yyyy-MM-dd HH24:mi:ss') as LOGDATE from t_kettle_step_log t where t.errors >0;
-
跳板：查询出转换日志id和作业日志id
select t.ROOT_CHANNEL_ID,t.PARENT_CHANNEL_ID from t_kettle_job_channel_log t where t.channel_id=?;
-
查询详细报错日志
select t.LOG_FIELD from t_kettle_transformation_log t where t.channel_id=?;
-
查询全局性信息
比如日志、所属主机、用户、客户端方式
拿到ROOT_CHANNEL_ID字段的值，导入查询
select t.JOBNAME,t.START_JOB_ENTRY,t.EXECUTING_SERVER,t.EXECUTING_USER,t.CLIENT from t_kettle_job_log t where t.channel_id=?;
扫一扫
988
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
