半径数据日志搜索验证事件
我正在使用RADIUS进行登录，并设置MySQL来记录所有会计数据和Auth Events。我感兴趣的有2个主表，第一个是RadPostauth表，如下所示，我将获得以下数据：
SELECT a.`authdate` as startdate, a.`username`, if (a.`reply` = "Access-Accept", "Login OK", "Login Fail") as Login, a.`macaddress`,a.`circuitid` FROM `radpostauth` as a WHERE a.`username` LIKE 'username@realm' AND a.`authdate` >= '2022-01-01 08:11:05' ORDER BY a.`authdate` DESC;
+---------------------+-------------------------+----------+-------------------------------------+----------------------------------+
| startdate | username | Login | macaddress | circuitid |
+---------------------+-------------------------+----------+-------------------------------------+----------------------------------+
| 2022-04-12 17:04:53 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-04-07 23:47:51 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-04-07 15:35:54 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-04-01 02:28:54 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-08 12:56:36 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 10:31:33 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 10:22:50 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 10:19:20 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 09:47:49 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 09:21:04 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 09:14:38 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 08:17:28 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-06 22:42:31 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-06 21:12:49 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-06 18:15:12 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-06 18:03:07 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-06 17:56:32 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
这是棘手的地方。我还有另一个名为RadAcct的表，该表包含每个用户名的会计更新。我想在radAcct中检查的常见链接字段是：
`radacct`.`username` = `radpostauth`.`username`
`radacct`.`acctstarttime` = `radpostauth`.`authdate`
这是我的radacct表中的一些示例数据。 RadAcct条目大约每60分钟写每60分钟，除非ACCTTERMINATATASES不是“ Acct-uptate”条目。每个条目在名为acctSessionId的字段名称中都有一个唯一的会话ID。我只为每个acctsessiond提供了第一个和最后一个条目，以使帖子保持较小。您可以看到AcctStarttime总是与RadPostauth表上的StartDate相同：
+---------------------+---------------------+---------------+--------------------+-------------------------+
| acctstarttime | acctstoptime | acctsessionid | acctterminatecause | username |
+---------------------+---------------------+---------------+--------------------+-------------------------+
| 2022-04-14 07:41:22 | 2022-04-14 08:44:21 | 00179D24 | Acct-Update | username@realm |
| 2022-04-12 16:14:09 | 2022-04-12 17:02:52 | 000516D4 | Port-Error | username@realm |
| 2022-04-07 23:47:51 | 2022-04-08 00:51:23 | 000516D4 | Acct-Update | username@realm |
| 2022-04-07 22:51:31 | 2022-04-07 23:26:40 | 0005158F | Port-Error | username@realm |
| 2022-04-07 15:35:54 | 2022-04-07 16:39:31 | 0005158F | Acct-Update | username@realm |
| 2022-04-07 14:54:10 | 2022-04-07 15:31:37 | 000498C5 | Port-Error | username@realm |
| 2022-04-01 02:28:54 | 2022-04-01 03:31:26 | 000498C5 | Acct-Update | username@realm |
| 2022-04-01 01:40:25 | 2022-04-01 02:27:39 | 000302D3 | Port-Error | username@realm |
| 2022-03-08 12:56:37 | 2022-03-08 14:01:10 | 000302D3 | Acct-Update | username@realm |
| 2022-03-08 12:19:07 | 2022-03-08 12:56:25 | 001530F5 | Port-Error | username@realm |
| 2022-03-07 10:31:33 | 2022-03-07 11:32:10 | 001530F5 | Acct-Update | username@realm |
这是我最终想成为我的最终显示 - 需要对AcctSessionId进行某种检查，而它与AcctStarttime匹配，并且可以找到ACCTSESSIONID，并且可以找到。最大（AcctStoptime）用于acctSessionId，然后返回AcctStoptime和acctterminatauta。搜索Max（AcctStoptime）时，当前的会话将使ACCTSTOPTIME无效，因此可以返回。这是我追求的示例：
+---------------------+-------------------------+----------+-------------------------------------+----------------------------------+-----------------------+----------------------+
| startdate | username | Login | macaddress | circuitid | acctstoptime | acctterminatecause |
+---------------------+-------------------------+----------+-------------------------------------+----------------------------------+-----------------------+----------------------+
| 2022-04-12 17:04:53 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | NULL | NULL |
| 2022-04-07 23:47:51 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | 2022-04-12 17:02:52 | Port-Error |
| 2022-04-07 15:35:54 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | 2022-04-07 23:26:40 | Port-Error |
| 2022-04-01 02:28:54 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | 2022-04-07 15:31:37 | Port-Error |
| 2022-03-08 12:56:36 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | 2022-04-01 02:27:39 | Port-Error |
| 2022-03-07 10:31:33 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | 2022-03-08 12:56:25 | Port-Error |
I am using Radius for logging and have MySQL set up to log all accounting data and auth events. There are 2 main tables that I am interested in, the first is the radpostauth table as follows where I am getting the following data:
SELECT a.`authdate` as startdate, a.`username`, if (a.`reply` = "Access-Accept", "Login OK", "Login Fail") as Login, a.`macaddress`,a.`circuitid` FROM `radpostauth` as a WHERE a.`username` LIKE 'username@realm' AND a.`authdate` >= '2022-01-01 08:11:05' ORDER BY a.`authdate` DESC;
+---------------------+-------------------------+----------+-------------------------------------+----------------------------------+
| startdate | username | Login | macaddress | circuitid |
+---------------------+-------------------------+----------+-------------------------------------+----------------------------------+
| 2022-04-12 17:04:53 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-04-07 23:47:51 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-04-07 15:35:54 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-04-01 02:28:54 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-08 12:56:36 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 10:31:33 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 10:22:50 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 10:19:20 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 09:47:49 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 09:21:04 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 09:14:38 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-07 08:17:28 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-06 22:42:31 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-06 21:12:49 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-06 18:15:12 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-06 18:03:07 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
| 2022-03-06 17:56:32 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX |
Here is where it gets tricky. I have another table called radacct which holds the accounting updates for each username. The common linking fields I want to check against in radacct are:
`radacct`.`username` = `radpostauth`.`username`
`radacct`.`acctstarttime` = `radpostauth`.`authdate`
Here is some sample data from my radacct table. Radacct entries are written roughly every 60 minutes or unless acctterminatecause isn't an 'Acct-Update' entry. Each entry has a unique session id in the field name called acctsessionid. I've only included the first and last entry for each acctsessionid to keep the post small. You can see that acctstarttime is always the same as startdate on the radpostauth table:
+---------------------+---------------------+---------------+--------------------+-------------------------+
| acctstarttime | acctstoptime | acctsessionid | acctterminatecause | username |
+---------------------+---------------------+---------------+--------------------+-------------------------+
| 2022-04-14 07:41:22 | 2022-04-14 08:44:21 | 00179D24 | Acct-Update | username@realm |
| 2022-04-12 16:14:09 | 2022-04-12 17:02:52 | 000516D4 | Port-Error | username@realm |
| 2022-04-07 23:47:51 | 2022-04-08 00:51:23 | 000516D4 | Acct-Update | username@realm |
| 2022-04-07 22:51:31 | 2022-04-07 23:26:40 | 0005158F | Port-Error | username@realm |
| 2022-04-07 15:35:54 | 2022-04-07 16:39:31 | 0005158F | Acct-Update | username@realm |
| 2022-04-07 14:54:10 | 2022-04-07 15:31:37 | 000498C5 | Port-Error | username@realm |
| 2022-04-01 02:28:54 | 2022-04-01 03:31:26 | 000498C5 | Acct-Update | username@realm |
| 2022-04-01 01:40:25 | 2022-04-01 02:27:39 | 000302D3 | Port-Error | username@realm |
| 2022-03-08 12:56:37 | 2022-03-08 14:01:10 | 000302D3 | Acct-Update | username@realm |
| 2022-03-08 12:19:07 | 2022-03-08 12:56:25 | 001530F5 | Port-Error | username@realm |
| 2022-03-07 10:31:33 | 2022-03-07 11:32:10 | 001530F5 | Acct-Update | username@realm |
This is what I want to end up with as my final display - there needs to be some sort of checking against acctsessionid where it matches against acctstarttime and username, finds the max(acctstoptime) for the acctsessionid and then returns acctstoptime and acctterminatecause. A current session will have NULL for acctstoptime when searching for max(acctstoptime) so that's fine to return. Here is an example of what I am chasing:
+---------------------+-------------------------+----------+-------------------------------------+----------------------------------+-----------------------+----------------------+
| startdate | username | Login | macaddress | circuitid | acctstoptime | acctterminatecause |
+---------------------+-------------------------+----------+-------------------------------------+----------------------------------+-----------------------+----------------------+
| 2022-04-12 17:04:53 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | NULL | NULL |
| 2022-04-07 23:47:51 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | 2022-04-12 17:02:52 | Port-Error |
| 2022-04-07 15:35:54 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | 2022-04-07 23:26:40 | Port-Error |
| 2022-04-01 02:28:54 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | 2022-04-07 15:31:37 | Port-Error |
| 2022-03-08 12:56:36 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | 2022-04-01 02:27:39 | Port-Error |
| 2022-03-07 10:31:33 | username@realm | Login OK | client-mac-address=xxxx.xxxx.xxxx | circuit-id-tag=XXXXXXXXXXXXXXX | 2022-03-08 12:56:25 | Port-Error |
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论