[SOLVED] read through syslog and translate IP's

genderbender · 12-10-2010, 04:08 AM

Hey grail, i'll send you an email.

grail · 12-10-2010, 07:07 AM

So I was kind of right. The lines that have nothing after IP is because they have less than 23 fields hence when the gensub removes $23.* and 23 is empty it then removes everything

Seems the easy fix was just to change 23 with NF and now I get data:

Code:

TMP_ERROR=$(awk -vtoday="$TODAY" '/err|notice|warn/ && $0 ~ today{line=$1 gensub("^.*"$12"|"$NF".*$","","g");arr[line]++}END{for(x in arr)print arr[x], x}' $LOG | sort -nr | head -$TOP_ERRORS)

This now also reduces the numbers in the top 5. Based on the new log you gave me access to I get:

Edit: Sorry

Completely forgot

Of course i don't have access to host details

genderbender · 12-10-2010, 07:10 AM

That looks good Grail, thanks for your help

Code looks a HELL of a lot cleaner, do you have a job? If not... interested? Only clause is, the company I work with won't want their mac address featured on a public forum, lol.

grail · 12-10-2010, 07:25 AM

Quote:

do you have a job?

Yes but more of a temp thing at the moment (had a family move and not much demand for my skills in my present location

)

Happy to help out when I can

genderbender · 01-11-2011, 10:16 AM

