How to query the most frequent or last logon user per computer?

Products

• CA Client Automation

Issue/Introduction

How do you query either the most frequently logged on user per computer or the last logged on user per computer?

Environment

CA Client Automation (ITCM) -- All supported versions.

Resolution

Using SQL:
Use either of the following queries in SQL, to determine either the most frequent user per computer, or the last logon user per computer.

-- Most frequent user (All computers)
select dh.host_name as 'Computer', 
du.user_name as 'Most Frequent User', 
link.ref_count as 'Login Count'
from ca_discovered_hardware dh
inner join ca_link_dis_hw_user link on dh.dis_hw_uuid=link.dis_hw_uuid
inner join ca_discovered_user du on du.user_uuid=link.user_uuid
inner join (
  select distinct dis_hw_uuid, 
  max(ref_count) as max_count
  from ca_link_dis_hw_user
  group by dis_hw_uuid) as max_users
  on link.dis_hw_uuid=max_users.dis_hw_uuid
  and link.ref_count=max_users.max_count
order by dh.host_name

-- Last logon user (All computers)
select dh.host_name as 'Computer', 
du.user_name as 'Last Logon User', 
link.ref_count as 'Login Count'
from ca_discovered_hardware dh
inner join ca_link_dis_hw_user link on dh.dis_hw_uuid=link.dis_hw_uuid
inner join ca_discovered_user du on du.user_uuid=link.user_uuid
inner join (
  select distinct dis_hw_uuid, 
  max(last_update_date) as max_update
  from ca_link_dis_hw_user
  group by dis_hw_uuid) as last_users
  on link.dis_hw_uuid=last_users.dis_hw_uuid
  and link.last_update_date=last_users.max_update
order by dh.host_name

Using DSM Reporter:
Add the following fields to a computer-based report:

The report results will display a row for each user that has logged on, for each computer.
The logon counts and last logon timestamp will be displayed in order to facilitate most frequent or last logged on user.

Note: Below report should display the Names (agent names) and Related (User) Names, the said columns are masked in the below screen shot.