SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


capture currently logged in users from sql


capture currently logged in users from sql

Author
Message
huum
huum
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 153
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28686 Visits: 39977
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.B
Alan.B
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5268 Visits: 7735
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)
SELECT DB_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)
SELECT DB_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
SELECT DBName,
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.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search