LV April SQL Users Group Meeting

Categories: News, Professional, SSSOLV
Tags: ,
Comments: No Comments
Published on: April 6, 2010

S3OLV or SSSOLV will be holding our meeting Thursday April 8th.  The User Group is in affiliation with PASS and meets monthly typically on the second thursday of the month.

This month we will be hearing from Stacia Misner (blog |blog 2 | @StaciaMisner).  She will be discussing some more information about SQL 2008 R2 and the Businesss Intelligence suite.  She may even be doing the presentation in French ;).

We are also planning on a presentation about some RedGate tools.

Hope to see you at the Learning Center @ 777 Rainbow.

If you have any questions you can contact me through the the comments here or through the group website.

IP and Default Trace…T-SQL Tuesday #005

Comments: 7 Comments
Published on: April 6, 2010

As chance would have it, I had been checking Adam’s blog daily for the last few days to find the next T-SQL Tuesday.  Not having seen it, I started working on my next post yesterday evening.  The fortunate thing is that the post I was working on fits well with this months topic.  T-SQL Tuesday #005 is being hosted by Aaron Nelson.  And now I have one more blog to bookmark since I didn’t have his bookmarked previously.

This month the topic is Reporting.  Reporting is a pretty wide open topic but not nearly as wide open as the previous two months.  Nonetheless, I think the topic should be handled pretty easily by most of the participants.  My post deals with reporting on User connections.  I have to admit that this one really just fell into my lap as I was helping somebody else.

The Story

Recently I came across a question on how to find the IP address of a connection.  From experience I had a couple of preconceptions on what could be done to find this information.  SQL server gives us a few avenues to be able to find that data.  There are also some good reasons to try and track this data as well.  However, the context of the question was different than I had envisioned it.  The person already knew how to gain the IP address and was already well onto their way with a well formed query.  All that was needed was just the final piece of the puzzle.  This final piece was to integrate sys.dm_exec_connections with the existing query which pulls information from trace files that exist on the server.

After exploring the query a bit, it also became evident that other pertinent information could prove quite useful in a single result set.  The trace can be used to pull back plenty of useful information depending on the needs.  Here is a query that you can use to explore that information and determine for yourself the pertinent information for your requirements, or if the information you seek is even attainable through this method.

Now, I must explain that there is a problem with joining the trace files to the DMVs.  The sys.dm_exec_connections maintains current connection information as does the sys.dm_exec_sessions DMV.  Thus mapping the trace file to these DMV’s could be problematic if looking for historical information.  So now the conundrum is really how to make this work.  So that the data returned in the report, some additional constraints would have to be placed on the query.  But let’s first evaluate the first go around with this requirement.

Query Attempts

While evaluating this query, one may spot the obvious problem.  If not seen at this point, a quick execution of the query will divulge the problem.  SPIDs are not unique, they are re-used.  Thus when querying historical information against current running information, one is going to get inaccurate results.  Essentially, for this requirement we have no better certain knowledge what the IP Address would be for those connections showing up in the trace files.  The IP Addresses of the current connections will cross populate and render inaccurate results from the historical information.

My next step gets us a little closer.  I decided to include more limiting information in the Join to the sys.dm_exec_connections view.  The way I determined to do this was that I needed to also include the loginname as a condition.  Thus in order to get that, I need to include sys.dm_exec_sessions in my query.  To make it all come together, I pulled that information into a CTE.  Here is the new version.

The information pulled back is much cleaner now.  But wait, this mostly reflects current connections or connections from the same person who happens to have the same SPID as a previous time that person connected.  Yes, an inherent problem with combining historical information to the current connection information in the server.

Solution

My recommendation to solve this need for capturing IP address information along with the person who connected, their computer hostname, and the time that they connected is to do so pre-emptively.  (I know this diverges from the report for a minute, but is necessary to setup for the report).  A solution I have implemented in the past is to use a logon trigger that records the pertinent information to a Table.

Tables

Trigger

This solution gets the IP Address from the ClientHost Field of EventData() which is where the logon occurred.  That should be enough to trace back to the source of the connection.  Also note that the application was not included in this, that is information that could be gained from the trace files.  What this does is create a structure for logging logon events as well as prevent logons from unauthorized sources using an account that has been compromised (while logging that action as well).  With this, I now have a solution that complements the trace file.  I could query both and create a more accurate report of the logon activity that has occurred historically (depending on retention policy etc.).  This last method is somewhat limited by the EventData function and the xml schema that comes with it.  You can check it out here.

Reports

So here are a couple of parting solutions that would be able to give us the relevant data for tracking these historical logons.

The above is a simple query to pull back the information from both the violations and successful logon audit tables.

The following is a solution for combining the previous requirement (as I have translated it to truly correlate to Host rather than IP).

The last such report that I wish to share related to this topic is to find current activity.  Since the original idea was to combine the trace with the DMV, I decided that I needed to at least look at the DMV for a relevant query.  These DMV’s provide current activity and need to be combined to provide a quick snapshot of the activity that is occuring on the server at a given moment.  One quick snapshot can tell me who is logged in, from what IP Address, the application, read and write activity, and what the SQL is that is being executed.

There are several queries out there similar to this last one.  The idea is to capture a quick glimpse based on the DMVs which in essence are a report of a subset of server relevant information.

Conclusion

When it is necessary to provide reports on activity occurring on the server, it pays to do a little prep work.  Be Prepared.  It is not an easy task to be able to go back in time and report on data that isn’t captured.  The little prep work that one may need to do is well worth the effort in the end.

Edit:  Corrected 1 detail and fixed some formatting.

page 1 of 1

Calendar
April 2010
M T W T F S S
« Mar   May »
 1234
567891011
12131415161718
19202122232425
2627282930  

Welcome , today is Wednesday, November 13, 2019