pfsense ipcad data to squid's access log transfer for lightsquid stats generation, using captive portal db to resolve IP addresses into user names (based on https://dl.dropboxusercontent.com/u/16308481/tolog.sh from http://ru.doc.pfsense.org/index.php/%D0%9F%D0%BE%D0%B4%D1%81%D1%87%D0%B5%D1%82_%D1%82%D1%80%D0%B0%D1%84%D0%B8%D0%BA%D0%B0_%D1%81_%D…

ipcad_cp_tolog.sh

#!/bin/sh
#
# IPCAD data transfer for LightSquid on pfSense
#

ttime=`/usr/bin/rsh 127.0.0.1 show ip accounting | grep 'Accounting data saved' | awk '{print ($4)}'`

/usr/bin/rsh 127.0.0.1 clear ip accounting

/usr/bin/rsh 127.0.0.1 show ip accounting checkpoint |                                               \
        awk -v vtime=$ttime 'BEGIN {                                                                       \
                p[0]="hopopt"; p[1]="icmp"; p[2]="igmp"; p[3]="ggp"; p[4]="ip-in-ip"; p[5]="st"; p[6]="tcp"; p[8]="egp"; p[11]="nvp-ii"; p[17]="udp"; p[20]="hmp"; p[27]="rdp";     \
                p[28]="irtp"; p[29]="iso-tp4"; p[30]="netblt"; p[33]="dccp"; p[35]="idrp"; p[41]="ipv6";  p[42]="sdrp";  p[43]="ipv6-route";  p[44]="ipv6-frag";  p[46]="rsvp";     \
                p[47]="gre"; p[50]="esp"; p[51]="ah"; p[54]="narp";  p[55]="mobile";  p[57]="skip";  p[58]="ipv6-icmp";  p[59]="ipv6-nonxt";  p[60]="ipv6-opts";  p[81]="vmtp";     \
                p[82]="secure-vmtp"; p[89]="ospf"; p[94]="ipip"; p[97]="etherip"; p[98]="encap"; p[108]="ipcomp"; p[112]="vrrp"; p[113]="pgm";  p[115]="l2tp";  p[124]="is-is";     \
                p[134]="rsvp-e2e-ignore"; p[135]="rfc6275"; p[136]="udplite"; p[137]="mpls-in-ip"; p[138]="manet"; p[139]="hip"; p[140]="shim6";  p[141]="wesp"; p[142]="roch";     \
        }                                                                                                  \
        $1 ~/^[1-9][0-9]?[0-9]?\./ {                                                                       \
                if ($7 in p)                                                                               \
                        vproto = p [$7];                                                                   \
                else                                                                                       \
                        vproto = "#" $7;                                                                   \
                vport = ""; vopts = "";                                                                    \
                if (vproto == "tcp" || vproto == "udp" || vproto == "dccp" || vproto == "#132")            \
                        vport = ":" $5;                                                                    \
                else if (vproto == "icmp")                                                                 \
                        vopts = "(" $5 ":" $6 ")";                                                         \
                username = "-"; \
                select = "select username from captiveportal where allow_time < " vtime " and ip = '\''" $2 "'\''"; \
                cmd = "echo \"" select ";" "\"| for dbfile in /var/db/captive*.db; /usr/local/bin/sqlite3 $dbfile; done"; \
                cmd | getline username; \
                close(cmd); \
                print (vtime ".000 1", $2, "TCP_MISS/200", $4, "CONNECT", $1 vport "\\" vproto vopts, username, "DIRECT/" $1, "-")  \
        }' >> /var/squid/logs/access.log

chown proxy:proxy /var/squid/logs/access.log