Added extraction of lastlog info in eDBadminGetUserInfo(...)

1 files changed, 104 insertions, 43 deletions

diff --git a/database/sqlite/administration.c b/database/sqlite/administration.c
index af8612b..389f446 100644
--- a/database/sqlite/administration.c
+++ b/database/sqlite/administration.c
@@ -423,7 +423,7 @@ inline int xml_set_flag(xmlNode *node, char *flagname, int flagged) {
 //  It can be several search field tags to limit the search even more.
 //
 xmlDoc *eDBadminGetUserInfo(eurephiaCTX *ctx, int getInfo, xmlDoc *srch) {
-        dbresult *uinf = NULL, *certinf = NULL;
+        dbresult *uinf = NULL, *qres = NULL;
         eDBfieldMap *uinfo_map = NULL;
         int flag = 0, uid = 0;
 
@@ -470,32 +470,32 @@ xmlDoc *eDBadminGetUserInfo(eurephiaCTX *ctx, int getInfo, xmlDoc *srch) {
                 sqlite_xml_value(root_n, XML_ATTR, "uid", uinf, 0, 4);
                 sqlite_xml_value(root_n, XML_NODE, "username", uinf, 0, 0);
 
-                info_n = xmlNewChild(root_n, NULL, (xmlChar *) "flags", NULL);
+                if( (getInfo & USERINFO_user) == USERINFO_user ) {
+                        info_n = xmlNewChild(root_n, NULL, (xmlChar *) "flags", NULL);
 
-                // set DEACTIVATED flag, if deactivated field is not NULL
-                xml_set_flag(info_n, "DEACTIVATED", (sqlite_get_value(uinf, 0, 2) != NULL));
+                        // set DEACTIVATED flag, if deactivated field is not NULL
+                        xml_set_flag(info_n, "DEACTIVATED", (sqlite_get_value(uinf, 0, 2) != NULL));
 
-                // set BLACKLISTED flag, if username is found in blacklist table
-                xml_set_flag(info_n, "BLACKLISTED", (atoi_nullsafe(sqlite_get_value(uinf, 0, 5))==1));
+                        // set BLACKLISTED flag, if username is found in blacklist table
+                        xml_set_flag(info_n, "BLACKLISTED", (atoi_nullsafe(sqlite_get_value(uinf, 0, 5))==1));
 
-                // set OPENSESSION flag, if user has a lastlog entry with sessionstatus == 2
-                xml_set_flag(info_n, "OPENSESSION", (atoi_nullsafe(sqlite_get_value(uinf, 0, 6))==1));
+                        // set OPENSESSION flag, if user has a lastlog entry with sessionstatus == 2
+                        xml_set_flag(info_n, "OPENSESSION", (atoi_nullsafe(sqlite_get_value(uinf, 0, 6))==1));
 
-                // set ERRATTEMPT flag, if user has an entry in attempts log with attemtps > 0
-                xml_set_flag(info_n, "ERRATTEMPT", (atoi_nullsafe(sqlite_get_value(uinf, 0, 8))==1));
+                        // set ERRATTEMPT flag, if user has an entry in attempts log with attemtps > 0
+                        xml_set_flag(info_n, "ERRATTEMPT", (atoi_nullsafe(sqlite_get_value(uinf, 0, 8))==1));
 
-                // set NEVERUSED flag, if login count == 0 and last_accessed == NULL
-                flag = xml_set_flag(info_n, "NEVERUSED", ((atoi_nullsafe(sqlite_get_value(uinf,0, 7))==0)
-                                                          && (sqlite_get_value(uinf, 0, 3) == NULL)));
+                        // set NEVERUSED flag, if login count == 0 and last_accessed == NULL
+                        flag = xml_set_flag(info_n, "NEVERUSED", ((atoi_nullsafe(sqlite_get_value(uinf,0, 7))==0)
+                                                                  && (sqlite_get_value(uinf, 0, 3) == NULL)));
 
-                // set RSETLASTUSED flag, if login count == 0 and last_accessed == NULL
-                xml_set_flag(info_n, "RSETLASTUSED", !flag && (sqlite_get_value(uinf,0,3)) == NULL);
+                        // set RSETLASTUSED flag, if login count == 0 and last_accessed == NULL
+                        xml_set_flag(info_n, "RSETLASTUSED", !flag && (sqlite_get_value(uinf,0,3)) == NULL);
 
-                // set RSETLOGINCNT flag, if login count == 0 and last_accessed != NULL
-                xml_set_flag(info_n, "RSETLOGINCNT", ((atoi_nullsafe(sqlite_get_value(uinf,0, 7))==0)
-                                                      && (sqlite_get_value(uinf,0,3)) != NULL));
+                        // set RSETLOGINCNT flag, if login count == 0 and last_accessed != NULL
+                        xml_set_flag(info_n, "RSETLOGINCNT", ((atoi_nullsafe(sqlite_get_value(uinf,0, 7))==0)
+                                                              && (sqlite_get_value(uinf,0,3)) != NULL));
 
-                if( (getInfo & USERINFO_user) == USERINFO_user ) {
                         sqlite_xml_value(root_n, XML_NODE, "activated", uinf, 0, 1);
                         sqlite_xml_value(root_n, XML_NODE, "deactivated", uinf, 0, 2);
                         info_n = sqlite_xml_value(root_n, XML_NODE, "last_accessed", uinf, 0, 3);
@@ -506,41 +506,102 @@ xmlDoc *eDBadminGetUserInfo(eurephiaCTX *ctx, int getInfo, xmlDoc *srch) {
 
                 if( (getInfo & USERINFO_certs) == USERINFO_certs ) {
                         // Extract certificate info
-                        certinf = sqlite_query(ctx,
-                                               "SELECT depth, digest, common_name, organisation, email, "
-                                               "       c.registered, c.certid,  uc.accessprofile, access_descr,"
-                                               "       fw_profile"
-                                               "  FROM openvpn_certificates c"
-                                               "  LEFT JOIN openvpn_usercerts uc ON (c.certid = uc.certid)"
-                                               "  LEFT JOIN openvpn_accesses a "
-                                               "         ON (uc.accessprofile = a.accessprofile)"
-                                               " WHERE uid = %i ORDER BY c.certid DESC", uid);
+                        qres = sqlite_query(ctx,
+                                            "SELECT depth, digest, common_name, organisation, email, "
+                                            "       c.registered, c.certid,  uc.accessprofile, access_descr,"
+                                            "       fw_profile"
+                                            "  FROM openvpn_certificates c"
+                                            "  LEFT JOIN openvpn_usercerts uc ON (c.certid = uc.certid)"
+                                            "  LEFT JOIN openvpn_accesses a "
+                                            "         ON (uc.accessprofile = a.accessprofile)"
+                                            " WHERE uid = '%i' ORDER BY c.certid DESC", uid);
 
                         info_n = xmlNewChild(root_n, NULL, (xmlChar *) "certificates", NULL);
-                        if( (certinf != NULL) && (sqlite_get_numtuples(certinf) > 0) ) {
+                        if( (qres != NULL) && (sqlite_get_numtuples(qres) > 0) ) {
                                 int i;
                                 xmlNode *cert, *acpr;
 
-                                for( i = 0; i < sqlite_get_numtuples(certinf); i++ ) {
+                                for( i = 0; i < sqlite_get_numtuples(qres); i++ ) {
                                         cert = xmlNewChild(info_n, NULL, (xmlChar *) "certificate", NULL);
 
-                                        sqlite_xml_value(cert, XML_ATTR, "certid",        certinf, 0, 6);
-                                        sqlite_xml_value(cert, XML_ATTR, "depth",         certinf, 0, 0);
-                                        sqlite_xml_value(cert, XML_ATTR, "registered",    certinf, 0, 5);
-                                        sqlite_xml_value(cert, XML_NODE, "digest",        certinf, 0, 1);
-                                        sqlite_xml_value(cert, XML_NODE, "common_name",   certinf, 0, 2);
-                                        sqlite_xml_value(cert, XML_NODE, "organisation",  certinf, 0, 3);
-                                        sqlite_xml_value(cert, XML_NODE, "email",         certinf, 0, 4);
-
-                                        acpr = sqlite_xml_value(cert, XML_NODE, "access_profile", certinf, 0, 8);
-                                        sqlite_xml_value(acpr, XML_ATTR, "accessprofile",        certinf, 0, 7);
-                                        sqlite_xml_value(acpr, XML_ATTR, "fwdestination",        certinf, 0, 9);
+                                        sqlite_xml_value(cert, XML_ATTR, "certid",        qres, 0, 6);
+                                        sqlite_xml_value(cert, XML_ATTR, "depth",         qres, 0, 0);
+                                        sqlite_xml_value(cert, XML_ATTR, "registered",    qres, 0, 5);
+                                        sqlite_xml_value(cert, XML_NODE, "digest",        qres, 0, 1);
+                                        sqlite_xml_value(cert, XML_NODE, "common_name",   qres, 0, 2);
+                                        sqlite_xml_value(cert, XML_NODE, "organisation",  qres, 0, 3);
+                                        sqlite_xml_value(cert, XML_NODE, "email",         qres, 0, 4);
+
+                                        acpr = sqlite_xml_value(cert, XML_NODE, "access_profile", qres, 0, 8);
+                                        sqlite_xml_value(acpr, XML_ATTR, "accessprofile",         qres, 0, 7);
+                                        sqlite_xml_value(acpr, XML_ATTR, "fwdestination",         qres, 0, 9);
                                 }
                         }
 
-                        if( certinf != NULL ) {
-                                sqlite_free_results(certinf);
+                        if( qres != NULL ) {
+                                sqlite_free_results(qres);
+                        }
+                }
+
+                if( (getInfo & USERINFO_lastlog) == USERINFO_lastlog ) {
+                        int i = 0;
+                        xmlNode *lastl = NULL, *sess = NULL, *tmp1 = NULL, *tmp2 = NULL;
+
+                        qres = sqlite_query(ctx,
+                                            "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"
+                                            "  FROM openvpn_lastlog ll"
+                                            "  LEFT JOIN openvpn_usercerts USING (uid, 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);
+
+                        if( qres == NULL ) {
+                                eurephia_log(ctx, LOG_ERROR, 0, "Quering the lastlog failed");
+                                xmlFreeDoc(doc);
+                                return NULL;
+                        }
+
+                        lastl = xmlNewChild(root_n, NULL, (xmlChar *) "lastlog", NULL);
+                        for( i = 0; i < sqlite_get_numtuples(qres); i++ ) {
+
+                                sess = xmlNewChild(lastl, NULL, (xmlChar*) "session", NULL);
+                                sqlite_xml_value(sess, XML_ATTR, "llid",                  qres, i, 0);
+                                sqlite_xml_value(sess, XML_ATTR, "session_status",        qres, i, 8);
+                                sqlite_xml_value(sess, XML_ATTR, "session_duration",      qres, i, 12);
+                                sqlite_xml_value(sess, XML_NODE, "sessionkey",            qres, i, 9);
+                                sqlite_xml_value(sess, XML_NODE, "login",                 qres, i, 10);
+                                sqlite_xml_value(sess, XML_NODE, "logout",                qres, i, 11);
+                                sqlite_xml_value(sess, XML_NODE, "session_closed",        qres, i, 13);
+
+                                tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "connection", NULL);
+                                sqlite_xml_value(tmp1, XML_ATTR, "bytes_sent",            qres, i, 14);
+                                sqlite_xml_value(tmp1, XML_ATTR, "bytes_received",        qres, i, 15);
+                                sqlite_xml_value(tmp1, XML_NODE, "protocol",              qres, i, 2);
+                                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);
+
+                                tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "certificate", NULL);
+                                sqlite_xml_value(tmp1, XML_ATTR, "certid",                qres, i, 1);
+                                sqlite_xml_value(tmp1, XML_ATTR, "uicid",                 qres, i, 16);
+                                sqlite_xml_value(tmp1, XML_ATTR, "depth",                 qres, i, 20);
+                                sqlite_xml_value(tmp1, XML_NODE, "digest",                qres, i, 21);
+                                sqlite_xml_value(tmp1, XML_NODE, "common_name",           qres, i, 22);
+                                sqlite_xml_value(tmp1, XML_NODE, "organisation",          qres, i, 23);
+                                sqlite_xml_value(tmp1, XML_NODE, "email",                 qres, i, 24);
+
+                                tmp2 = sqlite_xml_value(tmp1, XML_NODE, "access_profile", qres, i, 18);
+                                sqlite_xml_value(tmp2, XML_ATTR, "accessprofile",         qres, i, 17);
+                                sqlite_xml_value(tmp2, XML_ATTR, "fwdestination",         qres, i, 19);
                         }
+                        xmlSaveFormatFileEnc("-", doc, "UTF-8", 1);
                 }
 
                 return doc;