script that monitors record count 3

[watergrinder]

Hi All,

I want to write a script that monitors the count(*) of records in a table and do further actions if the count steadily increases. Say from 0 to 10 or so. Ideally it should be 0. How is it possible to keep track of count? Is it through cronjob? If so can you tell me how?

Thanks!!!

 

[Ghodmode]

The following assumes your DBMS is Oracle and your environment is all set up...

#!/bin/ksh

COUNTFILE="/tmp/countfile.txt"
SQLTEMP="/tmp/sqltemp.out"

ORIGINAL_COUNT=`cat $COUNTFILE | tr -d ' ' | sed -e 's/^$//d'`

sqlplus -s /nolog << ENDSQL
    connect user/password@database;
    set pagesize 0;
    set linesize 0;
    spool $SQLTEMP;
    select count(*) from my_table;
    spool off;
ENDSQL

CURRENT_COUNT=`cat $SQLTEMP | tr -d ' ' | sed -e 's/^$//d'`

rm $SQLTEMP
echo $CURRENT_COUNT > $COUNTFILE

# If the count has increased by more than 10, let someone know
if [ $CURRENT_COUNT -gt $(( ORIGINAL_COUNT + 10 )) ]
then
    echo "Holey Moley, The DB is growin'!!!" | mailx -s "DB Alert" bill_gates@microsoft.com
fi

You can put it into a script and run it from cron every ten minutes or so.

--
-- GhodMode

 

[watergrinder]

Thank you very much for the reply. I modified the script as I got an error with sed syntax. I removed sed and it is working ..
However, I want to store the count in the form shown below. So, it gets appended at the end each time a retrieval is made from d/b. This approach looks good to me. If the count increases steadily for 5 times ... mail will be sent.
Supposing that it should be 0, an example is shown below

0 5 7 9 11 15 17 19 24 25
# mail will be sent at this point

0 2 3 0 2 4 5 0 ... # not sent ..

Can you help me with the syntax.?

Modified script:

#!/bin/sh
# Temp files
COUNTFILE="/tmp/countfile.txt"
SQLTEMP="/tmp/sqltemp.out"
 
# Original Count
ORIGINAL_COUNT=`cat $COUNTFILE | tr -d ' '`
echo "Last Count $ORIGINAL_COUNT"
# | sed -e 's/^$//d'`
 
# Connect to sqlplus, spool the output to /tmp/sqltemp.out.
sqlplus -s /nolog << ENDSQL
    connect xx/yy@ccc;
    set pagesize 0;
    set feedback off;
    set echo off;
    set verify off;
    set linesize 100;
    spool $SQLTEMP;
    select count(*) from bb;
    spool off;
ENDSQL
 
# Current Count
CURRENT_COUNT=`cat $SQLTEMP | tr -d ' '`
echo "Current d/b count is $CURRENT_COUNT"
# | sed -e 's/^$//d'`
 
# remove tmp file
rm $SQLTEMP
echo $CURRENT_COUNT > $COUNTFILE
# Notify if the count has increased to 10.
if [ $CURRENT_COUNT -gt $((ORIGINAL_COUNT+10)) ]; then
    echo "Warning" | mailx -s "DB Alert" shell@unix.com
fi

 

[Ghodmode]

The sed code was supposed to remove blank lines, which may not have appeared in the output from your query anyway. It's just a precaution I've gotten myself in the habit of taking. [tt]^[/tt] is the beginning of the line and [tt]$[/tt] is the end of the line. So, that expression says If the beginning of the line is followed by the end of the line, delete the line. I should've tested it. The syntax is definitely wrong. "[tt] | sed /^$/d[/tt]" works, but you might not need it anyway.

Now for your other question ...

[ul]
[li]I put the sed code back in, with the correct syntax, but you might not need it anyway.[/li]
[li]You may want to consider an alternative like Perl as this starts to get more complex.[/li]
[li]I changed it so that it checks to see if the count has increased at all, rather than only if it has increased by 10. If you want to check by a specific increment, just increase the value of the variable [TT]INCREMENT[/TT][/li]
[/ul]

#!/bin/sh
# Temp files
COUNTFILE="/tmp/countfile.txt"
SQLTEMP="/tmp/sqltemp.out"
INCREMENT=1
 
# Original Count
ALLCOUNTS=`cat $COUNTFILE | tr -d ' ' | sed /^$/d`

