Prasanna s

February 7, 2014 1 minute read

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:

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)

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)

Former Member

March 12, 2014 at 8:46 pm

Thanks!!! It was really helpful 😉

AndakondaRamudu A

November 25, 2014 at 9:00 am

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

AndakondaRamudu A

June 17, 2015 at 9:48 am

HI Sir

i want know Query for 2007B

please share here if you have

Former Member

November 2, 2017 at 2:44 pm

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

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

See also:

Assigned Tags