How to append a newer event to an older?


(Feng Yu (Abcfy2)) #1

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 ?


(Mark Walkom) #2

By default you can't because each is a unique event.

You could perhaps use the aggregate filter to merge them.


(system) #3