# -d is the delimeter: a single space
# -f is the field number
COUNT1=`echo "$ALLCOUNTS" | cut -d" " -f1`
COUNT2=`echo "$ALLCOUNTS" | cut -d" " -f2`
COUNT3=`echo "$ALLCOUNTS" | cut -d" " -f3`
COUNT4=`echo "$ALLCOUNTS" | cut -d" " -f4`
COUNT5=`echo "$ALLCOUNTS" | cut -d" " -f5`

# This takes care of the values if the file isn't full yet
# && is a shortcut to if-then.
[ -z $COUNT1 ] && COUNT1=0
[ -z $COUNT2 ] && COUNT2=0
[ -z $COUNT3 ] && COUNT3=0
[ -z $COUNT4 ] && COUNT4=0
[ -z $COUNT5 ] && COUNT5=0

echo "Last Counts $ALLCOUNTS"
 
# Connect to sqlplus, spool the output to /tmp/sqltemp.out.
sqlplus -s /nolog << ENDSQL
    connect xx/yy@ccc;
    set pagesize 0;
    set feedback off;
    set echo off;
    set verify off;
    set linesize 100;
    spool $SQLTEMP;
    select count(*) from bb;
    spool off;
ENDSQL
 
# Current Count
CURRENT_COUNT=`cat $SQLTEMP | tr -d ' ' | sed /^$/d`
echo "Current d/b count is $CURRENT_COUNT"
 
# Add the new count, drop the oldest count
COUNT1=$COUNT2
COUNT2=$COUNT3
COUNT3=$COUNT4
COUNT4=$COUNT5
COUNT5=$CURRENT_COUNT

# If the count has steadily grown send an email
if [ $COUNT2 > $((COUNT1 + INCREMENT)) ]
then
    if [ $COUNT3 > $((COUNT2 + INCREMENT)) ]
    then
        if [ $COUNT4 > $((COUNT3 + INCREMENT)) ]
        then
            if [ $COUNT5 > $((COUNT4 + INCREMENT)) ]
            then
                echo "Warning" | mailx -s "DB Alert" shell@unix.com
            fi # End if for COUNT5 check
        fi # End if for COUNT4 check
    fi # End if for COUNT3 check
fi # End if for COUNT2 check

# remove tmp file
rm $SQLTEMP
echo "$COUNT1 $COUNT2 $COUNT3 $COUNT4 $COUNT5" > $COUNTFILE

--
-- GhodMode

 

[watergrinder]

Thanks you very much for the reply. I wanted to put desired count in a file, read it and do the logic dynamically. i.e if desired count is 10, the comparision will be done 10 times in order to check if the values increased steadily. I am using arrays and I am stuck at array initialization (tried 2, 3 ways .. gave error). I tried expr also, I don't know whether they are initialized to 0 because the print statement after that displays nulls ... Can you help? Thanks.

# Data File
COMPFILE=/home/myself/count.dat

# Temp files
COUNTFILE="/tmp/countfile.txt"
SQLTEMP="/tmp/sqltemp.out"

# Read number of comparisions. 
NO_OF_COMPS=`cat $COMPFILE` 
echo "Desired Number is $NO_OF_COMPS"

# Original Count
ALLCOUNTS=`cat $COUNTFILE | sed /^$/d`
echo "Countfile Initially: $ALLCOUNTS"

# -d is the delimeter: a single space
# -f is the field number
i=1
while [ i -le NO_OF_COMPS ]
do
    #allcounts[$i]=`echo "$ALLCOUNTS" | cut -d" " -f$i`
    allcounts[$i]=0 # WANT TO INITIALIZE TO 0??
    i=`expr $i + 1`
done
for j in "${allcounts[@]}"; do
  echo ${allcounts[$j]}
done

 

[PHV]

This line:
while [ i -le NO_OF_COMPS ]
should be replaced by this:
while [ [highlight]$[/highlight]i -le [highlight]$[/highlight]NO_OF_COMPS ]

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[watergrinder]

Also, I would appreciate if you can help me with the following:

1) echo "$COUNT1 $COUNT2 $COUNT3 $COUNT4 $COUNT5" > $COUNTFILE. How to dynamically write the above to a file? i.e if there are 10, then COUNT1 ... COUNT10 should be written

2) How to read them back and count?

