Modified version of “Hardware 03B – Computers for a specific primary console user”

istinct

R.Netbios_Name0 AS ‘Computer Name’,

SCU.SystemConsoleUser0 as ‘User’,

S.SiteName as ‘SMS Site Name’,

SEU.SerialNumber0 AS ‘Serial Number’,

SEU.SMBIOSAssetTag0 AS ‘Asset Tag’,

CS.Manufacturer0 AS ‘Manufacturer’,

CS.Model0 AS ‘Model’,

SCU.NumberOfConsoleLogons0 AS ‘Console Logons’, CAST(CAST(SCU.TotalUserConsoleMinutes0 AS Decimal(20, 4)) / CAST(SCUM.TotalConsoleTime0 AS Decimal(20, 4)) * 100 AS Decimal(20, 0)) AS ‘% of Total Console Use’,

SCU.TotalUserConsoleMinutes0 AS ‘Total Minutes on Console’,

SCU.LastConsoleUse0 AS ‘Last Console Use’ from

v_GS_SYSTEM_CONSOLE_USER SCU

INNER JOIN v_R_System_Valid R on R.ResourceID = SCU.ResourceID

LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on SCUM.ResourceID = SCU.ResourceID

LEFT JOIN v_GS_SYSTEM_ENCLOSURE_UNIQUE SEU on SEU.ResourceID = R.ResourceID

INNER JOIN v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = R.ResourceID

INNER JOIN v_RA_System_SMSAssignedSites SAS on SAS.ResourceID = R.ResourceID

left join v_Site S on S.SiteCode=SAS.SMS_Assigned_Sites0

Where

SCUM.TotalConsoleTime0 != 0

and (cast(SCU.TotalUserConsoleMinutes0 as Decimal (20,4)))/(cast(SCUM.TotalConsoleTime0 as Decimal(20,4))) >= .66

and SCU.SystemConsoleUser0 in (

‘gartek\garth’, ‘gartek\Ellen’, ‘gartek\ferd’)

Order by

R.Netbios_Name0