I've changed my sql query to do a lot of the hard work involved (previously done by the huge awk statemenet), is there any chance someone could give me a hand with an updated bit of code? The hostname section no longer works and the text doesn't go to the end of the line. I've been looking at it for awhile and I think the while loop is no longer functioning as expected. If you need anything from me, let me know. Here's the format of the logs (can't provide much):

1.2.3.4 crit 2011-01-11 15:00:99 %PIX-2-12473: Deny IP due to Land Attack from 7.7.7.7 to 7.7.7.7
1.2.3.4 warning 2011-01-11 15:11:21 10203040: Jan 11 15:11:21: %DOT11-4-MAXRETRIES: Packet to client abcd.abcd.abcd reached max retries, removing the client
1.2.3.4 err 2011-01-11 15.12.13 %PIX-3-16463: IP = 12.23.43.1, Keep-alives configured on but peer does not support keep-alives (type = None)

The sql selects log entries which occur $TODAY and are of level error, warning, emergency or critical; this was previously handled by the awk statement.

grail · 01-11-2011, 10:23 AM

Well as it is nearly 2am on a school night I am about to head off, but happy to have a peak tomorrow (unless someone else helps out).

Usual story though, now that sql is chucking out different stuff, show us based on the new entries above what the output would look like?

genderbender · 01-11-2011, 02:05 PM

I've provided you with some log content, here's what happens with the current code (made some rather naff changes myself):

Quote:

Below is a list of faults identified on the network, these are the top 5 faults occuring between 00:00 and 23:59 on 11-01-2011. The first column displays the number of times the fault occured.

227 127.0.0.2 access-list accesslistname denied 2 access_list/1.2.3.40) -> identity/1.2.3.4(0) hit-cnt 5 300-second interval [0x53fe54,
16 127.0.0.2 regular translation creation failed for protocol 50 src inside12:1.2.3.4 dst
9 127.0.0.2 regular translation creation failed for protocol 41 src outside13:1.2.3.4 dst
9 127.0.0.2 access-list accesslistname denied 2 access_list/1.2.3.40) -> identity/1.2.3.4(0) hit-cnt 5 300-second interval [0x53fe44,
7 127.0.0.2 IP = 1.2.3.4, Keep-alives configured on but peer does not support keep-alives (type =

Here's the code:

Code:

LOG=cisco.log
CONTACTEMAIL=blah@blah.com
TODAY=`date "+%Y"-"%m"-"%d"`
TODAYUSTIME=`date "+%d"-"%m"-"%Y"`
TOP_ERRORS=5

mysql -u myusername --password=mypassword syslog -B -e "select * from logs where datetime like '%$TODAY%' AND program not like 'x%';" > $LOG

if [ ! -f $LOG ]; then
        ERROR="Could not locate logfile, make sure mysql username and or password has not been changed and logging is fully functional on the server."
else
        TMP_ERROR=$(awk -vtoday="$TODAY" '/err|warn|crit|emer/ && $0 ~ today{line=$1 gensub("^.*"$10"|"$NF".*$","","g");arr[line]++}END{for(x in arr)print arr[x], x}' $LOG | grep -iv idontwantthis | grep -iv orthis | grep -iv orthishost | sort -nr | head -$TOP_ERRORS | tr -d "[]")
        while IFS='\n' read -r
                do
                        ERROR+="$REPLY\n"
                        set - $REPLY
                        HOSTN=$(host $2 | awk '{print $5}')
                        ERROR=${ERROR//$2/$HOSTN}
                done<<<"$TMP_ERROR"
fi
/usr/bin/printf "%b\n" "Below is a list of faults identified on the network, these are the top $TOP_ERRORS faults occuring between 00:00 and 23:59 on $TODAYUSTIME. The first column displays the number of times the fault occured.\n\n""$ERROR" | /bin/mail -s "Top $TOP_ERRORS alerts for $TODAYUKTIME" $CONTACTEMAIL
rm -f $LOG

grail · 01-11-2011, 09:35 PM

Quote:

The hostname section no longer works and the text doesn't go to the end of the line.

I'll deal with the last issue first - this is related to the gensub and what is being removed. The previous input was of the form:

Code:

1.2.3.4     local7  debug   debug   bf      2010-12-08 10:43:00     18736   18736: Dec  8 10:42:59.287: %SPANTREE-7-PORTDEL_SUCCESS: GigabitEthernet0/25 deleted from Vlan 101     63387

Whilst the new input looks like:

Code:

1.2.3.4 crit 2011-01-11 15:00:99 %PIX-2-12473: Deny IP due to Land Attack from 7.7.7.7 to 7.7.7.7

Which of course is quite different. Here the value of 'line', in awk script, would be:

Code:

1.2.3.4 Attack from 7.7.7.7 to

Which is not particularly useful. So based on the new input, which pieces do we need to keep?

As for the hostname section not working, I presume you are referring to:

Code:

HOSTN=$(host $2 | awk '{print $5}')

If not this section then please advise??
Assuming it is, throw in an echo and see what the value of $2 is and we can track down where it has gone wrong?

Something else I saw as part of maybe a tidy up in the TMP_ERROR value setting, it could look like:

Code:

TMP_ERROR=$(awk -vtoday="$TODAY" '/err|warn|crit|emer/ && $0 ~ today && !/idontwantthis|orthis|orthishost/{line=$1 gensub("^.*"$10"|"$NF".*$|[][]","","g");arr[line]++}END{for(x in arr)print arr[x], x}' $LOG | sort -nr | head -$TOP_ERRORS )

The changes should be obvious, but let me know?

Nominal Animal · 01-12-2011, 02:26 AM

May I butt in? Here's an awk script, top5.awk, which uses the IP address and error message as a key to a occurrence table; then uses a limited bubblesort to pick the N highest occurrences, then outputs only those but using host command to convert the IP address to a hostname.

Code:

#!/usr/bin/awk -f

BEGIN {
    from = "9999-99-99 99:99:99"
    ntop = N
    if (ntop < 1) ntop = 5
}


/:/ {
    when = $3 " " $4
    if (when < from) from = when

    msg = ""
    for (i = 5; i <= NF; i++) msg = msg " " $i

    # Use this if there are rows with extra fields before error message
    gsub(/^[^%]*%/, "", msg)

    item = $1 " " $2 " " msg

    count[item]++
}

END {
    n = ntop
    for (item in count) {
        c = count[item]
        if (c >= topcount[n]) {
            i = n
            while (i > 1 && c > topcount[i]) {
                topcount[i] = topcount[i-1]
                topitem[i] = topitem[i-1]
                i--
            }
            topcount[i] = c
            topitem[i] = item
        }
    }

    while (n > 1 && topcount[n] < 1) n--

    $0 = from
    gsub(/[-:]/, " ")

    # $1=Year, $2=Month, $3=Day, $4=Hour, $5=Minute, $6=Second
    if (n < 1) {
        printf("No problems since %04d-%02d-%02d %02d:%02d:%02d\n", $1, $2, $3, $4, $5, $6)

    } else {
        printf("Top %d problems since %04d-%02d-%02d %02d:%02d:%02d\n\n", n, $1, $2, $3, $4, $5, $6)

        for (i = 1; i <= n; i++) {
            $0 = topitem[i]
            level = $2
            cmd = "host -t CNAME " $1
            cmd |& getline name
            close(cmd)
            if (name ~ / pointer /) {
                gsub(/^.* pointer /, "", name)
                gsub(/\.+$/, "", name)
            } else {
                name = $1
            }
            $1 = ""
            $2 = ""
            printf("%9d %8s %-32s %s\n", topcount[i], level, name, $0)
        }
    }
}

You should be able to generate the summary report with hostnames via

Code:

TODAY=`date "+%Y-%m-%d"`
COUNT=5
mysql -u myusername --password=mypassword syslog -B -e "select * from logs where datetime like '%$TODAY%' AND program not like 'x%';" | ./top5.awk -v N=$COUNT

I'd be interested to know if this makes any sense

Nominal Animal

genderbender · 01-12-2011, 08:41 AM

Quote:

Originally Posted by Nominal Animal

I'd be interested to know if this makes any sense

Nominal Animal

When using this awk script with a pipe, it complained about having no file to work on, so I piped my sql to a file and it worked

... May just be easier than diagnosing problems with grails (I mean my) script.

Quote:

Originally Posted by Grail;

The hostname section no longer works and the text doesn't go to the end of the line.
I'll deal with the last issue first - this is related to the gensub and what is being removed. The previous input was of the form:
Code:

1.2.3.4 local7 debug debug bf 2010-12-08 10:43:00 18736 18736: Dec 8 10:42:59.287: %SPANTREE-7-PORTDEL_SUCCESS: GigabitEthernet0/25 deleted from Vlan 101 63387

Whilst the new input looks like:
Code:

1.2.3.4 crit 2011-01-11 15:00:99 %PIX-2-12473: Deny IP due to Land Attack from 7.7.7.7 to 7.7.7.7

Which of course is quite different. Here the value of 'line', in awk script, would be:
Code:

1.2.3.4 Attack from 7.7.7.7 to

Which is not particularly useful. So based on the new input, which pieces do we need to keep?

As for the hostname section not working, I presume you are referring to:
Code:

HOSTN=$(host $2 | awk '{print $5}')

If not this section then please advise??
Assuming it is, throw in an echo and see what the value of $2 is and we can track down where it has gone wrong?

Something else I saw as part of maybe a tidy up in the TMP_ERROR value setting, it could look like:
Code:

TMP_ERROR=$(awk -vtoday="$TODAY" '/err|warn|crit|emer/ && $0 ~ today && !/idontwantthis|orthis|orthishost/{line=$1 gensub("^.*"$10"|"$NF".*$|[][]","","g");arr[line]++}END{for(x in arr)print arr[x], x}' $LOG | sort -nr | head -$TOP_ERRORS )

The changes should be obvious, but let me know?

$2 was the hostname, also I've further simplified mysql to select only messages from a specific state, so sections of this awk one liner aren't needed (the $TODAY, 'err|warn|crit|emerg', and 'i dont want blah...').

Thanks to both of you for your help.

grail · 01-12-2011, 11:08 AM

Nice work Nominal

A few easy tidy ups to be had, but probably easier to follow.