Need to relate Radius log entries to DHCP ones

wrwe · December 13, 2008, 2:46pm

Hi, let say i have 2 log files(they are below) dhcp and radius. What i need is to put the information to a file just from good connections ( Auth: Login OK ) others just ignore. And the information should look like: time, login name, mac, ip, server. But the trouble is that from radius log i get different looking macs like 001cbf9bb638, 00-1c-bf-9b-b6-38 but in dhcp it always looks like this 00:48:54:52:3b:bb. The main question would be how can i now if it is a mac adress(what command sould help) and how can i convert to dhcp stile (00:48:54:52:3b:bb like this). Thanks being pacient reading this

RADIUS

Thu Dec  4 07:24:54 2008 : Auth: Login OK: [kava4186] (from client LINKSYS3 port 0 via TLS tunnel)
Thu Dec  4 07:24:54 2008 : Auth: Login OK: [kava4186] (from client LINKSYS3 port 21 cli 001cbf9bb638)
Thu Dec  4 07:50:52 2008 : Auth: Login OK: [limo5625] (from client LINKSYS3 port 0 via TLS tunnel)
Thu Dec  4 07:50:52 2008 : Auth: Login OK: [limo5625] (from client LINKSYS3 port 12 cli 0013e80a5b5d)
Thu Dec  4 08:00:14 2008 : Auth: Login OK: [boda7805] (from client LINKSYS3 port 0 via TLS tunnel)
Thu Dec  4 08:00:14 2008 : Auth: Login OK: [boda7805] (from client LINKSYS3 port 33 cli 0015afecda17)
Thu Dec  4 08:00:50 2008 : Error: rlm_eap: UserIdentity Unknown 
Thu Dec  4 08:00:50 2008 : Error: rlm_eap: Identity Unknown, authentication failed
Thu Dec  4 08:00:50 2008 : Auth: Login incorrect: [<no User-Name attribute>] (from client WILI-08 port 8 cli 00-17-31-AA-2D-77)
Thu Dec  4 08:00:52 2008 : Error: rlm_eap: UserIdentity Unknown 
Thu Dec  4 08:00:52 2008 : Error: rlm_eap: Identity Unknown, authentication failed
Thu Dec  4 08:00:52 2008 : Auth: Login incorrect: [<no User-Name attribute>] (from client WILI-08 port 8 cli 00-17-31-AA-2D-77)
Thu Dec  4 08:03:06 2008 : Auth: Login incorrect (rlm_ldap: User not found): [ADMIN\\Adminas] (from client WILI-08 port 0 via TLS tunnel)
Thu Dec  4 08:03:06 2008 : Auth: Login incorrect: [ADMIN\\Adminas] (from client WILI-08 port 8 cli 00-17-31-AA-2D-77)
Thu Dec  4 08:03:08 2008 : Error: rlm_eap: No EAP session matching the State variable.
Thu Dec  4 08:03:08 2008 : Error: rlm_eap: Either EAP-request timed out OR EAP-response to an unknown EAP-request
Thu Dec  4 08:03:08 2008 : Auth: Login incorrect: [ADMIN\\Adminas] (from client WILI-08 port 8 cli 00-17-31-AA-2D-77)
Thu Dec  4 08:03:10 2008 : Auth: Login incorrect (rlm_ldap: User not found): [ADMIN\\Adminas] (from client WILI-08 port 0 via TLS tunnel)
Thu Dec  4 08:03:10 2008 : Auth: Login incorrect: [ADMIN\\Adminas] (from client WILI-08 port 8 cli 00-17-31-AA-2D-77)
Thu Dec  4 08:03:13 2008 : Auth: Login incorrect (rlm_ldap: User not found): [ADMIN\\Adminas] (from client WILI-08 port 0 via TLS tunnel)
Thu Dec  4 08:03:13 2008 : Auth: Login incorrect: [ADMIN\\Adminas] (from client WILI-08 port 8 cli 00-17-31-AA-2D-77)
Thu Dec  4 08:03:15 2008 : Error: rlm_eap: No EAP session matching the State variable.
Thu Dec  4 08:03:15 2008 : Error: rlm_eap: Either EAP-request timed out OR EAP-response to an unknown EAP-request
Thu Dec  4 08:03:15 2008 : Auth: Login incorrect: [ADMIN\\Adminas] (from client WILI-08 port 8 cli 00-17-31-AA-2D-77)
Thu Dec  4 08:03:48 2008 : Auth: Login incorrect (rlm_ldap: User not found): [ADMIN\\Adminas] (from client WILI-08 port 0 via TLS tunnel)
Thu Dec  4 08:04:01 2008 : Auth: Login OK: [JOMO6060] (from client WILI-08 port 0 via TLS tunnel)
Thu Dec  4 08:04:01 2008 : Auth: Login OK: [JOMO6060] (from client WILI-08 port 8 cli 00-17-31-AA-2D-77)

