to track how many requests come from one login in a specific period of time..

in the future, on next week Monday, between 9-11am, I want to see how many times a sql login requests are serviced by the sql engine. is it possible to capture this through profiler or any other means?

how can I set this up to capture?

thanks-
LVL 5
25112Asked:
Who is Participating?
 
nemws1Database AdministratorCommented:
I believe so.  Of course, if a package logs in multiple times, that will still get logged that way.  It will be much easier for you to count logins/outs during certain time periods than using Profiler.
0
 
nemws1Database AdministratorCommented:
Through profiler, do the following:

1) Run Profiler, connect to your database.
2) In the Trace Properties dialog, de-select everything except "Audit Login"
3) Click on "Column Filters" (lower-right corner)
4) Click on "LoginName" in the left-hand list
5) Expand (click on the "+") by "Like"
6) In the empty field, enter in the username you want to track (make sure to include the domain if its an AD user)
7) Click "OK"
8) Before 9AM, Click "Run"
9) After 11AM, click on the red "stop" square in the main window.

Now you can just count the lines.  I usually do a "File" -> "Save As" -> "Trace Table" and then you can do SELECT() queries on the trace.  Like COUNT(*), or limit by hostname or time period (like 9:00am to 9:30am), etc.
0
 
25112Author Commented:
ok- thanks.

now, is there a way in that to distinguish between
successful connection attempts
vs
failed ones?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
nemws1Database AdministratorCommented:
Same steps, but after selecting "Audit Login" click on "Show All events" (lower right corner) and select "Audit Login Failed" under the "Security Audit" selection.

If this is something you want to do long-term, it would be better to turn on full login logging.  In SSMS, right click on the database name in the Object Explorer and select "Properties".  Select the "Security" option in the left-hand list and then select "Both failed and successful logins" under Login auditing.  You will have to restart the SQL Server process for this change to go into effect.  However, all logins, successful or not, will now be logged to the SQL Server log.  You can then query this log with a query like below.  Enter in something like 'login' for the value for @Search1, leave @log_type alone (set to 1).  The large the value for @log_depth, the further back into your logs it searches (depending on how often you rotate your log files - I rotate mine weekly).

USE master;
GO

SET NOCOUNT ON;

DECLARE @log_type INT;
DECLARE @log_depth INT;
DECLARE @Search1 VARCHAR(200);
DECLARE @Search2 VARCHAR(200);

SET @log_type = 1;				-- 1=error log, 2=agent log
SET @log_depth = 1;				-- max log + 1 to search
SET @Search1 = '';
SET @Search2 = '';

DECLARE @logfiles TABLE (
	fileno INT NULL
	, filedate DATETIME NULL
	, filesize INT NULL
);

DECLARE @log TABLE (
	LogDate DATETIME NULL
	, ErrorLevel VARCHAR(100) NULL
	, LogText VARCHAR(max) NULL
);

INSERT INTO @logfiles
EXEC sp_enumerrorlogs
	@p1 = @log_type
;

DECLARE lfcur CURSOR LOCAL READ_ONLY FORWARD_ONLY
FOR
SELECT fileno
FROM @logfiles
WHERE fileno < @log_depth
ORDER BY fileno
;

OPEN lfcur;

DECLARE @cur_fileno INT;

FETCH NEXT
FROM lfcur
INTO @cur_fileno
;

WHILE (@@FETCH_STATUS = 0)
BEGIN
	INSERT INTO @log
	EXEC sp_readerrorlog
		@p1 = @cur_fileno
		, @p2 = @log_type
		, @p3 = @Search1
		, @p4 = @Search2
	;

	FETCH NEXT
	FROM lfcur
	INTO @cur_fileno
	;
END;

CLOSE lfcur;
DEALLOCATE lfcur;

SELECT LogDate
	, ErrorLevel
	, LogText
FROM @log
ORDER BY LogDate DESC
;

GO

Open in new window

0
 
25112Author Commented:
thank you.

one last q: sometimes profiler capture the same login several times for the same spid over a second or two.. (for example, in 2 seconds the same spid may register 8 entries for this login).. how would you eliminate the duplicates to capture the exact number of distinct login attempts?
0
 
nemws1Database AdministratorCommented:
Does that happen in the middle of a capture or right at the beginning?  If its at the beginning, those are all existing connections, so you want to count those.

Otherwise, I'm not sure I've seen what you're describing.  Can you post a screenshot (blur out any sensitive data)?
0
 
25112Author Commented:
ok.. here Is an example.. (where a package is running).. so same spid repeated 3 times for same login for same spid.. that tricks the numbers, right?
spid.png
0
 
nemws1Database AdministratorCommented:
Hmm.. Well, you can execute multiple batches once you've connected and those will all show under the same SPID.  I wish I could see to the left of your screenshot.  Are all these events registering as "Audit Login" events?

If you want *just * logins and logouts, you should have just those 2 events selected in the Profiler.
0
 
25112Author Commented:
yes, I chose only Audit Logins. (not even logouts).. if you have a handy one, can you run a package and see if you get multiple entries like that, please? I saw the same for regular sql logins also, as well as the domain service account.
0
 
nemws1Database AdministratorCommented:
I'm using a dev box that I *know* has no agents, automated jobs, etc. and running queries against it.  I'm not seeing any extra logins/logouts.  Be careful, though.  If you're using SSMS to test, SSMS sometimes opens 2-3 connections (usually for Intellisense).  An SSIS package might connect/disconnect a couple times as well, I guess (checking to make sure data sources work correctly).  I would have you profile log*outs* as well.  Is that package connecting three times and then disconnecting all three, or is connect/disconnect repeated 3 times?

Maybe if you described to me what you're trying to accomplish I might have a better solution for you.
0
 
25112Author Commented:
I could see that the package ran only once. in other cases, I saw 10+ occurances of the same spid for the login within a second or lower single digit seconds, which was all related.

the project scope is: the team wants to investigate how many request for report happened within a period to confirm some statistics... the reports are always run by the same login thru the app.
0
 
nemws1Database AdministratorCommented:
Yeah, I think you should skip Profiler and turn on full SQL logging like I suggested before.
0
 
25112Author Commented:
will deploy it. will SQL Logging avoid the duplicate possibility (in situations like package running etc)
0
 
25112Author Commented:
helped much- thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.