Analysing trace information...

Starting simple!

This idea for me here is to show some basic stats

These queries show useful info on logins:

-- Shows latest login per person per DB
select loginname,DATABASEname, MAX(starttime) 'Latest login'
from MyTrace
where databasename is not null
group by loginname, DATABASEname
order by MAX(starttime) desc

-- count of DB hits
select loginname, COUNT(*) from MyTrace
group by loginname
order by count(*) desc


Round 2...

This grabs relevant data from the trace into a temp table, excluding DB's not required.

My idea is to have summary report and a hits count report.
The summary report, feeding into QV via SP would union information like:

• last login per DB
  
• top users per DB
  
• Longest CPU time per DB
  
• Errors per DB
  

Prerequisites: Set up a trace saving to a table called MyTrace


select * into  from  #TraceMe
      (
      select DatabaseName [DB], ntUSERNAME [MyName], starttime [MyDate]
      from  MyTrace
      ) raw
      
where   RAW.DB is not null
            and RAW.MYNAME is not null
       --  and RAW.DB not in ('DB1','DB1')

-- Summary table
select 'Last update' [Check], cast(MAX(mydate) as varchar(255)) from #TraceMe
  
-- Hits count
select DB, MYName, COUNT(*) 'Count' FROM #TraceMe
GROUP BY DB, MYName
ORDER BY DB, COUNT(*) DESC

DROP TABLE #TraceMe


Round 3...


Includes trace sources from 2 servers with 3 outputs

select * into #TraceMe from   
      (
      select 'Server1' [MyServer], DatabaseName [DB], ntUSERNAME [MyName], starttime [MyDate]
      from  <server>.<DB>.dbo.<Trace Result Table>
      
      union all
      
      select 'Server2' [MyServer], DatabaseName [DB], ntUSERNAME [MyName], starttime [MyDate]
      from  <server>.<DB>.dbo.<Trace Result Table2>    
      
      ) raw
      
where RAW.DB is not null
            and RAW.MYNAME is not null
            and db not in ('master','msdb','ReportServer','tempdb','') --system DB's
       --   and RAW.DB in ('DB1','DB2')


--Summary
select 'Last update' [Check], myserver [Detail], MAX(mydate) [Data]
from #TraceMe
group by myserver

  
-- hits  
select 'Server/DB Hits' [Note], [MyServer], DB, MyName, COUNT(*) 'Count'
FROM #TraceMe

GROUP BY [MyServer], DB, MYName
ORDER BY [MyServer], DB, COUNT(*) DESC

-- hits in last 3 minutes
select 'Server/DB Hits in last 3 minutes' [Note],[MyServer], DB, MyName, COUNT(*) 'Count'
FROM #TraceMe
where  DATEDIFF(MINUTE,mydate,GETDATE()) <=3
GROUP BY [MyServer], DB, MYName
ORDER BY [MyServer], DB, COUNT(*) DESC

--select DATEDIFF(MINUTE,mydate,GETDATE()), * from ##TraceMe

DROP TABLE #TraceMe