Dhcp

Dec  4 07:24:54 sunfire1 dhcpd: DHCPREQUEST for 192.168.1.100 from 00:1c:bf:9b:b6:38 via em3: wrong network.
Dec  4 07:24:54 sunfire1 dhcpd: DHCPNAK on 192.168.1.100 to 00:1c:bf:9b:b6:38 via em3
Dec  4 07:24:55 sunfire1 dhcpd: DHCPREQUEST for 192.168.4.5 from 00:06:4f:02:00:52 via em2
Dec  4 07:24:55 sunfire1 dhcpd: DHCPACK on 192.168.4.5 to 00:06:4f:02:00:52 via em2
Dec  4 07:24:56 sunfire1 dhcpd: DHCPDISCOVER from 00:1c:bf:9b:b6:38 via em3
Dec  4 07:24:57 sunfire1 dhcpd: DHCPOFFER on 10.2.247.225 to 00:1c:bf:9b:b6:38 (karolis) via em3
Dec  4 07:24:57 sunfire1 dhcpd: DHCPREQUEST for 10.2.247.225 (10.255.255.1) from 00:1c:bf:9b:b6:38 (karolis) via em3
Dec  4 07:24:57 sunfire1 dhcpd: DHCPACK on 10.2.247.225 to 00:1c:bf:9b:b6:38 (karolis) via em3
Dec  4 07:25:00 sunfire1 dhcpd: DHCPREQUEST for 192.168.3.4 from 00:30:4f:06:66:a3 via em2
Dec  4 07:25:00 sunfire1 dhcpd: DHCPACK on 192.168.3.4 to 00:30:4f:06:66:a3 via em2
Dec  4 07:25:01 sunfire1 dhcpd: uid lease 192.168.46.131 for client 00:1f:29:2c:49:96 is duplicate on itc
Dec  4 07:25:01 sunfire1 dhcpd: DHCPREQUEST for 192.168.45.129 from 00:1f:29:2c:49:96 via em2
Dec  4 07:25:01 sunfire1 dhcpd: DHCPACK on 192.168.45.129 to 00:1f:29:2c:49:96 via em2
Dec  4 07:25:07 sunfire1 dhcpd: DHCPREQUEST for 192.168.42.7 from 00:11:09:13:a0:0b via em2
Dec  4 07:25:07 sunfire1 dhcpd: DHCPACK on 192.168.42.7 to 00:11:09:13:a0:0b via em2
Dec  4 07:25:10 sunfire1 dhcpd: DHCPINFORM from 10.2.247.225 via em3
Dec  4 07:25:10 sunfire1 dhcpd: DHCPACK to 10.2.247.225 (00:1c:bf:9b:b6:38) via em3
Dec  4 07:25:16 sunfire1 dhcpd: DHCPREQUEST for 192.168.44.33 from 00:14:4f:26:d9:66 via em2
Dec  4 07:25:16 sunfire1 dhcpd: DHCPACK on 192.168.44.33 to 00:14:4f:26:d9:66 via em2
Dec  4 07:25:22 sunfire1 dhcpd: DHCPINFORM from 192.168.5.6 via em2
Dec  4 07:25:22 sunfire1 dhcpd: DHCPACK to 192.168.5.6 (00:06:4f:02:00:51) via em2
Dec  4 07:25:25 sunfire1 dhcpd: DHCPINFORM from 192.168.5.6 via em2
Dec  4 07:25:25 sunfire1 dhcpd: DHCPACK to 192.168.5.6 (00:06:4f:02:00:51) via em2
Dec  4 07:25:26 sunfire1 dhcpd: DHCPREQUEST for 193.219.42.107 from 00:40:f4:bd:5e:d6 (ragaisiopc) via em0
Dec  4 07:25:26 sunfire1 dhcpd: DHCPACK on 193.219.42.107 to 00:40:f4:bd:5e:d6 (ragaisiopc) via em0
Dec  4 07:25:27 sunfire1 dhcpd: DHCPREQUEST for 192.168.7.3 from 00:06:4f:02:00:59 via em2
Dec  4 07:25:27 sunfire1 dhcpd: DHCPACK on 192.168.7.3 to 00:06:4f:02:00:59 via em2
Dec  4 07:25:43 sunfire1 dhcpd: DHCPREQUEST for 192.168.44.25 from 00:14:4f:1f:b7:21 via em2
Dec  4 07:25:43 sunfire1 dhcpd: DHCPACK on 192.168.44.25 to 00:14:4f:1f:b7:21 via em2
Dec  4 07:25:44 sunfire1 dhcpd: DHCPREQUEST for 172.16.42.11 from 00:01:e6:ad:9d:71 via em0
Dec  4 07:25:44 sunfire1 dhcpd: DHCPACK on 172.16.42.11 to 00:01:e6:ad:9d:71 via em0
Dec  4 07:25:47 sunfire1 dhcpd: DHCPREQUEST for 192.168.5.7 from 00:06:4f:02:54:b1 via em2
Dec  4 07:25:47 sunfire1 dhcpd: DHCPACK on 192.168.5.7 to 00:06:4f:02:54:b1 via em2
Dec  4 07:25:49 sunfire1 dhcpd: DHCPREQUEST for 192.168.9.1 from 00:50:22:82:63:77 via em2
Dec  4 07:25:49 sunfire1 dhcpd: DHCPACK on 192.168.9.1 to 00:50:22:82:63:77 via em2
Dec  4 07:25:55 sunfire1 dhcpd: DHCPREQUEST for 10.2.254.211 from 00:06:4f:03:63:09 (hr) via em3
Dec  4 07:25:55 sunfire1 dhcpd: DHCPACK on 10.2.254.211 to 00:06:4f:03:63:09 (hr) via em3
Dec  4 07:26:05 sunfire1 dhcpd: DHCPREQUEST for 193.219.42.92 from 00:50:22:8d:9c:5b (biblio-stud) via em0
Dec  4 07:26:05 sunfire1 dhcpd: DHCPACK on 193.219.42.92 to 00:50:22:8d:9c:5b (biblio-stud) via em0
Dec  4 07:26:12 sunfire1 dhcpd: DHCPREQUEST for 192.168.45.111 from 00:1a:a0:60:31:2c via em2
Dec  4 07:26:12 sunfire1 dhcpd: DHCPACK on 192.168.45.111 to 00:1a:a0:60:31:2c via em2

