Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Wednesday, April 9, 2014 1:40 PM
Points: 39, Visits: 146
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: Yesterday @ 3:48 PM
Points: 12,908, Visits: 32,005
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1474638
Posted Wednesday, July 17, 2013 9:07 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:18 AM
Points: 562, Visits: 2,617
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1474672
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse