Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-15
14
Medium Priority
?
115 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-
0
Comment
Question by:25112
  • 7
  • 7
14 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 40264756
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
 
LVL 5

Author Comment

by:25112
ID: 40264866
ok- thanks.

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

Expert Comment

by:nemws1
ID: 40265202
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 5

Author Comment

by:25112
ID: 40274423
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
 
LVL 23

Expert Comment

by:nemws1
ID: 40274435
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
 
LVL 5

Author Comment

by:25112
ID: 40283567
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
 
LVL 23

Expert Comment

by:nemws1
ID: 40283640
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
 
LVL 5

Author Comment

by:25112
ID: 40283704
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
 
LVL 23

Expert Comment

by:nemws1
ID: 40283723
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
 
LVL 5

Author Comment

by:25112
ID: 40283851
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
 
LVL 23

Expert Comment

by:nemws1
ID: 40283974
Yeah, I think you should skip Profiler and turn on full SQL logging like I suggested before.
0
 
LVL 5

Author Comment

by:25112
ID: 40285431
will deploy it. will SQL Logging avoid the duplicate possibility (in situations like package running etc)
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 2000 total points
ID: 40285670
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
 
LVL 5

Author Comment

by:25112
ID: 40310987
helped much- thx
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question