wrwe · December 14, 2008, 11:41am

no ideas?

otheus · December 14, 2008, 12:04pm

So you need the MAC address and the IP address from the DHCP server??

Use awk or perl to scan in the DHCP file and remember mac/ip's. Then scan in the radius log, matching MACs to the hash-array used in step 1. Print the line from radius log with the extra info:

#!/usr/bin/perl

# To use: script dhcp.log radius.log

open(DHCP,shift @ARGV) || die "Cannot open DHCP logfile: $!";
open(RADIUS,shift @ARGV) || die "Cannot open RADIUS logfile: $!";

while (<DHCP>) { 
   next unless /DHCPACK on (\S+) to (\S+)/;
   $mac2ip{ lc($2) } = $1;
}

while (<RADIUS>) { 
   next unless /Auth: Login OK:.* cli (\w+)\)/;
   # remove trailing newline
   chomp;
   # grab mac address.
   $mac=lc($1);
   # convert to dhcp-style
   $mac =~ s/(\w\w)(\w\w)/$1:$2/g;
   # lookup ip from previous step
   $ip = exists $mac2ip{ $mac } ? $mac2ip{ $mac } : "UNKNOWN";
   # print original line with ip info
   print $_," $ip\n";
}

Tweaks might be necessary.

otheus · December 14, 2008, 12:34pm

I just realized my solution above won't work as-is. It won't work because over time, a MAC address will be assigned to different IPs. What you can do, then, is merge the two files together by date-time, and then run a modified version of the script:

{ awk '{ $1=""; print $0; }' radius.log; cat dhcp.log ; } | sort -k 1M,2 -k 2n,3 -k 3,4 -k  4r,5

(The last sort option tries to make sure DHCP always appears first for the same second.)

Now all the lines should be merged in order of time (hopefully). Then the script should work fine. It's basically the same code... just organized differently to process everything in the same stream. For input, provide the sorted output from the step above.

