From bfe23dd4341de02e7981fbdbd87550cdc19d6830 Mon Sep 17 00:00:00 2001 From: David Sommerseth Date: Wed, 26 Dec 2012 01:56:54 +0100 Subject: Update eurephiadm to extract VPN MAC and IP address info from the new places As the lastlog table doesn't contain MAC or IP addresses of the VPN client any more, make the lastlog extraction gather the data from the vpnaddr_history table instead. Signed-off-by: David Sommerseth --- database/sqlite/administration/useraccount.c | 13 +++++++------ 1 file changed, 7 insertions(+), 6 deletions(-) (limited to 'database/sqlite/administration/useraccount.c') diff --git a/database/sqlite/administration/useraccount.c b/database/sqlite/administration/useraccount.c index 37303b7..db527ed 100644 --- a/database/sqlite/administration/useraccount.c +++ b/database/sqlite/administration/useraccount.c @@ -233,17 +233,18 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType, qres = sqlite_query(ctx, "SELECT llid, ll.certid,protocol,remotehost,remoteport,macaddr," - " vpnipaddr, vpnipmask, sessionstatus, sessionkey," + " ip4addr, ip6addr, sessionstatus, ll.sessionkey," " locdt(login), locdt(logout)," " session_duration, locdt(session_deleted)," - " bytes_sent, bytes_received, uicid, accessprofile," + " bytes_sent, bytes_received, uicid, ll.accessprofile," " access_descr, fw_profile, depth, lower(digest)," " common_name, organisation, email" " FROM openvpn_lastlog ll" - " LEFT JOIN openvpn_usercerts USING (uid, certid)" + " JOIN openvpn_vpnaddr_history ovh ON (ovh.sessionkey = ll.sessionkey)" + " LEFT JOIN openvpn_usercerts ouc ON (ouc.uid = ll.uid AND ouc.certid = ll.certid)" " LEFT JOIN openvpn_accesses USING (accessprofile)" " LEFT JOIN openvpn_certificates cert ON(ll.certid=cert.certid)" - " WHERE uid = '%i' ORDER BY login, logout", uid); + " WHERE ll.uid = '%i' ORDER BY login, logout", uid); if( sqlite_query_status(qres) != dbSUCCESS ) { eurephia_log(ctx, LOG_ERROR, 0, "Querying the lastlog failed"); @@ -279,8 +280,8 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType, sqlite_xml_value(tmp1, XML_NODE, "remote_host", qres, i, 3); sqlite_xml_value(tmp1, XML_NODE, "remote_port", qres, i, 4); sqlite_xml_value(tmp1, XML_NODE, "vpn_macaddr", qres, i, 5); - sqlite_xml_value(tmp1, XML_NODE, "vpn_ipaddr" , qres, i, 6); - sqlite_xml_value(tmp1, XML_NODE, "vpn_netmask", qres, i, 7); + sqlite_xml_value(tmp1, XML_NODE, "vpn_ipv4addr" , qres, i, 6); + sqlite_xml_value(tmp1, XML_NODE, "vpn_ipv6addr", qres, i, 7); tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "certificate", NULL); assert( tmp1 != NULL ); -- cgit