[SOLVED] read through syslog and translate IP's

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

I've got a cisco error log which is pulled and mailed out via the following code (values are written to a mysql db). I really need to make the IP's in the second column hostnames, I don't really know how to start. I'll paste the code I've got and someone can come up with a bright idea

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;" > $LOG

if [ ! -f $LOG ]; then
        ERROR="No log file."
else
        precise_error=`cat $LOG | egrep "err|notice|warn" | awk '{print $6,$7,$3,$1,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22}' | grep $TODAY`
        count_errors=`printf "%b\n" "$precise_error" | awk '{print $4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22}' | sort -n | uniq -c | sort -nr | head -$TOP_ERRORS`
        ERROR=`printf "%b\n" "$count_errors"`
        rm -f $LOG
fi

/usr/bin/printf "%b\n" "Top $TOP_ERRORS network problems since $TODAYUSTIME.\n\n""$ERROR" | /bin/mail -s "Top $TOP_ERRORS alerts $TODAYUSTIME" $CONTACTEMAIL

grail · 12-08-2010, 06:57 AM

So its a case of the usual story ... you have presented code but we have zero idea what the data looks like we are unable to tell what all your code has produced??

You say you want hostnames ...what are we using to compare the IPs to to get this information?

I am afraid you will need to provide a lot more detail.

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

Here's a line from the original file (theres 15mb of logs like this, all structured in a similar way):

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

I plan to use host to convert 1.2.3.4 to myswitch.mycompany.com.

Here's a typical example of an email received/sent:

Code:

Top 5 network problems since 08/12/2010

      500 1.2.3.4 %SPANTREE-7-PORTDEL_SUCCESS: GigabitEthernet0/25 deleted from Vlan 101
      250 4.3.2.1 %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet0/50, changed state to down
      75 1.2.3.4 %SPANTREE-7-PORTDEL_SUCCESS: GigabitEthernet0/25 deleted from Vlan 105
      50 4.3.2.1 %SPANTREE-6-PORT_STATE: Line protocol on Interface GigabitEthernet0/20, changed state
      25 3.3.3.3 %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet0/20, changed state to up

I basically need something to cycle through the loop and convert the IP whilst maintaining the structure. I'm pretty open to ideas on the easiest/best way to do this... It may actually be easier to do this in mysql, hadn't thought of that yet...

grail · 12-08-2010, 09:40 AM

Yep ... still not getting the entire picture

How do I get from your input line, to one of those lines of output?

There seem to be an awful lot of fields being used in the awks (perhaps could be improved or is the wrong tool) and yet the output data does not seem to contain so many fields??

I am guessing that this input line is not in the top 5 and hence may not be part of your output, but makes it easier to see in and out if to understand the complexities.

First obvious thought is that you can call 'host' using the print or system functions in awk. Is there an issue with using these?

genderbender · 12-08-2010, 09:55 AM

The script grabs the entire mysql log, every field and outputs it to a text file. Each field in the field is seperated by tabs. Next I grep for JUST "err|notice|warn" messages in the log and use awk to print only certain fields. I than apply a secondary grep to show only text relevant to the current date "$TODAYUSTIME".

