report all queries and the machine that they were run from for a specified db user

I am trying to generate a report that contains the database name and machine the  query  was run from for a particular user that I specify. I see some queries  close to this in  the community but not exactly what i need.
Something like this but where i can specify the particular user that i'm interested in


SET @monitorID = --id of server
SET @FROM_DATE = GETDATE()-7
SET @TO_DATE = GETDATE()
SET @SQL =
'SELECT DISTINCT ' +
'MD.NAME [Database/Name]' +
',MM.Name [Application/cleint name] ' +
' FROM ignite.CONSW_' +
@monitorID +
' SW LEFT OUTER JOIN ignite.CON_SQL_NAME N ON SW.IZHO = N.HASH ' +
'LEFT OUTER JOIN ignite.CONST_' + @monitorID + ' ST ON SW.IZHO = ST.H ' +
'LEFT OUTER JOIN ignite.CONO_' + @monitorID + ' MD ON SW.IXOY = MD.ID ' +
'LEFT OUTER JOIN ignite.CONM_' + @monitorID + ' MM ON SW.PWMY = MM.ID ' +
'INNER JOIN ignite.CONU_' + @monitorID + ' U ON SW.XCUW = U.ID ' +
'WHERE SW.D BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +
'GROUP BY ISNULL(N.NAME, SW.IZHO), ST.ST, MM.Name, MD.Name ' +
'ORDER BY MD.NAME '
Exec (@SQL)

thank  you

Parents
  • Have you considered an alternative that might be a whole lot easier than querying the DPA repository DB. Specifically, using the "Find SQL" feature tab instead? Just specify a time range and filters for different values of user(s), program name(s), client host(s), and database name(s), hit the search button and then export the resulting list to a .csv file. 

    That's just using the canned filter options, you can also combine your search with the use of browser style matching of SQL statement text (i.e., in simple mode, return the SQL statements matching the most terms first)  or switch to Advanced mode to leverage boolean logic (AND, OR, NOT, etc.) as well as regular expressions. For example, only look for Insert or Delete statements involving Table EMP and referencing column 'Emp_Name" without referencing Emp_ID.

Reply
  • Have you considered an alternative that might be a whole lot easier than querying the DPA repository DB. Specifically, using the "Find SQL" feature tab instead? Just specify a time range and filters for different values of user(s), program name(s), client host(s), and database name(s), hit the search button and then export the resulting list to a .csv file. 

    That's just using the canned filter options, you can also combine your search with the use of browser style matching of SQL statement text (i.e., in simple mode, return the SQL statements matching the most terms first)  or switch to Advanced mode to leverage boolean logic (AND, OR, NOT, etc.) as well as regular expressions. For example, only look for Insert or Delete statements involving Table EMP and referencing column 'Emp_Name" without referencing Emp_ID.

Children
No Data