Press Ctrl / CMD + C to copy this to your clipboard.
This post will be reported to the moderators as potential spam to be looked at
I'm using Umbraco 7.6.4 and I'm attempting to retrieve through SQL the last login date for my site members. I'm using the following SQL
SELECT MEMB.LoginName, MEMB.Email, MEMBDATA.[datadate] AS LastLoginDate, membdata.*
FROM UmbracoICA.dbo.umbracoNode AS MEMBTYPEID
INNER JOIN UmbracoICA.dbo.cmsContent AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id
INNER JOIN UmbracoICA.dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType
INNER JOIN UmbracoICA.dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND MEMBDATA.propertytypeid = MEMBTYPES.id
INNER JOIN UmbracoICA.dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId
WHERE MEMBTYPEID.nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43'
AND MEMBTYPES.id = 218
AND LoginName = 'comahony'
The date that is returned is July of this year but against my actual member data in the back office:
Does anyone know how I can pull the last login date please?
Is there any reason you're doing this with SQL and not the member service? With the member service you could do this really easily if you do something like this:
var member = ApplicationContext.Services.MemberService.GetById(0);
Yes this is powering external reports so there's no direct communication to Umbraco itself or any of it's API's basically a series of cross business data is being collated together and I need to power RS reports directly from the SQL data.
did you work out a way of doing this? i have to delete all members where last login is a certain date. i can do this via the member service.....but it would take a long time.
slightly modified sql to get all members' LastLoginDate:
SELECT MEMB.Email, MAX(MEMBDATA.dataDate) AS LastLoginDate
FROM dbo.umbracoNode AS MEMBTYPEID INNER JOIN
dbo.cmsContent AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id INNER JOIN
dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType INNER JOIN
dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND MEMBDATA.propertytypeid = MEMBTYPES.id INNER JOIN
dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId
WHERE (MEMBTYPEID.nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')
GROUP BY MEMB.LoginName, MEMB.Email
is working on a reply...
Write your reply to:
Image will be uploaded when post is submitted