How to parse logs

Elastic Stack Logstash
1

Hello All,

I have a amazon redshift logging been stored on amazon s3. I want to use the data from this s3 bucket to visualize. Below are some sample logs which is generated from redshift.

'2016-11-29T00:00:04Z UTC [ db=dev user=rdsdb pid=29788 userid=1 xid=283300 ]' LOG: SELECT 1

'2016-11-29T00:00:04Z UTC [ db=dev user=rdsdb pid=29788 userid=1 xid=283301 ]' LOG: SET statement_timeout TO 120000
'2016-11-29T00:00:04Z UTC [ db=dev user=rdsdb pid=29788 userid=1 xid=283302 ]' LOG: select 'ConnectionCheckQuery'
'2016-11-29T00:00:04Z UTC [ db=dev user=rdsdb pid=29836 userid=1 xid=283303 ]' LOG: SELECT 1
'2016-11-29T00:00:04Z UTC [ db=dev user=rdsdb pid=29836 userid=1 xid=283304 ]' LOG: SET statement_timeout TO 120000
'2016-11-29T00:00:04Z UTC [ db=dev user=rdsdb pid=29836 userid=1 xid=283305 ]' LOG: select * from STV_FDISK_STATS where name = 'blocks_to_backup'
'2016-11-29T00:00:04Z UTC [ db=dev user=rdsdb pid=29836 userid=1 xid=283305 ]' LOG: SELECT pg_catalog.stv_fdisk_stats.node_num AS node_num, pg_catalog.stv_fdisk_stats.name AS name, pg_catalog.stv_fdisk_stats.value AS value FROM pg_catalog.stv_fdisk_stats WHERE pg_catalog.stv_fdisk_stats.name = 'blocks_to_backup'::Char(16);

I tried using grokconstructor for this and below is what i constructed.

\A'%{SYSLOGBASE2:timestamp}%{CRON_ACTION}%{NOTSPACE}%{CRON_ACTION}%{EMAILLOCALPART:db}%{CRON_ACTION}%{EMAILLOCALPART:user}%{CRON_ACTION}%{EMAILLOCALPART:pid}%{CRON_ACTION}%{EMAILLOCALPART:userid}%{CRON_ACTION}%{EMAILLOCALPART:xid}%{CRON_ACTION}%{NOTSPACE}%{SPACE}%{GREEDYDATA:sql}

As of now i am able to get some data but i still see that grokparse failure in logstash output.

{
      "message" => "'2016-11-28T03:18:16Z UTC [ db=dev user=rdsdb pid=29788 userid=1 xid=270214 ]' LOG: SET statement_timeout TO 120000\n",
     "@version" => "1",
   "@timestamp" => "2016-12-01T04:13:33.620Z",
         "type" => "redshift-access-log",
    "timestamp" => "2016-11-28T03:18:16Z UTC",
"timestamp8601" => "2016-11-28T03:18:16Z",
    "logsource" => "UTC",
           "db" => "db=dev",
         "user" => "user=rdsdb",
          "pid" => "pid=29788",
       "userid" => "userid=1",
          "xid" => "xid=270214",
          "sql" => "LOG: SET statement_timeout TO 120000\n",
         "tags" => [
    [0] "_dateparsefailure"
]

}

Can someone guide a help on this.

--
Niraj

2

Try using https://grokdebug.herokuapp.com to check your regex

3

As of now i am able to get some data but i still see that grokparse failure in logstash output.

No, but you have a _dateparsefailure so it's your date filter that doesn't work.

4

@magnusbaeck , Thanks for the reply. Sorry i didn't updated the thread. I managed to fix it by removing the additional date pattern i had. It works perfectly now.

5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.