Here is postgresql slow query log:
2015-12-14 14:36:26 CST [29680-1] aircare_root@aircare LOG: duration: 828.438 ms execute <unnamed>:
select ic.sourceid
, e.id equipmentId
, e.esn equipmentEsn
, e.alias equipmentAlias
, ic.source_label sourceLabel
, ic.cn_content cnContent
, ic.en_content enContent
, ic.date_created dateCreated
, ic.operator_id operatorId
, comm_user.username operatorUsername
, comm_user.nickname operatorNickName
from moni_inbox
inner join moni_info_center ic on ic.id = moni_inbox.info_center_id
left join moni_equipment e on ic.equipment_id = e.id
left join comm_user on comm_user.id=ic.operator_id
where moni_inbox.accepter_id= $1
and moni_inbox.has_read = $2
and ic.source_label = $3
and (e.type='compressor' or e.id is null)
order by ic.id desc
limit $4 offset $5
2015-12-14 14:36:26 CST [29680-2] aircare_root@aircare DETAIL: parameters: $1 = '17', $2 = 't', $3 = 'alarm', $4 = '10', $5 = '0'
With this grok pattern:
filter {
if [type] == "postgresql" {
grok {
match => { message =>
"^%{TIMESTAMP_ISO8601:timestamp} %{TZ} \[\d+\-\d+\] (?<pg_user>\S+)@(?<pg_db>\S+) %{WORD:log_level}:\s+%{GREEDYDATA:content}"
}
}
if "_grokparsefailure" in [tags] {
drop {}
}
if [content] =~ "^duration:" {
grok {
match => { content =>
"^duration: %{NUMBER:duration:float} ms\s+%{GREEDYDATA:slow_sql}"
}
add_tag => "slow_query"
remove_field => "content"
}
}
}
}
I can parse these logs:
{
"@timestamp" => "2015-12-14T07:30:29.370Z",
"@version" => "1",
"tags" => [
[0] "multiline",
[1] "slow_query"
],
"type" => "postgresql",
"host" => "shifudaotest",
"timestamp" => "2015-12-14 14:36:26",
"pg_user" => "aircare_root",
"pg_db" => "aircare",
"log_level" => "LOG",
"duration" => 828.438,
"slow_sql" => "execute <unnamed>: \n\t select ic.sourceid\n\t , e.id equipmentId\n\t , e.esn equipmentEsn\n\t , e.alias equipmentAlias\n\t , ic.source_label sourceLabel\n\t , ic.cn_content cnContent\n\t , ic.en_content enContent\n\t , ic.date_created dateCreated\n\t , ic.operator_id operatorId\n\t , comm_user.username operatorUsername\n\t , comm_user.nickname operatorNickName\n\t from moni_inbox\n\t inner join moni_info_center ic on ic.id = moni_inbox.info_center_id\n\t left join moni_equipment e on ic.equipment_id = e.id\n\t left join comm_user on comm_user.id=ic.operator_id\n\t where moni_inbox.accepter_id= $1\n\t and moni_inbox.has_read = $2\n\t and ic.source_label = $3 \n\t \n\t and (e.type='compressor' or e.id is null)\n\t order by ic.id desc\n\t limit $4 offset $5\n\t "
}
{
"@timestamp" => "2015-12-14T07:30:17.793Z",
"message" => "2015-12-14 14:36:26 CST [29680-2] aircare_root@aircare DETAIL: parameters: $1 = '17', $2 = 't', $3 = 'alarm', $4 = '10', $5 = '0'",
"@version" => "1",
"type" => "postgresql",
"host" => "shifudaotest",
"timestamp" => "2015-12-14 14:36:26",
"pg_user" => "aircare_root",
"pg_db" => "aircare",
"log_level" => "DETAIL",
"content" => "parameters: $1 = '17', $2 = 't', $3 = 'alarm', $4 = '10', $5 = '0'"
}
Now I want to append "parameters: $1 = '17', $2 = 't', $3 = 'alarm', $4 = '10', $5 = '0'" to the older slow_query event, and let the slow_query enent print the parameters if exist.
Like this:
... etc ...
"slow_query" => "slow_query ..."
"parameters" => {
"$1" => "17"
"$2" => "t"
}
... etc
How to ?