Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Sebastian Dammark 537 posts 1222 karma points
    Jun 10, 2020 @ 08:10
    Sebastian Dammark
    0

    Where to find Password Changed Date for Members

    Does any one know, where in the database I can find the date for when a Member last updated their password ?

  • Gerhard Grossberger 25 posts 127 karma points
    Jun 10, 2020 @ 08:52
    Gerhard Grossberger
    1

    I´d say it´s stored in [lastPasswordChangeDate] in table [umbracoUser]. Not sure if and how you can access it in CMS or API.

  • Sebastian Dammark 537 posts 1222 karma points
    Jun 10, 2020 @ 09:05
    Sebastian Dammark
    0

    Yeah I thought so as well :)

    I can't find the member in [umbracoUser], but I can find the member in [cmsMember], but this table doesn't have the [lastPasswordChangeDate] column :)

  • Wojciech Zasadni 5 posts 77 karma points
    Jun 10, 2020 @ 10:44
    Wojciech Zasadni
    1

    In V8 it is stored as normal property value (with alias: umbracoMemberLastPasswordChangeDate).

    It can be selected using:

    SELECT cm.LoginName, upd.DateValue, cpt.Alias
    FROM [cmsMember] as cm
    LEFT JOIN [umbracoContentVersion] as udv on udv.NodeId = cm.NodeId
    LEFT JOIN [umbracoPropertyData] as upd on upd.VersionId = udv.Id
    LEFT JOIN [cmsPropertyType] as cpt on cpt.Id = upd.PropertyTypeId
    WHERE cpt.Alias = 'umbracoMemberLastPasswordChangeDate'
    

    Result:

    enter image description here

    As I can see, in V7 it is also stored as property, (see: https://github.com/umbraco/Umbraco-CMS/blob/master-v7/src/Umbraco.Core/Models/Member.cs), so probably you can use something similar.

    I hope this will help. ;)

    Cheers,

  • Sebastian Dammark 537 posts 1222 karma points
    Jun 10, 2020 @ 11:05
    Sebastian Dammark
    0

    I almost think it's correct :)

    The only problem is that on U7 upd.versionId is a uniqueidentifier and udv.Id is an int, so I can't compare these 2.

    Now, I'm not an SQL wizard so I don't know how to go around this :)

  • Gerhard Grossberger 25 posts 127 karma points
    Jun 10, 2020 @ 11:20
    Gerhard Grossberger
    100

    Basically it´s the same, only they cleaned up the naming and types in v8.

    SELECT cm.LoginName, cv.VersionDate, cpt.Alias
    FROM [cmsMember] as cm
    inner join [dbo].[cmsContentVersion] cv on cv.ContentId = cm.nodeId
    inner join [dbo].[cmsPropertyData] pd on pd.versionId = cv.VersionId
    LEFT JOIN [cmsPropertyType] as cpt on cpt.Id = pd.PropertyTypeId
    where cpt.Alias = 'umbracoMemberLastPasswordChangeDate'
    and LoginName = '{MEMBERLOGIN HERE}' -- optional
    

    This should do the trick. Maybe double check if the versionDate is set correctly.

Please Sign in or register to post replies

Write your reply to:

Draft