Skip to Content
Author's profile photo Prasanna s

How to know the current user LOGIN status in 9-Version

Below query will help you to know the Current login status of SAP users.

Version Compatible:  9 version PL-4 and above.

Use of this Query:

1. You can know which user login currently

2. If same License is using more than one person then, you develop an small application in .Net and install it in all the clients. If the user want to login its easy to check that he can login or not.

QUERY:

select UserCode ,

(select case when Action = ‘I’ then ‘OPEN’ when Action = ‘O’ then ‘CLOSED’ end  from USR5 r1

where r1.UserCode = r.UserCode and r1.Date = r.Date and r1.Time = Max(r.Time)) [Login Status]

, DATE, ClientName, Max(r.Time) [Login Time]

from USR5 r where ProcName = ‘SAP Business One.exe’  and CONVERT(VARCHAR(50), r.DATE ,103) = CONVERT(VARCHAR(50), getdate() ,103)

group by

UserCode , DATE, ClientName

having MAX(time) = Max(r.Time)

OUTPUT:

Untitled.png

Feedback comments are welcome…..

Example for Multiple DB’s

NOTE: wherever i have mentioned “Your DB Name-” there you have to change it to your correct DB name.

select  ‘Your DB Name-1’ [DB Name], UserCode ,

(select case when Action = ‘I’ then ‘ACTIVE’ when Action = ‘O’ then ‘IN-ACTIVE’ else ‘IN-ACTIVE’ end

from [Your DB Name-1].dbo.USR5 r1

where r1.UserCode = r.UserCode and r1.Date = r.Date and r1.Time = Max(r.Time)) [Login Status]

, DATE, ClientName, Max(r.Time) [Login Time]

from [Your DB Name-1].dbo.USR5 r where ProcName = ‘SAP Business One.exe’  and CONVERT(VARCHAR(50), r.DATE ,103) = CONVERT(VARCHAR(50), getdate() ,103)

group by

UserCode , DATE, ClientName

having MAX(time) = Max(r.Time)

union all

select  ‘Your DB Name-2’ ,UserCode ,

(select case when Action = ‘I’ then ‘ACTIVE’ when Action = ‘O’ then ‘IN-ACTIVE’ else ‘IN-ACTIVE’ end

from [Your DB Name-2].dbo.USR5 r1

where r1.UserCode = r.UserCode and r1.Date = r.Date and r1.Time = Max(r.Time)) [Login Status]

, DATE, ClientName, Max(r.Time) [Login Time]

from [Your DB Name-2].dbo.USR5 r where ProcName = ‘SAP Business One.exe’  and CONVERT(VARCHAR(50), r.DATE ,103) = CONVERT(VARCHAR(50), getdate() ,103)

group by

UserCode , DATE, ClientName

having MAX(time) = Max(r.Time)

See also:

How to Implement SAP Business One projects successfully

How to count the entries user wise. **Helpful for go live**

Database design in SAP Business One

How to know the current user LOGIN status in 9-Version

