/* lastlog.c -- Function for Retrieving the eurephia lastlog records * * GPLv2 only - Copyright (C) 2008 - 2012 * David Sommerseth * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; version 2 * of the License. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. * */ /** * @file sqlite/administration/lastlog.c * @author David Sommerseth * @date 2009-09-13 * * @brief Function for Retrieving the eurephia lastlog records * */ #include #include #include #include #include #include #include #include #include #include #include #include #include #ifndef DRIVER_MODE #define DRIVER_MODE #endif #include #include "../sqlite.h" #define FMAP_LASTLOG /**< fieldmapping.h: Include declaration of tbl_sqlite_lastlog */ #include "../fieldmapping.h" /** * @copydoc eDBadminGetLastlog() */ xmlDoc *eDBadminGetLastlog(eurephiaCTX *ctx, xmlDoc *srch_xml, const char *sortkeys) { dbresult *res = NULL; eDBfieldMap *fmap = NULL, *fptr = NULL; int i = 0; xmlDoc *doc = NULL; xmlNode *fieldmap_n = NULL, *lastl = NULL, *sess = NULL, *tmp1 = NULL, *tmp2 = NULL; DEBUG(ctx, 20, "Function call: eDBadminGetLastLog(ctx, {xmlDoc})"); assert( (ctx != NULL) && (srch_xml != NULL) ); if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) { eurephia_log(ctx, LOG_CRITICAL, 0, "eurephia admin function call attempted with wrong context type"); return NULL; } tmp1 = eurephiaXML_getRoot(ctx, srch_xml, "lastlog_query", 1); fieldmap_n = xmlFindNode(tmp1, "fieldMapping"); fmap = eDBxmlMapping(ctx, tbl_sqlite_lastlog, "ll", fieldmap_n); // HACK: Remove table alias for some fields in the field mapping for( fptr = fmap; fptr != NULL; fptr = fptr->next) { switch( fptr->field_id ) { case FIELD_UNAME: free_nullsafe(ctx, fptr->table_alias); default: break; } } // Query the database, find the user defined in the user map res = sqlite_query_mapped(ctx, SQL_SELECT, "SELECT llid, ll.certid, protocol, remotehost, remoteport, macaddr," " vpnipaddr, vpnipmask, sessionstatus, sessionkey," " locdt(login), locdt(logout)," " session_duration, locdt(session_deleted)," " bytes_sent, bytes_received, uicid, accessprofile," " access_descr, fw_profile, depth, lower(digest)," " common_name, organisation, email, username, ll.uid" " FROM openvpn_lastlog ll" " LEFT JOIN openvpn_usercerts USING (uid, certid)" " LEFT JOIN openvpn_accesses USING (accessprofile)" " LEFT JOIN openvpn_users users ON( ll.uid = users.uid)" " LEFT JOIN openvpn_certificates cert ON (ll.certid = cert.certid)", NULL, fmap, eDBmkSortKeyString(fmap, sortkeys)); eDBfreeMapping(fmap); xmlFreeDoc(doc); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_ERROR, 0, "Querying the lastlog failed"); sqlite_log_error(ctx, res); sqlite_free_results(res); return NULL; } eurephiaXML_CreateDoc(ctx, 1, "lastlog", &doc, &lastl); assert( (doc != NULL) && (lastl != NULL) ); for( i = 0; i < sqlite_get_numtuples(res); i++ ) { xmlChar *tmp = NULL; sess = xmlNewChild(lastl, NULL, (xmlChar*) "session", NULL); sqlite_xml_value(sess, XML_ATTR, "llid", res, i, 0); xmlNewProp(sess, (xmlChar *) "session_status", (xmlChar *)SESSION_STATUS[atoi_nullsafe(sqlite_get_value(res, i, 8))]); sqlite_xml_value(sess, XML_ATTR, "session_duration", res, i, 12); sqlite_xml_value(sess, XML_NODE, "sessionkey", res, i, 9); sqlite_xml_value(sess, XML_NODE, "login", res, i, 10); sqlite_xml_value(sess, XML_NODE, "logout", res, i, 11); sqlite_xml_value(sess, XML_NODE, "session_closed", res, i, 13); tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "connection", NULL); sqlite_xml_value(tmp1, XML_ATTR, "bytes_sent", res, i, 14); sqlite_xml_value(tmp1, XML_ATTR, "bytes_received", res, i, 15); sqlite_xml_value(tmp1, XML_NODE, "protocol", res, i, 2); sqlite_xml_value(tmp1, XML_NODE, "remote_host", res, i, 3); sqlite_xml_value(tmp1, XML_NODE, "remote_port", res, i, 4); sqlite_xml_value(tmp1, XML_NODE, "vpn_macaddr", res, i, 5); sqlite_xml_value(tmp1, XML_NODE, "vpn_ipaddr" , res, i, 6); sqlite_xml_value(tmp1, XML_NODE, "vpn_netmask", res, i, 7); tmp1 = sqlite_xml_value(sess, XML_NODE, "username", res, i, 25); sqlite_xml_value(tmp1, XML_ATTR, "uid", res, i, 26); tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "certificate", NULL); sqlite_xml_value(tmp1, XML_ATTR, "certid", res, i, 1); sqlite_xml_value(tmp1, XML_ATTR, "uicid", res, i, 16); sqlite_xml_value(tmp1, XML_ATTR, "depth", res, i, 20); sqlite_xml_value(tmp1, XML_NODE, "digest", res, i, 21); tmp = (xmlChar *)sqlite_get_value(res, i, 22); xmlReplaceChars(tmp, '_', ' '); xmlNewChild(tmp1, NULL, (xmlChar *) "common_name", tmp); tmp = (xmlChar *)sqlite_get_value(res, i, 23); xmlReplaceChars(tmp, '_', ' '); xmlNewChild(tmp1, NULL, (xmlChar *) "organisation", tmp); sqlite_xml_value(tmp1, XML_NODE, "email", res, i, 24); tmp2 = sqlite_xml_value(tmp1, XML_NODE, "access_profile", res, i, 18); sqlite_xml_value(tmp2, XML_ATTR, "accessprofile", res, i, 17); sqlite_xml_value(tmp2, XML_ATTR, "fwdestination", res, i, 19); } sqlite_free_results(res); return doc; }