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

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.

ok- thanks.

now, is there a way in that to distinguish between
successful connection attempts
vs
failed ones?

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

Select allOpen in new window

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?

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)?

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

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.

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.

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.

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.

Yeah, I think you should skip Profiler and turn on full SQL logging like I suggested before.

will deploy it. will SQL Logging avoid the duplicate possibility (in situations like package running etc)

helped much- thx