CODEWITHSUNDEEP
sql-serverprofiler
Gabriel Guimarães
Gabriel Guimarães

Reputation: 2744

Find Which applications access my server using Profiler Trace with Application Name column

I Need to find out what are all the applications that use my sql server.

I'm using Profiler trace to do this (if there's another way to do this I would appreciate it)

On Profiler I'm using a Replay template, and after looking at the trace result I see that there's a column called Application Name, I'm wondering if there's a way to get the distinct ones (the trace is on a .trc file).

(By the way is this supposed to be posted on stackoverflow or serverfault?)

Thanks, Gabriel

Upvotes: 2

Views: 1599

Answers (2)

graz
graz

Reputation: 61

You can actually do this right from within Profiler in SQL Server 2008.

Create a trace with the following two events:

  • Security Audit : Audit Login
  • Security Audit : Existing Connection

For those two events, capture the following columns:

  • Event Class
  • Application Name
  • SPID (required)
  • Event Sub Class

Add a filter to Event Subclass to restrict it to values of 1. This filter will only capture non-pooled logins. This should give you all your existing connections and any new logins that occur during the time you are running your trace.

Next, in the organize columns, move Application Name up to the "Groups" section. This will now group all the results by the Application Name.

This is a pretty light weight trace and shouldn't put much (if any) load on the server if you restrict it to just those events and apply the filter.

(I'm pretty sure previous versions work the same way. I just don't have one in front of me to test.)

Upvotes: 1

8kb
8kb

Reputation: 11406

Try this: 

SELECT DISTINCT ApplicationName
FROM ::fn_trace_gettable('C:\YourFolder\YourTraceFile.trc', DEFAULT) t

Upvotes: 3

Related Questions