Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

capture currently logged in users from sql Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 28, 2015 11:48 AM
Points: 39, 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
Post #1474630
Posted Wednesday, July 17, 2013 8:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:29 AM
Points: 14,469, Visits: 38,077
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!
Post #1474638
Posted Wednesday, July 17, 2013 9:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 2,056, Visits: 6,895
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
Post #1474672
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse