Query to get list of technicians who haven't logged in to the application for last x days.(PGSQL)
Compatible from builds above 10600
Last Tested in builds PGSQL (14300)
QUERY:
SELECT AaaAccount.ACCOUNT_ID as "Login Account ID",
AaaUser.FIRST_NAME "Technician Name ( First Name )",
max(AaaLogin.NAME) "Login Name",
longtodate(max(aaaaccsession.opentime)) "Last login time",
CASE WHEN aaaaccsession.USER_HOST is NULL THEN '-' ELSE aaaaccsession.USER_HOST END as "IP Address",
AaaLogin.DOMAINNAME as "Domain",
AaaContactInfo.EMAILID as "Email",
dd.DEPTNAME as "Department",
LONGTODATE(AaaUser.CREATEDTIME) as "Account Created At",
repuser.FIRST_NAME as "Reporting Manager",
SDUser.ISVIPUSER as "VIP User" FROM AaaUser
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
left join UserDepartment ud on SDUser.USERID = ud.USERID
left join DepartmentDefinition dd on ud.DEPTID=dd.DEPTID
inner join portalusers pu on SDUser.USERID=pu.USERID
inner join portaltechnicians pt on pu.ID = pt.ID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN AaaAccount ON AaaAccount.login_id=AaaLogin.LOGIN_ID
LEFT JOIN AaaAccSession ON AaaAccount.ACCOUNT_ID = AaaAccSession.ACCOUNT_ID
left join aaauser repuser on repuser.USER_ID = SDUser.REPORTINGTO
WHERE SDUser.STATUS='ACTIVE' and aaaaccsession.session_id=(select max(session_id) from aaaaccsession where account_id=aaaaccount.account_id )
group by AaaAccount.ACCOUNT_ID,aaaaccsession.user_host,aaalogin.domainname,aaacontactinfo.emailid,dd.deptname,aaauser.createdtime,repuser.first_name,sduser.isvipuser,AaaUser.FIRST_NAME having extract(epoch from(now()::TIMESTAMP - to_timestamp(max(AaaAccSession.OPENTIME)/1000)::TIMESTAMP))/3600/24 > 15
UNION
SELECT AaaAccount.ACCOUNT_ID as "Login Account ID",
AaaUser.FIRST_NAME "Technician Name ( First Name )",
max(AaaLogin.NAME) "Login Name",
longtodate(max(aaaaccsession.opentime)) "Last login time",
CASE WHEN aaaaccsession.USER_HOST is NULL THEN '-' ELSE aaaaccsession.USER_HOST END as "IP Address",
AaaLogin.DOMAINNAME as "Domain",
AaaContactInfo.EMAILID as "Email",
dd.DEPTNAME as "Department",
LONGTODATE(AaaUser.CREATEDTIME) as "Account Created At",
repuser.FIRST_NAME as "Reporting Manager",
SDUser.ISVIPUSER as "VIP User" FROM AaaUser
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
left join UserDepartment ud on SDUser.USERID = ud.USERID
left join DepartmentDefinition dd on ud.DEPTID=dd.DEPTID
inner join portalusers pu on SDUser.USERID=pu.USERID
inner join portaltechnicians pt on pu.ID = pt.ID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN AaaAccount ON AaaAccount.login_id=AaaLogin.LOGIN_ID
LEFT JOIN AaaAccSession ON AaaAccount.ACCOUNT_ID = AaaAccSession.ACCOUNT_ID
left join aaauser repuser on repuser.USER_ID = SDUser.REPORTINGTO
WHERE SDUser.STATUS='ACTIVE' and aaaaccsession.session_id is null
group by AaaAccount.ACCOUNT_ID,aaaaccsession.user_host,aaalogin.domainname,aaacontactinfo.emailid,dd.deptname,aaauser.createdtime,repuser.first_name,sduser.isvipuser,AaaUser.FIRST_NAME
ORDER BY 1
AaaUser.FIRST_NAME "Technician Name ( First Name )",
max(AaaLogin.NAME) "Login Name",
longtodate(max(aaaaccsession.opentime)) "Last login time",
CASE WHEN aaaaccsession.USER_HOST is NULL THEN '-' ELSE aaaaccsession.USER_HOST END as "IP Address",
AaaLogin.DOMAINNAME as "Domain",
AaaContactInfo.EMAILID as "Email",
dd.DEPTNAME as "Department",
LONGTODATE(AaaUser.CREATEDTIME) as "Account Created At",
repuser.FIRST_NAME as "Reporting Manager",
SDUser.ISVIPUSER as "VIP User" FROM AaaUser
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
left join UserDepartment ud on SDUser.USERID = ud.USERID
left join DepartmentDefinition dd on ud.DEPTID=dd.DEPTID
inner join portalusers pu on SDUser.USERID=pu.USERID
inner join portaltechnicians pt on pu.ID = pt.ID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN AaaAccount ON AaaAccount.login_id=AaaLogin.LOGIN_ID
LEFT JOIN AaaAccSession ON AaaAccount.ACCOUNT_ID = AaaAccSession.ACCOUNT_ID
left join aaauser repuser on repuser.USER_ID = SDUser.REPORTINGTO
WHERE SDUser.STATUS='ACTIVE' and aaaaccsession.session_id=(select max(session_id) from aaaaccsession where account_id=aaaaccount.account_id )
group by AaaAccount.ACCOUNT_ID,aaaaccsession.user_host,aaalogin.domainname,aaacontactinfo.emailid,dd.deptname,aaauser.createdtime,repuser.first_name,sduser.isvipuser,AaaUser.FIRST_NAME having extract(epoch from(now()::TIMESTAMP - to_timestamp(max(AaaAccSession.OPENTIME)/1000)::TIMESTAMP))/3600/24 > 15
UNION
SELECT AaaAccount.ACCOUNT_ID as "Login Account ID",
AaaUser.FIRST_NAME "Technician Name ( First Name )",
max(AaaLogin.NAME) "Login Name",
longtodate(max(aaaaccsession.opentime)) "Last login time",
CASE WHEN aaaaccsession.USER_HOST is NULL THEN '-' ELSE aaaaccsession.USER_HOST END as "IP Address",
AaaLogin.DOMAINNAME as "Domain",
AaaContactInfo.EMAILID as "Email",
dd.DEPTNAME as "Department",
LONGTODATE(AaaUser.CREATEDTIME) as "Account Created At",
repuser.FIRST_NAME as "Reporting Manager",
SDUser.ISVIPUSER as "VIP User" FROM AaaUser
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
left join UserDepartment ud on SDUser.USERID = ud.USERID
left join DepartmentDefinition dd on ud.DEPTID=dd.DEPTID
inner join portalusers pu on SDUser.USERID=pu.USERID
inner join portaltechnicians pt on pu.ID = pt.ID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN AaaAccount ON AaaAccount.login_id=AaaLogin.LOGIN_ID
LEFT JOIN AaaAccSession ON AaaAccount.ACCOUNT_ID = AaaAccSession.ACCOUNT_ID
left join aaauser repuser on repuser.USER_ID = SDUser.REPORTINGTO
WHERE SDUser.STATUS='ACTIVE' and aaaaccsession.session_id is null
group by AaaAccount.ACCOUNT_ID,aaaaccsession.user_host,aaalogin.domainname,aaacontactinfo.emailid,dd.deptname,aaauser.createdtime,repuser.first_name,sduser.isvipuser,AaaUser.FIRST_NAME
ORDER BY 1
New to ADSelfService Plus?
Related Articles
Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...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 ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...Script to send reminder mail to the users who haven't logged into the application for the last X days.
Please follow the below steps to send mail to the users who haven't logged in for the last X days: Step 1: Navigate to Reports--->New Query Report--->Execute the given query--->Now the report will be generated. Step 2: Save the report with a name ...Query to get complete SLA details. (PGSQL)
Tested in build PGSQL (14300) To get the complete list of SLA configured in the application. PGSQL: SELECT sdo.name "Site Name" , sla.slaname "SLA", MAX(sla.duebydays) "SLA Days", MAX(sla.duebyhours) "SLA Hours", MAX(sla.duebyminutes) "SLA Minutes" , ...