Find and awk with today's date

Hi All,
Solaris 10 o/s
With your help I developed the following script.

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec   egrep �c  'NS Primary Error' '{}' '+' 

which returns the counts I needed nelow:

/oracle/diag/rdbms/musidp/musidp/trace/abcdef_d001_21751.trc:15
/oracle/diag/rdbms/musidp/musidp/trace/ abcdef _d000_21750.trc:20
/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d001_22002.trc:1524
/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d000_22001.trc:1291

There is one additional requirement and those counts can only be related to today�s date. Right now those counts include several dates in each trace file. I have come up with the following but it returns no data. I know for a fact that when I inspected the trace files I see that date 2015-01-22 in the trace file.

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec egrep  'NS Primary Error' '{}' '+' | awk '/2015-01-22/ {print}'

Is there some other way to write this
Any assistance would be appreciated.
Thanks

try this:

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec awk '/2015-01-22/ && /NS Primary Error/' '{}' '+'

Here is a solution that displays the count of lines containing "NS Primary Error" within all files containing "2015-01-22":

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec awk '
    function pa() { if(FN) print FN":"CNT }
    FNR==1{pa(); FN=CNT=x }
    /2015-01-22/ {FN=FILENAME}
    /NS Primary Error/{CNT++}
    END{pa()}' '{}' '+'

Morning,

Thanks for this. I will run it and get back to you on the results today.

regards
al

---------- Post updated at 07:11 AM ---------- Previous update was at 06:57 AM ----------

Morning Chubler,

I ran your script and it errored out. See below.

-trace> find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec awk '
>     function pa() { if(FN) print FN":"CNT }
>     FNR==1{pa(); FN=CNT=x }
>     /2015-01-22/ {FN=FILENAME}
>     /NS Primary Error/{CNT++}
>     END{pa()}' '{}' '+'
awk: syntax error near line 2
awk: bailing out near line 2

Here is some output from a trace file to help better understand what is needed. Each block has a different time stamp and needs to be included in the count. Notice they are NOT on the same line.

*** 2015-01-03 15:00:18.382
error encountered when answering new connection:
  NS Primary Error: TNS-12560: TNS:protocol adapter error
  NT Generic Error: TNS-00530: Protocol adapter error
  Solaris Error: 130: Software caused connection abort
 
*** 2015-01-05 10:23:09.180
unexpected error 12560 for connection:
  NS Primary Error: TNS-12535: TNS:operation timed out
  NS Secondary Error: TNS-12560: TNS:protocol adapter error
  NT Generic Error: TNS-00505: Operation timed out

Thanks for looking at this for me.

regards

al

Sorry about the delay getting back to you on this. Real world commitments and a Public holiday are to blame.

On Solaris try /usr/xpg4/bin/awk instead, which is POSIX awk (or nawk if that is not available).

Your demo input is interesting and raises a couple of further questions:

1) should the date "2015-02-22" and "NS Primary Error" both occur ANYWHERE in the file or within the same error block?

2) Is the format of these error blocks consistent i.e starting with "***" and a blank line between them?

Hi

No rush we are all busy.

I wrote up a detail for ease of understanding. Sorry if it is long. See below:

My operating system is:

OPERATING SYSTEM:
uname -a
SunOS snslcsunu04 5.10 Generic_150400-13 sun4u sparc SUNW,SPARC-Enterprise

DESCRIPTION OF PROBLEM:

We have 5 servers the each have over a hundred oracle databases. The issue isn�t with the databases it is with the servers. We are getting a lot of dropped connections on the server but don�t know which databases are refusing connections. I need to design a metric that can track those databases via numbers as to which one is getting worse or better.

We need to analyze the trace files every day on the �TNS-12535: TNS:operation timed out� for example for all the databases in the
/oracle/diag/rdbms/*/*/trace directory. Where the * is the name of the database twice for clarification.

Here is MY THINKING and what I got so far.

find /oracle/diag/rdbms/*//trace �type f -name '*d00.trc' -mtime 0 -exec egrep -c 'TNS-12535: TNS:operation timed out' '{}' '+'
OUTPUT:
/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d001_21751.trc:11
/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d000_21750.trc:27
/oracle/diag/rdbms/ghijkl/ghijkl/trace/ghijkl_d001_22002.trc:61
/oracle/diag/rdbms/ghijkl/ghijkl/trace/ghijkl_d000_22001.trc:57

GOAL: My final OUTPUT will be the same as above but with lesser numbers and/or lesser lines.

Now I need to go through these 4 above looking for the data in this case �2015-01-26�. First though I need to remove the excess information on the string the colon and the number . I do that by using awk. Here is my syntax:

find /oracle/diag/rdbms/*//trace -type f -name '*d00.trc' -mtime 0 -exec egrep -c 'TNS-12535: TNS:operation timed out' '{}' '+' | awk -F: '{print $1}'

