Question from the myITforum list:
Need some help from all you experts out there, I have created an SMS report for all machines in a particular collection, but some of the machines are logged onto by local accounts, these are not being picked up on the report as there is no entry in the v_R_User, I have looked at the SQL that creates this view, it only detects accounts that are domain accounts. How can I get the ones that use local accounts to show up, do I need a left, inner, outer, right join somewhere?
SELECT SYS.Netbios_Name0 AS [Computer Name],
SYS.User_Name0 AS [KID],
RUS.Full_User_Name0 AS [Full Name],
SYS.AD_Site_Name0 AS [AD Site],
SYS.User_Domain0 AS [Domain],
CS.Manufacturer0 AS [Manufacturer],
CS.Model0 AS [Model],
FCM.SiteCode AS [SMS Site Code]
FROM v_R_System SYS
JOIN v_FullCollectionMembership FCM ON SYS.ResourceID=FCM.ResourceID
JOIN v_R_User RUS ON RUS.User_Name0 = SYS.User_Name0
JOIN v_GS_Computer_System CS on SYS.ResourceID = CS.ResourceID
WHERE FCM.CollectionID = 'P000128C'
ORDER BY SYS.Netbios_Name0, SYS.User_Name0
~~~
[Steve] The reason that there are no results shown when matching records are missing from the joined table is due to the specified JOIN statement. A JOIN statement by default, is an equi-join. An equi-join will only return records when a match is found on the specified condition. To return all records regardless of a matching condition from the LEFT side of the JOIN, change the JOIN type to LEFT OUTER JOIN (below).
SELECT SYS.Netbios_Name0 AS [Computer Name],
SYS.User_Name0 AS [KID],
RUS.Full_User_Name0 AS [Full Name],
SYS.AD_Site_Name0 AS [AD Site],
SYS.User_Domain0 AS [Domain],
CS.Manufacturer0 AS [Manufacturer],
CS.Model0 AS [Model],
FCM.SiteCode AS [SMS Site Code]
FROM v_R_System SYS
JOIN v_FullCollectionMembership FCM ON SYS.ResourceID=FCM.ResourceID
LEFT OUTER JOIN v_R_User RUS ON RUS.User_Name0 = SYS.User_Name0
JOIN v_GS_Computer_System CS on SYS.ResourceID = CS.ResourceID
WHERE FCM.CollectionID = 'P000128C'
ORDER BY SYS.Netbios_Name0, SYS.User_Name0