Tracking Logins with Extended Events

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I was building a question the other day on Extended Events and needed to build a new session. One of the easier sessions to build is with the logins, so I decided to use that, and then wrote this post on how to build the session.

The first thing is to to to the Management tab in SSMS and then the Extended Events folder. I right click the Sessions folder and select New Session.

2015-11-18 10_40_06-Cortana

This gives you a dialog. Like many in SSMS, we start with a name. You could select run at server startup, which I’d do in the case of this being a production system.

2015-11-18 10_40_55-Photos

When I click next, I get to the "Events" tab, which lists all events. I’ll scroll down to login and select that. I need to click the arrow to the right.

2015-11-18 10_41_12-Photos

Once I do that, my event is in the session.

2015-11-18 10_41_19-Photos

After I pick the events, I choose the fields I’m going to capture. There is a "Configure" button in the upper right that you click. This scrolls the dialog over.

2015-11-18 10_41_38-Photos

I can select any number of fields for capture. You can see I’ve picked the client_hostname. I would also add the NT_Username and Username from the list. You could add more, but in this case, I’m more concerned with seeing who’s logged in.

I could add filters, but I choose not to. I click on Data Storage to determine where to store this data.

2015-11-18 10_45_59-Photos

For auditing, I might want a file. In this case, for testing, I’ll use the ring buffer, in memory storage.

2015-11-18 10_46_15-Photos

That’s it for getting the session set up. However it’s not started. To do that, I need to right click the session and select Start.

2015-11-18 10_47_34-Start

This will allow the server to start collecting data. Is it working? Let’s see. We’ll watch the data. Right click the session again and select Watch Live Data

2015-11-18 10_47_43-Cortana

This pops open a window. I usually make this a separate vertical tab group. Once that’s open, I’ll click "New Query" in SSMS, which will log me in again

2015-11-18 10_48_05-Photos

As you can see, a few events popped up here. I am capturing data. Don’t forget to stop the session after this if you don’t need it.

SQLNewBlogger

This post came out of work I was doing, and which I’d likely do as a DBA. However as soon as I got things working and tested, I knew this was a good post. In fact, I got a couple posts from the process. The setup and testing took about 20 minutes, including a little research. However the writing for this was about 10 minutes.

References

A few things I used.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , , . Bookmark the permalink.

4 Responses to Tracking Logins with Extended Events

  1. Ken T. says:

    Thanks Steve. It can get interesting when you want to store this info to .xel files for later query and summarization. Grant Fritchey has a nice sample script that reads and parses the XML.

    • way0utwest says:

      Storing and managing the data is hard(er). It’s certainly a chore, and it’s one reason why I’m not sure I’d build a long term monitoring solution when I can buy one and plug into it.

  2. Larry Meklemburg, Solon, OH, USA says:

    You made it simple and easy. I tried it on my most used system. Unfortunately it was a 2008 SQL Server. I jumped over to my test 2016 server and voila, there everything was. Have a great Christmas and Happy New Year.

    • way0utwest says:

      It is pretty easy to set up and get going. I did futz with things a bit, but once you start to play with EE, you start to see what works and what doesn’t.

      Merry Christmas.

Comments are closed.