Query report to extract Active Technician Login and IP Address (MSSQL & PGSQL)

Query report to extract Active Technician Login and IP Address (MSSQL & PGSQL)

Tested in builds PGSQL (14300) or MSSQL (14306)

Go to Reports- New Query Report and execute this query.

select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of Logins", LONGTODATE(max(acs.OPENTIME)) as "Last Login Time", acs.user_host_name as "Ip Address",al.NAME as "Login Name", al.DOMAINNAME as "Domain", aci.EMAILID as "Email", dd.DEPTNAME as "Department", LONGTODATE(au.CREATEDTIME) as "Account Created At", repuser.FIRST_NAME as "Reporting Manager", sd.ISVIPUSER as "VIP User" 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 left join usersitemapping usm on au.USER_ID=usm.USERID inner join portalusers pu on sd.USERID=pu.USERID inner join portaltechnicians pt on pu.ID = pt.ID 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 usm.SITEID in ($Site) and (acs.OPENTIME is NULL or acs.OPENTIME > <from_today> ) group by ac.ACCOUNT_ID, au.FIRST_NAME, al.NAME, al.DOMAINNAME, aci.EMAILID, dd.DEPTNAME, au.CREATEDTIME, sd.REPORTINGTO, sd.ISVIPUSER, repuser.FIRST_NAME,acs.user_host_name

order by au.FIRST_NAME

                    New to ADSelfService Plus?