Asset Information with username in SCCM

Asset Information with username in SCCM

Create a new report with following SQL query : 

select  distinct
    R.ResourceID,
    R.User_Name0,
    R.Netbios_Name0 AS 'Computer_Name',
    R.Resource_Domain_OR_Workgr0 AS 'Domain_workgroup',
    S.SiteName as 'SMSSiteName',
    OS.Caption0 AS 'OperatingSystem',
    OS.CSDVersion0 AS 'ServicePackLevel',
    SEQ.SerialNumber0 AS 'SerialNumber',
    SEQ.SMBIOSAssetTag0 AS 'AssetTag',
    CS.Manufacturer0 AS 'Manufacturer',
    CS.Model0 AS 'Model',
    WS.LastHWScan AS 'Last_Scan_Date',
    RAM.TotalPhysicalMemory0 AS 'Memory_KBytes',
    Pro.NormSpeed0 AS 'Processor_GHz',
    (Select sum(LD.Size0) from v_GS_LOGICAL_DISK LD where LD.ResourceID = R.ResourceID) As 'DiskSpace_MB',
    (Select sum(LD.FreeSpace0) from v_GS_LOGICAL_DISK LD where LD.ResourceID = R.ResourceID) As 'Free_Disk_Space_MB',
    U.Full_User_Name0
  from
    dbo.v_R_System_Valid R
    inner join dbo.v_GS_OPERATING_SYSTEM OS on (OS.ResourceID = R.ResourceID)
    inner join dbo.v_GS_WORKSTATION_STATUS WS on (WS.ResourceID = OS.ResourceID)
    inner join dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE SEQ on (SEQ.ResourceID = R.ResourceID)
    inner join dbo.v_GS_COMPUTER_SYSTEM CS on (CS.ResourceID = R.ResourceID)
    inner join dbo.v_GS_X86_PC_MEMORY RAM on (RAM.ResourceID = R.ResourceID)
    inner join dbo.v_GS_PROCESSOR Pro on (Pro.ResourceID = R.ResourceID)
    inner join dbo.v_FullCollectionMembership FCM on (FCM.ResourceID = R.ResourceID)
    inner join dbo.v_Site S on (FCM.SiteCode = S.SiteCode)
    left outer join dbo.v_R_User U on U.Unique_User_Name0 = R.User_Domain0+'\'+R.User_Name0
--    inner join v_GS_LOGICAL_DISK LD on (v_GS_LOGICAL_DISK.ResourceID = R.ResourceID) and LD.DeviceID0=SUBSTRING(OS.WindowsDirectory0,1,2)
--    left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on (SCUM.ResourceID = R.ResourceID)
 Where
    FCM.CollectionID = @CollectionID
Order by
    R.Netbios_Name0

Create Prompt value for Collection : 

Prompt SQL Statement : 

begin
if (@__filterwildcard = '')
select CollectionID, Name from v_Collection order by Name
else
select CollectionID, Name from v_Collection
WHERE CollectionID like @__filterwildcard
order by Name
end