bash - Processing multi line logs with AWK to gather SQL statements -

i have following entries in log file:

2016-01-25 21:12:41 utc:172.31.21.125(56665):user@production:[21439]:error:  bind message supplies 1 parameters, prepared statement "" requires 0  2016-01-25 21:12:41 utc:172.31.21.125(56665):user@production:[21439]:statement:  select count(*) total (                 select 1 count                   leads_search_criteria_entities                   inner join entities e on entity_id = e.viq_id                   left join companies_user cu on cu.entity_id = e.viq_id                   criterium_id = 644 , ((                 ( cu.udef_type null -- if not set user, check calculated value                   , is_university >= 50                 ) or (                   cu.udef_type not null -- if set user, use                   , cu.udef_type = 'university'                 )               ))                   group e.viq_id                    order e.viq_id                 ) x 2016-01-25 21:14:11 utc::@:[2782]:log:  checkpoint starting: time 2016-01-25 21:14:16 utc::@:[2782]:log:  checkpoint complete: wrote 51 buffers (0.0%); 0 transaction log file(s) added, 0 remov ed, 0 recycled; write=5.046 s, sync=0.038 s, total=5.091 s; sync files=18, longest=0.008 s, average=0.002 s 2016-01-25 21:19:11 utc::@:[2782]:log:  checkpoint starting: time 

i capture sql statements not sure how can awk.

update:

expected outcome:

select count(*) total ( select 1 count leads_search_criteria_entities inner join entities e on entity_id = e.viq_id left join companies_user cu on cu.entity_id = e.viq_id criterium_id = 644 , (( ( cu.udef_type null -- if not set user, check calculated value , is_university >= 50 ) or ( cu.udef_type not null -- if set user, use , cu.udef_type = 'university' ) )) group e.viq_id order e.viq_id ) x 

my current working solution uses sed got stuck, helps filtering lines have select (multiple lines itself) , next line after that. suggestion appreciated

sed -n "/:statement:/,/2016/p" out 

i don't recommend using sed this. first thought awk solution might this:

/^2016/&&line~/:statement:/ {   sub(/.*:statement:/,"",line)   print line } /^2016/ {   line="" } {   $1=$1   line=sprintf("%s %s",line,$0) } end {   if (line~/:statement:/) {     sub(/.*:statement:/,"",line)     print line   } } 

obviously shrink this. wrote , ran (for testing) one-liner.

the idea here that:

• we'll append variable, resetting every time our input line starts year. (you replace regexp matching date if want run next year without modification),
• when new log line (or end), strip off cruft before sql statement , print result.

note $1=$1. purpose of change line's whitespace, newlines , tabs , multiples spaces collapsed single spaces. experiment removing see impact.