This SCCM report is really a complicated one, I used lots of views to do the information I need, such as : AD Site, Machine Name, Last Logon User, Last Update Scan, Last Logon Timestamp, Update Status, Operating System, Email, Department, Job Title, and Manager Name.
and here is the table view map:
here is the SQL statement:
SELECT dbo.v_R_System.AD_Site_Name0 AS [AD Site], dbo.v_R_System.Name0 AS [Computer Name], dbo.v_R_System.User_Name0 AS [Last Logon User], CONVERT(VARCHAR(26),
dbo.v_UpdateScanStatus.LastScanTime, 100) AS [Last Update Scan], CONVERT(VARCHAR(26), dbo.v_R_System.Last_Logon_Timestamp0, 100) AS [Last Logon Timestamp],
CASE WHEN dbo.v_Update_ComplianceStatusAll.Status = '0' THEN 'Unkonwn' WHEN dbo.v_Update_ComplianceStatusAll.Status = '1' THEN 'Not Required' WHEN dbo.v_Update_ComplianceStatusAll.Status = '2' THEN
'Needs Update' WHEN dbo.v_Update_ComplianceStatusAll.Status = '3' THEN 'Update Installed' END AS 'Update Status', dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], dbo.v_R_User.Mail0,
dbo.v_R_User.department0, dbo.v_R_User.title0, dbo.v_R_User.manager0
FROM dbo.v_R_System INNER JOIN
dbo.v_Update_ComplianceStatusAll ON dbo.v_R_System.ResourceID = dbo.v_Update_ComplianceStatusAll.ResourceID INNER JOIN
dbo.v_FullCollectionMembership ON dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.ResourceID INNER JOIN
dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_Update_ComplianceStatusAll.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
dbo.v_UpdateScanStatus ON dbo.v_Update_ComplianceStatusAll.ResourceID = dbo.v_UpdateScanStatus.ResourceID INNER JOIN
dbo.v_AuthListInfo ON dbo.v_Update_ComplianceStatusAll.CI_ID = dbo.v_AuthListInfo.CI_ID INNER JOIN
dbo.v_R_User ON dbo.v_R_System.User_Name0 = dbo.v_R_User.User_Name0For SSRB, I add these two as the parameters:
where dbo.v_AuthListInfo.title=@update_title and dbo.v_Collection.name=@collection_name