示例sql:
# User@Host: zjzc_app[zjzc_app] @ [10.22.18.164] Id: 6069153
# Query_time: 153.908486 Lock_time: 0.000113 Rows_sent: 27 Rows_examined: 430083120
SET timestamp=1472662954;
SELECT t.* FROM
(
SELECT
t1.sn AS clientSn,
t1.userNick,
t1.mobilePhone,
t3.personName,
t2.availableBalance,
(SELECT IFNULL(SUM(amount) , 0) FROM ClientRechargeOrder t WHERE t.clientSn= t1.sn AND t.status ='2') AS rechargeAmount,
(SELECT IFNULL(SUM(amount) , 0) FROM ClientWithDrawOrder t WHERE t.clientSn= t1.sn AND t.status IN ('1','2','3','4') ) AS withdrawAmount,
( (SELECT IFNULL(SUM(capitalBalance) , 0) FROM ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '1')
+
(SELECT IFNULL(SUM(capitalBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '1')
) AS investAmount,
( (SELECT IFNULL(SUM(yieldBalance) , 0) FROM ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '2')
+
(SELECT IFNULL(SUM(yieldBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '2')
) AS yieldAmount,
(SELECT IFNULL(SUM(t0.amount) , 0) FROM ClientCoupon t,Coupon t0 WHERE t.clientSn= t1.sn AND t.status = '2' AND t.couponSn = t0.sn AND t0.type IN (1,2)) AS cashCouponAmount
FROM Client t1 , ClientAssetInfo t2 , ClientPersonalInfo t999
WHERE t1.sn = t2.clientSn AND t1.sn = t3.clientSn
) t WHERE (t.rechargeAmount + t.yieldAmount + t.cashCouponAmount - t.withdrawAmount - t.investAmount - t.availableBalance) != 0;
grok 表达式:
\s*# User@Host:\s+\S+\[%{USER:user}\]\s+@\s+\[%{IP:clientip}\]\s+(?<id>(\S+\s+)*\S+)\s*#\s+Query_time:\s+%{NUMBER:Query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s
+Rows_examined: %{NUMBER:rows_examined}\s*
\s*SET\s+timestamp=%{NUMBER:timestamp};\s*(?<query>(\s*\S+\s*).*)\s*
输出:
{
"user": [
[
"zjzc_app"
]
],
"clientip": [
[
"10.252.148.164"
]
],
"id": [
[
"Id: 6069153"
]
],
"Query_time": [
[
"153.908486"
]
],
"lock_time": [
[
"0.000113"
]
],
"rows_sent": [
[
"27"
]
],
"rows_examined": [
[
"430083120"
]
],
"timestamp": [
[
"1472662954"
]
],
"query": [
[
"SELECT t.* FROM"
]
]
}
此时没有在开头加上(?m)
/******************************************
(?m)\s*# User@Host:\s+\S+\[%{USER:user}\]\s+@\s+\[%{IP:clientip}\]\s+(?<id>(\S+\s+)*\S+)\s*#\s+Query_time:\s+%{NUMBER:Query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent:
%{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\s*
\s*SET\s+timestamp=%{NUMBER:timestamp};\s*(?<query>(\s*\S+\s*).*)\s*
{
"user": [
[
"zjzc_app"
]
],
"clientip": [
[
"10.252.148.164"
]
],
"id": [
[
"Id: 6069153"
]
],
"Query_time": [
[
"153.908486"
]
],
"lock_time": [
[
"0.000113"
]
],
"rows_sent": [
[
"27"
]
],
"rows_examined": [
[
"430083120"
]
],
"timestamp": [
[
"1472662954"
]
],
"query": [
[
"SELECT t.* FROM\n \t (\n \t\t\tSELECT \n \t\t\tt1.sn AS clientSn,\n \t\t\tt1.userNick,\n \t\t\tt1.mobilePhone,\n \t\t\tt3.personName,\n \t\t\tt2.availableBalance,\n \t\t\t(SELECT IFNULL
(SUM(amount) , 0) FROM ClientRechargeOrder t WHERE t.clientSn= t1.sn AND t.status ='2') AS rechargeAmount,\n \t\t\t(SELECT IFNULL(SUM(amount) , 0) FROM ClientWithDrawOrder t WHERE t.clientSn= t1.sn
AND t.status IN ('1','2','3','4') ) AS withdrawAmount,\n \t\t\t( (SELECT IFNULL(SUM(capitalBalance) , 0) FROM ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '1') \n \t\t\t + \n \t\t\t
(SELECT IFNULL(SUM(capitalBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '1') \n \t\t\t) AS investAmount,\n \t\t\t( (SELECT IFNULL(SUM(yieldBalance) , 0) FROM
ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '2') \n \t\t\t + \n \t\t\t (SELECT IFNULL(SUM(yieldBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '2') \n
\t\t\t) AS yieldAmount,\n \t\t\t(SELECT IFNULL(SUM(t0.amount) , 0) FROM ClientCoupon t,Coupon t0 WHERE t.clientSn= t1.sn AND t.status = '2' AND t.couponSn = t0.sn AND t0.type IN (1,2)) AS
cashCouponAmount\n \t\t\tFROM Client t1 , ClientAssetInfo t2 , ClientPersonalInfo t999\n \t\t\tWHERE t1.sn = t2.clientSn AND t1.sn = t3.clientSn\n \t ) t WHERE (t.rechargeAmount +
t.yieldAmount + t.cashCouponAmount - t.withdrawAmount - t.investAmount - t.availableBalance) != 0;\n"
]
]
}