Hi,
Does anybody know if there is a report that I can generate that lists users along with their SSMS(program) usages?
Philip, DPA does not have a report per se that will show this information, but we could use a custom query to mine the data from the DPA repository database. I assume you want to know which SQL statements those users are executing from within SSMS. Here is something to get you started. Replace the instance name 'AVANTIS' with your instance name as listed in the DPA home page.
CONSW - the main session waits table that has all wait times, SQLs, user, etc
CONPR - lookup table for program, CONSW.UDPW is the program id value
CONU - lookup table for database user, CONSW.XCUW is the user id value
CONST - contains the SQL Text, CONSW.IZHO is the sql hash value
DECLARE @DBID varchar(5), @SQL varchar(1000);
SELECT @DBID = ID FROM ignite.COND WHERE NAME = 'AVANTIS'; -- substitute the instance name here
SET @SQL = 'SELECT u.NAME AS [Username], pr.NAME AS [Program], st.ST AS [SQL Text], sum(sw.QP)/100 as [WaitSeconds]
FROM ignite.CONSW_'+@DBID+' sw
INNER JOIN ignite.CONU_'+@DBID+' u ON u.ID = sw.XCUW
INNER JOIN ignite.CONPR_'+@DBID+' pr ON pr.ID = sw.UDPW
INNER JOIN ignite.CONST_'+@DBID+' st ON st.H = sw.IZHO
WHERE pr.NAME like ''%Management Studio%''
GROUP BY u.NAME, pr.NAME, st.ST
ORDER BY [WaitSeconds] DESC';
EXEC (@SQL)
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.