while (<>) {
 if  (/DHCPACK on (\S+) to (\S+)/) {
      $mac2ip{ lc($2) } = $1
 }
   
 if (/Auth: Login OK:.* cli (\w+)\)/) {
   # remove trailing newline
   chomp;
   # grab mac address.
   $mac=lc($1);
   # convert to dhcp-style
   $mac =~ s/(\w\w)(\w\w)/$1:$2/g;
   # lookup ip from previous step
   $ip = exists $mac2ip{ $mac } ? $mac2ip{ $mac } : "UNKNOWN";
   # print original line with ip info
   print $_," $ip\n";
  }
}

wrwe · December 15, 2008, 1:49pm

otheus:

I just realized my solution above won't work as-is. It won't work because over time, a MAC address will be assigned to different IPs. What you can do, then, is merge the two files together by date-time, and then run a modified version of the script:
{ awk '{ $1=""; print $0; }' radius.log; cat dhcp.log ; } | sort -k 1M,2 -k 2n,3 -k 3,4 -k  4r,5
(The last sort option tries to make sure DHCP always appears first for the same second.)

Now all the lines should be merged in order of time (hopefully). Then the script should work fine. It's basically the same code... just organized differently to process everything in the same stream. For input, provide the sorted output from the step above.
while (<>) {
 if  (/DHCPACK on (\S+) to (\S+)/) {
   $mac2ip{ lc($2) } = $1
 }
   
 if (/Auth: Login OK:.* cli (\w+)\)/) {
   # remove trailing newline
   chomp;
   # grab mac address.
   $mac=lc($1);
   # convert to dhcp-style
   $mac =~ s/(\w\w)(\w\w)/$1:$2/g;
   # lookup ip from previous step
   $ip = exists $mac2ip{ $mac } ? $mac2ip{ $mac } : "UNKNOWN";
   # print original line with ip info
   print $_," $ip\n";
  }
}

First of all thanks for your reply, for taking it deeper and writing almost everything :}}} it helps me a lot.
New to perl but thats not a problem. The problem is that i can't run the first script i comes with error that cannot find log file dhcp, i have named it dhcp.log, and radius radius.log. And the biggest problem is that i pasted just a small part of logs thay reach 2mb so merging them takes lots of time. And i noticed that the time differs in some log sentences, so they can't be merged by time i think.

otheus · December 15, 2008, 4:35pm

Does this have to be done in "real time"?? Unless you're using a 90's computer, merging a couple of 2 MB files is no big deal.

How does the time differ in some instances? Post those lines and we can figure it out.

wrwe · December 15, 2008, 5:42pm

i do it in a remote server so it sakes time merging. Bus let's say it doesn't matter, but how i understand the radius time can differ from given acknoligment time (think the ack line is just what i need from that log). I think from radius i should take login and mac, from dhcp ack time, ip, save them two in a temporary files and merge them by mac i don't know.. but i need to make mac readable to dhcp log. And maby do it with sh csh sed awk, because i tried to read pearl, kinda difficult to me. Can u give me a hint of finding the string after cli(where mac adress goes) ill try to convert it whith sed or smth, i know you are sick and tired of such requests but thanks in advance.

otheus · December 15, 2008, 6:04pm

I think awk (er, GNU awk) is the most straight-forward:

awk '/Auth: Login OK/ {
   sub(/)$/,"");          # strip ending parenthesis
   mac=toupper($NF); 
   gensub(/([A-Z0-9][A-Z0-9])([A-Z0-9][A-Z0-9])/,"\\1:\\2","g",mac);
}'

I don't know if gensub is available in nawk or mawk, but it is in gawk (or Linux/awk).

wrwe · December 15, 2008, 6:25pm

otheus:

I think awk (er, GNU awk) is the most straight-forward:
awk '/Auth: Login OK/ {
   sub(/)$/,"");          # strip ending parenthesis
   mac=toupper($NF); 
   gensub(/([A-Z0-9][A-Z0-9])([A-Z0-9][A-Z0-9])/,"\\1:\\2","g",mac);
}'
I don't know if gensub is available in nawk or mawk, but it is in gawk (or Linux/awk).

thanks i will try.

wrwe · December 16, 2008, 12:19pm

doesn't work omg i'm so $#!@ i'll try something with

sed 's#.*cli \(.*\)).*#\1#'

otheus · December 17, 2008, 1:34am

Actually, you might be able to use sed to do the mac conversion too:

s/\([0-9A-F][0-9A-F]\)\([0-9A-F][0-9A-F]\)/\1:\2/g;