Details from the Low-Use User Report

Please sign in to leave a comment.

• 

  Kim Jue

  Jason,

  One thing you can do is query the database for when the users last logged in with a query like:
  select p.FIRST_NAME
  ,p.LAST_NAME
  ,p.LAST_LOGIN
  from PERSONS p
  order by p.LAST_LOGIN

  December 17, 2015 17:37

  0
• 

  Kim Jue

  Jason,

  Here is another query from our DBA, but he warns that this can be hard on the database, so run it during a slow time notification wise.

  WITH cte AS (select 1 as org_id)
  select r.target_name as userid,
  p.person_id,
  p.first_name||' '||p.last_name as username,
  max(p.last_login) as last_login,
  max(ns.delivered_when_created) as last_ntfn_received,
  max(e.creation_date) as last_ntfn_sent_if_exists
  from persons p
  inner join dvc d on d.person_id = p.person_id
  inner join recipients r on d.dvc_id = r.recipient_id and r.deleted_id = -1
  left join ( select sender,creation_date from evs where ((select org_id from cte) is null or company_id = (select org_id from cte))
  union all
  select sender,creation_date from evs_arc where ((select org_id from cte) is null or company_id = (select org_id from cte))) as e on e.sender = r.target_name
  left join (select recipient_id,ntfn_id from recipient_ntfn union all select recipient_id,ntfn_id from recipient_ntfn_arc) rn on r.recipient_id = rn.recipient_id
  left join (select ntfn_id from dvc_ntfn union all select ntfn_id from dvc_ntfn_arc) dn on dn.ntfn_id = rn.ntfn_id
  left join (select dvc_ntfn_id, ntfn_id from live_ntfn union all select dvc_ntfn_id, ntfn_id from live_ntfn_arc) ln on ln.dvc_ntfn_id = dn.ntfn_id
  left join (select ntfn_id from ntfn union all select ntfn_id from ntfn_arc) n on n.ntfn_id = ln.ntfn_id
  left join notifications_summary ns on n.ntfn_id = ns.ntfn_id
  where ((select org_id from cte) is null or r.org_id = (select org_id from cte))
  and p.deleted_id = -1
  and r.deleted_id = -1
  group by r.target_name, p.person_id, username
  order by last_login desc nulls last

  December 17, 2015 19:03

  0
• 

  Kim Jue

  Sample data from last query

  userid PERSON_ID username last_login last_ntfn_received last_ntfn_sent_if_exists
  companyadmin 5 Administrator 12/17/15 5:33 PM 12/15/15 12:26 AM 12/16/15 5:21 PM
  jdoe 200284 Doe 11/23/15 2:34 PM NULL NULL
  bsmith 200076 Smith 11/18/15 5:59 PM 12/16/15 5:21 PM NULL
  swhite 200009 White 11/16/15 3:15 PM NULL NULL
  kjue 200005 Jue 3/12/15 7:05 PM NULL NULL
  cinderella 200149 ash 8/20/14 8:41 PM NULL NULL
  ariel 200018 Princess 7/2/14 12:25 AM NULL NULL
  sleepingb 200062 Beauty 5/15/14 12:09 AM NULL NULL

  December 17, 2015 23:12

  0