Skip to Content

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.

 

image

 

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.

 

image

 

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)

 

image

 

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

 

image

 

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

 

image

 

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

 

image

 

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 DATETIME

DECLARE @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 = 1

END

ELSE

BEGIN

SET @Code = (Select Max(Code) from [@USER_DATA]) + 1

SET @Name = (Select Max(Name) from [@USER_DATA]) + 1

END

 

IF DATEDIFF(s, @LastLoginTime, @CurrentLoginTime) > 60 OR @LastLoginTime IS NULL

BEGIN

INSERT INTO [@USER_DATA] (Code, Name, U_UserCode, U_LoginDate, U_LoginTime) VALUES (@Code, @Name, @usercode, @CurrentLoginDate, @CurrentLoginTime)

END

END

 

 

image

 

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.

17 Comments
You must be Logged on to comment or reply to a post.
  • Sounds cool, but i wouldn’t use any of the options above if we have already UI object initialized – faster and more stable to use UI.Company.UserName (or DI.UserName/UserSignature in case of SSO usage). Makes sense?
    • Hi Alexey,

      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

  • Hi 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

    • Hi 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

  • Hello,

    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

  • 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

  • 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.

      • 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.