How to track who is the current user logged into SAP Business One?
In this blog i want to highlight some ways you can track who the current user logged into SAP Business One is. I will show you how to do this via FMS, the UI API (2 ways) and finally by using the SP_TransactionNotification stored procedure.
1. Via Formatted Search
If your business process requires you to display the current user, for example in a field on a SAP Business One form, then a FMS is a good way to achieve this.
You can write a simple query and assign it to a particular field and it will return the current logged in user for you:
SELECT T0.U_NAME FROM OUSR T0 WHERE INTERNAL_K = $[USER]
2. Reading the value from Main Module Screen via UI API
Via the UI you can read the value from the top of the Modules Main Menu.
You will need to read the caption value from the static text which has a UID of 8 on Form 169.
The code you need to use is and you can include it in the part of your code that most fits your business requirement:
Dim oForm As SAPbouiCOM.Form
Dim oStatic As SAPbouiCOM.StaticText
Dim sLoggedInUser As String
oForm = SBO_Application.Forms.GetForm(169, 0)
oStatic = oForm.Items.Item("8").Specific
sLoggedInUser = oStatic.Caption
MsgBox("The current logged in User is " & sLoggedInUser)
3. Track user logins/logouts via UI API Events
To do this we first need to create a user defined table in SAP Business One – in my example it’s called USER_DATA. We also define 3 user defined fields (UDFs).
- UserCode records the current SAP Business One User
- LoginTimeDetails records the date and time the user logged in
- Direction records if the user logged in or logged out
In the code we will catch the application Company Changed event and then write the usercode, time and direction into the user defined table.
If EventType = SAPbouiCOM.BoAppEventTypes.aet_CompanyChanged Then
Try
oRS = oCompany.GetBusinessObject
(SAPbobsCOM.BoObjectTypes.BoRecordset)
oUserName = SBO_Application.Company.UserName
oDate = DateTime.Now
oRS.DoQuery(“Select Max(Code) from [@USER_DATA]”)
oCurrentLogCode = oRS.Fields.Item(0).Value
oCode = oCurrentLogCode
oName = oCurrentLogCode
If oCurrentLogCode = “” Then
oCode = 1
oName = 1
Else
oCode = oCode + 1
oName = oName + 1
End If
oRS.DoQuery(“INSERT INTO [@USER_DATA] (Code, Name,
U_UserCode, U_LoginTimes, U_Direction) VALUES (” & oCode & “,” &
oName & “,'” & oCurrentUser & “‘,'” & oDate & “‘,’Logged Out’)”)
oRS.DoQuery(“INSERT INTO [@USER_DATA] (Code, Name,
U_UserCode, U_LoginTimes, U_Direction) VALUES (” & oCode + 1 &
“,” & oName + 1 & “,'” & oUserName & “‘,'” & oDate & “‘,’Logged In’)”) Catch ex As Exception
MsgBox(ex.Message.ToString())
End Try
oCurrentUser = oUserName
End If
4. Via the SP_TransactionNotification
If not already done create the same User Defined Table as described above in 3 or simply add the additonal user defined fields to the existing table. The UDFs are:
- UserCode records the current SAP Business One User
- LoginDate records the date the user logged in
- LoginTime records the time the user logged in
We use the same principle in this SP_TransactionNotification as we used via the UI code above but we will record Date and Time in two different UDFs and we will also only log a new entry if Login Time is null or its more that 60 seconds since the same user logged in.
DECLARE @usercode NVARCHAR(8)
DECLARE @CurrentLoginDate DATETIMEDECLARE @CurrentLoginTime NVARCHAR(8)
DECLARE @LastLoginTime NVARCHAR (8)
DECLARE @Code NVARCHAR (8)
DECLARE @Name NVARCHAR (30)
IF @object_type = '12' and @transaction_type = 'U'
BEGIN
SET @usercode = (SELECT user_code FROM [OUSR] WHERE INTERNAL_K = @list_of_cols_val_tab_del)SET @CurrentLoginDate = GETDATE()
SET @CurrentLoginTime = CONVERT(VARCHAR(8), GETDATE(), 108)
SET @LastLoginTime = (SELECT MAX(U_LoginTime) FROM [@USER_DATA] WHERE U_UserCode = @usercode)
IF (Select Max(Code) from [@USER_DATA]) IS NULL
BEGIN
SET @Code = 1SET @Name = 1END
ELSE
BEGIN
SET @Code = (Select Max(Code) from [@USER_DATA]) + 1SET @Name = (Select Max(Name) from [@USER_DATA]) + 1
END
IF DATEDIFF(s, @LastLoginTime, @CurrentLoginTime) > 60 OR @LastLoginTime IS NULL
BEGININSERT INTO [@USER_DATA] (Code, Name, U_UserCode, U_LoginDate, U_LoginTime) VALUES (@Code, @Name, @usercode, @CurrentLoginDate, @CurrentLoginTime)
END
END
Please note in this example i have directly updated via SQL Update stataements a User defined Table. This is permitted as this is not a UDO User defined table.
Nice summary, a lot's of people has been asked these question in several threads.
Regards
J
Yes absolutely - requirements will be different for each user but the aim of the blog is to show some different methods that might be useful in other scenarios too.
Thanks for your comment.
Thanks,
Lisa
Would it be posisble to gnerate a message box via the sbo_sp_transactionnotification which would show which user is logged in?
Regards
Arsh
I do not think its possible to invoke a messagebox via the stored procedure without using something like VB Script to call the stored procedure and then display the message box.
Cheers,
Lisa
can we have username in Query Report?
in SBO 8.8 is possible to pass a parameter with the user code (Usercode@) but only for layouts.
Is possible to pass the user code logged in SBO to a Crystal reports that is a report???
Best regards, Santi
I'm not sure if this is possible. I would suggest that you post a question on the SAP Business One Reporting & Printing Reporting and Printing
Cheers,
Lisa
Dear Lisa,
I am too late to comment on this but Can we get Wrk Station ID also in this ?
Thanks
Ashish Gupte
Thanks Lisa for such a nice trick here,
But How if I Need to know realtime, now, who's the users login on SAP, which ip address, using what username ?
can I get that with a simple query ? because this often asked by the customer
Thanks,
Hendra
Thanks Lisa for wonder full tip ..
Is there any other way to get the Out put , it means Through Query wise not FMS
Starting from version 8.81 you can use the following query in the Stored Procedure to get the current user code:
SELECT TOP 1 UserCode FROM USR5 WHERE SessionID = @@SPID ORDER BY Date DESC, Time DESC
This would return the last logged in user of the current session ID...
With some minor changes something similar should be possible on HANA...
The same query works in formatted searches by the way, but only if the user has the permission to run non-select queries. Otherwise the formatted searches run under a different session ID using read-only credentials.
Enjoy...
Joerg.
hello Joerg Aldinger
what about sap B1 2007B...
if you have query plz share here
thanks for advance
--Ramudu
You can use Lisa's method of capturing Updates to object type 12 in the SBO_SP_TransactionNotification and insert/update into a UDT like this:
DELETE FROM [@USERLOGINS] WHERE Code = CAST(@@SPID AS nvarchar(8));
INSERT INTO [@USERLOGINS] (Code, Name, U_UserCode) VALUES (CAST(@@SPID AS nvarchar(8)), CAST(@@SPID AS nvarchar(8)), @list_of_cols_val_tab_del);
Then, later on you can consult that table like:
SELECT U_UserCode FROM [@USERLOGINS] WHERE Code = CAST(@@SPID AS nvarchar(8));
This will be 99% correct, not 100%, since the update to object type 12 also happens when a super user updates some other user, and thus overwrites his previous login. But it will be correct for any user not updating other user's data.
On a side note, my recommendation is to get rid of 2007 as soon as possible. You (or your customer) is missing out on a lot of new stuff and improvements.
Hope that helps.
Joerg.