March 17, 2018 at 9:18 pm
I need to setup a sql audit to capture all queries running on a server from any unauthorized locations, and one of the columns that is required is Row Count.
In sql trace, this is possible. But I can't seem to find corresponding column in extended events. I looked into these 2 DMVs:
select * from sys.dm_xe_object_columns
where object_name = 'sql_statement_completed'
select pkg.name as PackageName, obj.name as ActionName
from sys.dm_xe_packages pkg
inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
where obj.object_type = 'action'
order by 1, 2
But all they have is reads/writes, but not actual number of rows.
Is it possible to get this info in SQL 2008 or SQL 2008 R2 ?
March 20, 2018 at 1:32 pm
select * from sys.dm_exec_sessions
There is a row count column in there. I think you need active connection at the time of running this, even though a process may have completed already. This could help.
----------------------------------------------------
March 20, 2018 at 1:45 pm
sqld-_-ba - Saturday, March 17, 2018 9:18 PMI need to setup a sql audit to capture all queries running on a server from any unauthorized locations, and one of the columns that is required is Row Count.In sql trace, this is possible. But I can't seem to find corresponding column in extended events. I looked into these 2 DMVs:
select * from sys.dm_xe_object_columns
where object_name = 'sql_statement_completed'
select pkg.name as PackageName, obj.name as ActionName
from sys.dm_xe_packages pkg
inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
where obj.object_type = 'action'
order by 1, 2But all they have is reads/writes, but not actual number of rows.
Is it possible to get this info in SQL 2008 or SQL 2008 R2 ?
I get row_count and last_row_count when I use that on 2014. I think your query is correct. It is not easy dealing with extended events on 2008, I was using the ExtendedEventManager up on Codeplex. It helped quite a bit. I have no idea what you get with the current versions of SSMS. I was using the 2008 version of SSMS and would guess that is what you would need to use. Here is the link to that AddIn if you are interested:
ExtendedEventManager
Sue
March 24, 2018 at 5:36 pm
.
March 25, 2018 at 3:34 am
If you're using SQL 2008 R2, I'd suggest you stick with trace. While XE existed on 2008 R2, it was fairly limited. It became a lot more usable in 2012.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply