sccm: top console users report

let me preface this post by saying, this is in a 2007 environment. in a 2012 environment, you have user/device affinity. :-) right?

you’re thinking to yourself… self, this already exists natively in configmgr, why am i reading this blog post? let me try to answer that. in some environments, it is not uncommon for domain accounts to run as services on a workstation (e.g. blackberry, mcafee, etc). when this happens, the top console user for a system ends up being the service account.

i figured the best thing to do would be to write my own and eventually elevate it to a view of some type that i could use for reporting. i started off by taking apart the v_gs_system_console_usage_maxgroup view and creating my own query. what I ended up with is a bit frankenstein with some of the field names that do not make sense. this is somewhat because the original query used a different method of calculation… and mostly because i am freakin’ lazy.

here’s the final outcome:

SELECT
    SYS.Name0,
    SYS.ResourceID,
    SYS_CUG.GroupID,
    SYS_CUG.SystemConsoleUser0,
    SYS_CUG.LastConsoleUse0,
    SYS_CUG.TotalUserConsoleMinutes0,
    SYS_CUG.TimeStamp
FROM
    dbo.v_GS_SYSTEM_CONSOLE_USER AS SYS_CUG INNER JOIN
    (
        SELECT ResourceID, MAX(TotalUserConsoleMinutes0) AS GroupID_MAX
        FROM dbo.v_GS_SYSTEM_CONSOLE_USER
        WHERE
            SystemConsoleUser0 LIKE '%myfilter%'
            AND TotalUserConsoleMinutes0 > 0
        GROUP BY ResourceID
    ) AS CSL_UNIQ ON CSL_UNIQ.ResourceID = SYS_CUG.ResourceID AND CSL_UNIQ.GroupID_MAX = SYS_CUG.TotalUserConsoleMinutes0
    inner join v_R_System sys on sys.ResourceID = sys_cug.ResourceID

 

the part highlighted above is where you would apply your filtering of any user names. this can be a positive filter to pull in any names that match some wildcard or negatively by excluding any that you do not want to see. it seems to work, but if you find different, bad, or better results, please comment, and let me know!