Assigned tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michael Carrabine
      Michael Carrabine

      Thanks Prasanna. This is very helpful.

      Author's profile photo Former Member
      Former Member

      hi prasan,

      Awesome!!!!

      Its very helpful.

      Please write more for us...

      Author's profile photo Balaji Sampath
      Balaji Sampath

      Hi Prasan

      its helpful and it shown for one database it would be appreciate this query will catch all the database.

      Author's profile photo Prasanna s
      Prasanna s
      Blog Post Author

      Hi Balaji,

      Just add "UNION ALL" and DatabaseName.dbo in front of table name you will get the result.

      However am updating the query with 2 database.

      Wherever i have mentioned "Your DB Name-1" you have to change it to your correct DB name.

      Try and let me know your feedback.

      Author's profile photo Balaji Sampath
      Balaji Sampath

      Hi Prasanna

      Thanks for your efforts.   I did what you told but i getting following error

      Msg 208, Level 16, State 1, Line 1

      Invalid object name 'USR5'.

      I attached modified query.

      select 'ST_Live', UserCode ,

      (select case when Action = 'I' then 'ACTIVE' when Action = 'O' then 'IN-ACTIVE' else 'IN-ACTIVE' end  from USR5 r1

      where r1.UserCode = r.UserCode and r1.Date = r.Date and r1.Time = Max(r.Time)) [Login Status]

      , DATE, ClientName, Max(r.Time) [Login Time]

      from [ST_Live].dbo.USR5 r where ProcName = 'SAP Business One.exe'  and CONVERT(VARCHAR(50), r.DATE ,103) = CONVERT(VARCHAR(50), getdate() ,103)

      group by

      UserCode , DATE, ClientName

      having MAX(time) = Max(r.Time)

      union all

      select 'TT_Live' ,UserCode ,

      (select case when Action = 'I' then 'ACTIVE' when Action = 'O' then 'IN-ACTIVE' else 'IN-ACTIVE' end  from USR5 r1

      where r1.UserCode = r.UserCode and r1.Date = r.Date and r1.Time = Max(r.Time)) [Login Status]

      , DATE, ClientName, Max(r.Time) [Login Time]

      from [TT_Live].dbo.USR5 r where ProcName = 'SAP Business One.exe'  and CONVERT(VARCHAR(50), r.DATE ,103) = CONVERT(VARCHAR(50), getdate() ,103)

      group by

      UserCode , DATE, ClientName

      having MAX(time) = Max(r.Time)

      Regards

      Balaji

      Author's profile photo Prasanna s
      Prasanna s
      Blog Post Author

      What is your patch level, query will support from Version- 9

      Author's profile photo Balaji Sampath
      Balaji Sampath

      Hi Prasanna

      SAP BUSINESS ONE 9.0 PL 8

      Regards

      Balaji

      Author's profile photo Prasanna s
      Prasanna s
      Blog Post Author

      Try this below query,

      select 'ST_Live' [DB Name], UserCode ,

      (select case when Action = 'I' then 'ACTIVE' when Action = 'O' then 'IN-ACTIVE' else 'IN-ACTIVE' end 

      from [ST_Live].dbo.USR5 r1

      where r1.UserCode = r.UserCode and r1.Date = r.Date and r1.Time = Max(r.Time)) [Login Status]

      , DATE, ClientName, Max(r.Time) [Login Time]

      from [ST_Live].dbo.USR5 r where ProcName = 'SAP Business One.exe'  and CONVERT(VARCHAR(50), r.DATE ,103) = CONVERT(VARCHAR(50), getdate() ,103)

      group by

      UserCode , DATE, ClientName

      having MAX(time) = Max(r.Time)

      union all

      select 'TT_Live' ,UserCode ,

      (select case when Action = 'I' then 'ACTIVE' when Action = 'O' then 'IN-ACTIVE' else 'IN-ACTIVE' end 

      from [TT_Live].dbo.USR5 r1

      where r1.UserCode = r.UserCode and r1.Date = r.Date and r1.Time = Max(r.Time)) [Login Status]

      , DATE, ClientName, Max(r.Time) [Login Time]

      from [TT_Live].dbo.USR5 r where ProcName = 'SAP Business One.exe'  and CONVERT(VARCHAR(50), r.DATE ,103) = CONVERT(VARCHAR(50), getdate() ,103)

      group by

      UserCode , DATE, ClientName

      having MAX(time) = Max(r.Time)

      Author's profile photo Former Member
      Former Member

      Thanks!!! It was really helpful 😉

      Author's profile photo AndakondaRamudu A
      AndakondaRamudu A

      Hi sir

      Thanks for  your valuable efforts ....

      You Blogs are helpful for me ...Thanks

      i want to know the query for SAP  2007B PL:17

      Author's profile photo AndakondaRamudu A
      AndakondaRamudu A

      HI Sir

      i want know Query for 2007B

      please share here if you have

      Author's profile photo Former Member
      Former Member

      Hi, I've modified the query to show all users currently logged in all SAP databases. The information can be found in SBO-COMMON.dbo.srgc.

      Here is the new query:

      DECLARE @MyCursor CURSOR;
      DECLARE @MyField nvarchar(100);
      DECLARE @sqla nvarchar(max);
      DECLARE @sqlb nvarchar(max);
      BEGIN
      
      SET @sqla =
      ' 
      select
      UserCode,
      (select case when Action=''I'' then ''OPEN'' when Action=''O'' then ''CLOSED'' end from MYDB.dbo.USR5 r1
      where r1.UserCode=r.UserCode and r1.Date=r.Date and r1.Time=Max(r.Time)) [Login Status], 
      ''MYDB'' as ''Database'', 
      DATE, 
      ClientName, 
      Max(r.Time) [Login Time]
      from MYDB.dbo.USR5 r where ProcName=''SAP Business One.exe'' and CONVERT(VARCHAR(50), r.DATE, 103)=CONVERT(VARCHAR(50), getdate(), 103)
      group by
      UserCode, DATE, ClientName
      having MAX(time)=Max(r.Time)
      ';
      
      SET @sqlb = '';
      
          SET @MyCursor = CURSOR FOR
          SELECT dbname FROM "sbo-common".dbo.srgc      
      	
          OPEN @MyCursor 
          FETCH NEXT FROM @MyCursor 
          INTO @MyField
      
          WHILE @@FETCH_STATUS = 0
          BEGIN
      	  SET @sqlb = @sqlb + ' union all ' + replace(@sqla,'MYDB',@MyField);
            FETCH NEXT FROM @MyCursor 
            INTO @MyField 
          END; 
      
          CLOSE @MyCursor ;
          DEALLOCATE @MyCursor;
      	
      	SET @sqlb = substring(@sqlb, 11, len(@sqlb));
      	SET @sqlb = @sqlb + ' order by usercode';
      	execute(@sqlb);
      
      END;

      Hope it helps.

      Thanks