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

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

    1. Prasanna s 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.

      (0) 
      1. 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

        (0) 
            1. Prasanna s 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)

              (0) 

Leave a Reply