Issue with awk command

I am new to unix.I have a requirement to get few fields from the log file as below.

Log

app9/cc-gr_base.log.2017-07-19.gz:[2017-07-19 23:59:20,352] hostname 1500523166993 NA:NA:NA http-nio-8080-exec-56 INFO  Points balance from MIS for user with userId: 19651069, first name: DEREK RICHARD and last name: BOUDREAU is 99243 (VAROrderManager)


app9/cc-gr_base.log.2017-07-19.gz:[2017-07-19 23:40:44,921] app9.aplprd.bridge2solutions.net 1500522052487 NA:NA:NA http-nio-8080-exec-53 INFO  Points balance from MIS for user with userId: 8604826, first name: SUSAN and last name: PAICE is 5195 (VAROrderManager)

Have written the below command

awk -F 'Points balance from MIS for user with|(VAROrderManager)|' '{print $2}' rbcdup.txt

Output

userId: 8604826, first name: SUSAN and last name: PAICE is 5195 (



 userId: 19651069, first name: DEREK RICHARD and last name: BOUDREAU is 99243 (

Now the issue is am getting left bracket ( in the output. My intention is to get only

userId: 19651069, first name: DEREK RICHARD and last name: BOUDREAU is 99243

How can this be achieved.

---------- Post updated at 12:21 AM ---------- Previous update was at 12:06 AM ----------

Log

app9/cc-gr_base.log.2017-07-19.gz:[2017-07-19 23:59:20,352] hostname 1500523166993 NA:NA:NA http-nio-8080-exec-56 INFO  Points balance from MIS for user with userId: 19651069, first name: DEREK RICHARD and last name: BOUDREAU is 99243 (VAROrderManager)


app9/cc-gr_base.log.2017-07-19.gz:[2017-07-19 23:40:44,921] app9.aplprd.bridge2solutions.net 1500522052487 NA:NA:NA http-nio-8080-exec-53 INFO  Points balance from MIS for user with userId: 8604826, first name: SUSAN and last name: PAICE is 5195 (VAROrderManager)

Code

awk -F 'Points balance from MIS for user with|(VAROrderManager)|' '{print $2}' rbcdup.txt

Welcome to the forum.

As parentheses have a special meaning in regular expressions, you need to escape them, in this case and on my system with TWO back slashes: \\(
Is it possible to allow for a wildcard: .VAROrderManager. ?

Or use a different approach:

awk 'gsub (/^.*user with |.VAROrderManager.*$/, _)' file1
userId: 19651069, first name: DEREK RICHARD and last name: BOUDREAU is 99243 
userId: 8604826, first name: SUSAN and last name: PAICE is 5195 

Appreciate your help,it worked

The output is now properly obtained as below with spaces .This is expected as there are few other statements in the parent log which doesnot match the criteria.

userId: 5220818, first name: JOAN and last name: CHARLES is 23063



 userId: 8604826, first name: SUSAN and last name: PAICE is 5195



 userId: 17064784, first name: RAFAEL ALFONSO and last name: FABREGAS is 31970



 userId: 21930053, first name: AERRON and last name: GALANG is 3631



 userId: 8604826, first name: SUSAN and last name: PAICE is 5195

My next requirement was to make this .txt file to excel.On searching the forum i wrote the below code

awk 'BEGIN{ OFS="|"; print "Column1|Column2|Column3|Column4|Column5|Column6"};
> NR > 1{print $4, $4, $6, $7, $8, $9;}' file.txt > file.xls

The excel is generated but not in proper format.

1. All headers came in one column
2. The space in the .txt file has occupied in excel with many cells having |
3. The last column is not displayed with value.Ex 5195,3631 etc

Column1|Column2|Column3|Column4|Column5|Column6
|||||
name:|LENORE|and|last|name:|HARDER
|||||
|||||
|||||
name:|YITZCHOK|and|last|name:|PLOPPER
|||||
|||||
|||||
name:|IGOR|and|last|name:|REVA

Can the shell convert the txt into excel.any links available further on this.Thanks.

The desired output is as follows in xcel

UserID     First name   Lastname   Points
8604826  SUSAN         PAICE        5195

Try:

awk -F'^.*user with |: |, | (and|is) | [(]' '
  NF<8 {
    next
  } 
  NR==1 {
    print $2, $4, $6, "Points"
  }
  {
    print $3, $5, $7, $8}
' OFS=\| file

Output:

userId|first name|last name|Points
19651069|DEREK RICHARD|BOUDREAU|99243
8604826|SUSAN|PAICE|5195