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

BI Team is asking me about finding out who has ever run query or queries against a specific database on an instance we are currently monitoring using DPA. T-SQL code to be executed against DPA database will be useful. I am not really looking for a report.

Parents
  • 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. 

Reply
  • 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. 

Children
No Data