Database User Activity Monitoring - MSSQL

[echwallah]

We have a customerwho have made a request to provide capability if how Dynatrace or an extension or any other option can be leveraged to Monitor Database User Activity and also generate reports. The environment is on MSSQL

Anyone with tith the posible options to achieve this please provide

[michal_lewi]

Dear echwallah,

you can use this extension:
https://www.dynatrace.com/hub/detail/custom-database-extension/
it allow you to execute custom querries.

Best Reagrds
Michał

[echwallah]

Thanks @michal_lewi please guide which table srtucture in MSSQL will I be querying to get list of users accessing the databases  and their activity they have performed like SELECT/INSERT/UPDATE. The objective is to get a record of users and activities performed against the database.

[jaume_reverte]

Hello echwallah, 

For SQL Server should be pretty straight forward, try something like this:

SELECT 
    s.session_id, 
    s.login_name, 
    s.host_name, 
    r.start_time, 
    r.status, 
    r.command, 
    r.sql_handle
FROM 
    sys.dm_exec_sessions s
JOIN 
    sys.dm_exec_requests r ON s.session_id = r.session_id;

Hope you a good monitoring! 

[echwallah]

Thanks @jaume_reverte bu this will only run and give the current record. What we need is also historical data like past 7 days, past one month. This query only provides the current record

[jaume_reverte]

I don't know a way to perform that action. We usually create something similar to the query I sent, and when it has been running for a long time, we have the historical data then. 

Hope someone have another answer.