SELECT AaaUser.FIRST_NAME "Technician",
MAX(AaaLogin.NAME) "LoginName",
MAX(AaaContactInfo.EMAILID) "Email",
MAX(AaaAccSession.USER_HOST) "IP Address",
longtodate(MAX(AaaAccSession.OPENTIME)) "Last Logged In Time" FROM AaaUser
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
INNER JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
INNER JOIN AaaAccount ON AaaAccount.login_id=AaaLogin.LOGIN_ID
INNER JOIN AaaAccSession ON AaaAccount.ACCOUNT_ID = AaaAccSession.ACCOUNT_ID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
WHERE SDUser.STATUS = 'ACTIVE'
GROUP BY AaaUser.FIRST_NAME
客户运行后进一步提出需求：
可不可以查过去一个月的登录记录？
select ac.ACCOUNT_ID as "Account Id", au.FIRST_NAME as "User Name", LONGTODATE(acs.OPENTIME) as "Login Time", LONGTODATE(acs.OPENTIME) as "Login Time", LONGTODATE(acs.CLOSETIME) as "Logout Time", acs.USER_HOST as "IP Address", al.NAME as "Login Name", al.DOMAINNAME as "Domain Name", aci.EMAILID as "Email address", dd.DEPTNAME as "Department Name", LONGTODATE(au.CREATEDTIME) as "Account Created Time", repuser.FIRST_NAME as "Reporting To", sd.ISVIPUSER as "IsVipUser" from aaauser au left join AaaUserContactInfo auci on au.USER_ID=auci.USER_ID left join AaaContactInfo aci on auci.CONTACTINFO_ID = aci.CONTACTINFO_ID inner join sduser sd on au.USER_ID=sd.USERID left join UserDepartment ud on sd.USERID = ud.USERID left join DepartmentDefinition dd on ud.DEPTID=dd.DEPTID inner join portalusers pu on sd.USERID=pu.USERID inner join aaalogin al on au.USER_ID = al.USER_ID inner join aaaaccount ac on al.LOGIN_ID = ac.LOGIN_ID left join aaaaccsession acs on ac.ACCOUNT_ID = acs.ACCOUNT_ID left join aaauser repuser on repuser.USER_ID = sd.REPORTINGTO where pu.STATUS = 'ACTIVE' and acs.CLOSETIME > 0 and acs.OPENTIME between <from_lastmonth> and <to_lastmonth> order by acs.OPENTIME;