How to find out all queries run against a specific database since we started monitoring using DPA?

https://thwack.solarwinds.com/product-forums/database-performance-analyzer-dpa/f/forum/365/custom-report---top-sql-for-database-user

I just modified it as I see that all tables in DPA database have schema called "ignite". It's working as of 8/6/2021.

declare

@from_date varchar(16) = '2021-05-03 00:00',
@to_date varchar(16) = '2021-08-07 00:00',
@iname varchar(50) = '<INSERT SERVER NAME>',
@dbname varchar(50) = '<INSERT DATABASE NAME>',
@sql varchar(4000),
@dbid varchar(3)

if object_id('tempdb..#topsqlalldata') is not null
begin
drop table #topsqlalldata
end

create table #topsqlalldata
(
sql_hash varchar(100),
timesecs bigint,
dbuser_id varchar(100),
machine_id varchar(100),
program_id varchar(100),
dbuser varchar(100),
machine varchar(100),
program varchar(100),
sql_text varchar(500),
last_date datetime,
executions bigint
)

--begin
select @dbid = id from ignite.cond where name = @iname
select @sql = 'insert into #topsqlalldata ' +
'(sql_hash,timesecs,dbuser_id,machine_id,program_id,last_date) ' +
'select sw.izho, sum(sw.qp)/100 timesecs' +
', sw.xcuw,sw.pwmy,sw.udpw,max(sw.d) ' +
'from ignite.consw_' + @dbid + ' sw ' +
'inner join ignite.cono_' + @dbid + ' o on sw.ixoy = o.id ' +
'where sw.d between ' +
'convert(datetime,' + '''' + @from_date + '''' + ', 101) and ' +
'convert(datetime,' + '''' + @to_date + '''' + ', 101) ' +
'and upper(o.name) = upper(' + '''' + @dbname + '''' + ') ' +
'group by sw.izho,sw.xcuw,sw.pwmy,sw.udpw'
print @sql
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set dbuser = (select name from ignite.conu_' + @dbid + ' u ' +
'where dbuser_id = u.id)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set machine = (select name from ignite.conm_' + @dbid + ' m ' +
'where machine_id = m.id)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set program = (select name from ignite.conpr_' + @dbid + ' p ' +
'where program_id = p.id)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set sql_text = (select substring(st,1,500) from ignite.const_' + @dbid + ' s ' +
'where sql_hash = s.h and s.p = 0)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set executions = (select sum(execs) from ignite.conss_' + @dbid + ' ss ' +
'where sql_hash = ss.h ' +
'and ss.d between ' +
'convert(datetime,' + '''' + @from_date + '''' + ', 101) and ' +
'convert(datetime,' + '''' + @to_date + '''' + ', 101))'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set sql_hash = (select name from ignite.con_sql_name n ' +
'where sql_hash = n.hash) ' +
'where sql_hash in (select hash from ignite.con_sql_name)'
exec (@sql)

select sql_hash,timesecs,dbuser,machine,program,sql_text,last_date,executions
from #topsqlalldata
order by last_date desc
--order by timesecs desc

if object_id('tempdb..#topsqlalldata') is not null
begin
drop table #topsqlalldata
end
--end
go

https://thwack.solarwinds.com/product-forums/database-performance-analyzer-dpa/f/forum/365/custom-report---top-sql-for-database-user

I just modified it as I see that all tables in DPA database have schema called "ignite". It's working as of 8/6/2021.

declare

@from_date varchar(16) = '2021-05-03 00:00',
@to_date varchar(16) = '2021-08-07 00:00',
@iname varchar(50) = '<INSERT SERVER NAME>',
@dbname varchar(50) = '<INSERT DATABASE NAME>',
@sql varchar(4000),
@dbid varchar(3)

if object_id('tempdb..#topsqlalldata') is not null
begin
drop table #topsqlalldata
end

create table #topsqlalldata
(
sql_hash varchar(100),
timesecs bigint,
dbuser_id varchar(100),
machine_id varchar(100),
program_id varchar(100),
dbuser varchar(100),
machine varchar(100),
program varchar(100),
sql_text varchar(500),
last_date datetime,
executions bigint
)

--begin
select @dbid = id from ignite.cond where name = @iname
select @sql = 'insert into #topsqlalldata ' +
'(sql_hash,timesecs,dbuser_id,machine_id,program_id,last_date) ' +
'select sw.izho, sum(sw.qp)/100 timesecs' +
', sw.xcuw,sw.pwmy,sw.udpw,max(sw.d) ' +
'from ignite.consw_' + @dbid + ' sw ' +
'inner join ignite.cono_' + @dbid + ' o on sw.ixoy = o.id ' +
'where sw.d between ' +
'convert(datetime,' + '''' + @from_date + '''' + ', 101) and ' +
'convert(datetime,' + '''' + @to_date + '''' + ', 101) ' +
'and upper(o.name) = upper(' + '''' + @dbname + '''' + ') ' +
'group by sw.izho,sw.xcuw,sw.pwmy,sw.udpw'
print @sql
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set dbuser = (select name from ignite.conu_' + @dbid + ' u ' +
'where dbuser_id = u.id)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set machine = (select name from ignite.conm_' + @dbid + ' m ' +
'where machine_id = m.id)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set program = (select name from ignite.conpr_' + @dbid + ' p ' +
'where program_id = p.id)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set sql_text = (select substring(st,1,500) from ignite.const_' + @dbid + ' s ' +
'where sql_hash = s.h and s.p = 0)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set executions = (select sum(execs) from ignite.conss_' + @dbid + ' ss ' +
'where sql_hash = ss.h ' +
'and ss.d between ' +
'convert(datetime,' + '''' + @from_date + '''' + ', 101) and ' +
'convert(datetime,' + '''' + @to_date + '''' + ', 101))'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set sql_hash = (select name from ignite.con_sql_name n ' +
'where sql_hash = n.hash) ' +
'where sql_hash in (select hash from ignite.con_sql_name)'
exec (@sql)

select sql_hash,timesecs,dbuser,machine,program,sql_text,last_date,executions
from #topsqlalldata
order by last_date desc
--order by timesecs desc

if object_id('tempdb..#topsqlalldata') is not null
begin
drop table #topsqlalldata
end
--end
go

The schema being "ignite" is some an environmental item. When you setup DPA's repository on SQL server if you use the option "Let DPA create the repository user" it will create all tables with the "ignite" schema. If however you provide a user that is for instance sysadmin and exists on the server or a user whos default schema is dbo then the tables will use the dbo schema. 

Most alerts and reports on THWACK are written with the schema not added to the tables. Some users may need to add ignite. as you have and some may not need to it depends on the permissions of the user connecting, what their default schema is on the database and what was selected on install.