i can't match log - field using grok patten in logstash
#Log
2015-06-27 01:32:00, 002 DEBUG [pool-2-thread-1 [container11-13]] db.QueryInterceptor (QueryInterceptor.java:57) - @@@@@@@@@ query @@@@@@@@@@
SELECT A.CID
, A.CDN_CODE
, B.FILE_PATH
,NVL2(ME.PATH,'/'||ME.PATH, B.FILE_PATH||B.SAVE_FILE_NAME) AS SAVE_FILE_NAME
, C.STAT
,B.CONTENT_NAME
,B.PLAY_TIME
,B.BAND_WIDTH
,NVL2(ME.NSIZE,NSIZE, B.FILE_SIZE) AS FILE_SIZE
,C.TRANS_YN
,NVL(C.SEND_CNT , 0 ) AS SEND_CNT
,B.FILE_TYPE
,(SELECT
EF.VIDEO_BITRATE_KBPS
FROM
MF_M_ENCODEPRESET EF
, TB_CDN_PLATFORM_MAP PM
WHERE EF.ID = PM.ENCODEPRESET_ID
AND PM.COMPANY_CODE = ?) AS TRANS_BITRATE_BYTES
FROM TB_CONTENT_ASSET A
, TB_CONTENT B
, TB_CONTENT_PUB_STATUS C LEFT JOIN MF_M_ENCODEOUTPUT ME ON C.JOB_ID = ME.JOB_ID
, TB_CDN D
WHERE 1=1
AND A.CID = B.CID
AND A.CID = C.CID
AND A.CDN_CODE = C.CDN_CODE
AND A.CDN_CODE = D.COMPANY_CODE
AND A.CDN_CODE = ?
AND C.LINK_TYPE = 'S'
AND C.STAT IN ('W' , '22','E')
AND C.SEND_CNT < 50
ORDER BY C.STAT, C.UPDATE_DATE, C.CID
#pattern ( logstash filtering )
match => { "message" => ["%{TIMESTAMP_ISO8601:timestamp}, %{NUMBER:millis} %{LOGLEVEL:loglevel} [%{DATA:threads} [%{DATA:container}]] %{PACKAGE_NAME:packageName} (%{SOURCE_INFO}) - %{GREEDYDATA:logData}","%{TIMESTAMP_ISO8601:timestamp}, %{NUMBER:millis} %{LOGLEVEL:loglevel} [%{DATA:threads} [%{DATA:container}]] %{PACKAGE_NAME:packageName} (%{SOURCE_INFO}) - %{GREEDYDATA:logData}%{SPACE}%{QUERY:query}"]
}
#GROK PATTERN
PACKAGE_NAME (?:[a-zA-Z0-9-]+.)+[A-Za-z0-9-]+
SOURCE_INFO (?:[A-Za-z0-9_.-]+):%{NUMBER:line}
USERNAME [a-zA-Z0-9._-]+
USER %{USERNAME}
INT (?:[+-]?(?:[0-9]+))
BASE10NUM (?<![0-9.+-])(?>[+-]?(?:(?:[0-9]+(?:.[0-9]+)?)|(?:.[0-9]+)))
NUMBER (?:%{BASE10NUM})
BASE16NUM (?<![0-9A-Fa-f])(?:[+-]?(?:0x)?(?:[0-9A-Fa-f]+))
BASE16FLOAT \b(?<![0-9A-Fa-f.])(?:[+-]?(?:0x)?(?:(?:[0-9A-Fa-f]+(?:.[0-9A-Fa-f]*)?)|(?:.[0-9A-Fa-f]+)))\b
POSINT \b(?:[1-9][0-9])\b
WORD \b\w+\b
NOTSPACE \S+
SPACE \s
DATA .*?
GREEDYDATA .*
QUOTEDSTRING (?>(?<!\)(?>"(?>\.|[^\"]+)+"|""|(?>'(?>\.|[^\']+)+')|''|(?>(?>\\.|[^\\]+)+`)|``))
UUID [A-Fa-f0-9]{8}-(?:[A-Fa-f0-9]{4}-){3}[A-Fa-f0-9]{12}
URN, allowing use of RFC 2141 section 2.3 reserved characters
URN urn:[0-9A-Za-z][0-9A-Za-z-]{0,31}:(?:%[0-9a-fA-F]{2}|[0-9A-Za-z()+,.:=@;$_!*'/?#-])+
paths
PATH (?:%{UNIXPATH}|%{WINPATH})
UNIXPATH (/([\w_%!$@:.,+~-]+|\.)*)+
TTY (?:/dev/(pts|tty([pq])?)(\w+)?/?(?:[0-9]+))
URIHOST %{IPORHOST}(?::%{POSINT:port})?
uripath comes loosely from RFC1738, but mostly from what Firefox
doesn't turn into %XX
URIPATH (?:/[A-Za-z0-9$.+!'(){},~:;=@#%&_-])+
#URIPARAM ?(?:[A-Za-z0-9]+(?:=(?:[^&]))?(?:&(?:[A-Za-z0-9]+(?:=(?:[^&]))?)?))?
URIPARAM ?[A-Za-z0-9$.+!'|(){},~@#%&/=:;_?-[]<>]*
URIPATHPARAM %{URIPATH}(?:%{URIPARAM})?
URI %{URIPROTO}://(?:%{USER}(?::[^@]*)?@)?(?:%{URIHOST})?(?:%{URIPATHPARAM})?
Months: January, Feb, 3, 03, 12, December
MONTH \b(?:[Jj]an(?:uary|uar)?|[Ff]eb(?:ruary|ruar)?|Mm?r(?:ch|z)?|[Aa]pr(?:il)?|[Mm]a(?:y|i)?|[Jj]un(?:e|i)?|[Jj]ul(?:y)?|[Aa]ug(?:ust)?|[Ss]ep(?:tember)?|Oo?t(?:ober)?|[Nn]ov(?:ember)?|[Dd]e(?:c|z)(?:ember)?)\b
MONTHNUM (?:0?[1-9]|1[0-2])
MONTHNUM2 (?:0[1-9]|1[0-2])
MONTHDAY (?:(?:0[1-9])|(?:[12][0-9])|(?:3[01])|[1-9])
Days: Monday, Tue, Thu, etc...
DAY (?:Mon(?:day)?|Tue(?:sday)?|Wed(?:nesday)?|Thu(?:rsday)?|Fri(?:day)?|Sat(?:urday)?|Sun(?:day)?)
Years?
YEAR (?>\d\d){1,2}
HOUR (?:2[0123]|[01]?[0-9])
MINUTE (?:[0-5][0-9])
'60' is a leap second in most time standards and thus is valid.
SECOND (?:(?:[0-5]?[0-9]|60)(?:[:.,][0-9]+)?)
TIME (?!<[0-9])%{HOUR}:%{MINUTE}(?::%{SECOND})(?![0-9])
datestamp is YYYY/MM/DD-HH:MM:SS.UUUU (or something like it)
DATE_US %{MONTHNUM}[/-]%{MONTHDAY}[/-]%{YEAR}
DATE_EU %{MONTHDAY}[./-]%{MONTHNUM}[./-]%{YEAR}
ISO8601_TIMEZONE (?:Z|[+-]%{HOUR}(?::?%{MINUTE}))
ISO8601_SECOND (?:%{SECOND}|60)
TIMESTAMP_ISO8601 %{YEAR}-%{MONTHNUM}-%{MONTHDAY}[T ]%{HOUR}:?%{MINUTE}(?::?%{SECOND})?%{ISO8601_TIMEZONE}?
DATE %{DATE_US}|%{DATE_EU}
DATESTAMP %{DATE}[- ]%{TIME}
TZ (?:[APMCE][SD]T|UTC)
DATESTAMP_RFC822 %{DAY} %{MONTH} %{MONTHDAY} %{YEAR} %{TIME} %{TZ}
DATESTAMP_RFC2822 %{DAY}, %{MONTHDAY} %{MONTH} %{YEAR} %{TIME} %{ISO8601_TIMEZONE}
DATESTAMP_OTHER %{DAY} %{MONTH} %{MONTHDAY} %{TIME} %{TZ} %{YEAR}
DATESTAMP_EVENTLOG %{YEAR}%{MONTHNUM2}%{MONTHDAY}%{HOUR}%{MINUTE}%{SECOND}
Syslog Dates: Month Day HH:MM:SS
SYSLOGTIMESTAMP %{MONTH} +%{MONTHDAY} %{TIME}
PROG [\x21-\x5a\x5c\x5e-\x7e]+
SYSLOGPROG %{PROG:program}(?:[%{POSINT:pid}])?
SYSLOGHOST %{IPORHOST}
SYSLOGFACILITY <%{NONNEGINT:facility}.%{NONNEGINT:priority}>
HTTPDATE %{MONTHDAY}/%{MONTH}/%{YEAR}:%{TIME} %{INT}
Log formats
SYSLOGBASE %{SYSLOGTIMESTAMP:timestamp} (?:%{SYSLOGFACILITY} )?%{SYSLOGHOST:logsource} %{SYSLOGPROG}:
Log Levels
LOGLEVEL ([Aa]lert|ALERT|[Tt]race|TRACE|[Dd]ebug|DEBUG|[Nn]otice|NOTICE|[Ii]nfo|INFO|[Ww]arn?(?:ing)?|WARN?(?:ING)?|[Ee]rr?(?:or)?|ERR?(?:OR)?|[Cc]rit?(?:ical)?|CRIT?(?:ICAL)?|[Ff]atal|FATAL|[Ss]evere|SEVERE|EMERG(?:ENCY)?|[Ee]merg(?:ency)?)
#QUERY
QUERY %{WORD:query_type}(?:\n)?%{SPACE}(?:(?:[\a-zA-Z()'0-9-_*+,. ]+?)?(?:\n)?%{SPACE}(?:FROM|INTO|TEMPORARY TABLE)(?:\n)?%{SPACE})?(?:%{WORD}.)?%{WORD:table}
SDATE (?:(\d\d){1,4}-\d{1,2}-(?:(?:0[1-9])|(?:[12][0-9])|(?:3[01])|[1-9])\s(?:2[0123]|[01]?[0-9]):(?:[0-5][0-9]):(?:(?:[0-5]?[0-9]|60)(?:[:.,][0-9]+)?))
HEXID (%{BASE16NUM}:%{NUMBER})