Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member197621
Active Contributor

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

12 Comments
Labels in this area