Products

Evoq Content Overview Content Creation Workflow Asset Management Mobile Responsive Personalization Content Analytics SEO Integrations Security Website PerformanceEvoq Engage Overview Community Management  Dashboard  Analytics  Member Profile Gamification Advocate Marketing Community Engagement  Ideas  Answers  Discussions  Groups  Wikis  Events Mobile ReadyDNN Support PackagesEvoq: CMS FeaturesEvoq OnDemandCompare DNN's CMS Products

Solutions

Content Management SystemMulti-Channel PublishingDigital MarketingOnline Community ManagementIntranet Software SolutionOur CustomersPrime

Resources

Test DrivesSchedule A DemoDocumentation CenterWebinarsWhite PapersProduct ManualsRequest PricingData SheetsCase StudiesEvoq Preferred Products Content Management Ecommerce Forms Identity Management and Authentication Site Management Social Themes

Partners

DNN Partners Partner Directory Become a DNN Partner Partner Program Overview  Hosting  Implementation  ISV  Training  Competencies

Community

Participate Ask a Question Forums Community Showcase DNN MVP  Program Overview  Lifetime MVPs  Honorary MVPs Subscribe to DNN Digest Advisory Groups  Awareness Advisory Group  Developer Advisory Group  Partner Advisory Group  Technology Advisory GroupLearn Documentation Wiki Community Blog Video Library Project History Development RoadmapDownload DNN Store Language Packs Manuals Nightly BuildsSecurity Policies Security Center

Blog

About

News Press Releases News Coverage Press KitCareersContact DNN

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsReportsReportsHelp with old users query?Help with old users query?
Previous
 
Next
New Post
3/22/2008 12:42 PM
 
Mike H


Joined: 2/16/2008
Posts: 34

I am a newbie who is totally lost

I am trying to write a report that would display "Full Name" "Email Address" "Last Login Date" and have only users that haven't login in the last 6 months show in the report.

Seems simple but boy have I gotten some funky things to appear.

Any Master willing to lend a hand?

 

Thanks,

Mike
 
New Post
3/22/2008 2:05 PM
 
workcontrol

Joined: 6/30/2006
Posts: 147

Try this

SELECT     u.FirstName, u.LastName, u.DisplayName, u.Email, am.LastLoginDate
FROM         {databaseOwner}{objectQualifier}aspnet_Membership AS am INNER JOIN
                      {databaseOwner}{objectQualifier}aspnet_Users AS au ON am.UserId = au.UserId INNER JOIN
                      {databaseOwner}{objectQualifier}Users AS u ON au.UserName = u.Username
WHERE     (am.LastLoginDate NOT BETWEEN DATEADD(month, - 6, GETDATE()) AND GETDATE())

I have sql black belt

Robert Tango
www.workcontrol.com
Custom Modules: UserManager|UserDirectory|UserImport|PortalSSO
 
New Post
3/23/2008 7:47 PM
 
Mike H


Joined: 2/16/2008
Posts: 34

Big time thanks work!!!     Quick reply :)

Well it got me a heck of a lot closer than I was.......but....... It is listing the most recent logins.

In other words it listed everyone that logged in today and no one else.

What I was hoping to get was only people who's last login was older than 6 months ago from today.

Any thoughts?

 

Thanks again

Mike
 
New Post
3/27/2008 3:19 PM
 
Fuji Nguyen

Joined: 5/31/2003
Posts: 219

Try this one

SELECT     u.FirstName, u.LastName, u.DisplayName, u.Email, am.LastLoginDate
FROM         {databaseOwner}{objectQualifier}aspnet_Membership AS am INNER JOIN
                      {databaseOwner}{objectQualifier}aspnet_Users AS au ON am.UserId = au.UserId INNER JOIN
                      {databaseOwner}{objectQualifier}Users AS u ON au.UserName = u.Username
WHERE

am.UserId NOT IN
(
SELECT UserId
FROM         {databaseOwner}{objectQualifier}aspnet_Membership
WHERE LastLoginDate BETWEEN DATEADD(month, - 6, GETDATE()) AND GETDATE()
)

This script is slight modification from Robert's script.  It gets all users and then excludes the accounts not login in the past 6 months (using sub query NOT IN)

Fuji Nguyen
FREE Visitor Hit Counter
Visit opensource.indyneinc.com for detail.
 
New Post
3/28/2008 11:05 PM
 
Mike H


Joined: 2/16/2008
Posts: 34

OK I'm stumped

Each one of these scripts only gives me who logged on today. Now I did realize today after thinking about it that my site is in development so I don't have anyone that hasn't logged in for 6 months but I would think it would just make the grid empty. Right?

Maybe someone with a history that has user logins older than 6 months could try these scripts and see if they work for them?

Also I'm hosted on godaddy so I don't know if that would make a difference? Seems it has messed with me on some other database issues (permissions primarily).

Please let me any other ideas.

Thanks to all that have replied I really appreciate it!!!!

Mike
 
 Page 1 of 2
12Next 
Previous
 
Next
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsReportsReportsHelp with old users query?Help with old users query?


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out