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)
How to Implement SAP Business One projects successfully
How to count the entries user wise. **Helpful for go live**
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
3 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 |