Log file parsing

Turbocapitalist · 10-19-2016, 01:47 PM

Quote:

Originally Posted by dfco

awk -F"~" '{print $1}' testfile.log

-F sets the Field Separator. So -F "~" is also the same as starting with a clause 'BEGIN { FS="~" }'

However, given the data you showed above, changing the Field Separator is probably not important.

Do the numbers you want always appear in the same position in each line?

dfco · 10-19-2016, 02:53 PM

Thanks for your recommendations.

With the following input :
APP_9874 Target: BKG (Instance Name: [BKG])
APP_9889 Inserted rows - Requested: 1218 Applied: 1218 Rejected: 1 Affected: 1218

I tried the script:
awk 'BEGIN{ OFS=";"}
$2 == "Target:" {TARGET_TABLE_NAME = $3}
$2 == "Inserted" {INSERTED_AFFECTED_ROWS = $12;
INSERTED_REJECTED_ROWS = $10}
{print TARGET_TABLE_NAME,INSERTED_AFFECTED_ROWS,INSERTED_REJECTED_ROWS;
TARGET_TABLE_NAME = "";
INSERTED_AFFECTED_ROWS = 0;
INSERTED_REJECTED_ROWS = 0;}' testfile.log

The result is:
BKG;;
;1218;1

The result is expected but my first question is : can I have the result in only one line:
BKG;1218;1

Thanks mates.

Habitual · 10-19-2016, 02:53 PM

Quote:

Originally Posted by dfco

The log is a csv file and is generated by an application which insert/update/delete records into a table.

Is there some secret to the name of this program?

dfco · 10-19-2016, 02:56 PM

Yes the numbers always appear in the same position/order in each line.

TB0ne · 10-19-2016, 03:19 PM

Quote:

Originally Posted by dfco

Maybe I need to write I am a beginner and complex Linux scripting is something new. The log is a csv file and is generated by an application which insert/update/delete records into a table.

And AGAIN, you are not telling us anything...saying "an application" tells us nothing, nor does it clarify anything to say "insert/update/delete records into a table". WHAT table? Where? How does the 'it' know what to update/insert/delete???? You are telling us nothing.

Quote:

I tried to make simplify my need with the last example I gave :

Telling us what you NEED over and over again, and ignoring questions that are asked, is pointless. We KNOW WHAT YOU WANT/NEED, but you are not answering questions.

dfco · 10-19-2016, 03:29 PM

@TB0ne. Are you serious ? Your questions do not make sense.

grail · 10-19-2016, 03:59 PM

