SQL query to return the last login of CRM users

apollo7
apollo7 used Ask the Experts™
on
CRM 4.0 to D365 CE upgrade

We are trying to upgrade CRM 4.0 to D365 CE.  Part of the process is to establish the last login of CRM Users.  We have tried the query  below in the CRM 4.0 database but it is not returning the values we want.

select us.FullName, max(a.CreatedOn) as LastLoginDate from Audit a
inner join SystemUser us on a.ObjectId = us.SystemUserId
where
a.Operation = 4 and us.IsDisabled = 0
group by us.FullName

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
The fullnames could have a duplicate(s).

select us.FullName, a.LastLoginDate
from (
    select ObjectId, max(CreatedOn) as LastLoginDate
    from Audit
    group by ObjectId
) as a
inner join SystemUser us on a.ObjectId = us.SystemUserId
order by FullName, LastLoginDate

Author

Commented:
Thanks Scott, will give this a try
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Oops, sorry, left off the WHERE conditions:

select us.FullName, a.LastLoginDate
from (
    select ObjectId, max(CreatedOn) as LastLoginDate
    from Audit
    where Operation = 4
    group by ObjectId
) as a
inner join SystemUser us on a.ObjectId = us.SystemUserId
where us.IsDisabled = 0
order by FullName, LastLoginDate
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
If Audit was not enabled, is it possible to get the LastLoginDate from another table or from IIS?

Author

Commented:
Thanks for the query
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
If Audit was not enabled, is it possible to get the LastLoginDate from another table or from IIS?

Yes.  Sorry, just now saw this, been extremely busy the last few days.

select us.FullName, COALESCE(a.LastLoginDate, ot.LastLoginDate) AS LastLoginDate
from SystemUser us
outer apply (
    select ObjectId, max(CreatedOn) as LastLoginDate
    from Audit
    where Operation = 4 and us.IsDisabled = 0 /*check this table if not Disabled*/
    group by ObjectId
) as a
outer apply (
    select ObjectId, max(CreatedOn) as LastLoginDate
    from other_table
    where ... and us.IsDisabled = 1 /*check this table if Disabled*/
    group by ObjectId
) as ot
order by FullName, LastLoginDate

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial