We help IT Professionals succeed at work.

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

25112
25112 asked
on
126 Views
Last Modified: 2014-09-08
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-
Comment
Watch Question

Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
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.

Author

Commented:
ok- thanks.

now, is there a way in that to distinguish between
successful connection attempts
vs
failed ones?
Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
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

Author

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?
Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

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

Author

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
Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
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.

Author

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.
Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
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.

Author

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.
Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

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

Author

Commented:
will deploy it. will SQL Logging avoid the duplicate possibility (in situations like package running etc)
IT Supervisor
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
helped much- thx

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.