So it turns out none of us have mentioned yet, but please use [code][/code] tags around your code or data (maintains format and takes up less room

On to your problem, now that we have more information. First of all, your dba or oracle dev could easily extract this information directly from the database.

As to the issue of gleaning the data from the log file, your first issue would be on how to identify which date you wish to look for which I will leave to you

Here is a possible solution ... crude but seems to work:

Code:

#!/usr/bin/awk -f

/SOURCE/{ date = gensub(/^.*E /,"","1") }

/Target/{ tg = $3 }

/Inserted/{
  ins[date][tg]["app"] = $8
  ins[date][tg]["rej"] = $10 
  ins[date][tg]["aff"] = $12 
}

/Updated/{
  upd[date][tg]["app"] = $8
  upd[date][tg]["rej"] = $10 
  upd[date][tg]["aff"] = $12 
}

/Deleted/{
  del[date][tg]["app"] = $8
  del[date][tg]["rej"] = $10 
  del[date][tg]["aff"] = $12 
}

END{
  for(d in ins){
    print "Results for source date :-",d
    for(t in ins[d])
      printf("%s,%d,%d,%d,%d,%d,%d,%d,%d,%d\n",t,
             ins[d][t]["app"],ins[d][t]["aff"],ins[d][t]["rej"],
             upd[d][t]["app"],upd[d][t]["aff"],upd[d][t]["rej"],
             del[d][t]["app"],del[d][t]["aff"],del[d][t]["rej"] )
  }
}

dfco · 10-19-2016, 04:15 PM

Thanks a lot Grail. I will try.

This particular info are not stored in the database but only in the log files. Only option is to parse the files. It is unfortunate because it would have been easier with SQL queries.

allend · 10-19-2016, 04:29 PM

This was my more pedestrian solution with a script called using the -f option to awk.

Code:

function initialise() {
      TARGET_TABLE_NAME = "";
      INSERTED_APPLIED_ROWS = 0;
      INSERTED_AFFECTED_ROWS = 0;
      INSERTED_REJECTED_ROWS = 0;
      UPDATED_APPLIED_ROWS = 0;
      UPDATED_AFFECTED_ROWS = 0;
      UPDATED_REJECTED_ROWS = 0;
      DELETED_APPLIED_ROWS = 0;
      DELETED_AFFECTED_ROWS = 0;
      DELETED_REJECTED_ROWS = 0 }

BEGIN { initialise(); OFS="," }

$2 == "Target:"  { TARGET_TABLE_NAME = $3 }
$2 == "Inserted" { INSERTED_APPLIED_ROWS = $8;
                   INSERTED_AFFECTED_ROWS = $12;
                   INSERTED_REJECTED_ROWS = $10 }
$2 == "Updated"  { UPDATED_APPLIED_ROWS = $8;
                   UPDATED_AFFECTED_ROWS = $12;
                   UPDATED_REJECTED_ROWS = $10 }
$2 == "Deleted"  { DELETED_APPLIED_ROWS = $8;
                   DELETED_AFFECTED_ROWS = $12;
                   DELETED_REJECTED_ROWS = $10 }
/^$/ { print TARGET_TABLE_NAME,
        INSERTED_APPLIED_ROWS,INSERTED_AFFECTED_ROWS,INSERTED_REJECTED_ROWS,
        UPDATED_APPLIED_ROWS,UPDATED_AFFECTED_ROWS,UPDATED_REJECTED_ROWS,
        DELETED_APPLIED_ROWS,DELETED_AFFECTED_ROWS,DELETED_REJECTED_ROWS;
       initialise() }

grail · 10-19-2016, 04:52 PM

Actually, if you switch auditing on this is exactly the sort of information that is available.

dfco · 10-19-2016, 04:54 PM

@Grail : What do you mean ?

grail · 10-19-2016, 05:29 PM

http://docs.oracle.com/cd/B19306_01/...t.htm#BABCFIHB

Your head DBA should probably know about this and also your lead Developer

dfco · 10-19-2016, 05:50 PM

You mean DB audit. Maybe for the DML (insert, update, delete, ...) operations but not sure for the rejected records. Not sure my DBA will accept to add more monitor stuffs in DB prod. I am aware of those options and came to the forum because best option is too parse the log files.

TB0ne · 10-20-2016, 08:17 AM

Quote:

Originally Posted by dfco

@TB0ne. Are you serious ? Your questions do not make sense.

What is unclear???? We have been asking you SEVERAL TIMES to tell us where this data is coming from. All you've told us is "an application". What about that don't you understand?

Habitual asked you, I asked you earlier, and Turbocapitalist also asked you questions you didn't answer. It took you to post #15 to actually tell us "oracle data integrator", but you STILL don't say what "the application" is that's going to USE this data, how. Is that so hard?

dfco · 10-20-2016, 10:59 AM

Thanks Grail and Allend.

I tried :

Code:

awk '
function initialise() {
      TARGET_TABLE_NAME = "";
      INSERTED_AFFECTED_ROWS = 0;
      INSERTED_REJECTED_ROWS = 0;
      UPDATED_AFFECTED_ROWS = 0;
      UPDATED_REJECTED_ROWS = 0;
      DELETED_AFFECTED_ROWS = 0;
      DELETED_REJECTED_ROWS = 0 }

BEGIN{ initialise(); OFS=";"} 
$2 == "Target:" {TARGET_TABLE_NAME = $3} 
$2 == "Inserted" {INSERTED_AFFECTED_ROWS = $12;
				  INSERTED_REJECTED_ROWS = $10}
$2 == "Updated" {UPDATED_AFFECTED_ROWS = $12;
				  UPDATED_REJECTED_ROWS = $10}
$2 == "Deleted" {DELETED_AFFECTED_ROWS = $12;
				  DELETED_REJECTED_ROWS = $10}				  
 { print TARGET_TABLE_NAME,INSERTED_AFFECTED_ROWS,INSERTED_REJECTED_ROWS,UPDATED_AFFECTED_ROWS,UPDATED_REJECTED_ROWS,DELETED_AFFECTED_ROWS,DELETED_REJECTED_ROWS; 
initialise() }
' testfile.log

And the result is :

Code:

BKG;0;0;0;0;0;0
;1218;1;0;0;0;0
;0;0;1218;1;0;0
;0;0;0;0;1218;1

Is it possible to have only one line :

Code:

BKG;1218;1;1218;1;1218;1

Thanks a lot.