Next I count the 5 most common errors (ones where there's duplication) and reformat (again) listening the number of times the error has occured. Then I send it to an email address or email group. It's always been VERY sloppy and poorly maintained, must of been one of the earliest scripts I wrote. From what I remember I had issues with the date and primary key (obviously finding duplicate error messages won't work if the date and a unique number is included).

What would you need from me to make this a tad clearer?

No issues with calling host via awk by the way. I'm getting the sed and awk book for Christmas, I hope it stops me writing crap like this, haha.

GrapefruiTgirl · 12-08-2010, 10:17 AM

I'm not quite understand what exactly you are having trouble with here, but I'm going to offer a suggestion anyhow, in case it helps.

You say you have no trouble using `host` from awk, and it also appears that the code you have, works as expected. Therefore, I'm guessing the problem is "how to change the IP's to names, using my current code?"

If so, maybe this, or something similar to this:

Code:

count_errors=`printf "%b\n" "$precise_error" |\
 awk '{CMD="host " $5; CMD | getline var; close(CMD); gsub("\t|.* |[.]$","",var) ; $5=var;
 print $4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22}' |\
 sort -n | uniq -c | sort -nr | head -$TOP_ERRORS`

I haven't tested it, so when you do test it, please copy + paste for us the output when you run it, especially if it produces errors. I have run MY part of the code, and it works, but adding it into YOUR code, I have not tested.

Also, if an IP does not resolve to a nice neat string with the domain name at the end, this produces undesirable/useless string for the domain name, so you may want to investigate further, perhaps printing BOTH the IP, and the results of `host`, in your final output?

PS, if my code works or helps, I'll be happy to explain its exact purpose & theory of operation, if you are not clear on it.

genderbender · 12-08-2010, 01:27 PM

Hmmmm, in theory a good idea - however the unique comes after the host command and the log is 15mb, so it will run the host command several times (I think anyway) and once complete pipe the data to unique. It usually takes about 20 seconds to complete, but it took much longer (I ended up going home and leaving it to finish running). When I checked my email at home I had the following:

Code:

Top 5 network problems since 08/12/2010

And nothing else, lol. I'll pipe the output to a file or something and do some further analysis/experimentation tomorrow. I think you're onto something using host in that way, it looks like a possible solution.

grail · 12-09-2010, 06:41 AM

Well personally I would probably do the host option at the end only on the 5 winners.

I am still a little perplexed at why we can't make the initial filtering a bit tidier. For starters the use of grep when awk is around is a complete waste.

Would it be possible to get maybe 50 to 100 lines of the initial log file and the corresponding output with your current script?
Obviously change any sensitive information

I just feel that singularly naming so many fields within the awk script looks very early programming (as you have said) and could easily be tidied up.

My only additive so far would be ditch the second call to sort in reverse and just use tail.

GrapefruiTgirl · 12-09-2010, 07:09 AM

Quote:

Originally Posted by grail

Well personally I would probably do the host option at the end only on the 5 winners.

I fully agree - perhaps had I more closely examined the larger picture, rather than the specific single requirement as I interpreted it (using host inside the existing awk) I would have realized that my host command will host-ize the entire list. Not desireable, really.

Quote:

I am still a little perplexed at why we can't make the initial filtering a bit tidier. For starters the use of grep when awk is around is a complete waste.

Again I agree, at least about the grep and other tools (hafta see the logfile to comment on all the $fields being referenced). Ideally, this entire problem will be solved within the confines of a single awk command (and/or possibly with a trailing `sort` command or something small like this - depends how determined you are to get it all inside one command.

)

Cheers!

genderbender · 12-09-2010, 07:41 AM

Here's 40 lines from my log with IP's and MAC's removed, some lines of the log were removed completely as after text replacement they just became junk.

This will be online for one day:
http://pastebin.com/14NgkQB6

The scripts just a cronjob which runs once a night detailing how broke bits of the network are, I don't really care how sloppy the code is or how long it takes to compute (thats the main reason it looks so vile). There's just been an additional need for hostnames in the place of IP's and seeing as it's my code...

grail · 12-09-2010, 09:40 AM

So I am not real sure about all the 'printf "%b\n"' stuff as my output appears to be the same with or without it?
So here is a little something to consider:

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;" > $LOG

if [ ! -f $LOG ]; then
    ERROR="No log file."
else
    ERROR="$(awk -vtoday="$TODAY" '/err|notice|warn/ && $0 ~ today{line=$1 gensub("^.*"$12"|"$23".*$","","g");arr[line]++}END{for(x in arr)print arr[x], x}' $LOG | sort -nr | head -$TOP_ERRORS)
fi

/usr/bin/printf "%b\n" "Top $TOP_ERRORS network problems since $TODAYUSTIME.\n\n""$ERROR" | /bin/mail -s "Top $TOP_ERRORS alerts $TODAYUSTIME" $CONTACTEMAIL

Probably some more tweaks in there but I am get ting sleepy

genderbender · 12-09-2010, 10:14 AM

Here's the output:

Code:

Top 5 network problems since 09-12-2010.

267 1.2.3.4 abnormal duration field from Client, DOS attack] - Source MAC:[AA:BB:CC:DD:EE:FF] 
216 1.2.3.4
41 2.3.4.5
12 3.4.5.6
7 4.5.6.7 %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet0/5, changed state to up

You're also missing a " from this (at the end of the line):

Code:

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

This is also more errors than I'm used to, a typical day has 50 errors and not too many more (I'll test this with my original script shortly). Oh and yeah, still no hostname, lol.

Code:

Top 5 network problems since 09-12-2010.

364 1.2.3.4 abnormal duration field from Client, DOS attack] - Source MAC:[AA:BB:CC:DD:EE]        
9 2.3.4.5 %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet0/5, changed state to up        
8 3.4.5.6 %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet0/5, changed state to down        
6 4.5.6.7 %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet0/16, changed state to up        
5 7.8.9.10 %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet0/16, changed state to down

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

Yes sorry about the missing quotes I had actually taken them out on one incarnation. Like I said, I was tired

SO how about:

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;" > $LOG

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

    while IFS='\n' read -r
    do
        ERROR+="$REPLY\n"
        set - $REPLY

        HOSTN=$(<host magic here> $2)
        ERROR=${ERROR//$2/$HOSTN}
    done<<<"$TMP_ERROR"
fi

/usr/bin/printf "%b\n" "Top $TOP_ERRORS network problems since $TODAYUSTIME.\n\n""$ERROR" | /bin/mail -s "Top $TOP_ERRORS alerts $TODAYUSTIME" $CONTACTEMAIL

genderbender · 12-10-2010, 03:36 AM

The ordering works, however it's not displaying the actual error (it does occasionly which is weird :S).

Here's the email:

Code:

Top 5 network problems since 10-12-2010.

22 myswitch1.network.com
12 myswitch2.network.com
7 myrouter1.network.com
5 myswitch.network.com %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet0/8, changed state to up	
5 myswitch.network.com. %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet0/8, changed state to down

Here's what I get if I print to STDOUT

Code:

22 myswitch1.network.com.\n14 myswitch2.network.com.\n7 myrouter1.network.com.\n5 myswitch.network.com. %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet0/8, changed state to up \n5 myswitch.network.com. %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet0/8, changed state to down \n

grail · 12-10-2010, 03:57 AM

Would you be able to show say just a single line that comprises the 22 entry? I am guessing but I think it may have repeated data in it, ie if the value of $23 is earlier in the line
than $12 then it has removed the entire line. (guessing of course)