sql query for top user of a computer

this pulls data from the v_gs_system_console_user table to determine who the top user is.  there’s a drawback to the asset intelligence method of gathering this information that’s presented in the v_gs_system_console_usage table in that if you have applications running as service accounts performing logins, they may show up as the top user.

declare @machine nvarchar(50)
set @machine = 'myComputerName'

select   top 1
         sys.name0 as 'name',
         usr.systemconsoleuser0 as 'user',
         usr.numberofconsolelogons0 as '# logons',
         usr.totaluserconsoleminutes0 as '# mins'
from     v_r_system sys
         inner join v_gs_system_console_user usr
         on sys.resourceid = usr.resourceid
where    sys.name0 like @machine
order by usr.totaluserconsoleminutes0 desc

 

and something for fun, i gathered from this post.  if you want the second most active or top user, then you’d run the query like this:

declare @machine nvarchar(50)
set @machine = 'myComputerName'

select   top 1 *
from     (
         select   top 2
                  sys.name0 as 'name',
                  usr.systemconsoleuser0 as 'user',
                  usr.numberofconsolelogons0 as '# logons',
                  usr.totaluserconsoleminutes0 as '# mins'
         from     v_r_system sys
                  inner join v_gs_system_console_user usr
                  on sys.resourceid = usr.resourceid
         where    sys.name0 like @machine
         order by usr.totaluserconsoleminutes0 desc
         ) as temp
order by [# mins] asc

 

depending on whether you want to track this by the numberofconsolelogons0 field or the totaluserconsoleminutes0 field, you would simply change the order by [field] asc/desc expression.