capture currently logged in users from sql

Question

capture currently logged in users from sql

huum

Say Hey Kid

Points: 693
More actions
July 17, 2013 at 7:55 am

#277607

I want to capture users logged int o my database from 9-5 and then get the average per day.
can i please have the guidence

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

huum (7/17/2013)
I want to capture users logged int o my database from 9-5 and then get the average per day.
can i please have the guidence

users don't actually login at a database level.

their LOGIN logs into the server, and they may potentially use multipel databases as USERS.

throughout the day you can use something like sp_who2 or sp_whoisactive to see who is currently in.

you can create an extended event to monitor activity on a per-database basis, but you need to know what you really want to monitor; # of connections vs # of users.

You could use a trace to do the same thing, but eventually, extended events will fully replace traces in the future; i believe i read that traces are expected to be deprecated in favor of extended events.

Now what is it you really want to track?

For Example, i have SSMS open , with 6 tabs open on the SandBox database , so that's six spids, all on the same database, but it's still just me...do i count as one user or 6 connections?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

You can do something like what I did below. I'm not 100% clear about what you are trying to do but this should help....

You would have an agent job run frequently (say, every minute) and populate a login table. Then you would create a view against the table that collects the login info (I'm using a temp table called #loginInfo in my example). You could use that view for whatever metrics you are looking for.

-- (1) Create a job that collects login info like so (this would need to run frequently)

IF OBJECT_ID('tempdb..#loginInfo') IS NULL

CREATE TABLE #loginInfo

(event_id int identity primary key,

DBName varchar(100) not null,

LoginName varchar(100) not null,

LoginTime smalldatetime);

-- (2) variable or parameter for the db you want to track

DECLARE @myDB varchar(100)='ajbTest'

IF NOT EXISTS (SELECT * FROM #loginInfo)--If the table is empty

INSERT INTO #loginInfo (DBName, LoginName, LoginTime)

SELECTDB_NAME(dbid) AS DBName,

loginame AS LoginName,

CAST(login_time AS smalldatetime) AS LoginTime

FROM sys.sysprocesses

WHERE DB_NAME(dbid)=@myDB

GROUP BY dbid, loginame, CAST(login_time AS smalldatetime)

ELSE --if the table is not empty

INSERT INTO #loginInfo (DBName, LoginName, LoginTime)

SELECTDB_NAME(dbid) AS DBName,

loginame AS LoginName,

CAST(login_time AS smalldatetime) AS LoginTime

FROM sys.sysprocesses

WHERE DB_NAME(dbid)=@myDB

GROUP BY dbid, loginame, CAST(login_time AS smalldatetime)

EXCEPT

SELECTDBName,

LoginName,

LoginTime

FROM #loginInfo;

GO

--Captured data (included for review)

SELECT * FROM #loginInfo

-- (3) Create a view that tracks connections between 9AM and 5PM

SELECT DBName, LoginName, COUNT(DBName) AS TotalConnections

FROM #loginInfo

WHERE DATEPART(HOUR, loginTime)>=9 AND DATEPART(HOUR, loginTime)<=17

GROUP BY DBName, LoginName

Again, this is not perfect but my get you in the right direction.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001