(update 2013/04/18 Jonathan Kehayias helpfully provided a way to do it by adding the existing_connection event - but there appears to be a bug with the histogram, and it doesn't return the right data. Connect item if you want to vote on it: https://connect.microsoft.com/SQLServer/feedback/details/785042/extended-events-histogram-does-not-properly-save-existing-connection-results)
First of all, MANY thanks to Jonathon Kehayias for all the XE wisdom and code - I modified the snot out of his http://www.sqlskills.com/blogs/jonathan/tracking-sql-server-database-usage/ lock-tracking-using-a-histogram to build this...
Say I want to figure out what servers are connecting to my SQL Server. This is something you'd traditionally either poll sysprocesses for periodically, or probably run a trace. Both have issues, though, especially if they're busy servers. So, how can we get that? Extended Events.
The easiest way to do this would be using Extended Events - create a "bucketizer" (now called the histogram), and watch for new logins, saving the servername and incrementing a counter each time it happens. As a bonus, we could then filter it - exclude particular applications, servers, etc (see the commented out code). And, since it's a pretty basic XE, overhead is very low. The one caveat is that you can only get one piece of information out - servername. I'd LOVE to get the App Name & DB Name as well, but you can only get one piece of information at a time. Downer. : - (
One caveat: If you have servers that stay connected, and don't open new connections, you won't see them here. The obvious exclusion, then, is replication, but maybe your app hangs on for a long time (we've seen that with third-party tools). Easy answer for those is to use SP_WHOISACTIVE.