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)

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