Trace

• somubhandaru23

  SSC Journeyman

  Points: 81

  More actions

  April 23, 2015 at 10:30 am

  #296348

  Im running a Trace to find out the HostName, DatabaseName, LoginName and StartTime, since im using a convert function in the below code, i need to how i can use group by function because i have 287949 rows. Please help!!!

  SELECT te.Name,CONVERT(NVARCHAR(MAX),TextData) as TextData, HostName, LoginName, StartTime,

  DatabaseName FROM ::fn_trace_gettable('D:\InstanceName_Audit\NSK1MSSQL0120150105-1321_6.trc', DEFAULT) t

  JOIN sys.trace_events te ON t.EventClass=te.trace_event_id

  WHERE starttime BETWEEN CONVERT(DATETIME,'2015-04-01') AND CONVERT(DATETIME,'2015-04-07')

  AND databasename NOT IN ('msdb','master','model')

  AND textdata NOT LIKE '%exec sp_reset_connection%'

  AND textdata NOT LIKE '%-- network protocol: TCP/IP%'

  AND textdata NOT LIKE '%UPDATE STATISTICS%'

  AND name NOT IN ('User Error Message')

  AND textdata NOT LIKE '%SELECT v.name AS [Name], v.object_id AS [ID], v.create_date AS [CreateDate]%'

  AND textdata NOT LIKE '%SELECT TOP 26 sysEmployee.[EmployeeNumber], sysEmployee.[FirstNam], sysEmployee.[LastName]%'

  AND textdata NOT LIKE '%SELECT tbl.name AS [Name], tbl.object_id AS [ID], tbl.create_date AS [CreateDate]%'

  AND textdata NOT LIKE '%SELECT TOP 26 sysEmployee.[EmployeeNumber], sysEmployee.[FirstNam], sysEmployee.[LastName]%'

  AND textdata NOT LIKE '%SELECT tbl.name AS [Name], tbl.object_id AS [ID], tbl.create_date AS [CreateDate]%'

  AND textdata NOT LIKE '%SELECT sysHomepages.[sysDashboardID], sysHomepages.[AutoLoad] FROM sysHomepages%'