英文:
Jq trim with regex or position of char
问题
The CloudWatch log format for Aurora PostgreSQL delayed queries can be transformed into the desired HTML table format using the jq command. Here's a jq command that will achieve this:
jq -r '
.events[] | select(.message | contains("duration:")) | .message | capture("^(?<timestamp>[^:]+):(?<clientIP>[^:]+)\\((?<port>[^)]+)\\):(?<userDB>[^@]+)@postgres:\\[(?<pid>[^]]+)\\]:LOG: duration: (?<duration>[^ ]+) ms statement: (?<query>.*)$") | "<tr><td>\(.timestamp)</td><td>\(.clientIP)</td><td>\(.userDB)</td><td>\(.duration)</td><td>\(.query)</td></tr>"' your_input_file.json
Replace your_input_file.json with the actual filename containing your JSON data.
This jq command will parse the JSON data, extract the relevant fields from the "message" field, and format them into an HTML table row (<tr>) with table data (<td>) for each field. It will produce the clean HTML table format you desire.
英文:
The CloudWatch log format for Aurora Postgresql delayed queries is as follows.
{
"nextForwardToken": "f/37657393414703861243420284798371757973629840396172525568/s",
"events": [
{
"ingestionTime": 1688615727267,
"timestamp": 1688615724000,
"message": "2023-07-06 03:55:24 UTC:11.11.11.11(51400):testUser@postgres:[9802]:LOG: duration: 2004.016 ms statement: select pg_sleep(2);"
},
{
"ingestionTime": 1688615925270,
"timestamp": 1688615922000,
"message": "2023-07-06 03:58:42 UTC:111.22.331.41(1230):testService@postgres:[8602]:LOG: duration: 2009.526 ms statement: select pg_sleep(2);"
}
],
"nextBackwardToken": "b/37657388999156311934356902534929058635511305773902397440/s"
}
I want to create html format using jq command.
I want to parse message field and The desired format like this:
| Time(UTC) | clientIP | user@db | Query_time(ms) | Query | |||
|---|---|---|---|---|---|---|---|
| 2023-07-06 03:55:24 | 11.11.11.11 | testUser@postgres | 2004.016 | select pg_sleep(2) | |||
| 2023-07-06 03:58:42 | 111.22.331.41 | testService@postgres | 2009.526 | select pg_sleep(2) |
To create the table, I tried various methods.
<1><br>
I wanted to apply the pattern to startswith and trim, but I couldn't find the material and it didn't apply.
/usr/local/bin/jq -r '
def to_row: "<tr>",.[],"</tr>";
def to_cell: "<td>",.,"</td>";
(
.events[] | select(.message | contains("duration:")) | .message/"\n" | map(
(select(startswith("\(.[0:4])-\(.[4:6])-\(.[6:8]) \(.[8:10]):\(.[10:12]):\(.[12:14])")))
| map(@html | to_cell)
| to_row
)
'
<2><br>
This method seems to be able to split the fields, but not in the desired direction.
.events[] | .message | split(":")
<tr>
<td>
2023-07-06 03
</td>
<td>
55
</td>
<td>
24 UTC
</td>
<td>
11.11.11.11(51400)
</td>
<td>
testUser@postgres
</td>
<td>
[9802]
</td>
<td>
LOG
</td>
<td>
duration
</td>
<td>
2004.016 ms statement
</td>
<td>
select pg_sleep(2);
</td>
</tr>
Is there a way to make it clean in the jq command?
答案1
得分: 1
这里是一种使用正则表达式和 capture 的方法：
.events[].message | capture([
"(?<date>[\\d-]+ [\\d:]+)",
"(?<ip>[\\d.]+)",
"(?<user>[^:]+)",
"", "LOG", "duration",
"(?<time>[\\d.]+)",
"(?<query>.*)"
] | join("[^:]*: *"))
| [.date, .ip, .user, .time, .query | @html "<td>\(.)</td>"]
| "<tr>\(add)</tr>"
<tr><td>2023-07-06 03:55:24</td><td>11.11.11.11</td><td>testUser@postgres</td><td>2004.016</td><td>select pg_sleep(2);</td></tr>
<tr><td>2023-07-06 03:58:42</td><td>111.22.331.41</td><td>testService@postgres</td><td>2009.526</td><td>select pg_sleep(2);</td></tr>
要添加一些简单的美化效果，你可以将最后一行替换为
| "<tr>\n\(map(" \(.)\n") | add)</tr>"
<tr>
<td>2023-07-06 03:55:24</td>
<td>11.11.11.11</td>
<td>testUser@postgres</td>
<td>2004.016</td>
<td>select pg_sleep(2);</td>
</tr>
<tr>
<td>2023-07-06 03:58:42</td>
<td>111.22.331.41</td>
<td>testService@postgres</td>
<td>2009.526</td>
<td>select pg_sleep(2);</td>
</tr>
英文:
Here's one way using regular expressions with capture:
.events[].message | capture([
"(?<date>[\\d-]+ [\\d:]+)",
"(?<ip>[\\d.]+)",
"(?<user>[^:]+)",
"", "LOG", "duration",
"(?<time>[\\d.]+)",
"(?<query>.*)"
] | join("[^:]*: *"))
| [.date, .ip, .user, .time, .query | @html "<td>\(.)</td>"]
| "<tr>\(add)</tr>"
<tr><td>2023-07-06 03:55:24</td><td>11.11.11.11</td><td>testUser@postgres</td><td>2004.016</td><td>select pg_sleep(2);</td></tr>
<tr><td>2023-07-06 03:58:42</td><td>111.22.331.41</td><td>testService@postgres</td><td>2009.526</td><td>select pg_sleep(2);</td></tr>
To add some simple pretty-printing, you could replace the last line with
| "<tr>\n\(map(" \(.)\n") | add)</tr>"
<tr>
<td>2023-07-06 03:55:24</td>
<td>11.11.11.11</td>
<td>testUser@postgres</td>
<td>2004.016</td>
<td>select pg_sleep(2);</td>
</tr>
<tr>
<td>2023-07-06 03:58:42</td>
<td>111.22.331.41</td>
<td>testService@postgres</td>
<td>2009.526</td>
<td>select pg_sleep(2);</td>
</tr>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论