Debian Codegraph @

database/sqlite/administration/lastlog.c

raw
1 /* lastlog.c -- Function for Retrieving the eurephia lastlog records
2 *
3 * GPLv2 only - Copyright (C) 2008 - 2012
4 * David Sommerseth <[email protected]>
5 *
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; version 2
9 * of the License.
10 *
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
15 *
16 * You should have received a copy of the GNU General Public License
17 * along with this program; if not, write to the Free Software
18 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
19 *
20 */
21
22 /**
23 * @file sqlite/administration/lastlog.c
24 * @author David Sommerseth <[email protected]>
25 * @date 2009-09-13
26 *
27 * @brief Function for Retrieving the eurephia lastlog records
28 *
29 */
30
31 #include <string.h>
32 #include <unistd.h>
33 #include <assert.h>
34
35 #include <libxml/tree.h>
36 #include <sqlite3.h>
37
38 #include <eurephia_nullsafe.h>
39 #include <eurephia_context.h>
40 #include <eurephia_log.h>
41 #include <eurephia_xml.h>
42 #include <eurephia_values.h>
43 #include <eurephiadb_session_struct.h>
44 #include <eurephiadb_mapping.h>
45 #include <passwd.h>
46
47 #ifndef DRIVER_MODE
48 #define DRIVER_MODE
49 #endif
50 #include <eurephiadb_driver.h>
51
52 #include "../sqlite.h"
53
54 #define FMAP_LASTLOG /**< fieldmapping.h: Include declaration of tbl_sqlite_lastlog */
55 #include "../fieldmapping.h"
56
57
58 /**
59 * @copydoc eDBadminGetLastlog()
60 */
61 xmlDoc *eDBadminGetLastlog(eurephiaCTX *ctx, xmlDoc *srch_xml, const char *sortkeys)
62 {
63 dbresult *res = NULL;
64 eDBfieldMap *fmap = NULL, *fptr = NULL;
65 int i = 0;
66
67 xmlDoc *doc = NULL;
68 xmlNode *fieldmap_n = NULL, *lastl = NULL, *sess = NULL, *tmp1 = NULL, *tmp2 = NULL;
69
70 DEBUG(ctx, 20, "Function call: eDBadminGetLastLog(ctx, {xmlDoc})");
71 assert( (ctx != NULL) && (srch_xml != NULL) );
72
73 if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) {
74 eurephia_log(ctx, LOG_CRITICAL, 0,
75 "eurephia admin function call attempted with wrong context type");
76 return NULL;
77 }
78
79 tmp1 = eurephiaXML_getRoot(ctx, srch_xml, "lastlog_query", 1);
80 fieldmap_n = xmlFindNode(tmp1, "fieldMapping");
81 fmap = eDBxmlMapping(ctx, tbl_sqlite_lastlog, "ll", fieldmap_n);
82
83 // HACK: Remove table alias for some fields in the field mapping
84 for( fptr = fmap; fptr != NULL; fptr = fptr->next) {
85 switch( fptr->field_id ) {
86 case FIELD_UNAME:
87 free_nullsafe(ctx, fptr->table_alias);
88 default:
89 break;
90 }
91 }
92
93 // Query the database, find the user defined in the user map
94 res = sqlite_query_mapped(ctx, SQL_SELECT,
95 "SELECT llid, ll.certid, protocol, remotehost, remoteport, macaddr,"
96 " vpnipaddr, vpnipmask, sessionstatus, sessionkey,"
97 " locdt(login), locdt(logout),"
98 " session_duration, locdt(session_deleted),"
99 " bytes_sent, bytes_received, uicid, accessprofile,"
100 " access_descr, fw_profile, depth, lower(digest),"
101 " common_name, organisation, email, username, ll.uid"
102 " FROM openvpn_lastlog ll"
103 " LEFT JOIN openvpn_usercerts USING (uid, certid)"
104 " LEFT JOIN openvpn_accesses USING (accessprofile)"
105 " LEFT JOIN openvpn_users users ON( ll.uid = users.uid)"
106 " LEFT JOIN openvpn_certificates cert ON (ll.certid = cert.certid)",
107 NULL, fmap, eDBmkSortKeyString(fmap, sortkeys));
108 eDBfreeMapping(fmap);
109 xmlFreeDoc(doc);
110 if( sqlite_query_status(res) != dbSUCCESS ) {
111 eurephia_log(ctx, LOG_ERROR, 0, "Querying the lastlog failed");
112 sqlite_log_error(ctx, res);
113 sqlite_free_results(res);
114 return NULL;
115 }
116 eurephiaXML_CreateDoc(ctx, 1, "lastlog", &doc, &lastl);
117 assert( (doc != NULL) && (lastl != NULL) );
118 for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
119 xmlChar *tmp = NULL;
120 sess = xmlNewChild(lastl, NULL, (xmlChar*) "session", NULL);
121 sqlite_xml_value(sess, XML_ATTR, "llid", res, i, 0);
122 xmlNewProp(sess, (xmlChar *) "session_status",
123 (xmlChar *)SESSION_STATUS[atoi_nullsafe(sqlite_get_value(res, i, 8))]);
124 sqlite_xml_value(sess, XML_ATTR, "session_duration", res, i, 12);
125 sqlite_xml_value(sess, XML_NODE, "sessionkey", res, i, 9);
126 sqlite_xml_value(sess, XML_NODE, "login", res, i, 10);
127 sqlite_xml_value(sess, XML_NODE, "logout", res, i, 11);
128 sqlite_xml_value(sess, XML_NODE, "session_closed", res, i, 13);
129
130 tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "connection", NULL);
131 sqlite_xml_value(tmp1, XML_ATTR, "bytes_sent", res, i, 14);
132 sqlite_xml_value(tmp1, XML_ATTR, "bytes_received", res, i, 15);
133 sqlite_xml_value(tmp1, XML_NODE, "protocol", res, i, 2);
134 sqlite_xml_value(tmp1, XML_NODE, "remote_host", res, i, 3);
135 sqlite_xml_value(tmp1, XML_NODE, "remote_port", res, i, 4);
136 sqlite_xml_value(tmp1, XML_NODE, "vpn_macaddr", res, i, 5);
137 sqlite_xml_value(tmp1, XML_NODE, "vpn_ipaddr" , res, i, 6);
138 sqlite_xml_value(tmp1, XML_NODE, "vpn_netmask", res, i, 7);
139
140 tmp1 = sqlite_xml_value(sess, XML_NODE, "username", res, i, 25);
141 sqlite_xml_value(tmp1, XML_ATTR, "uid", res, i, 26);
142
143 tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "certificate", NULL);
144 sqlite_xml_value(tmp1, XML_ATTR, "certid", res, i, 1);
145 sqlite_xml_value(tmp1, XML_ATTR, "uicid", res, i, 16);
146 sqlite_xml_value(tmp1, XML_ATTR, "depth", res, i, 20);
147 sqlite_xml_value(tmp1, XML_NODE, "digest", res, i, 21);
148
149 tmp = (xmlChar *)sqlite_get_value(res, i, 22);
150 xmlReplaceChars(tmp, '_', ' ');
151 xmlNewChild(tmp1, NULL, (xmlChar *) "common_name", tmp);
152
153 tmp = (xmlChar *)sqlite_get_value(res, i, 23);
154 xmlReplaceChars(tmp, '_', ' ');
155 xmlNewChild(tmp1, NULL, (xmlChar *) "organisation", tmp);
156
157 sqlite_xml_value(tmp1, XML_NODE, "email", res, i, 24);
158
159 tmp2 = sqlite_xml_value(tmp1, XML_NODE, "access_profile", res, i, 18);
160 sqlite_xml_value(tmp2, XML_ATTR, "accessprofile", res, i, 17);
161 sqlite_xml_value(tmp2, XML_ATTR, "fwdestination", res, i, 19);
162 }
163 sqlite_free_results(res);
164 return doc;
165 }