I was trolling the forums lately on MyITForm.com and was reading some conversations about logged on user records. It made me think of all the sources where you could get that information. Off the top of my head there's
V_GS_COMPUTER_SYSTEM.username0
V_HS_COMPUTER_SYSTEM.username0
V_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 (SMS 2003 SP3 Asset Intelligence stuff)
V_HS_SYSTEM_CONSOLE_USER.SystemConsoleUser0
V_R_SYSTEM.user_domain0\user_name0
If you unioned all of the records from those sources together, you'd have as good of a list as you could expect.
So I created a SQL query that will pull all user records for a given resourceID from those views (which include historical data) that aren't NULL, cuz honestly, what does a null record tell you? NULL.
Some notes about this:
- This does not enumerate logon/logoff times, it's a list of the logged on users at specific times. The SYSTEM_CONSOLE_USER records within the results represent the actual logon times because those come from the logon event parsing stuff with AI, but the other records indicate who was logged in at the time of discovery/inventory. So don't run this expecting to see the exact times people logged on/off.
- I've got two versions, one for SQL 2005 that uses common table expressions (CTE) and a SQL 2005 version that doesn't. I don't have SQL 2000 installed anywhere so I haven't actually tested that one on SQL 2000, but I've done this long enough to know it probably works just fine.
SQL 2005 Version (uses CTE)
(make sure you define a resourceID)
WITH CS (resourceID, timestamp, username0) AS
(
SELECT
resourceID,
TIMESTAMP,
username0
FROM
v_GS_COMPUTER_SYSTEM
WHERE
name0 IS NOT NULL
UNION ALL
SELECT
resourceID,
TIMESTAMP,
username0
FROM
v_HS_COMPUTER_SYSTEM
WHERE
name0 IS NOT NULL
UNION ALL
SELECT
resourceID,
LastConsoleUse0,
SystemConsoleUser0
FROM
dbo.v_GS_SYSTEM_CONSOLE_USER
UNION ALL
SELECT
resourceID,
LastConsoleUse0,
SystemConsoleUser0
FROM
dbo.v_HS_SYSTEM_CONSOLE_USER
UNION ALL
SELECT
wss.resourceID,
wss.lasthwscan,
sys.user_domain0 + '\' + sys.user_name0
FROM
v_GS_Workstation_Status wss
INNER JOIN v_R_System sys
ON sys.resourceID = wss.resourceID
AND sys.obsolete0 = 0
AND sys.decommissioned0 = 0
AND sys.client0 = 1
AND sys.netbios_name0 IS NOT NULL
)
SELECT DISTINCT
main.resourceID,
main.timestamp,
UPPER(main.username0) AS [User Name]
FROM
CS main
INNER JOIN v_R_System sys
ON sys.resourceID = main.resourceID
AND sys.obsolete0 = 0
AND sys.decommissioned0 = 0
AND sys.client0 = 1
AND sys.netbios_name0 IS NOT NULL
WHERE
main.username0 IS NOT NULL
and main.resourceID = @ResourceID
ORDER BY
main.resourceID,
main.timestamp DESC,
UPPER(main.Username0)
SQL 2000 Version (untested)
SELECT DISTINCT
main.resourceID,
main.timestamp,
UPPER(main.username0) AS [User Name]
FROM
(
SELECT
resourceID,
TIMESTAMP,
username0
FROM
v_GS_COMPUTER_SYSTEM
WHERE
name0 IS NOT NULL
UNION ALL
SELECT
resourceID,
TIMESTAMP,
username0
FROM
v_HS_COMPUTER_SYSTEM
WHERE
name0 IS NOT NULL
UNION ALL
SELECT
resourceID,
LastConsoleUse0,
SystemConsoleUser0
FROM
dbo.v_GS_SYSTEM_CONSOLE_USER
UNION ALL
SELECT
resourceID,
LastConsoleUse0,
SystemConsoleUser0
FROM
dbo.v_HS_SYSTEM_CONSOLE_USER
UNION ALL
SELECT
wss.resourceID,
wss.lasthwscan,
sys.user_domain0 + '\' + sys.user_name0
FROM
v_GS_Workstation_Status wss
INNER JOIN v_R_System sys
ON sys.resourceID = wss.resourceID
AND sys.obsolete0 = 0
AND sys.decommissioned0 = 0
AND sys.client0 = 1
AND sys.netbios_name0 IS NOT NULL
) AS main
INNER JOIN v_R_System sys
ON sys.resourceID = main.resourceID
AND sys.obsolete0 = 0
AND sys.decommissioned0 = 0
AND sys.client0 = 1
AND sys.netbios_name0 IS NOT NULL
WHERE
main.username0 IS NOT NULL
and main.resourceID = @ResourceID
ORDER BY
main.resourceID,
main.timestamp DESC,
UPPER(main.Username0)
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
