101
0/304
Loading content...
A platform security team audits session telemetry to detect account sharing and session hijacking.
Table: log_info
Notes:
Task: Return accounts that were active from two different IP addresses at any overlapping instant.
Overlap definition:
Output requirements:
Supported submission environments:
log_info:
| account_id | ip_address | login | logout |
|------------|------------|---------------------|---------------------|
| 1 | 101 | 2024-03-01 09:00:00 | 2024-03-01 09:30:00 |
| 1 | 202 | 2024-03-01 08:00:00 | 2024-03-01 11:30:00 |
| 2 | 301 | 2024-03-01 20:30:00 | 2024-03-01 22:00:00 |
| 2 | 302 | 2024-03-02 20:30:00 | 2024-03-02 22:00:00 |
| 3 | 401 | 2024-03-01 16:00:00 | 2024-03-01 16:59:59 |
| 3 | 402 | 2024-03-01 17:00:00 | 2024-03-01 17:59:59 |
| 4 | 501 | 2024-03-01 16:00:00 | 2024-03-01 17:00:00 |
| 4 | 502 | 2024-03-01 17:00:00 | 2024-03-01 17:59:59 |[
{"account_id":1},
{"account_id":4}
]Account 1 has fully overlapping sessions on different IPs. Account 4 overlaps at the exact boundary timestamp 17:00:00, which is also considered overlap.
log_info:
| account_id | ip_address | login | logout |
|------------|------------|---------------------|---------------------|
| 7 | 7001 | 2024-06-10 10:00:00 | 2024-06-10 10:45:00 |
| 7 | 7002 | 2024-06-10 10:45:01 | 2024-06-10 11:15:00 |
| 8 | 8101 | 2024-06-11 09:00:00 | 2024-06-11 12:00:00 |
| 8 | 8102 | 2024-06-11 11:59:59 | 2024-06-11 13:00:00 |[
{"account_id":8}
]Account 7 has a one-second gap, so no overlap. Account 8 overlaps for one second from 11:59:59 to 12:00:00 and is flagged.
log_info:
| account_id | ip_address | login | logout |
|------------|------------|---------------------|---------------------|
| 11 | 9001 | 2024-07-01 09:00:00 | 2024-07-01 10:00:00 |
| 11 | 9001 | 2024-07-01 09:15:00 | 2024-07-01 09:45:00 |
| 11 | 9002 | 2024-07-01 10:00:01 | 2024-07-01 10:30:00 |
| 12 | 9101 | 2024-07-02 14:00:00 | 2024-07-02 15:00:00 |
| 12 | 9102 | 2024-07-02 14:30:00 | 2024-07-02 14:40:00 |
| 12 | 9102 | 2024-07-02 14:30:00 | 2024-07-02 14:40:00 |[
{"account_id":12}
]Overlapping sessions from the same IP do not trigger a flag for account 11. Account 12 has a different-IP overlap and duplicate rows still yield a single account in output.
Constraints