Audit Logons with Extended Events

Some time ago, I wrote an article for SQL Server 2008 to help determine the use of the server since SQL Server 2008 was reaching End Of Life. In that article, I shared a reasonable use of server side trace to capture all of the logon events to the server. Afterall, you have to find out the source of connections and who is using the server if you need to migrate it to a newer SQL Server version. You can read that article here.

Soon after, from various sources, I received requests on how to perform a logon audit using the more preferred, robust, venerable, awesome tool called Extended Events (XEvents). In response, I would share a login audit session to each person. In this article, I will share my login audit solution and give a brief explanation. I use a solution like this on more than 90% of my client servers and I find it highly useful.

Auditing Logons

The Events to capture SQL Server logon/logoff activities were not a part of the original release of XEvents in 2008. The requisite events did not become available until SQL Server 2012. Why mention SQL Server 2008 at all given it has reached its End of Life you may ask? Well, as luck would have it, there are still plenty of 2008/R2 instances out there in the world that are yet to be upgraded still. So, it is useful to continually point it out. This session, and this information, does NOT apply to anything prior to SQL Server 2012.

The events in XEvents that we need happen to be called: sqlserver.login and sqlserver.logout. Here is a little more info on those events along with a query that can retrieve the details being shown here.

As shown in the preceding image, there are two events of interest that are needed for auditing logon events – sort of. These events are only useful to capture the successful connection (or connection pooling reuse connections) and the logoff events. To take this a step further and capture the failed logins, we can also use an Event that was available in 2008, error_reported. The use of the logout event would be an optional event in this case but could be of use during the troubleshooting under certain circumstances.

Focusing on the login and error_reported events, I have two very useful XEvent Sessions I like to use for my client servers. First part of the audit logon solution is the audit of successful logins with this XEvent Session.

Next, I like to use a separate session for the failed logins. This makes it easy to keep the audit files separate and only share the necessary files to others (e.g. only the failed logins instead of giving them both successful and failed which may be way too much information). In addition, I like this method because it makes searching the audit files easier and more focused.

In this second session, I have the predicate defined such that I will only get the failed login events. Funny thing about this, I can often catch 3rd party vendors trying to login to client systems in very odd ways (including putting the password into the user name box which in turn causes the password to be stored in clear text in the error log).

Could these sessions be combined into a single all-purpose session? Sure! That is up to your discretion. I prefer the separate sessions for ease of management and review.

The Extra Mile

Let’s say we wanted to go ahead and add the logout event to our session, we can then proceed with a slight modification to the successful login session so that it would look something like the following.

And, to show what might occur with this session, I can see data similar to this for the logout event.

See how easy that is!

The Wrap

This article has shown how to audit the logon events for SQL Server 2012 and beyond through the use of XEvents. It also happens to be an excellent follow up to another recent article – here. An essential function of any DBA is to have a clear understanding of the activity (logons) that is occurring on the servers. This audit session will help you do exactly that!

Through the power of XEvents, we can accomplish a great many things and reach extensive insights into our database footprint. This post is just one of many in the XE Series, of which you can read more – here.

In addition, this post is similar to many in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

Decoded Statistics Names and More

Every now and again I am asked about the meaning behind the automatically generated names for statistics in SQL Server. The quick answer is short, sweet and really easy. I give them a quick explanation and then often refer them to the blog post by Paul Randal on the topic.

The better answer is to show them what the auto-generated names really mean, alongside the great explanation from Paul. Finally, after years of the topic being on my backlog, I am sharing a script that will help decode those names and help to prove out fully what’s in a statistic name.

Fun with Stats

First let’s look at an example for an auto-generated statistic name.

_WA_Sys_0000000A_0000001B

It is important to understand the naming convention used here and that there are four distinct parts to the name. Each part is separated by an underscore (_). The leading underscore is unnecessary, so just ignore it. Here is a quick breakdown of the four parts

  1. WA – as Paul explained, this is directly related to where the SQL team is based – WAshington state.
  2. Sys – also as Paul explained, this just means that the stat was auto-created. We will be able to see more of that later in the script results.
  3. 0000000A – This value is a Hex value converted to a string and represents the Column ID for the statistic.
  4. 0000001B – This value is a Hex value converted to a string and represents the Object ID for the object upon which the statistic was created.

Given that parts 3 and 4 of the auto-generated name are Hex strings, we have to do a little trickery in order to convert them to the exact hexadecimal format so we can then properly convert the values to their integer counterparts. Once getting those conversions performed, the rest of the script is fairly easy. Let’s take a look.

As you can see, I am parsing the name into the various parts using the PARSENAME function. After splitting the parts out, I use some conversion techniques to try and get the hexadecimal representation (using VARBINARY). In the result set, I have included the object_id from the sys.stats view as well as the column_id from the sys.stats_columns view to demonstrate that the conversion is accurate. Here is an example result set. Also of note, I have filtered the results strictly down to those stats that have been auto created.

Looking at the results, the columns with “Parsed” in the name are those that have been decoded direct from the stats name, while the rest have come from the various views. Shown here, I have the ColumnID from the two sources in red, and the ObjectID in blue to help correlate the values.

That is great, now let’s step it up a notch to see just a few more results.

This extended query will give us similar output to the following.

If you are looking closely at the code, you may be wondering why I have near identical versions of the query connected via a UNION statement. Well, if the stat is not an auto generated stat, then the parsing statements will error, and I would like to be able to retrieve extended information regarding all statistics so I can better understand what is going on as a whole with my statistics.

Wrapping it Up

Auto-generated statistics names can seem like they are entirely random, but there is a method to the madness. With a little effort and a bit of TSQL trickery, we can decode those names and reveal what the names really mean.

This article helps to decode those names and as a bonus, I show you how to pull additional pertinent information about your statistics to help you on your path to being an expetional data professional.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

page 1 of 1

Calendar
April 2020
M T W T F S S
« Mar   May »
 12345
6789101112
13141516171819
20212223242526
27282930  

Welcome , today is Sunday, May 31, 2020