如何使用 SQL 语句查询 JumpServer 相关信息？

逃离我推掉我的手 2024-03-27 10:59 58阅读 0赞

> 官网地址：[JumpServer - 开源堡垒机 - 官网][JumpServer - _ -]
> 
> 在线电话：400-052-0755
> 
> 技术支持：[JumpServer 技术咨询][JumpServer]

--------------------

#### 1. 概述 ####

本篇文章主要介绍如何通过 SQL 语句查询页面查询不到的 JumpServer 的相关信息

#### 2. SQL语句 ####

2.1 查询七天内没有登陆 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;

![676cb059ec2a7fa3ee903e7f0ad3632a.png][]

2.2 查询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;

![840fb42e3b498ef14546f5d1ea616f5a.png][]

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

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;

![7dab98527d7a693ad31d7fa6ac7d9722.png][]

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

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

![e92c350ed520c0668c6e5115834aa0a2.png][]

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

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

![aa0fee36f028eab7f283e4ebb33d7220.png][]

2.6 查询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;

![3c27fdd038841bccf0fb2f0d6dc12b49.png][]

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

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;

![85c0520a5600cb71002f2c3438a607f6.png][]

[JumpServer - _ -]: https://jumpserver.org/
[JumpServer]: https://jinshuju.net/f/sQ91MK
[676cb059ec2a7fa3ee903e7f0ad3632a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/a4515783fc9e47ed8749ef1baac1c620.png
[840fb42e3b498ef14546f5d1ea616f5a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/9a38fb7287724c59bb2f385720825109.png
[7dab98527d7a693ad31d7fa6ac7d9722.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/a893a962123e469294e5b6343313bffc.png
[e92c350ed520c0668c6e5115834aa0a2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/88ba6800675f491aa4b8b7da13599527.png
[aa0fee36f028eab7f283e4ebb33d7220.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/414951923e104aab8da96265741dfa2d.png
[3c27fdd038841bccf0fb2f0d6dc12b49.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/9f3f9ba3053141789201689246c5ea09.png
[85c0520a5600cb71002f2c3438a607f6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/96a9219454ca4d95be25d15c0d36b913.png