3

When user account is assigned an IP address, I create a table entry for the account/IP mapping. This data in the table is updated every 5 minutes. Right now I'm only keeping real-time data but I need to figure out how to store this historically.

So let's say at 09:00:00 I have the following:

10.10.100.20   dom\jsmith
10.20.5.30     dom\psanders
10.90.220.5    dom\kwest

This all stays the same for the next 45 minutes and then at 09:50:00

10.10.100.30   dom\jsmith
10.10.100.20   dom\jsmith
10.20.5.30     dom\bpeters
10.90.220.5    dom\sparker

In this case jsmith logged into a term server so his account maps to a second IP. The more difficult problem for me is bpeters shares a desk with psanders and the same for kwest and sparker.

Goals:

  • Recall who was logged in on 8/10 at 9:00.
  • Recall what IPs did jsmith have between two dates/time periods
  • Recall who was assigned a given IP on a given date

I thought about creating a table for each time period but over a long period of time that's a lot of tables and probably really inefficient. The number of IPs to track is probably 20k and individual accounts 15k.

As a novice to DB engineering I don't know if I'm missing a simple solution here or if I should invest a good amount of time into DB schema design.

0

Shouldn't be too difficult, the below assumes you are using SQL Server. My design would be to have a table called "CurrentLogOn", which would look like:

LogOnID (PK - AutoIncrementKey)
UserName
IPAddress
LogOnTime (DateTime, default to GETDATE())
LogOffTime (DateTime, no default)

You would then want a second table called "HistoricalLogOn", which would look similar to the first. The reason to separate the tables would be so CurrentLogOn table operates with the smallest number of records for performance reasons.

HistoricalLogOnID (PK - AutoIncrementKey)
LogOnID
UserName
IPAddress
LogOnTime (DateTime, default to GETDATE())
LogOffTime (DateTime, no default)

I would then place a trigger on the CurrentLogOn table. The trigger would be an INSERT trigger (would trigger when a record is inserted) and would look like:

CREATE TRIGGER UpdateUserLogOn
AFTER INSERT
AS
UPDATE CurrentLogOn
SET LogOffTime = GETDATE()
WHERE UserName = (SELECT UserName FROM Inserted)
AND LogOffTime IS NULL
END

This should update the "old" logon every time the user logs in again. Note that this assumes a record is added to the table each time a user logs on.

Lastly, at the end of the day or at some point during the day, you will want to move all "old" data into the history table. To do so you could run a job that does two operations:

INSERT INTO HistoricalLogOn (LogOnID, UserName, IPAddress, LogOnTime, LogOffTime)
SELECT LogOnID, UserName, IPAddress, LogOnTime, LogOffTime
FROM CurrentLogOn
WHERE LogOffTime IS NOT NULL;
GO
DELETE FROM CurrentLogOn
WHERE LogOffTime IS NOT NULL;
GO

This last part will insert the old records into the history table and subsequently delete them from the Current table.

  • Many thanks! Exactly what I was looking for. This looks to be a solid approach. I'll give this implementation a shot. – idleline Aug 26 '14 at 19:23
  • Just re-reading your requirements, it might not be EXACTLY what you are looking for as it essentially logs off a person when they log on from another machine. This may not be accurate as they may be logging on to another machine at the same time. Without a log off event triggering an update on the CurrentLogOn table though (which you didn't describe if one exists), it would be difficult to track log offs. – blobbles Aug 27 '14 at 0:07
0

Looking at the data you posted, what would be the expected result from your point of view? The fact that users share the same desk/IP shouldn't be a problem as long as both users are reported while they're logged in. You could either use your current table design (a single table with Row_Id, update_time, IP and User) or an aggregated table with Row_Id, IP, User, login_tim, and logout_time.

I'd decide based on the expected number of unchanged logins. For example, if users usually login in the morning and logout in the afternoon, then I'd go for the aggregated version. But if the scenario is more like a user to log in and log out within a short period of time and a high frequeny, then I'd go for the current design since aggragation wouldn't reduce the number of rows significantly.

How many rows do you expect per day (given your current design)?

  • The expected result would be accurate (within 5 minutes) accounting of which user was assigned what IP at a given point in time. Realtime data has 20-25k records. Unfortunately I don't know how many records would change in a given day (unique user to IP mappings). There are some service account entries I would need to ignore and then start tracking that information. – idleline Aug 26 '14 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.