OUTPUT: Notice there are no trailing : or numbers.

/oracle/diag/rdbms/musidp/musidp/trace/musidp_d001_21751.trc
/oracle/diag/rdbms/musidp/musidp/trace/musidp_d000_21750.trc
/oracle/diag/rdbms/musiop/musiop/trace/musiop_d001_22002.trc
/oracle/diag/rdbms/musiop/musiop/trace/musiop_d000_22001.trc

You would think that the following addition to the syntax would work
| egrep �c �{}� �+� would work returning some or all of the same output files as before but fewer numbers for example.

These numbers are what I am looking for. These represent all trace files for all databases for only today that had disconnects with different timestamps during that day.

Ex.

/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d001_21751.trc:6
/oracle/diag/rdbms/abcdef/abcdef/trace/abcdef_d000_21750.trc:14
/oracle/diag/rdbms/ghijkl/ghijkl/trace/ghijkl_d001_22002.trc:25
/oracle/diag/rdbms/ghijkl/ghijkl/trace/ghijkl_d000_22001.trc:31

But it doesn�t. It produces the following:

find /oracle/diag/rdbms/*//trace -type f -name '*d00.trc' -mtime 0 -exec egrep 'TNS-12535: TNS:operation timed out' '{}' '+' | awk -F:| egrep -c '2015-01-26'

OUTPUT:
awk: syntax error near line 1
awk: bailing out near line 1
0

Can someone please suggest what I is written wrong in the syntax above or maybe an entirely another way of accomplishing this project?

It would be much appreciated.

Thanks in advance.

Try this:

find /oracle/diag/rdbms/*/*/trace -type f -name '*d00*.trc' -mtime 0  -exec \
/usr/xpg4/bin/awk  '/NS Primary/ && /2015-01-03/ {F++} END{ if(F) print FILENAME":"F}' RS="" '{}' ';'

Replace /usr/xpg4/bin/awk marked in red above with, nawk if you get /usr/xpg4/bin/awk is not found errors.

Hi Chubler,

Just received your message and will try this tomorrow at work.. I saw that FILENAME and after reading about it knew it would be applied some where some how.

thanks

I will let you know how it comes out.

regards
al

---------- Post updated 01-27-15 at 08:49 AM ---------- Previous update was 01-26-15 at 07:03 PM ----------

Good Morning Chubler,

Just a note to say "THANKS!" for your help in this effort. The suggestion worked as it was supposed to without changing awk to nawk. Your effort will all me to create the metric now on a spread sheet tracking those databases that are having connections refused on a daily basis via server. In the string is the db name I change the names to protect the innocent as they say. I can say though it was a Navy program that you had a hand in helping keeping our servicemen/women paid on time and ships in good repair.

Again thanks,

al

---------- Post updated at 11:32 AM ---------- Previous update was at 08:49 AM ----------

Morning Chubler,

Just a quick question. I was trying to understand a section of the code and can't seem to find any information on it.

{F++} END{ if(F) print FILENAME":"F}

Can you briefly explain the F++ and the F means in this code? Can you recommend a good book to read on learning this subject?

It would be appreciated.

thanks

al

You are most welcome. Glad to hear the updated program is making your life easier.

I'll try and explain that code segment.

Firstly F has now special meaning and I picked the variable F to represent "Found". It's quite common here to use short variable names but for you own benefit later down the track I'd suggest using LinesFound or something like that.

F++ Has it's roots in the C programming language it increments the variable and could be also written as F=F+1 in basic and many other languages. Note if F is uninitialized (as in on the first matching line) it will be set to 1 by this command.

END{ if(F) print FILENAME":"F} The END condition is a special awk condition and only applies once after the all files have been processed. In this block we are testing our F variable and if it is non-zero we print the input file name followed by a colon character ":" and the F variable's value. This is to avoid printing "filename:" when no lines are found.

Morning,

I will put your explanation in my note book.

That is what I was surmising but wasn't sure. I am some what familiar with programming but never really had a chance to get my head into it. It was great working with you.

Regards

al