'Radius data log search for auth events
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 |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|