diff options
Diffstat (limited to 'database/sqlite')
| -rw-r--r-- | database/sqlite/administration.c | 89 | ||||
| -rw-r--r-- | database/sqlite/fieldmapping.h | 15 |
2 files changed, 101 insertions, 3 deletions
diff --git a/database/sqlite/administration.c b/database/sqlite/administration.c index 2d6e9d3..361e2ec 100644 --- a/database/sqlite/administration.c +++ b/database/sqlite/administration.c @@ -1339,10 +1339,93 @@ int eDBadminEditAdminAccess(eurephiaCTX *ctx, xmlDoc *grant_xml) { } -xmlDoc *eDBadminGetLastlog(eurephiaCTX *ctx, xmlDoc *usersrch, xmlDoc *certsrch, - const char *sortkeys) +xmlDoc *eDBadminGetLastlog(eurephiaCTX *ctx, xmlDoc *srch, const char *sortkeys) { - return NULL; + 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 != NULL) ); + + tmp1 = eurephiaXML_getRoot(ctx, srch, "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(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," + " login, logout, session_duration, session_deleted," + " bytes_sent, bytes_received, uicid, accessprofile," + " access_descr, fw_profile, depth, 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, sortkeys); + eDBfreeMapping(fmap); + xmlFreeDoc(doc); + if( res == NULL ) { + eurephia_log(ctx, LOG_ERROR, 0, "Quering the lastlog failed"); + return NULL; + } + eurephiaXML_CreateDoc(ctx, 1, "lastlog", &doc, &lastl); + assert( (doc != NULL) && (lastl != NULL) ); + for( i = 0; i < sqlite_get_numtuples(res); i++ ) { + 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); + sqlite_xml_value(tmp1, XML_NODE, "common_name", res, i, 22); + sqlite_xml_value(tmp1, XML_NODE, "organisation", res, i, 23); + 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; } xmlDoc *eDBadminGetAttemptsLog(eurephiaCTX *ctx, xmlDoc *usersrch, xmlDoc *certsrch, diff --git a/database/sqlite/fieldmapping.h b/database/sqlite/fieldmapping.h index fb8a97c..5714e64 100644 --- a/database/sqlite/fieldmapping.h +++ b/database/sqlite/fieldmapping.h @@ -59,4 +59,19 @@ static eDBfieldMap tbl_sqlite_eurephiaadmacc[] = { {0, NULL, FIELD_NONE, ft_UNDEF, NULL, NULL, NULL} }; +static eDBfieldMap tbl_sqlite_lastlog[] = { + {TABLE_LASTLOG, NULL, FIELD_UID, ft_INT, "uid", NULL, NULL}, + {TABLE_LASTLOG, NULL, FIELD_CERTID, ft_INT, "certid", NULL, NULL}, + {TABLE_LASTLOG, NULL, FIELD_REMOTEIP, ft_STRING, "remotehost", NULL, NULL}, + {TABLE_LASTLOG, NULL, FIELD_VPNIP, ft_STRING, "vpnipaddr", NULL, NULL}, + {TABLE_LASTLOG, NULL, FIELD_SESSTATUS, ft_STRING, "status", NULL, NULL}, + {TABLE_LASTLOG, NULL, FIELD_LOGIN, ft_DATETIME, "login" , NULL, NULL}, + {TABLE_LASTLOG, NULL, FIELD_LOGOUT, ft_DATETIME, "logout", NULL, NULL}, + {TABLE_LASTLOG, NULL, FIELD_RECID, ft_INT, "llid", NULL, NULL}, + {TABLE_LASTLOG, NULL, FIELD_UNAME, ft_STRING, "username", NULL, NULL}, + {TABLE_LASTLOG, NULL, FIELD_MACADDR, ft_STRING, "macaddr", NULL, NULL}, + {TABLE_LASTLOG, NULL, FIELD_UICID, ft_STRING, "uicid", NULL, NULL}, + {0, NULL, FIELD_NONE, ft_UNDEF, NULL, NULL, NULL} +}; + #endif /* !FIELDMAPPING_H_ */ |