July 21, 2010 at 1:07 am
Hi,
I need to audit after logon and logout for users.
i use this article http://qa.sqlservercentral.com/articles/SQLServerCentral/sqlserver2005logontriggers/2366/
but i need to adjust it for this scenario.
i need to capture the first logon for the user and the last logout for the user and not all logon and logout that the user do while he is in the ERP.
how can i adjust the script for that or do i need different approach?
THX
July 21, 2010 at 7:42 am
you mean the first and last login per day?
once the data is in the table "Logging " fromt hat article, you can easily create a view that might do a row_number() , or MIN/MAX() /GROUP BY user based on the date to get the first ands lasts on a per day basis.
so that article puts all teh information in a table, so you can simply query it for the detaisl you need.
Lowell
July 21, 2010 at 8:28 am
hi,
i need to know how much time the user spent in the erp program.so if the user enter to the program and then he open new windows from that program (this will fire new logon event) and then close the windows (this will fire new logout event) like that all day i'll have a lot of logon and logout events.
can i use this info to calculate how much real total time the erp program was open on that specific user per day?(we close the erp program if no activity is made for 10 min.)
THX
July 21, 2010 at 9:16 am
can you infer that ALL logins are only used for the ERP program?
a login does not know what database you are connecting to...the login happens before all that.
assuming you are using the link you provided,I think you'd need to add the application name to the logging table to be sure:
INSERT INTO Logging (
EventTime,
EventType,
LoginName,
HostName,
AppName,
NTUserName,
NTDomainName,
Success,
FullLog)
VALUES
(
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/ApplicationName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS VARCHAR(100)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER),
@message_body)
then you could grab the login/logout pairs, get the datediff in minutes on a per-user basis, and calculate the total time...
Lowell
July 21, 2010 at 9:59 am
after setting up that logging demo example, this query gets me the groups of login/logut elapsed times: note i am testing for a specific application name. i could then add tehm up on a per-day basis...i think that's what you are after.
/*--results:
ElapsedMinutes RW LoggingID EventTime EventType LoginName HostName AppName
7 1 1 2010-07-21 11:49:52.973 AUDIT_LOGIN sa D223 Microsoft SQL Server Management Studio - Query
NULL 2 7 2010-07-21 11:50:39.067 AUDIT_LOGIN sa D223 Microsoft SQL Server Management Studio - Query
*/
SELECT
DATEDIFF(MINUTE,X.EventTime,Y.EventTime) As ElapsedMinutes,
X.*
FROM
(
select ROW_NUMBER() over (PARTITION BY LoginName,HostName ORDER BY EventTime) AS RW,
* FROM logging
WHERE eventtype = 'AUDIT_LOGIN'
AND AppName = 'Microsoft SQL Server Management Studio - Query'
) X
LEFT OUTER JOIN (
select ROW_NUMBER() over (PARTITION BY LoginName,HostName ORDER BY EventTime) AS RW,
* FROM logging
WHERE eventtype = 'AUDIT_LOGOUT'
AND AppName = 'Microsoft SQL Server Management Studio - Query')
Y ON X.LoginName = Y.LoginName
AND X.HostName = Y.HostName
AND X.RW = Y.RWc
Lowell
July 21, 2010 at 3:52 pm
why i can't filter the login to capture only users that use the erp.
we have a table with the erp users in database called system.
when i add the syntax to the proc i get an error message in the sql
1.The activated proc [dbo].[LoggingProc] running on queue Logging_demo.dbo.LoggingQueue output the following: 'The server principal "sa" is not able to access the database "system" under the current security context.'
2.The activated proc [dbo].[LoggingProc] running on queue Logging_demo.dbo.LoggingQueue output the following: 'The service queue "LoggingQueue" is currently disabled.'
from the article with me added syntax in BOLD
--The proc to handle the events
-- Set options required for the XML data type.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE LoggingProc
AS
SET NOCOUNT ON;
DECLARE @message_body XML,
@message_type_name NVARCHAR(256),
@dialog UNIQUEIDENTIFIER ;
--Endless loop
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION ;
-- Receive the next available message
WAITFOR (
RECEIVE TOP(1)
@message_type_name=message_type_name,
@message_body=message_body,
@dialog = conversation_handle
FROM LoggingQueue
), TIMEOUT 2000
--Rollback and exit if no messages were found
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION ;
BREAK ;
END ;
--End conversation of end dialog message
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + cast(@dialog as nvarchar(40)) ;
END CONVERSATION @dialog ;
END ;
ELSE
BEGIN
IF (CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)) in (select USERLOGIN from system.dbo.USERS))
BEGIN
INSERT INTO Logging (
EventTime,
EventType,
LoginName,
HostName,
NTUserName,
NTDomainName,
Success,
FullLog)
VALUES
(
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS VARCHAR(100)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER),
@message_body)
END
END
COMMIT TRANSACTION
END
GO
July 21, 2010 at 4:57 pm
I'd think sa has access to everything if it exists...
if you don't filter by application name, your hours using the ERP will be inflated for developers who connect with SSMS to do stuff on the server.
for this part:
system.dbo.USERS
do you really have a database named [system], which has a table dbo.USERS? could that be a typo?
i guess you are assuming that a login on the server must be spelled the same as in your table?
Lowell
July 21, 2010 at 5:01 pm
also this part:
'The service queue "LoggingQueue" is currently disabled.'
you have to kick everyone off of the database to run the script to set up the queue; exclusive access is required. after that the proc will run fine. same thing happened to me because i had two windows open to my dev server's sandbox where i tested the code.
Lowell
July 22, 2010 at 12:37 pm
thanks for your help.
i'm looking on different approach and i will update later.
July 23, 2010 at 8:04 am
If you're looking at alternatives, you might look into a Server Side trace as mentioned in
http://qa.sqlservercentral.com/Forums/Topic909777-146-1.aspx
I got one started for my Logins (and Logouts) based upon the information here.
Steve
July 23, 2010 at 2:43 pm
yes this is one of my thought.
the question is when working with the profiler for logon and logout event,
how i can calculate to actual time that the user spent in the erp per day and make it reliable results?
THX
July 26, 2010 at 7:30 am
That is the beauty of using server side traces, you don't use profiler. Instead, you execute an sp_trace_create statement to create a trace to a trace file, execute a number of sp_trace_setevent statements to set exactly what you want to capture and for which events (Login and Logut in your case) and sp_trace_setstatus statements to start and stop the trace. Once the trace is stopped, you can select into a table from ::fn_trace_gettable the fields that you have captured.
Once you have it in a table, you can do some selects to get the Login and Logout date/time and perform calculations. If you capture Event Class (Login = 14 and Logout = 15), SPID and Login Name, you can identify the information you need to do the calculations.
I always have on trace running all the time but just before midnight I start an new trace and stop the old one and insert from the old one into a table. I have set up a table that has Trace Tracking information so that it is totally automated and I can see if any problems occured.
Steve
July 26, 2010 at 11:58 am
steve block (7/26/2010)
That is the beauty of using server side traces, you don't use profiler. Instead, you execute an sp_trace_create statement to create a trace to a trace file, execute a number of sp_trace_setevent statements to set exactly what you want to capture and for which events (Login and Logut in your case) and sp_trace_setstatus statements to start and stop the trace. Once the trace is stopped, you can select into a table from ::fn_trace_gettable the fields that you have captured.Once you have it in a table, you can do some selects to get the Login and Logout date/time and perform calculations. If you capture Event Class (Login = 14 and Logout = 15), SPID and Login Name, you can identify the information you need to do the calculations.
I always have on trace running all the time but just before midnight I start an new trace and stop the old one and insert from the old one into a table. I have set up a table that has Trace Tracking information so that it is totally automated and I can see if any problems occured.
Steve
with a trace file can you have it output the text data to a varchar(max) column? Profiler creates a varchar(4000) i think and i always get truncated queries when outputting to a table
July 26, 2010 at 12:18 pm
I'm really not sure but you could try it. You could also ask that question in the other thread I provided. I know there were some experts there.
What are you trying to capture from the Data field that is not in one of the other fields?
Steve
July 28, 2010 at 2:08 am
how i can write this logon trigger that if i the user have an error in the trigger his logon processes will still be successfully but i will be notified with an error.
THX
CREATE TRIGGER UTRIG_CAPTURE_PRIORITY_LOGINS
ON ALL SERVER WITH EXECUTE AS SELF
FOR LOGON
AS
BEGIN
BEGIN TRY
if (ORIGINAL_LOGIN() in (select reverse(USERLOGIN) from system.dbo.USERS)) and (select count(*) from master.sys.sysprocesses where loginame = ORIGINAL_LOGIN() and hostname = HOST_NAME())= 0 and not(HOST_NAME() = 'TEST-SQL')
begin
INSERT INTO Audit_Logging.dbo.TBL_SQL_LOGON_LOGOUT_AUDIT (LOGIN_NAME,HOST_NAME,LOGON_DATE) values (ORIGINAL_LOGIN(),HOST_NAME(),GETDATE())
end
END TRY
BEGIN CATCH
--ALERT ME BUT STILL ALOW LOGON
END CATCH
END
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply