TRACK USER PRODUCTIVITY 1

[jaimekash]

Is there anyway to create a report that tracks user, login, logout, active time for each time a user logs into the databse.

 

[SQLSister]

Depends. First define active time? Time actually doing something? That would be very hard to get and have it be usefule in any way. Say I'm entering data, it takes me 10 minutes to gather all the data I need and type it into the form and hit submit. It takes milleseconds for the database to insert the record. From a database perspective I was only active for milliseconds.

I belive SQL 2005 has better tools for grabbing meta data that 2000. But you certainly can have a proc that inserts the login time when the person logs in from the user interface. And it could update as logged out anytime the user actually logs out or after a set period of time with no action or when the site is left or the window closed.

I do believe that time logged into the system says nothing useful at all about user productivity. I can stay logged in all day and do nothing or I can efficiently organize my work beforehand and be logged in only a short time that I need to actaully do the work inthe database. Measure productivity in terms of tasks accomplished not time on the product.

"NOTHING is more important in a database than integrity." ESquared

 

[jaimekash]

Thank you for the post. I would ultimately like to have a table that shows the same fields as the view under Management, Current Activity, Process Info. The fields in particular that I would like to see User, Database, Status, Command, Application, Waiting Type, Login Time, Last Batch & Host.

What we are trying to get to is exactly how many users actually go into the databases and how many batches they run daily. Is there anyway to get this aside from exporting it from Enterpise Manager.

Thanx.

 

[SQLSister]

Profiler can grab what you want but it will significantly slow down your production server. I suggest you look at third party audit solutions. Not familar with the performance of them but I do know that they often claim to be faster performing than profiler.

"NOTHING is more important in a database than integrity." ESquared