Output only some part of a log line using grep

Question

I have lines like these -

/mnt/internal-app/logs/internal-app.log_2019-08-21.log.gz:2019-08-21 07:31:14,153 5458142 [XNIO-3 task-4] INFO  c.c.p.i.m.ws.FileManger [FileName.java:1838] - UUIDs in this bucket 8501792126581991569,8073766106536916628,4830289023695906800,6135982080116553120,8306484440313978157,9040948912536460872,8471856544054164043,5431263453539111247,7661719762428556576
/mnt/internal-app/logs/internal-app.log_2019-08-21.log.gz:2019-08-21 07:31:14,153 5458144 [XNIO-3 task-4] INFO  c.c.p.i.m.ws.FileManger [FileName.java:1838] - UUIDs in this bucket 6501792126581991569,8073766106536916628,4830289023695906800,6135982080116553120,8306484440313978157,9040948912536460872,8471856544054164043,5431263453539111247,7661719762428556576

What I ultimately need to do is, collect all the UUIDs and prepare an SQL insert statement like this -

insert into sometable (uuid) values ("6501792126581991569","8073766106536916628")..(..);

There are huge number of such lines, almost 500k. I am unable to apply regex by opening the file in my sublime text editor.

So, I am trying it via grep.

I tried this -

zgrep "UUIDs in this bucket" /mnt/internal-app/logs/internal-app.log_2019-08-2* | grep -Eo ".* UUIDs in this bucket(.*)" | cut -d: -f5

It printed more than what I needed -

1838] - UUIDs in this bucket 8501792126581991569,8073766106536916628,4830289023695906800,6135982080116553120,8306484440313978157,9040948912536460872,8471856544054164043,5431263453539111247,7661719762428556576

How do I pick only from the UUIDs?

Update

Corrected sql query syntax -

insert into sometable (uuid) values ("6501792126581991569"),("8073766106536916628")..(..);

Answer 1

If you want all of the numbers after UUIDs in this bucket, you can use sed like so:

$ zcat file.gz | sed -n 's/^.*UUIDs in this bucket //p' 
8501792126581991569,8073766106536916628,4830289023695906800,6135982080116553120,8306484440313978157,9040948912536460872,8471856544054164043,5431263453539111247,7661719762428556576
6501792126581991569,8073766106536916628,4830289023695906800,6135982080116553120,8306484440313978157,9040948912536460872,8471856544054164043,5431263453539111247,7661719762428556576

Or, use perl and output the full SQL statement:

$ zcat file.gz | perl -ne 'chomp;if(s/^.*UUIDs in this bucket //){@uuids=split(/,/); $k{$_}++ for @uuids} END{ print "insert into sometable (uuid) values (" , join ",",map{qq/"$_"/} keys(%k); print ");\n"}' 
insert into sometable (uuid) values ("6135982080116553120","4830289023695906800","8501792126581991569","9040948912536460872","7661719762428556576","8471856544054164043","8306484440313978157","6501792126581991569","5431263453539111247","8073766106536916628");

Or, slightly more legibly:

$ zcat file.gz | 
    perl -ne 'chomp;
              if(s/^.*UUIDs in this bucket //){
                @uuids=split(/,/); 
                $k{$_}++ for @uuids
              }
              END{
                print "insert into sometable (uuid) values (" , 
                           join ",",map{qq/"$_"/} @uuids; 
                print ");\n"
            }'
insert into sometable (uuid) values ("6501792126581991569","8073766106536916628","4830289023695906800","6135982080116553120","8306484440313978157","9040948912536460872","8471856544054164043","5431263453539111247","7661719762428556576");

Answer 2

If you are willing/able to use tools other than grep you could do it with awk fairly easily since it looks like you always want the end of the line. You can have it print only the last field like:

zcat /mnt/internal-app/logs/internal-app.log_2019-08-2* | awk '/UUIDs in this bucket/ {print $NF}'

I don't know if any forms of zgrep don't support Perl style regex, but assuming yours does too you could do it with that like

zgrep -Po 'UUIDs in this bucket \K.*' /mnt/internal-app/logs/internal-app.log_2019-08-2*

since the \K tells the pattern to not count everything before it as part of the match. So this will only print the things that come after the match.

Answer 3

Another perl way to generate the SQL code:

zcat -f /mnt/internal-app/logs/internal-app.log_2019-08-2* |
  perl -lne 'BEGIN{$"=q(",")}
             @u = m{(?:UUIDs in this bucket |\G,)\K\d+}g;
             print qq(insert into sometable (uuid) values ("@u");) if @u'