Capture Login activity

Question

Capture Login activity

mtz676

SSCrazy Eights

Points: 9214
More actions
August 24, 2021 at 9:50 am

#3923419

https://www.sqlservercentral.com/forums/topic/with-sqlaudit-you-cannot-track-hostname-or-ip-address
"The host name is dependent on the connection string. If people leave this off, it's why you can't audit for it. You can use HOST_NAME to get this information if it's passed. You can probably, in most cases, I won't say all, get the IP address from sys.dm_exec_connections.
@Grant Frritchey - Is the above statement referring to SQL Audit or Extended event
@Grant Fritchey & All
Using SQL Audit I can audit only the relevant SQL Login activity (Like a select/insert etc..DML).When I audit there is Only 1 record in the result set.Also can I capture hostname and client app here as in extended events.
Extended events capture all information, but there are several records when I try audit a login.How I can filter everything and have only 1 record in the result set.(capture only the relevant select/DML) as in SQL audit.Any references can help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27056 More actions · Answer 1

Why don't you ask this question in the original thread?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 2

Sounds good to me but unless I am not responding with an answer to the original thread I wasn't sure if it would be appropriate to get into someone's thread with my own set of questions.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Michael L John wrote:

Why don't you ask this question in the original thread?

That's almost funny, Michael... how many times have we seen people say "start a new thread for a different question" even if it's a related question?

I'm with you that there's nothing wrong with asking a question on a related thread. Most folks that answer questions monitor for all questions and additional posts anyway.

Shifting gears to the question at hand... one fairly easy method is to change the server setting to capture all logins in the SQL Server error log. Some find that annoying because it can hide problems if you don't now how to filter or you don't now how to read the contents of a log file using a bit of unsupported but commonly used code. I agree that's a bit of a mess but it is fairly safe.

Another way is to make login triggers for the server. The spooky part there is that if you do it wrong, you won't be able to log into the server. Still, the correct methods are fairly well documented if you Google for them. I do recommend testing any solution on a "burner" instance before applying to a real server.

You can also buy software that will do all of this for you and take it out of reach of DBAs if you need audit information for potential court appearances or SEC guaranteed audits, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 4

@thanks Jeff..I can check the error log way but can I get references on how I can filter the all data but the login records into the .xel files. By that I mean...if a login runs a select ... my .xel should have ONLY 1 record displaying the relevant select details(like the .audit files do cleanly when opting for SQL database Audit/Server audit way)...As mentioned I have already captured the relevant data using extended events but there is a lot thats needs to be filtered and that is where I need directions. No triggers(not averse to it but no for now) and can't afford a 3rd party tool.

This reply was modified 3 years, 3 months ago by mtz676.

Michael L John One Orange Chip Points: 27056 More actions · Answer 5

mtz676 wrote:

@Thanks Jeff..I can check the error log way but can I get references on how I can filter the all data but the login records into the .xel files. By that I mean...if a login runs a select ... my .xel should have ONLY 1 record displaying the relevant select details(like the .audit files do cleanly when opting for SQL database Audit/Server audit way)...As mentioned I have already captured the relevant data using extended events but there is a lot thats needs to be filtered and that is where I need directions. No triggers(not averse to it but no for now) and can't afford a 3rd party tool.

This is why I suggested that you post in the original thread. The folks who answered are very well versed in extended events and can probably give you better answers.

Jeff's answer was strictly about capturing logins, not the associated SQL statements.

As for filtering the extended events, you can add a WHERE clause to the event definition to filter. Also, if you pull the results into a table you can filter, painfully, by using LIKE, CHARINDEX, and so forth.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/