Monday, November 29, 2010

SMS Report to find last logon user details of computers

Select distinct
v_R_System.Netbios_Name0 AS "Computer Name",
v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS "Console User",
v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS "Console Logons",
v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS "Total Minutes on Console",
v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS "Last Console Use"
from v_R_System INNER JOIN
(SELECT
ResourceID,
SystemConsoleUser0,
NumberOfConsoleLogons0,
TotalUserConsoleMinutes0,
LastConsoleUse0,
ROW_NUMBER() OVER(PARTITION BY ResourceID ORDER BY LastConsoleUse0 DESC) AS Point
FROM
v_GS_SYSTEM_CONSOLE_USER) v_GS_SYSTEM_CONSOLE_USER
ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
AND v_GS_SYSTEM_CONSOLE_USER.Point = 1
Order by v_R_System.Netbios_Name0

Description: This will get you the top console user info who logged in to the machines most recently.

Another way to find this is, If you have hardware inventory configured to run on a shorter period, something like it is configured to run daily, get the last logon user info from v_GS_COMPUTER_SYSTEM (This view gets updated with user info every time hardware inventory runs).
If you have heartbeat discovery running daily, get the last logon user info from v_r_system (This view gets updated with user info every time hearbeat discovery runs).

1 Comments:

Blogger Joshua said...

is it possible to specify an collection and datet range? I am looking to get soley "NumberOfConsoleLogons0" count for all of the computers in a collection within a specific time.

February 22, 2011 at 10:54 AM  

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home