[lms] statystki dla radiusa - postgres

9 Gru 2015


      Witam
Męczę się by ożenić freeradiusa z lms, sesje i statystyki.
jak na razie mam coś takiego:
#v+
accounting {
        reference = "%{tolower:type.%{%{Acct-Status-Type}:-none}.query}"
type {
                start {
                        query = "UPDATE nodes SET lastonline =
EXTRACT(EPOCH FROM now()) WHERE name='%{SQL-User-Name}'; \
                        INSERT INTO nodesessions
(customerid,nodeid,ipaddr,mac,start,stop,tag,type) VALUES \
                                ((SELECT ownerid FROM nodes WHERE
name='%{SQL-User-Name}'),\
                                (SELECT id FROM nodes WHERE
name='%{SQL-User-Name}'),\
                                INET_ATON('%{Framed-IP-Address}'),\
                                '%{Calling-Station-Id}',\
                                '%{integer:Event-Timestamp}',\
                                '%{integer:Event-Timestamp}',\
                                '%{Acct-Session-Id}',\
                                '1')"
                }
interim-update {
                        query = "UPDATE nodes SET lastonline =
EXTRACT(EPOCH FROM now()) WHERE name='%{User-Name}';\
                                UPDATE nodesessions \
                                SET     \
                                        download =
(('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) +
'%{%{Acct-Output-Octets}:-0}'::bigint), \
                                        upload =
(('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) +
'%{%{Acct-Input-Octets}:-0}'::bigint), \
                                        stop = '%{integer:Event-Timestamp}'
\
                                WHERE tag = '%{Acct-Session-Id}'"
                }
stop {
                        query = "UPDATE nodes SET lastonline =
EXTRACT(EPOCH FROM now()) WHERE name='%{User-Name}';\
                                UPDATE nodesessions \
                                SET     \
                                        download =
(('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) +
'%{%{Acct-Output-Octets}:-0}'::bigint), \
                                        upload =
(('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) +
'%{%{Acct-Input-Octets}:-0}'::bigint), \
                                        stop =
'%{integer:Event-Timestamp}', \
                                        terminatecause =
'%{Acct-Terminate-Cause}' \
                                WHERE tag = '%{Acct-Session-Id}'"
                }
 #
                #  No Acct-Status-Type == ignore the packet
                #
                none {
                     query = "SELECT true"
                }
        }
}
#v-
niestety musiałem użyć 2x INSERT/UPDATE w query, bo freeradius wykonuje
tylko pierwsze jeśli się uda.
do tego momentu jest ok,
problem pojawił mi się kiedy muszę dodać dane do tabeli stats a raczej je
updateować.
podpowie ktoś?
przy update mam takie dane:
#v+
(3)   Service-Type = Framed-User
(3)   Framed-Protocol = PPP
(3)   NAS-Port = 52
(3)   NAS-Port-Type = Ethernet
(3)   User-Name = "Wioletta_W
(3)   Calling-Station-Id = "E4:88:88:CC:0A:1A"
(3)   Called-Station-Id = "pppoe_server"
(3)   NAS-Port-Id = "bridge1"
(3)   Acct-Session-Id = "8160002f"
(3)   Framed-IP-Address = 192.168.3.206
(3)   Acct-Authentic = RADIUS
(3)   Event-Timestamp = "Dec  9 2015 14:20:32 CET"
(3)   Acct-Session-Time = 600
(3)   Acct-Input-Octets = 15262
(3)   Acct-Input-Gigawords = 0
(3)   Acct-Input-Packets = 160
(3)   Acct-Output-Octets = 106
(3)   Acct-Output-Gigawords = 0
(3)   Acct-Output-Packets = 11
(3)   Acct-Status-Type = Interim-Update
(3)   NAS-Identifier = "MikroTik"
(3)   Acct-Delay-Time = 0
(3)   NAS-IP-Address = 172.21.7.202
#v-
z góry dzięki
-- 
Pozdrawiam
Marcin / nicraM