allcounts[$i]=`echo "$ALLCOUNTS" | cut -d" " -f$i` reads each one of them but how to count?

Thanks in advance.

 

[watergrinder]

Thanks for quick response but I am still having the same problem.

 

[PHV]

The whole block:
i=1
while [ i -le NO_OF_COMPS ]
do
#allcounts[$i]=`echo "$ALLCOUNTS" | cut -d" " -f$i`
allcounts[$i]=0 # WANT TO INITIALIZE TO 0??
i=`expr $i + 1`
done
may be replaced (if ksh) by this:
set -A allcounts $ALLCOUNTS
The number of counts in the array is:
${#allcounts[*]}


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[watergrinder]

I think I found the answer to my first question:

echo ${#allcounts[*]}


I put a set -x to see how they are initialized. The following is the output

Desired Number is 5
Countfile Initially: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
+ i=1
+ [ 1 -le 5 ]
+ allcounts[1]=10
+ + expr 1 + 1
i=2
+ [ 2 -le 5 ]
+ allcounts[2]=10
+ + expr 2 + 1
i=3
+ [ 3 -le 5 ]
+ allcounts[3]=10
+ + expr 3 + 1
i=4
+ [ 4 -le 5 ]
+ allcounts[4]=10
+ + expr 4 + 1
i=5
+ [ 5 -le 5 ]
+ allcounts[5]=10
+ + expr 5 + 1
i=6
+ [ 6 -le 5 ]
+ echo

+ echo

+ echo

+ echo

+ echo

 

[aigles]

To initialize the array from the file (index start at 0):
[tt]
set -A allcounts $(<$COUNTFILE)
[/tt]

To write the array back to the file :
[tt]
echo ${allcounts[*]} > $COUNTFILE
[/tt]

Jean Pierre.

 

[watergrinder]

Thanks.

# Original Count
ALLCOUNTS=`cat $COUNTFILE | sed /^$/d`
#echo "Countfile Initially: $ALLCOUNTS"

set -A allcounts $(<$COUNTFILE)

echo ${#allcounts[*]}

Gives 2 3 4 5 6 7 8 9 NULL (BLANK)
where as COUNTFILE initially is 1 2 3 4 5 6 7 8 9

Whereas echo ${allcounts[*]} > $COUNTFILE
puts back 1 2 3 4 5 6 7 8 9

Any clues?

 

[watergrinder]

Sorry it's

for j in "${allcounts[@]}"; do
  echo ${allcounts[$j]}
done

that gives 2 3 4 5 6 7 8 9 NULL

 

[watergrinder]

Ok It is

echo ${allcounts[$j-1]}

 

[aigles]

When you execute the for loop, the variable j is set to the element array value and not to the index (like in awk for in).

To display all the elements of the array, you must do :

for j in "${allcounts[@]"; do
  echo $j
done

Jean Pierre.

 

[watergrinder]

Can I append some elements to array? Ex: If the desired count in below script is > actual, I want to append some dummy elements (Ex: X) to the actual array until the length becomes desired length. Can we append X to allcounts (integer array)?

# Temp files
COUNTFILE="/tmp/countfile.txt"
SQLTEMP="/tmp/sqltemp.out"

# Desired Count
COUNT_DESIRED=`cat $COMPFILE` 
echo "Desired Count: $COUNT_DESIRED"

# Actual Count
DISPLAY_ACTUAL=`cat $COUNTFILE | sed /^$/d`
echo "Actual: $DISPLAY_ACTUAL"

set -A allcounts $(<$COUNTFILE)
COUNT_ACTUAL=${#allcounts[*]}
echo "Actual Count: $COUNT_ACTUAL"

if [ $COUNT_DESIRED -gt $COUNT_ACTUAL ]
then
     echo "Desired greater than actual"
     let difference=$COUNT_DESIRED-$COUNT_ACTUAL
     echo " Difference: $difference"
     
fi 

for j in "${allcounts[@]}"; do
  echo ${allcounts[$j-1]}
done

echo ${allcounts[*]} > $COUNTFILE

 

[watergrinder]

Any answers

 

[watergrinder]

Hi, Is it possible? thanks.

 

[aigles]

If you want to extend your array by one element, you can do (general method) :
[tt]
allcounts[${#allcounts[*]}]=X
[/tt]

Jean Pierre.