Wednesday, August 28, 2013

SCCM Query to get Logon Information

There was an entry in the MSFT System Center forum was request for some form of assistance to retrieve information for the last console logon for machines which I posted .
Heaving posted that I decided to add in something extra to include the number of console logons to the machine as well as the Total Duration. Hopefully this helps anyone who needs this.

select VRS.Name0 as 'Computer Name'
, Resource_Domain_OR_Workgr0
, User_Name0 'Last Logon Name'
, VOS.Caption0
,VSC.NumberOfConsoleLogons0 'Total Console Logons Counts'
,VSC.TotalUserConsoleMinutes0 'Total Console Logon Minutes'

from v_R_System VRS 
left join dbo.v_GS_OPERATING_SYSTEM VOS on VRS.ResourceID = VOS.ResourceID
left join dbo.v_GS_SYSTEM_CONSOLE_USER VSC on VSC.ResourceID = VRS.ResourceID