Query to find the last logged time of a contact in the portal
select aas.session_id "SessionID", aal.name "User Name",aau.FIRST_NAME "Contact Name",org.NAME "Account Name", parentorg.NAME "Parent Account Name",suborg.name "Sub Account", aas.user_host "User Host", aas.application_host "Application Host", longtodate(aas.opentime) "Start Time", longtodate(aas.closetime) "End Time", aas.status "Status" from AaaAccSession aas left join aaalogin aal on aas.account_id=aal.login_id left join sduser sd on aal.user_id=sd.userid left join departmentuser depu on sd.userid=depu.userid left join aaauser aau on depu.userid=aau.user_id LEFT JOIN Customer_Requester custreq ON sd.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN SUBACCOUNT sab ON custreq.CUSTOMER_ID=sab.subaccountid LEFT JOIN AaaOrganization suborg ON sab.subaccountid=suborg.ORG_ID LEFT JOIN SUBACCOUNT sab2 ON custreq.CUSTOMER_ID=sab2.parentaccountid LEFT JOIN AaaOrganization parentorg ON sab.parentaccountid=parentorg.ORG_ID where (depu.USERTYPE = 'Contact') group by aas.session_id
New to ADSelfService Plus?
Related Articles
Login Frequency Query Report
The below report would help us find the last logged in time of the users in SCP 11.0 SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP ...query to find the unapproved contacts -> PGSQL
Below is the query to find the unapproved contacts -> PGSQL-> can be used from version 11 SELECT aaauser.User_id AS "User ID", aaauser.first_name "First name", aaauser.last_name "Last name", hd.id "Portal ID", hd.displayname "Portal Name", ...Query to take report of all techinician details with last login time (V10.6) (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) The query is compatiable with version 10.6 and above: Query: Select au.FIRST_NAME "Technicianname" , ar.NAME "Role",al.name "Login Name",aci.emailid "EmailID",SDUSER.STATUS ...List of contacts logged into the system
Mysql select aas.session_id "SessionID", aal.name "User Name",aau.FIRST_NAME "Contact Name",org.NAME "Account Name", aas.user_host "User Host", aas.application_host "Application Host", longtodate(aas.opentime) "Start Time", longtodate(aas.closetime) ...Last logged in time of SupportReps
select max(au.first_name)'Technician',al.name"Login Name", longtodate(max(acs.opentime))"Last Logged in Time" from aaaaccsession acs left join aaaaccount act on act.account_id=acs.account_id left join aaalogin al on al.login_id=act.login_id left join ...