Query to Pull out Login Hours

Query to Pull out Login Hours

Purpose 
      To generate the Total Login Hours per Technician. 

Query :
  1. select ai as "Account ID", tn as "Technician Name ( First Name )", lh as "Login hours", LONGTODATE(llt) as "Last Login Time", ln as "Login Name", domain as "Domain", email as "Email", dept as "Department", LONGTODATE(act) as "Account Created At", rm as "Reporting Manager", vu as "VIP User" from ( select ac.ACCOUNT_ID as "ai", au.FIRST_NAME as "tn", CASE WHEN max(acs.CLOSETIME) is NULL THEN '-' ELSE CAST(trunc(sum(CASE WHEN (acs.CLOSETIME = 0 and acs.STATUS='CLOSED') THEN 0 WHEN (acs.CLOSETIME = 0 and acs.STATUS='ACTIVE') THEN CAST(extract(epoch from now()) AS BIGINT)*1000 - acs.OPENTIME ELSE acs.CLOSETIME - acs.OPENTIME END)/3600000,0) AS varchar) || ' Hours ' || CAST(trunc(sum(CASE WHEN (acs.CLOSETIME = 0 and acs.STATUS='CLOSED') THEN 0 WHEN (acs.CLOSETIME = 0 and acs.STATUS='ACTIVE') THEN CAST(extract(epoch from now()) AS BIGINT)*1000 - acs.OPENTIME ELSE acs.CLOSETIME - acs.OPENTIME END)%3600000/60000, 0) AS varchar) || ' Minutes' END as "lh", (max(acs.OPENTIME)) as "llt", al.NAME as "ln", al.DOMAINNAME as "domain", aci.EMAILID as "email", dd.DEPTNAME as "dept", (au.CREATEDTIME) as "act", repuser.FIRST_NAME as "rm", sd.ISVIPUSER as "vu" 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 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'  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 order by au.FIRST_NAME) as loginhours order by tn
  2.  
Note : To Generate Login hours per Technician of only current month, Migrate to Reports > Login Reports >  Login Hours of Technicians in Current Month.

In additional, to generate the Total Login Hours per Technician between a range,
  1. select ai as "Account ID", tn as "Technician Name ( First Name )", lh as "Login hours", LONGTODATE(llt) as "Last Login Time", ln as "Login Name", domain as "Domain", email as "Email", dept as "Department", LONGTODATE(act) as "Account Created At", rm as "Reporting Manager", vu as "VIP User" from ( select ac.ACCOUNT_ID as "ai", au.FIRST_NAME as "tn", CASE WHEN max(acs.CLOSETIME) is NULL THEN '-' ELSE CAST(trunc(sum(CASE WHEN (acs.CLOSETIME = 0 and acs.STATUS='CLOSED') THEN 0 WHEN (acs.CLOSETIME = 0 and acs.STATUS='ACTIVE') THEN CAST(extract(epoch from now()) AS BIGINT)*1000 - acs.OPENTIME ELSE acs.CLOSETIME - acs.OPENTIME END)/3600000,0) AS varchar) || ' Hours ' || CAST(trunc(sum(CASE WHEN (acs.CLOSETIME = 0 and acs.STATUS='CLOSED') THEN 0 WHEN (acs.CLOSETIME = 0 and acs.STATUS='ACTIVE') THEN CAST(extract(epoch from now()) AS BIGINT)*1000 - acs.OPENTIME ELSE acs.CLOSETIME - acs.OPENTIME END)%3600000/60000, 0) AS varchar) || ' Minutes' END as "lh", (max(acs.OPENTIME)) as "llt", al.NAME as "ln", al.DOMAINNAME as "domain", aci.EMAILID as "email", dd.DEPTNAME as "dept", (au.CREATEDTIME) as "act", repuser.FIRST_NAME as "rm", sd.ISVIPUSER as "vu" 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 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 (acs.OPENTIME between <from_lastmonth> and <to_thismonth> )  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 order by au.FIRST_NAME) as loginhours order by tn

If you want to change the date limit, please use the template shown below

  1. Here is the example for getting this week data - acs.OPENTIME >= <from_thisweek> AND acs.OPENTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>
Compatibility :
      Database : PGSQL (Tested at Build No : 13008)

                New to ADManager Plus?

                  New to ADSelfService Plus?

                    • Related Articles

                    • Login frequency

                      This report is used to find the Technician and Requester Login frequency. SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP Address", ...
                    • Query to Pull out Login and LogOut History and to Find User Status

                      Purpose        To generate the report which contains  Login and LogOut History of the users and to find out the user status currently. Query 1  Below query provides the users login and logout history of the users. SELECT au.first_name as ...
                    • Request created in out of business hours

                      This report is used to find the request created out of business hours. Based on this report, resources can be allocated to manage the load. To make any changes to a query, refer to the KB article below. ...
                    • SAML Auto Login with ADFS (in Intranet)

                      Steps to enable Auto-logon: Step 1: In the AD FS server, under Authentication Methods, make sure that Windows Authentication is selected. Step 2: Run the below powershell query to check if "Chrome" is present in the supported WIA agents: ...
                    • Login diectly with SAML / Query to enable AD or Local Auth when there is an issue with SAML

                      Issue: When users have AD and/or local authentication enabled along with SAML, the login page is shown when a link from an email is clicked and users need to click "Login with SAML" again. Workaround 1: You can bookmark, <sdp_url>/SamlRequestServlet ...