July 22, 2011 at 2:20 am
Hi
I need help writing a question that give me usernames that have logged into more than 3 different computers whitin 5 minutes
I only want to know if the user logged in to diffrent computers , not the same computer 3 times...
I have a database with:
computerName, userName , logonTime (date and time)
And running MSSQL 2008R2
easy? 🙂
//bjorn
July 22, 2011 at 3:50 am
bjorn.gabrielsson (7/22/2011)
easy? 🙂
Well... Not me, but the solution is.
N 56°04'39.16"
E 12°55'05.25"
July 22, 2011 at 4:01 am
Here is a solution for you
CREATE TABLE#Sample
(
ComputerName VARCHAR(100),
UserName VARCHAR(100),
LogonTime DATETIME
)
DECLARE@a INT = 100000
INSERT#Sample
SELECT TOP(@a)ABS(CHECKSUM(NEWID())) % 20 AS ComputerName,
ABS(CHECKSUM(NEWID())) % 20 AS UserName,
DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % 86400, '20110722') AS LogonTime
FROMmaster..spt_values AS v1
CROSS JOINmaster..spt_values AS v2
CREATE NONCLUSTERED INDEX IX_Sample ON #Sample (UserName) INCLUDE (LogonTime, ComputerName)
-- SwePeso
;WITH cteSource(UserName, LogonTime, ComputerName, maxLogonTime)
AS (
SELECTs.UserName,
s.LogonTime,
f.ComputerName,
f.MaxLogonTime
FROM#Sample AS s
CROSS APPLY(
SELECTw.ComputerName,
MAX(w.LogonTime) AS MaxLogonTime
FROM#Sample AS w
WHEREw.UserName = s.UserName
AND w.LogonTime >= s.LogonTime
AND w.LogonTime < DATEADD(MINUTE, 5, s.LogonTime)
GROUP BYw.ComputerName
) AS f(ComputerName, MaxLogonTime)
)
SELECTUserName,
LogonTime,
MAX(MaxLogonTime) AS MaxLogonTime
FROMcteSource
GROUP BYUserName,
LogonTime
HAVINGCOUNT(*) >= 3
DROP TABLE#Sample
N 56°04'39.16"
E 12°55'05.25"
July 22, 2011 at 5:14 am
Thanx,
But i don´t think this solution works.
It may be a bit to complicated for me, i cant quite make out how it works.
The problem is that usernames is shown even if they have not logged in 3 or more times within 5 min and usernames is shown multiple times.
I will give som more detail to what i want to do:
From a log with the fields : username, computername and logontime i want to show the usernames that have loged on to 3 or more computers within 5 minutes.
I would like to set a timespan , the db is quite large.. so lets sa within 5min the last 60 min
I only want to see the username once, and it would be nice to see the login count
July 22, 2011 at 5:52 am
Start with the simple things to see if the algorithm calculates the correct usernames.
Then, and only then, streamline things to suit your presentation needs.
The cross apply seeks the table for every row within, for all other logons made by same username the following 5 minutes.
The cross apply then groups and returns with unique computernames that have been used for same username the following 5 minutes.
The I group by the username and logontime used as origin for seek.
If the count is >= 3, then there have been at least 3 different computer names for same username in a 5 minute period.
N 56°04'39.16"
E 12°55'05.25"
July 22, 2011 at 5:56 am
Using the same sample data and table as before...
-- User Supplied Parameters
DECLARE@UseOnlyTheLastMinutes SMALLINT = 60,
@LogonInterval TINYINT = 5,
@NumberOfLogons TINYINT = 3
-- SwePeso
;WITH cteSource(UserName, LogonTime, ComputerName, maxLogonTime)
AS (
SELECTs.UserName,
s.LogonTime,
f.ComputerName
FROM#Sample AS s
CROSS APPLY(
SELECTw.ComputerName
FROM#Sample AS w
WHEREw.UserName = s.UserName
AND w.LogonTime >= s.LogonTime
AND w.LogonTime < DATEADD(MINUTE, @LogonInterval, s.LogonTime)
GROUP BYw.ComputerName
) AS f(ComputerName)
WHEREs.LogonTime >= DATEADD(MINUTE, -@UseOnlyTheLastMinutes, GETDATE())
)
SELECT DISTINCTUserName
FROMcteSource
GROUP BYUserName,
LogonTime
HAVINGCOUNT(*) >= @NumberOfLogons
N 56°04'39.16"
E 12°55'05.25"
July 24, 2011 at 1:57 am
Thank you, i think i got it now..
I will implement it into my system after the summer.
//bjorn
July 24, 2011 at 3:20 am
No problem.
Your name sound Swedish. Have you considered joining you local PASS user group? In Stockholm there is SQLUG and in Malmö we have Scania.
N 56°04'39.16"
E 12°55'05.25"
July 24, 2011 at 4:07 am
yes, swedish
I will check them out..
//bjorn
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply