概述

本篇文章主要介绍如何通过 SQL 对JumpServer数据库进行查询操作，通过直接対数据库进行查询操作可以获取更多在页面上无法直接查询到的相关信息。

sql语句

查询七天内没有登陆 JumpServer 的僵尸用户；

select username,name from users_user where TO_DAYS(NOW()) - TO_DAYS(users_user.last_login) >= 7 or last_login is NULL and is_service_account=0;

运行结果：

mysql> use jumpserver;
Database changed
mysql> select username,name from users_user where TO_DAYS(NOW()) - TO_DAYS(users_user.last_login) >= 7 or last_login is NULL and is_service_account=0;
+---------------+----------------------+
| username      | name                 |
+---------------+----------------------+
| zuzhiuser     | zuzhiuser            |
| admin         | Administrator        |
| appuser       | appuser              |
| tester        | tester               |
| administrator | window-administrator |
| apppointtest  | apppointtest         |
+---------------+----------------------+
6 rows in set (0.00 sec)

mysql>

查询30天内没有登陆 JumpServer 的僵尸用户；

select username,name from users_user where TO_DAYS(NOW()) - TO_DAYS(users_user.last_login) >= 30 or last_login is NULL and is_service_account=0;

运行结果：

mysql> select username,name from users_user where TO_DAYS(NOW()) - TO_DAYS(users_user.last_login) >= 30 or last_login is NULL and is_service_account=0;
+---------------+----------------------+
| username      | name                 |
+---------------+----------------------+
| administrator | window-administrator |
+---------------+----------------------+
1 row in set (0.00 sec)

查询没有登录过资产的僵尸用户；

SELECT username,email from users_user where users_user.id not in(SELECT DISTINCT REPLACE(terminal_session.user_id,'-','') FROM terminal_session) and is_service_account=0;

运行结果：

mysql> SELECT username,email from users_user where users_user.id not in(SELECT DISTINCT REPLACE(terminal_session.user_id,'-','') FROM terminal_session) and is_service_account=0;
+---------------+-----------------------+
| username      | email                 |
+---------------+-----------------------+
| zuzhiuser     | commonuser1@123.com   |
| appuser       | appuser@123.com       |
| tester        | tester@123.com        |
| administrator | yan.jin@fit2cloud.com |
+---------------+-----------------------+
4 rows in set (0.03 sec)

查询日志统计时间内没有登陆过的资产

select id, name from assets_asset where id not in(SELECT distinct replace(terminal_session.asset_id,'-','') FROM terminal_session);

运行结果：

mysql> select id,name from assets_asset where id not in(SELECT distinct replace(terminal_session.asset_id,'-','') FROM terminal_session);
+----------------------------------+----------------------+
| id                               | name                 |
+----------------------------------+----------------------+
| 5be438ed03414aa0b2cdfb050a539c2a | 打印机               |
| ce39236406184ca49de366c2102cf81b | 10.1.13.75-RemoteAPP |
| a10ed8d26ab243a9b17b7dae518e9898 | server-YZJ01         |
+----------------------------------+----------------------+
3 rows in set (0.03 sec)

查询近30天内的登录的用户与用户登录的次数

select username,count( * ) AS 'numbertime' from audits_userloginlog where TO_DAYS( now( ) ) - TO_DAYS( datetime ) <= 30 GROUP BY username ORDER BY 'numbertime' DESC;

运行结果：

mysql> select username,count( * ) AS 'numbertime' from audits_userloginlog where TO_DAYS( now( ) ) - TO_DAYS( datetime ) <= 30 GROUP BY username ORDER BY 'numbertime' DESC;
+--------------+------------+
| username     | numbertime |
+--------------+------------+
| admin        |         27 |
| appointtest  |          3 |
| tester       |          7 |
| zuzhiuser    |          6 |
| apppointtest |          6 |
| appuser      |          4 |
+--------------+------------+
6 rows in set (0.24 sec)

查询30天内的资产登录与资产登录的次数

select asset,count(*) as number from terminal_session where TO_DAYS( now() ) - TO_DAYS(date_end) <=30 GROUP BY asset order by count(*) DESC;

运行结果：

mysql> select asset,count(*) as number from terminal_session where TO_DAYS( now() ) - TO_DAYS(date_end) <=30 GROUP BY asset order by count(*) DESC;
+------------------------------------+--------+
| asset                              | number |
+------------------------------------+--------+
| TestServer10.1.13.210(10.1.13.210) |      5 |
| Remote-App(10.1.13.2)              |      4 |
| win-app2012(10.1.13.52)            |      4 |
| 10.1.13.75-RemoteAPP(10.1.13.75)   |      1 |
| JS-RemoteAPPServer(10.1.13.2)      |      1 |
| NFS-server(10.1.13.138)            |      1 |
+------------------------------------+--------+
6 rows in set (0.23 sec)

统计每个组织中的资产数量

SELECT t.NAME, COUNT(*) FROM assets_asset a, orgs_organization t WHERE LEFT ( a.org_id, 8 ) = LEFT ( t.id, 8 ) GROUP BY t.NAME ORDER BY COUNT(*) DESC;

运行结果：

mysql> SELECT t.NAME, COUNT(*) FROM assets_asset a, orgs_organization t WHERE LEFT ( a.org_id, 8 ) = LEFT ( t.id, 8 ) GROUP BY t.NAME ORDER BY COUNT(*) DESC;
+-----------+----------+
| NAME      | COUNT(*) |
+-----------+----------+
| SYSTEM    |        5 |
| Default   |        5 |
| testzuzhi |        1 |
+-----------+----------+
3 rows in set (0.06 sec)